PGXN

Contents

• • Delete a key from the row_log
  • Update a key in the row_log

Sometimes deleting some parts of the data history is intended, e.g. if there are semantic errors in the data that you don't like to see being reproduced when restored. In order to apply corrections against the complete audit trail there are two options: Deleting or updating a key hence a column.

Delete a key from the row_log

Call pgmemento.delete_key and pass the audit_id, column name and old value (any type). This removes the given key-value pair from the JSONB logs. The subsequent value will then also cover the life time of the old value. Keep in mind that this action can also produce emtpy logs in the row_log table.

sql SELECT pgmemento.delete_key(555, 'column_B', 'old_value'::text);

The audit trail in the new_data column of the row_log table is also covered by the delete_key function. If you delete a key-value pair from a DELETE event log, it will also be removed from the new_data log where the value has been introduced. If you delete a key from an UPDATE event log, it will also be removed from the new_data log of the same event as if the column wasn't updated during that transaction. The new value for the given key then replaces the old one (you have just removed from the old_data log) in the new_data log, where it has been introduced.

Before: | old_data | new_data | | ------------------------- | -------------------------- | | ... | ... | | {"column_B": "ancient"} | {"column_B": "old_value"} | | ... | ... | | {"column_B": "ancient"} | {"column_B": "new_value"} |

After: | old_data | new_data | | ------------------------- | -------------------------- | | ... | ... | | {"column_B": "ancient"} | {"column_B": "new_value"} | | ... | ... | | {} | {} |

As you can see in this example, deleting a key from the row_log with pgmemento.delete_key can produce an empty JSONB. This can have a negative side effect when reverting some events that expect the existence of that specific key, e.g. DELETE, TRUNCATE, ALTER COLUMN, DROP COLUMN. In this case the restored value will be NULL. Reverting an ALTER COLUMN event would then transform existing data into NULL.

Update a key in the row_log

Call pgmemento.update_key, pass the audit_id, the path to the key in the form of an array, the old and new value which can be of any type.

sql SELECT pgmemento.update_key(555, '{column_B}', 'old_value'::text, 'corrected value'::text);

Both, the old_data and new_data columns will be updated, no matter if logging for these columns is active or not.