Re: foreign key locks, 2nd attempt

Excerpts from Robert Haas's message of mar ene 31 10:17:40 -0300 2012:

> I suspect you are right that it is unlikely, but OTOH that sounds like
> an extremely painful recovery procedure. We probably don't need to
> put a ton of thought into handling this case as efficiently as
> possible, but I think we would do well to avoid situations that could
> lead to, basically, a full-cluster shutdown. If that happens to one
> of my customers I expect to lose the customer.

Okay, so the worst case here is really bad and we should do something
about it. Are you okay with a new pg_class column of type xid? The
advantage is not only that we would be able to track it with high
precision; we would also get rid of a lot of code in which I have little
confidence.

> I have a couple of other concerns about this patch:
>
> 1. I think it's probably fair to assume that this is going to be a
> huge win in cases where it avoids deadlocks or lock waits. But is
> there a worst case where we don't avoid that but still add a lot of
> extra multi-xact lookups? What's the worst case we can imagine and
> how pathological does the workload have to be to tickle that case?

Hm. I haven't really thought about this. There are some code paths
that now have to resolve Multixacts that previously did not; things like
vacuum. I don't think there's any case in which we previously did not
block and now block, but there might be things that got slower without
blocking. One thing that definitely got slower is use of SELECT FOR
SHARE. (This command previously used hint bits to mark the row as
locked; now it is always going to create a multixact). However, I
expect that with foreign keys switching to FOR KEY SHARE, the use of FOR
SHARE is going to decline, maybe disappear completely, so it shouldn't
be a problem.

> 2. What algorithm did we end up using do fix the set of key columns,
> and is there any user configuration that can or needs to happen there?

Currently we just use all columns indexed by unique indexes (excluding
expressional and partial ones). Furthermore we consider "key column"
all columns in a table without unique indexes. Noah disagrees with this
choice; he says we should drop this last point, and that we should relax
the first to "columns actually used by foreign key constraints". I
expect that this is a rather simple change.

Currently there's nothing that the user can do to add more columns to
the set considered (other than creating more unique indexes, of course).
We discussed having an ALTER TABLE command to do it, but this isn't seen
as essential.

> Do we handle cleanly the case where the set of key columns is changed
> by DDL?

Hmm, I remember thinking about this at some point, but now I'm not 100%
sure. I think it doesn't matter due to multis being so ephemeral. Let
me try and figure it out.

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