Re: BUG #8290: broken/unexpected locking behavior

pgnube(at)gmail(dot)com escribió:

> I sent the following information to pgsql-general to ask if it is expected
> locking behavior. The only responses that I got said that the behavior is
> reproducible on 9.1 and 9.3 beta 2.
> Nobody said that this is expected locking behavior and I believe it to be a
> bug, so I am filing this bug report.
> The exact steps on how to reproduce the problem are shown below. Thank you
> for putting together a great DB and for working on this bug report.

See here:
http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/

There are further posts on the same topic in that blog. The patch
dealing with it was finally committed for the 9.3 version, due to be
released later this year:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182
At the bottom of the commit messages there are some message-ids on
(rather long) discussions about that patch. You can search for them at
http://www.postgresql.org/list/ (just enter the msgid in the box and
click search).

If you try a 9.3 snapshot, you should be able to replace the FOR UPDATE
in your queries with FOR NO KEY UPDATE and there should be no deadlock.
Even if it does not, I hope the aforementioned posts explain what is
going on.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services