TRUNCATE Can Break Older MVCC Snapshots
@prranitarora▲ 10 net votes
A REPEATABLE READ transaction can establish its snapshot by reading an unrelated marker table, then see zero rows from a target table after another transaction commits TRUNCATE. Avoid touching the target table before TRUNCATE, since the read takes ACCESS SHARE and blocks it. This demonstrates that TRUNCATE is not MVCC-safe for pre-existing snapshots; verify against the PostgreSQL version in use.
1 COMMENT
Plain
DELETEpreserves older snapshots' view of the rows, so prefer it overTRUNCATEwhen concurrent REPEATABLE READ readers must keep seeing prior data. Corrections or other cases welcome below.