> I doubt it'd get tested enough to notice, if it's not in the default
> build.
> 
> I actually think that both of these are pretty good candidates to put
> into 7.3.3.  I'm just trying to adopt an appropriately paranoid stance
> and ask hard questions about how much they've been tested.  Between
> Kevin and Sean it seems that the deferred-triggers change has gotten
> enough testing to warrant some trust, but I'm not hearing anything
> about the FK-deadlock one :-(.
> 
> BTW, if anyone is looking for that patch, it was at
> http://archives.postgresql.org/pgsql-hackers/2003-04/msg00260.php

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.

Test case:

test_pg=# 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
test_pg=# CREATE TABLE for_test (id int references prim_test(id), name 
text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE TABLE
test_pg=# INSERT INTO prim_test VALUES ('1');
INSERT 4383707 1
test_pg=# INSERT INTO prim_test VALUES ('2');
INSERT 4383708 1
test_pg=# INSERT INTO for_test VALUES (1, 'foo');
INSERT 4383710 1
test_pg=# INSERT INTO for_test VALUES (2, 'bar');
INSERT 4383711 1

t1:
test_pg=# BEGIN ;
BEGIN
test_pg=# UPDATE for_test set name ='FOO' where id = 1;
UPDATE 1
test_pg=# UPDATE for_test set name ='Bar' where id = 2;
UPDATE 1

t2:
test_pg=# BEGIN ;
BEGIN
test_pg=# UPDATE for_test set name = 'BAR' where id = 2;
UPDATE 1
test_pg=# UPDATE for_test set name = 'Foo' where id = 1;
ERROR:  deadlock detected

regards,
bhuvaneswaran





Mon, 19 May 2003 09:48:02 +0530 (IST)
"A.Bhuvaneswaran" <bhuvansql@myrealbox.com>
Re: Heads up: 7.3.3 this Wednesday

On Mon, 19 May 2003, A.Bhuvaneswaran wrote:

> > I doubt it'd get tested enough to notice, if it's not in the default
> > build.
> >
> > I actually think that both of these are pretty good candidates to put
> > into 7.3.3.  I'm just trying to adopt an appropriately paranoid stance
> > and ask hard questions about how much they've been tested.  Between
> > Kevin and Sean it seems that the deferred-triggers change has gotten
> > enough testing to warrant some trust, but I'm not hearing anything
> > about the FK-deadlock one :-(.
> >
> > BTW, if anyone is looking for that patch, it was at
> > http://archives.postgresql.org/pgsql-hackers/2003-04/msg00260.php
>
> 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.
>
> Test case:
>
> test_pg=# 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
> test_pg=# CREATE TABLE for_test (id int references prim_test(id), name
> text);
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE TABLE
> test_pg=# INSERT INTO prim_test VALUES ('1');
> INSERT 4383707 1
> test_pg=# INSERT INTO prim_test VALUES ('2');
> INSERT 4383708 1
> test_pg=# INSERT INTO for_test VALUES (1, 'foo');
> INSERT 4383710 1
> test_pg=# INSERT INTO for_test VALUES (2, 'bar');
> INSERT 4383711 1
>
> t1:
> test_pg=# BEGIN ;
> BEGIN
> test_pg=# UPDATE for_test set name ='FOO' where id = 1;
> UPDATE 1
> test_pg=# UPDATE for_test set name ='Bar' where id = 2;
> UPDATE 1
>
> t2:
> test_pg=# BEGIN ;
> BEGIN
> test_pg=# UPDATE for_test set name = 'BAR' where id = 2;
> UPDATE 1
> test_pg=# UPDATE for_test set name = 'Foo' where id = 1;
> ERROR:  deadlock detected

Assuming you meant interleaving those statements, that's just a garden
variety deadlock (try it again without the foreign key, it still
deadlocks).


Tue, 20 May 2003 07:46:40 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Heads up: 7.3.3 this Wednesday