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:
TRANSACTIONLOG (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"} |
TABLEEVENT_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 |
ROWLOG_
| 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"} |