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

On Tue, 2007-12-11 at 11:49 +0000, Gregory Stark wrote:
> "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.

D'oh. Completely agreed. Mia culpa.

I had that bit in my original design, but I was looking elsewhere on
this clearly. I'd been trying to think about how to do this since about
2 years ago and it was only the CREATE INDEX CONCURRENTLY stuff that
showed me how. Thanks for nudging me.

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

Agreed. I wasn't suggesting implementing without, just noting that it
might have been possible, but it seems not as you say. I don't think its
important to be able to do that with less than AccessExclusiveLock.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com