4

I want to track changes on some tables in Postgres. Is there any native means in PG for doing this? Probably not, because this requirement may be very specific depending on use-case.

I would like to observe only some columns in table for changes, and make a copy of the row, if such a filed changes. The row should be copied in another, similar table, which additionally has a column for current user, change time-stamp, id of the original row and of cause own primary key column.

Are there any good patterns for doing this? Which native Postgres tools could I use, and what should I implement myself?

Valentin H
  • 7,240
  • 12
  • 61
  • 111
  • 1
    Easiest way to implement is via triggers. You can set the trigger to run per row on update and then check if `NEW.column<>OLD.column` and write the row to log table based on that. – Sami Kuhmonen Apr 20 '15 at 09:23
  • Is that the most common way for this kind of problem? I'm new to such "advanced" DB-issues. Someone in the company urged me not to use triggers. – Valentin H Apr 20 '15 at 09:29
  • 1
    Triggers are meant for operations that validate or otherwise handle data on (or instead of) insert, update, delete etc operations. So they are the most common way of handling this kind of operation. And since they are run by the database server inside the transaction block where the insert/update/delete happens, it is reliable and deterministic. But do note that if your trigger function has a bug, it will cause errors on the database actions. In this case the function is very simple and easily tested, so shouldn't be a problem. – Sami Kuhmonen Apr 20 '15 at 09:31
  • 3
    https://wiki.postgresql.org/wiki/Audit_trigger_91plus –  Apr 20 '15 at 09:48
  • @a_horse_with_no_name Interesting, thank you. will definitely check this. At the first glance, it is not 100% what I want. I would like to use the change history in my application and the user i want to log is an application user, not postgres-user. But anyway, having such an audit logging for free is great. – Valentin H Apr 20 '15 at 10:52

0 Answers0