Audit_column_log

The pgmemento.audit_column_log table stores information about audited columns, which is important when restoring previous versions of tuples and tables. It contains the following columns:

• id SERIAL: Primary Key
• audit_table_id INTEGER NOT NULL: Foreign Key to pgmemento.audit_table_log
• column_name TEXT NOT NULL: The name of the column
• ordinal_position INTEGER: The ordinal position within the table
• data_type TEXT: The column's data type (incl typemods)
• column_default TEXT: The column's default expression
• not_null BOOLEAN: A flag to tell, if the column is a NOT NULL column or not
• txid_range numrange: Stores the transaction IDs when the column has been created and dropped

The txid_range column behaves in the same way like in the audit_table_log table. Lower boundary exclusive, upper boundary inclusive. When a column is ranamed or altered the range for the old version is closed and a new row is inserted. If the table is renamed there will also be new entries for all its columns in the audit_column_log because they need to reference to a new ID in audit_table_log. To trace different versions of the same column check the ordinal_position value.

Get historic column lists

When restoring historic records it's important to know the column name and data type for the requested time / transaction. You can use range operators like @> or && to filter for historic columns. pgMemento provides two functions that return a list of column names, data types and ordinal positions.

SELECT
  column_name,
  data_type,
  ordinal_position
FROM
  pgmemento.get_column_list_by_txid(10, 'table_A', 'public');

When querying by a range of transaction IDs you have to use the table's log_id as input, as the name could have changed. You can retrieve if from the audit_table_log or by using the audit_table_check function. If some columns have changed by name or data type all the different versions will appear in the result set. To avoid name abiguity a counter is included into the result. The txid_range column is returned as well to allow for further filtering.

SELECT
  column_name,
  column_count,
  data_type,
  ordinal_position,
  txid_range
FROM
  pgmemento.get_column_list_by_txid_range(1, 10, 1);