Contents

pgMemento uses two logging stages. The first trigger is fired BEFORE each statement on each audited table. Every transaction is only logged once in the transaction_log table. The PK value is stored in a local transaction variable. Within the trigger procedure the corresponding table operations are logged as well in the table_event_log table.

A table operation is logged once per table per transaction and statement timestamp. This means if e.g. two UPDATEs happen during one transaction they are both logged as long as their execution time differs. If not, then all row logs are referenced to the first event that has been inserted into table_event_log. In the next chapter you will see why this doesn’t produce consistency issues.

The second logging stage is related to the data that has changed. Row-level triggers are fired AFTER each operations on the audited tables. For each row change table event metadata (unix timestamps, table and event type) is stored again to allow referencing to logs from the BEFORE phase.

For example, an UPDATE command on table_A changing the value of some rows of column_B to new_value will appear in the log tables like this:

TRANSACTION_LOG (columns process_id, client_port not displayed)

ID txid txid_time user_name client_name application_name session_info
10 1000000 2020-07-02 18:53:00.100 felix ::1/128 psql {“client_user”:“fxku”}

TABLE_EVENT_LOG

ID transaction_id stmt_time op_id table_operation table_name public_name event_key
10 10 2020-07-02 18:53:00.100 4 UPDATE table_A public 1581097980;1581097980;1000000;4;table_A;public

ROW_LOG

ID audit_id event_key old_data
1 555 1581097980;1581097980;1000000;4;table_A;public {“column_B”:“old_value”}
2 556 1581097980;1581097980;1000000;4;table_A;public {“column_B”:“old_value”}
3 557 1581097980;1581097980;1000000;4;table_A;public {“column_B”:“old_value”}

As you can see only the changes are logged. DELETE and TRUNCATE commands would cause logging of complete rows while INSERTs would leave the old_data field blank. Thus, there is no data redundancy.

But, if you init pgMemento to also log new values you will see the opposite behavior in the new_data column. It will be empty on deletions but the whole row is logged on INSERTs. For UPDATEs only the changed columns are logged, too, but with the new value instead:

old_data new_data
{“column_B”:“old_value”} {“column_B”:“new_value”}