Re: foreign key locks, 2nd attempt

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.

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

>> >> 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.
>
> No trade-off.  I just thought it was safer: my thought was that if
> there's no nominated key column, the safer bet was that any of them
> could have been.  But then, in reality there cannot be any foreign key
> here anyway.  I'll revert that bit.

OK.

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