Re: improving foreign key locks

Excerpts from Florian Pflug's message of vie nov 26 10:48:39 -0300 2010:

> To me, the whole thing seems to be special case of allowing to not only lock whole tuples FOR UPDATE or FOR SHARE, but also individual fields or sets of fields. Except that for simplicity, only two sets are supported, which are
> A) All fields
> B) All fields which are included in some unique constraint, including primary keys.
>
> I'd therefore suggest to extend the FOR SHARE / FOR UPDATE syntax to be
> SELECT FOR { SHARE | UPDATE } [ OF <table1>[.<field1>], ... ]
> and obtain what you call a "KEY LOCK" if (for a particular table) the set of fields is a subset of (B). Otherwise, we'd obtain a full SHARE lock. Thus we'd always lock at least the fields the user told us to, but sometimes more than those, for the sake of a more efficient implementation.

This would require some additions in ri_FetchConstraintInfo(). Right
now it does a single syscache lookup and then extracts a bunch of
attributes from there. If we're going to implement as you suggest, we'd
have to:

1. add a relcache lookup in there, and extract column names involved in
the FK.

2. store those column names in RI_ConstraintInfo; currently it's about
68 bytes, it'd grow to ~2116 bytes (current size plus RI_MAX_NUMKEYS * NAMEDATALEN).

Additionally, we'd have to expend some more cycles at the parse analysis
phase (of the "FOR SHARE OF x.col1, x.col2" query) to verify that those
columns belong into some non-partial unique index.

Is the performance gain sufficient to pay these costs?

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