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.