Thread

1. Re: Heads up: 7.3.3 this Wednesday

   A.Bhuvaneswaran <bhuvansql@myrealbox.com> — 2003-05-19T04:18:02Z

   > 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
   
   
   
   
   
   

2. Re: Heads up: 7.3.3 this Wednesday

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-20T14:46:40Z

   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).