Re: [PATCHES] Non-transactional pg_class, try 2 - Mailing list pgsql-hackers

On Mon, 2006-06-26 at 13:58 -0400, Alvaro Herrera wrote:
> Ok, let's step back to discuss this again.  Sorry for the length -- this
> is a description of the problem I'm trying to solve, the issues I found,
> and how I tried to solve them.

Thanks. This is good.

> The relminxid Patch
> ===================
> 
> What I'm after is not freezing for read-only media, nor archive, nor
> read-only tables. 

OK, but I am... but I'm happy to not to confuse the discussion.

> Now, why do we need to vacuum whole databases at a time?

> So, we have to do something to cope with frozen databases.  I see two
> ways:
> 
> 1. Remove the special case, i.e., process frozen databases in VACUUM
>    like every other database.
>    This is the easiest, because no extra logic is needed.  Just make
>    sure they are vacuumed in time.  The only problem would be that we'd
>    need to uselessly vacuum tables that we know are frozen, from time to
>    time.  But then, those tables are probably small, so what's the
>    problem with that?

> 2. Mark frozen databases specially somehow.
>    To mark databases frozen, we need a way to mark tables as frozen.
>    How do we do that?  As I explain below, this allows some nice
>    optimizations, but it's a very tiny can full of a huge amount of
>    worms.

At this stage you talk about databases, yet below we switch to
discussing tables. Not sure why we switched from one to the other.

> Marking a Table Frozen
> ======================
> 
> Marking a table frozen is simple as setting relminxid = FrozenXid for a
> table.  As explained above, this cannot be done in a regular postmaster
> environment, because a concurrent transaction could be doing nasty stuff
> to a table.  So we can do it only in a standalone backend.

Surely we just lock the table? No concurrent transactions?

> On the other hand, a "frozen" table must be marked with relminxid =
> a-regular-Xid as soon as a transaction writes some tuples on it.  Note
> that this "unfreezing" must take place even if the offending transaction
> is aborted, because the Xid is written in the table nevertheless and
> thus it would be incorrect to lose the unfreezing.
> 
> This is how pg_class_nt came into existence -- it would be a place where
> information about a table would be stored and not subject to the rolling
> back of the transaction that wrote it.  So if you find that a table is
> frozen, you write an unfreezing into its pg_class_nt tuple, and that's
> it.
> 
> Nice optimization: if we detect that a table is fully frozen, then
> VACUUM is a no-op (not VACUUM FULL), because by definition there are no
> tuples to remove.

Yes please, but we don't need it anymore do we? Guess we need it for
backwards compatibility? VACUUM still needs to vacuum every table.

> Another optimization: if we are sure that unfreezing works, we can even
> mark a table as frozen in a postmaster environment, as long as we take
> an ExclusiveLock on the table.  Thus we know that the vacuum is the sole
> transaction concurrently accessing the table; and if another transaction
> comes about and writes something after we're finished, it'll correctly
> unfreeze the table and all is well.

Why not just have a command to FREEZE and UNFREEZE an object? It can
hold an ExclusiveLock, avoiding all issues. Presumably FREEZE and
UNFREEZE are rare commands?


> Where are the problems in this approach?
> 
> 1. Performance.  We'll need to keep a cache of pg_class_nt tuples.  This
> cache must be independent of the current relcache, because the relcache
> is properly transactional while the pg_class_nt cache must not be.
> 
> 2. The current implementation puts the unfreezing in LockRelation.  This
> is a problem, because any user can cause a LockRelation on any table,
> even if the user does not have access to that table.

That last bit just sounds horrible to me. But thinking about it: how
come any user can lock a relation they shouldn't even be allowed to know
exists? Possibly OT.

I can see other reasons for having pg_class_nt, so having table info
cached in shared memory does make sense to me (yet not being part of the
strict definitions of the relcache).

--  Simon Riggs EnterpriseDB          http://www.enterprisedb.com