Retention internals¶

The column¶

When retention is set, the utility hook appends a ColumnDef for _$purge_ts (timestamptz) to the CREATE TABLE statement before PostgreSQL processes it.

Injection is conditional on the column not already being present. That is the normal case on restore: pg_dump emits _$purge_ts as an ordinary column alongside WITH (retention = ...), so injecting blindly would produce a duplicate.

The column is deliberately nullable. A NOT NULL constraint is checked by ExecConstraints before table_tuple_insert runs, so it would reject rows before the access method could populate the value. Enforcement treats a null deadline as not yet eligible, so a null cannot cause early deletion.

Populating it¶

On every insert path:

clock_timestamp() + retention_days * interval '1 day'

Two details are load-bearing:

clock_timestamp(), not now(). It gives each row its true arrival even inside a long transaction, and since it is always at or after now() within a transaction, it also yields the later — more conservative — deadline.
Interval addition, not seconds. timestamptz + interval '90 days' is calendar-correct across a daylight-saving boundary; 90 * 86400 is not, and a retention period means calendar days.

The slot's cached values cannot be edited in place, because for some slot types they are only a view onto an underlying tuple. A fresh tuple is built and stored back, which is the supported way to change a row in transit.

Preserving it across an update¶

tuple_update fetches the old row and carries its deadline into the new version.

Without that, an UPDATE could set _$purge_ts into the past and then delete the row, defeating retention entirely on any table granting both update and delete. The old value always wins: an update can change the row, never how long it must be kept.

Checking it on delete¶

tuple_delete receives the row's address but not its contents, so the row is fetched to read its deadline. The comparison uses now() — transaction start — so every row in one purge run is judged against a single consistent instant rather than drifting as the scan proceeds.

A row whose deadline has not passed raises. At every isolation level.

Why it raises rather than skipping¶

Silently passing over an ineligible row is not available. tuple_delete returns TM_Result, and the executor, not the access method, decides what each value means:

Return	Result
`TM_Deleted`	Clean skip at READ COMMITTED; serialization failure under a transaction-level snapshot
`TM_Ok` without deleting	Silent everywhere, but the command tag counts rows it did not remove, `AFTER DELETE` triggers fire for surviving rows, and `RETURNING` hands back rows that still exist
Anything else	Rejected outright by the executor

There is no "skip this row" member. So the guarantee is enforced loudly instead, and the purge procedure carries an explicit WHERE _$purge_ts < now() so nothing in normal operation reaches the error.

Restore mode¶

pg_vault_tables.restore_mode is a superuser-only boolean, off by default.

While off, a supplied deadline is discarded and recomputed. While on, it is preserved.

The switch has to be explicit because the two cases cannot be told apart by inspecting the value: a legitimately restored old row and a deliberately backdated one look identical.