Re: augmenting MultiXacts to improve foreign keys - Mailing list pgsql-hackers

Excerpts from Robert Haas's message of mar sep 13 11:02:51 -0300 2011:
> 
> On Fri, Sep 9, 2011 at 5:31 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > [ multixact complexity ]
> 
> I wonder if it's a mistake to be thinking about solving this problem
> by extending the MultiXact mechanism.  Pushing xmax out-of-line so
> that we have room to store tuple information seems expensive,
> especially because there's no convenient way to undo it once the locks
> are old enough not to be interesting any more.  The current system
> works because we never need both pieces of information at the same
> time, but that's not going to be true any more.

Hmm, it doesn't look that way to me: whenever you lock a row, all
previous lockers that are gone can now be forgotten.  Locks that are old
enough not to be interesting, are constantly and automatically gone.

The only reason that multixact now needs to persist beyond currently
running transaction is the chance that there might be an "update xmax"
hiding somewhere; and tuples marked with those are going to be removed
by vacuum anyway.  (I have been thinking that long before vacuum, we
could remove the multixact and replace it with a plain Xid, if the
lockers are all gone -- which is another part of your "undo it once the
locks are old enough".)

The expensive bit is the reason why I used a hint bit to mark this
possibility; we distinguish the cheap case of locked-but-not-updated
from the expensive one of locked-and-updated with hint bits, so the
cheap case stays cheap; and the expensive one requires a bit more work,
yes, but this brings more concurrency overall.

> I'm wondering if it would be possible to modify the main lock manager,
> or create a special-purpose tuple lock manager, to record all tuple
> locks, both awaited and granted.  You'd need to make sure that if
> there were more than a few locks the information could spill to disk
> somehow, and you'd also need to make sure that you didn't pull that
> information in from disk more often than necessary - i.e. you should
> try to keep enough summary info in memory to determine whether there
> *might* be a conflicting lock that spilled out, so that you only need
> to go examine the spilled data if there's a possibility that you might
> find something interesting there.

This is where we started, back when we were creating SELECT FOR SHARE:
trying to spill the lock table.  That idea went down in flames; consider
that someone might request to block an entire huge table, and you're in
trouble.  There might have been other problems I don't recall with that
idea.

I don't want to go back to that drawing board -- obviously I'm not very
keen of going great lengths down the same path, only to fail, twice.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support