Re: BUG #4648: needless deadlock on tables having foreign-key

Konstantin wrote:
> Preparation:
> a) Create tables and insert a few rows.
>
> CREATE TABLE parent (pid integer PRIMARY KEY);
> CREATE TABLE child (id integer PRIMARY KEY, pid integer REFERENCES
> parent(pid) );
> INSERT INTO parent VALUES(1);
> INSERT INTO child VALUES(1,1);
>
> b) open two sessions via psql to database.
>
> Bug:
>
> Execute in Session1:
> =======================
> test=# BEGIN; SELECT * FROM parent WHERE pid = 1 FOR UPDATE;
> BEGIN
> pid
> -----
> 1
> (1 row)
> =======================
>
>
> Execute in Session2:
> =======================
> test=# BEGIN; UPDATE child set pid=1 where id = 1;
> BEGIN
> UPDATE 1
> test=# UPDATE child set pid=1 where id = 1;
> =======================

Hmm, the first UPDATE should've blocked already. It should've fired a RI
trigger to lock the parent tuple in shared mode, but it looks like
that's not happening for some reason.

> Used PostgreSQL:
> PostgreSQL 8.1.16 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
> 20060404 (Red Hat 3.4.6-9)

I can reproduce this on CVS HEAD too. I'll try to figure out what's
going on..

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com