On Tue, 2007-12-11 at 11:49 +0000, Gregory Stark wrote: > "Simon Riggs" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
