Mario Weilguni wrote: Hi,
> T1: BEGIN; > T2: BEGIN; > -- these are the queries similar to those from the foreign key code > T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; > T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; > T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; > T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; > -- DEADLOCK OCCURS! > T1: UPDATE master1 set t='foo' where id=1000; > T2: UPDATE master2 set t='foo' where id=1001; Actually, in 8.1 the FK code issues queries like T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x; which takes only a share lock on the tuple, not an exclusive lock, which solves the blocking and deadlocking problem. If you have a test case where it fails on 8.1 I certainly want to see it. > p.s. Is it possible to modify logging so that the "SELECT 1 FROM ONLY...." > are > logged? Maybe this could help me finding out which queries the foreign key > code really issues. Hmm, actually, those queries should be logged normally, because AFAIK they are issued just like any other query, via SPI. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
