"A.Bhuvaneswaran" <bhuvanbk@yahoo.com> writes:
> 7.3.2: I applied the above patch and did install and restarted postgresql,
> but the 'deadlock detected' error on FK update still exist. The below is
> the test case. Someone *advice* me, if it the above mentioned patch is not
> intended to address the below case.
That is not a foreign-key deadlock; it's a plain old deadlock. It would
happen exactly the same way without the foreign key, because the
contention is directly for the rows being updated.
An example of what the patch fixes:
regression=# CREATE TABLE prim_test (id int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'prim_test_pkey' for table 'prim_test'
CREATE TABLE
regression=# CREATE TABLE for_test (id int, name text,
regression(# ref int references prim_test(id));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
regression=# INSERT INTO prim_test VALUES ('1');
INSERT 566517 1
regression=# INSERT INTO prim_test VALUES ('2');
INSERT 566518 1
regression=# INSERT INTO for_test VALUES (11, 'foo', 1);
INSERT 566520 1
regression=# INSERT INTO for_test VALUES (12, 'fooey', 1);
INSERT 566521 1
regression=# INSERT INTO for_test VALUES (21, 'fooey', 2);
INSERT 566522 1
regression=# INSERT INTO for_test VALUES (22, 'fooey', 2);
INSERT 566523 1
regression=# begin;
BEGIN
regression=# UPDATE for_test set name ='FOO' where id = 11;
UPDATE 1
-- in client 2 do
regression=# begin;
BEGIN
regression=# UPDATE for_test set name = 'BAR' where id = 22;
UPDATE 1
regression=# UPDATE for_test set name = 'BAR' where id = 12;
UPDATE 1
-- back to client 1, do
regression=# UPDATE for_test set name ='FOO' where id = 21;
UPDATE 1
This deadlocks in 7.3, but works in CVS tip.
regards, tom lane