Re: improving foreign key locks

Excerpts from Florian Pflug's message of vie nov 26 10:48:39 -0300 2010:
> On Nov25, 2010, at 23:01 , Alvaro Herrera wrote:
> > So I've been working on improving locks for foreign key checks, as
> > discussed in a thread started by Joel Jacobson a while ago. I've posted
> > about this:
> > http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
> > http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks_part_2/
>
> 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.

The problem with this idea is that it's not possible to implement it.

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