Re: foreign key locks, 2nd attempt

On Tue, Jan 31, 2012 at 9:19 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> 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 think it's butt-ugly, but it's only slightly uglier than
relfrozenxid which we're already stuck with. The slight amount of
additional ugliness is that you're going to use an XID column to store
a uint4 that is not an XID - but I don't have a great idea how to fix
that. You could mislabel it as an OID or a (signed) int4, but I'm not
sure that either of those is any better. We could also create an mxid
data type, but that seems like it might be overkill.

>> 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.

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.

>> 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.

Why the special case for tables without unique indexes? Like Noah, I
don't see the point. Unless there's some trade-off I'm not seeing, we
should want the number of key columns to be as minimal as possible, so
that as many updates as possible can use the "cheap" path that doesn't
involve locking the whole tuple.

>>  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.

I thought part of the point here was that multixacts aren't so
ephemeral any more: they're going to stick around until the table gets
frozen. I'm worried that's going to turn out to be a problem somehow.
With respect to this particular issue, what I'm worried about is
something like this:

1. Transaction A begins.
2. Transaction B begins and does some updating or locking of table T,
and then commits.
3. Transaction C begins and does DDL on table T, acquiring
AccessExclusiveLock while it does so, and changes the set of key
columns. It then commits.
4A.Transaction A now accesses table T
and/or
4B. Transaction D begins and accesses table T.

At step 4, A and/or D have an up-to-date relcache entries that
correctly describes the current set of key columns in T. But the work
done by transaction B was done with a different set of key columns
(could be more or less), and A and/or D mustn't get confused on that
basis. Also, in the case of A, there is the further possibility that
A's snapshot can't see B as committed yet (even though C subsequently
held an AccessExclusiveLock on the table).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company