To upgrade from v0.6 to v0.7, first, please make sure that you have upgraded to v0.6.1 already. To check that you can run the following query:

sql SELECT table_operation FROM pgmemento.table_event_log WHERE op_id = 21;

If the result is not ADD AUDIT_ID, you know you're on v0.6. If there is no such event in the table_event_log you can safely run the update to v0.7. Simply run the UPGRADE_v061_to_v07.sql script with psql from inside a shell environment. It will replace all functions and alter log tables. The script should finish with the message "pgMemento upgrade completed!".

bash psql -h localhost -p 5432 -U my_user -d my_database -f UPGRADE_061_to_V07.sql

Note, log triggers will be switched off for a short amount of time during the upgrade process. This is when some of the log logs tables are dropped and their copies are renamed to take their place. This might only take some seconds. If you care for keeping all changes in the logs during the upgrade, you should disable writes for the time of the upgrade. pgMemento's event triggers are disabled for the entire upgrade process, so avoid schema migrations.

Breaking changes

JSONB field in row_log renamed

As v0.7 provides the feature to also log new values in a separate JSONB column - new_data - the changes column has been renamed to old_data.

Foreign key between table_event_log and row_log

The former foreign key column event_id in row_log referencing the table_event_log is gone. Both tables now store an event_key consisting of transaction and statement timestamps (in epoch format), internal txid, operation id, table and schema name. It can be used for joins. However, no foreign key constraint is enforced. The event_key allows for easier partitioning of these tables.

Updated timestamp columns

The stmt_date column in transaction_log is renamed to txid_time to easier distinguish between the new timestamp column - stmt_time - in table_event_log which stores the statement_timestamp().

Table OID columns in logs tables

v0.7 stops relying on OID columns in the log tables. Instead the table and schema names are used, e.g. as new columns in table_event_log. The audit_table_log introduces a new column log_id to trace tables when they get renamed. Due to these changes, there are breaking changes in some function signatures that also required an OID argument.

  • audit_table_check now returns the log_id from audit_table_log as first column instead of table OID
  • delete_audit_table_log works with the new log_id
  • delete_table_event_log now requires table and schema name instead of one OID
  • get_column_list_by_txid_range replaces table_oid argument with table_log_id
  • get_txid_bounds_to_table replaces table_oid argument with table_log_id
  • log_table_event second table_oid argument is replaced by arguments tablename and schemaname
  • restore_record_definition replaces table_oid argument with table_log_id

DROP AUDIT_ID event behavior changed

The drop_table_audit function was updated to now separately choose if the audit trail shall be removed or not and if everything should be logged for the last time or not. Before, you could only choose between removal or logging. With this change the DROP AUDIT_ID could no longer be treated like a TRUNCATE event and got it's own op_id value of 81. If a user decides to log the whole content of a table where auditing shall be stopped, pgMemento will now log an extra TRUNCATE event before the DROP AUDIT_ID event.

Triggers renamed

Log triggers and event triggers were renamed to be potentially less invasive. The general log_ prefix is replaced with pgememento_ so that it's immediately visible where the triggers are coming from.

Changed function signatures

Because of the new features of v0.7 like a configurable audit_id column many functions have changed their signatures. Have a look at the list in the UPGRADE script, as all changed functions are dropped explicitly.