Re: foreign key locks, 2nd attempt

Excerpts from Tom Lane's message of jue feb 23 12:28:20 -0300 2012:
>
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Sure. The problem is that we are allowing updated rows to be locked (and
> > locked rows to be updated). This means that we need to store extended
> > Xmax information in tuples that goes beyond mere locks, which is what we
> > were doing previously -- they may now have locks and updates simultaneously.
>
> > (In the previous code, a multixact never meant an update, it always
> > signified only shared locks. After a crash, all backends that could
> > have been holding locks must necessarily be gone, so the multixact info
> > is not interesting and can be treated like the tuple is simply live.)
>
> Ugh. I had not been paying attention to what you were doing in this
> patch, and now that I read this I wish I had objected earlier.

Uhm, yeah, a lot earlier -- I initially blogged about this in August
last year:
http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/

and in several posts in pgsql-hackers.

> This
> seems like a horrid mess that's going to be unsustainable both from a
> complexity and a performance standpoint. The only reason multixacts
> were tolerable at all was that they had only one semantics. Changing
> it so that maybe a multixact represents an actual updater and maybe
> it doesn't is not sane.

As far as complexity, yeah, it's a lot more complex now -- no question
about that.

Regarding performance, the good thing about this patch is that if you
have an operation that used to block, it might now not block. So maybe
multixact-related operation is a bit slower than before, but if it
allows you to continue operating rather than sit waiting until some
other transaction releases you, it's much better.

As for sanity -- I regard multixacts as a way to store extended Xmax
information. The original idea was obviously much more limited in that
the extended info was just locking info. We've extended it but I don't
think it's such a stretch.

I have been posting about (most? all of?) the ideas that I've been
following to make this work at all, so that people had plenty of chances
to disagree with them -- and Noah and others did disagree with many of
them, so I changed the patch accordingly. I'm not closed to further
rework, but I'm not going to entirely abandon the idea too lightly.

I'm sure there are bugs too, but hopefully there are as shallow as
interested reviewer eyeballs there are.

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