From PostgreSQL wiki
Jump to navigationJump to search

v0.2 12 Dec 2007

Postgres supports the concept of freezing tuples, so they can live forever within the database without needing further writes. Currently there is no command that will guarantee that a table has been completely frozen. This makes it difficult to reliably write data files to WORM media for longer term archiving. (WORM means Write-Once, Read-Many). It's also a pain having to VACUUM a large table again just because a small number of rows need to be frozen.

So we need a DDL command that will ensure all tuples are frozen and then mark the table as read-only. Ideally, we would like to do this in a way that doesn't hold long full table locks, since we want the data to remain accessible at all times.


would be my first thought, but I'm guessing everybody will press me towards supporting the more obvious


This command will place a ShareLock (only) on the table, preventing anybody from writing to the table while we freeze it. The ShareLock is incompatible with any transaction that has written to the table, so when we acquire the lock all writers to the table will have completed. We then run the equivalent of a VACUUM FREEZE which will then be able to freeze *all* rows in one pass (rather than all except the most recent).

The freeze pass will progress until it sees a potentially invisible row, i.e. one that has been written by a recent TransactionId. At that point the ALTER TABLE will wait for that TransactionId to complete, before continuing to progress. We assume that there are few rows that will be potentially invisible, so we don't bother to update OldestXmin as we progress through the table. So a table recently updated by a long running transaction could cause the ALTER TABLE to wait for an extended period, much longer than the time for the table scan.

On completion of the freeze pass we will then update the pg_class entry to show that it is now read-only, so we will emulate the way VACUUM does this.

This form of the ALTER TABLE command will need to be mangled so it can only run outside of a transaction block and also so it takes only a ShareLock rather than an AccessExclusiveLock.

Reversing the process is simpler, since we only have to turn off the flag in pg_class:


possibly able to do this without grabbing an AccessExclusiveLock, though that isn't an important part of this implementation.

Read-only tables never need VACUUMing, so we would be able to make autovacuum and explicit vacuum ignore them.

Read-only tables may not be written to, yet would still allow implicit or explicit INSERT, UPDATE and DELETE privileges to be held on the table. Attempts to write to the table will result in a specific "read only table cannot be modified" ERROR. This allows a table to be placed into read-only mode for long periods of time and flipped back to read-write if some updates are required. That is useful for various performance reasons, see later. We can't use the privilege mechanism to prevent writes since superusers bypass them. (Thoughts?)

Requests for tuple-level SHARE locks (e.g. FOR SHARE) against read-only tables will be ignored, since they are effectively already there. So we don't need to change the internals of the locking, nor edit the RI code to remove the call to SHARE lock referenced tables. Do this during post-parse analysis.

Tables can be copied to WORM media by using


This would also use a ShareLock rather than an AccessExclusiveLock, piggy-backing off the work mentioned above.

Running SET TABLESPACE and SET READ ONLY at the same time might sound like a good plan, but ISTM will require two fairly different code paths, so if we do it at all it will be a later addition.