Skip site navigation (1) Skip section navigation (2)

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

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Konstantin <kostya2702(at)rambler(dot)ru>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4648: needless deadlock on tables having foreign-key
Date: 2009-02-12 11:54:11
Message-ID: 49940DE3.3000200@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Thue Janus KristensenDate: 2009-02-12 12:52:15
Subject: BUG #4650: can't specify table schema in CREATE TABLE AS
Previous:From: Harald Armin MassaDate: 2009-02-12 10:46:46
Subject: Re: BUG #4646: Default password is patently absurd

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group