Re: foreign key locks, 2nd attempt

Excerpts from Robert Haas's message of mar ene 31 14:12:10 -0300 2012:
>
> On Tue, Jan 31, 2012 at 11:58 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Well, we're already storing a multixact in Xmax, so it's not like we
> > don't assume that we can store multis in space normally reserved for
> > Xids.  What I've been wondering is not how ugly it is, but rather of the
> > fact that we're bloating pg_class some more.
>
> I don't think another 4 bytes in pg_class is that big a deal. We
> don't do relcache rebuilds frequently enough for that to really matter
> much. The bigger cost of this patch seems to me to be that we're
> going to have to carry around multi-xact IDs for a long time, and
> probably fsync and/or WAL-log them moreso than now. I'm not sure how
> much you've worried about that, but a new column in pg_class seems
> like line noise by comparison.

I'm not too worried by either fsyncing or WAL logging, because those
costs are only going to be paid when a multixact is used at all; if we
avoid having to wait for an arbitrary length of time at some point, then
it doesn't matter than we some things are bit slower. I worry about a
new pg_class column because it's going to be paid by everyone, whether
they use multixacts or not.

But, having never heard anybody stand against this proposal, I'll go do
that.

> >> What about SELECT FOR UPDATE?  That's a pretty common case, I think.
> >> If that's now going to force a multixact to get created and
> >> additionally force multixact lookups when the row is subsequently
> >> examined, that seems, well, actually pretty scary at first glance.
> >> SELECT FOR UPDATE is fairly expensive as it stands, and is commonly
> >> used.
> >
> > SELECT FOR UPDATE is still going to work without a multi in the simple
> > cases.  The case where it's different is when somebody else grabs a KEY
> > SHARE lock on the same tuple; it's now going to get a multi, where it
> > previously blocked.  So other transactions later checking the tuple will
> > have a bit of a larger cost.  That's okay considering that it meant
> > the other transaction did not have to wait anymore.
>
> OK. I assume that the different treatment of SELECT FOR SHARE is due
> to lack of bit space?

Yes. I gave preference for SELECT FOR UPDATE and SELECT FOR KEY SHARE
because those are presumably going to be used much more frequently than
SELECT FOR SHARE; one because it's part of the standard and there are
plenty of use cases; the other because we're going to use it internally
very frequently.

Now, perhaps we could fix that (i.e. have a separate hint bit for SELECT
FOR SHARE), but I don't think it's justified.

In the meantime, here's an updated version which fixes some funny
border cases, mostly involving locks acquired in aborted
subtransactions. Interestingly, it seems to me the code in heapam.c is
now clearer than before.

The other bit about columns to be considered keys isn't yet changed in
this version.

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