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