Re: Pet Peeves?

Guy Rouillier wrote:

> Back in March 2005, I started an email thread titled "Debugging
> deadlocks". Most of the experienced PGers participated in that thread.
> The basic issue at that time was that inserting a row into a table with a
> foreign key placed an exclusive row-level lock (SELECT FOR UPDATE) on the
> reference table (the table to which the foreign key refers). If you
> happen to do inserts on two different tables, each with a foreign key to
> the same reference table, deadlocks are pretty easy to create. This is
> especially true if the reference table has low cardinality, which is
> often the case.
>
> I don't know if this situation has been improved since that time.

We fixed this in 8.1 IIRC. FKs now use "SELECT FOR SHARE", which only
takes a shared lock not exclusive, and does away with most deadlocks of
this ilk. Of course, there are other ways to get in deadlock still.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"I can see support will not be a problem. 10 out of 10." (Simon Wittber)
(http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)