Re: [HACKERS] Per-table freeze limit proposal

On Wed, 2005-11-16 at 07:52 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > On Wed, 2005-11-16 at 08:31 +0000, Simon Riggs wrote:
>
> > > All tuples might be frozen or might not be, the point is you don't know.
> > > That's why you can't use FrozenTransactionId.
>
> > Thinking some more, when initdb issues VACUUM FREEZE we know for certain
> > that nobody else is issuing commands against the database at that point,
> > which is equivalent to a table lock. So we should be able to have a
> > VACUUM FREEZE detect that and if so, set FrozenTransactionId.
> >
> > In normal concurrent running, I would like VACUUM FREEZE to issue a full
> > table SHARE lock to ensure that we can set FrozenTransactionId for that
> > also. Otherwise we will not be able to move frozen tables to read only
> > media.
>
> You missed one point however. Even if VACUUM FREEZE freezes all tuples,
> any transaction following that one is able to insert non-frozen tuples
> into the table. At that instant, having marked the table with Frozen is
> bogus, no matter what amount of locks you took on it.

(OK I think we are getting there now, after my usual comms errors.)

We need something stronger than VACUUM FREEZE then. Perhaps an ALTER
TABLE READONLY. That would do a FREEZE and place a permanent table share
lock, so we wouldn't need to set/unset the Frozen state. We'd do that as
a permissions thing, rather than an actual lock. That way copies of the
data could still be taken with ease and the copies would not themselves
be READONLY.

Not sure what you'd call it to make a whole database readonly all at
once...but whatever we call it we know initdb wants to run it on
template1.

Then your original thought becomes fully viable.

This is particularly important because I see the need to be able to
freeze older data partitions and migrate them to readonly media as part
of very high volume data applications.

Best Regards, Simon Riggs