Data Modification Tracking

ProvSQL can track the provenance of data-modification operations – INSERT, UPDATE, and DELETE – when run on provenance-enabled tables [Bourhis et al., 2020].

Note

Data modification tracking requires PostgreSQL ≥ 14.

Enabling Update Provenance

Update-provenance tracking is disabled by default. Enable it for a session:

SET provsql.update_provenance = on;

Or permanently in postgresql.conf:

provsql.update_provenance = on

INSERT

Inserting a row into a provenance-enabled table always creates a new input gate for that row. When update_provenance is enabled, the statement additionally creates an update gate, logged in the update_provenance table, that is multiplied into each inserted row’s provenance – so the insertion as a whole can later be undone:

INSERT INTO employees(name, dept)
VALUES ('Alice', 'R&D');

-- The new row already has a provenance token
SELECT name, provenance() FROM employees WHERE name = 'Alice';

DELETE

Deleting a row does not remove it from the table: its provenance is changed to mark the deletion, allowing hypothetical reasoning. The undo mechanism (see below) relies on this.

DELETE FROM employees WHERE name = 'Alice';

UPDATE

An UPDATE is modelled as a DELETE followed by an INSERT. The new row gets a fresh provenance token; the old token continues to exist in the circuit.

UPDATE employees SET dept = 'Sales' WHERE name = 'Bob';

Undoing Updates

ProvSQL provides an undo function that rolls back the provenance effects of a specific logged modification. Every provenance-enabled DML statement is recorded in the update_provenance table; pass its provsql token to undo to reverse its effect:

CREATE TABLE t(id INT PRIMARY KEY);
SELECT add_provenance('t');

INSERT INTO t VALUES (1), (2), (3);
DELETE FROM t WHERE id = 3;

-- Row 3's provenance is now zeroed; undo the DELETE to restore it
SELECT undo(provsql)
FROM update_provenance
WHERE query = 'DELETE FROM t WHERE id = 3;';

Transactions

The statements of one transaction share a transaction gate (transaction_token), logged in update_provenance with query_type = 'TRANSACTION' (and no query text); the tx_token column of each statement’s row names it. undo reverses a single statement or a whole transaction:

BEGIN;
DELETE FROM t WHERE id = 1;
UPDATE t SET v = 'B' WHERE id = 2;
COMMIT;

-- Reverse the whole transaction, both statements at once
SELECT undo(provsql)
FROM update_provenance
WHERE query_type = 'TRANSACTION'
ORDER BY ts DESC LIMIT 1;

ts and the start of valid_time are the commit time. A transaction that rolls back leaves no trace, log rows included, whereas undo keeps the modification in the history and records its reversal.

Limitations

Update tracking is still experimental, both in the operations it supports and in performance.

Under READ COMMITTED, a DELETE blocked on a row that a concurrent transaction deletes affects zero rows once that transaction commits, but is still logged. Under REPEATABLE READ it gets a serialization failure instead.