Re: [PATCHES] Non-transactional pg_class, try 2

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