search

arkhipov / temporal_tables

Temporal Tables PostgreSQL Extension
BSD 2-Clause "Simplified" License
935 stars 47 forks source link

Is it possible to exclude some columns from being considered by versioning? #42

Closed exhuma closed 5 years ago

exhuma commented 6 years ago

I have a table containing meta-information about network devices. One such information is a timestamp when the device was last seen on the network. This value is updated regularly, and as such would trigger a new history entry every time the device is polled even if nothing else has changed.

At the moment I am working on implementing a history of changes to these devices and came across temporal tables which does exactly what I want.

Unfortunately the issue explained above will create plenty of useless revisions.

I could move the column from the DB into a separate table and reference it, but that would become quite cumbersome as the value is used as a filter in almost all application query.

It would help a lot if I could simply ignore that column from the versioning process.

mlt commented 5 years ago

I think you can mimic this behavior using WHEN clause. However, you'd need to check WHEN (OLD.col IS DISTINCT FROM NEW.col) for those cols you would want the trigger to fire rather than excluding those you are not interested in.

mlt commented 5 years ago

Works for me. Perhaps it can be closed. One would need 2 separate triggers: for insert & delete (can't use WHEN clause with NEW/OLD) and another one for updates.

On a side note, it would be better to place last seen in a separate table.

exhuma commented 5 years ago

I agree. There are workarounds and this can be closed.