Re: WORM and Read Only Tables (v0.1)

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> So... VACUUM FREEZE table SET READ ONLY;
>
> would be my first thought, but I'm guessing everybody will press me
> towards supporting the more obvious
>
> ALTER TABLE table SET READ ONLY;
>
> 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).
> 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.

To be clear it if it meets a block for which a tuple is not freezable -- that
is, it has an xmin or xmax more recent than the global xmin then it needs to
block waiting for the backend which that recent xmin. Then presumably it needs
to update its concept of recent global xmin going forward.

You might be best off grabbing a list of txid->xmin when you start and sorting
them by xmin so you can loop through them sleeping until you reach the first
txid with an xmin large enough to continue.

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

I'm not sure how this interacts with:

> 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.

Since queries which think they hold FOR SHARE tuple locks will be magically
losing their share locks if you turn off the read-only flag. Do you need to
obtain an exclusive lock on the table to turn it read-write?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!