You are not only interested in the historic version before the second transaction ID parameter and want to inspect the evolution of one row? Then, use the
pgmemento.restore_records function. This will return all versions within the transaction ID frame. Narrowing the filter window can be really helpful if your audit trail is already quite long.
The returned rows also include the corresponding event_id and transaction_id to facilitate the analysis of your audit trail. Therefore, the column definition list needs to be extended by these two columns. Again, the
pgmemento.restore_record_definition function is of help, but when restoring multiple versions is has to be called with different parameters. Like
pgmemento.restore_records it needs two transaction ID values. Instead of passing the table and schema name you have to pass the table's
log_id from the
audit_table_log table as the table could have been ranamed. To get the
log_id of an already deleted table or renamed table either query the
pgmemento.audit_table_log table or use the helper functions by pgMemento.
```sql SELECT pgmemento.restore_record_definition(1, 10, 1);
AS (id integer, column_B text, column_C character, audit_trail_id bigint, event_id integer, transaction_id integer)
SELECT * FROM pgmemento.restore_records(1, 10, 'table_A', 'public', 555) AS (id integer, column_B text, column_C character, audit_trail_id bigint, event_id integer, transaction_id integer); ```
The queried versions can, of course, be restored to JSONB, too.
Consider schema changes
If the table layout of the rows you want to restore has changed, pgMemento will try to restore columns from all versions. To avoid column ambiguity, a count suffix will be appended to the names. The
pgmemento.restore_record_definition function also follows this behaviour.
Note: In the final result there is no way to see, when a column version did not exist. You can only guess when a value hops from one column to another. The JSONB output would be a perfect fit to tackle this problem, but so far the conversion happens after the restore process. Thus, multiple column versions also appear in the JSONB result. However, there is a workaround to get JSONB objects with distinct keys (see next paragraph).
RENAME COLUMN events represent an edge case which does not produce a row version because nothing is logged in the
pgmemento.row_log table. However, the renamed column does appear in the result set but without any values. This might change in the future. For now, as soon as an event is logged in the
row_log table the current value of the renamed column will appear in the result and the value of the previous version will be NULL.
Multiple version with
Instead of calling
pgmemento.restore_records to get all versions at once, it is also possible to iterate over all events with the
pgmemento.restore.record function with JSONB output. This can be done with a LATERAL construct. To see the evolution of a tuple for all events use a fixed lower boundary for the transaction ID window. To see logs only when a change ocurred use a lead window functions to get pairs of subsequent events (see outcommented field in query):
first_value(e.transaction_id) OVER () AS first_tid,
e.transaction_id AS filter_tid,
--e.transaction_id AS first_tid,
--COALESCE(lead(e.transaction_id) OVER (), e.transaction_id + 1) AS filter_tid,
ON r.event_key = e.event_key
e.table_name = 'table_A'
AND e.schema_name = 'public'
AND (r.audit_id = 10 OR e.op_id IN (1,11,12,2,21,22))
AND e.transaction_id > (
log_id = 1 // public.table_A
pgmemento.restore_record(i.first_tid, i.filter_tid, 'table_A', 'public', 10, TRUE)
AS (log JSONB)
Note: In the result of the tuple evolution the version you see in the JSONB log column, represents the version BEFORE this event occured. So, in case of an INSERT it will be empty. In the change view you'll see the version AFTER the event happend. Thus, for INSERT you see the inserted tuple.
Multiple version for all rows
pgmemento.restore_recordsets to retrieve all versions of all rows that have changed during the given transaction ID frame. Be careful, as this can reproduce the entire history of the given table. Better use it only along with smaller transaction ID windows.