Thread: Deferred FK / PK deletion problems

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Came across an odd bug while dealing with deferred foreign keys.
Short story: messing around with the PK table screws up deferred
constraints on the FK table. Here's a quick script to demonstrate.
Confirmed as broken on today's cvs version, as well as on 8.2 and
8.1:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;
use Time::HiRes qw/gettimeofday tv_interval/;

my $t0 = [gettimeofday];
my $type = 'M';

my $port = 5432;
my $dbh1 = DBI->connect("dbi:Pg:dbname=greg;port=$port",'greg','',
  {AutoCommit=>0,PrintError=>0,RaiseError=>0});
my $dbh2 = DBI->connect("dbi:Pg:dbname=greg;port=$port",'greg','',
  {AutoCommit=>0,PrintError=>0,RaiseError=>0});
$dbh1->{InactiveDestroy} = 1; $dbh2->{InactiveDestroy} = 1;

$dbh1->do("DROP TABLE bar; DROP TABLE foo");
$dbh1->commit();
$dbh1->{RaiseError}=1; $dbh2->{RaiseError}=1;

$dbh1->do(qq{

CREATE TABLE foo (
  foo_id INT NOT NULL PRIMARY KEY
);

CREATE TABLE bar (
  bar_id INT NOT NULL PRIMARY KEY,
  foo_id INT NOT NULL
);

ALTER TABLE bar ADD CONSTRAINT bar_ref_foo FOREIGN KEY (foo_id)
REFERENCES foo(foo_id) DEFERRABLE INITIALLY DEFERRED;

});

$dbh1->commit();

go(1, "INSERT INTO foo VALUES (1)");
go(1, "INSERT INTO bar VALUES (1,1)");
go(1, "COMMIT");

go(1, "DELETE FROM foo");

if (fork) {
   $type = 'F';
   go(2, "INSERT INTO bar VALUES (3,1);");
   go(2, "COMMIT");
   exit;
}
sleep 1;

go(1, "INSERT INTO foo VALUES (1)");
go(1, "COMMIT");

my $run = 1;
sub go {
    my ($db,$com) = @_;
    $run++;
    printf "DB $db [%0.3f] {$type$run} RUN: $com\n", tv_interval($t0);
    my $dbh = $db==1 ? $dbh1 : $dbh2;
    my $res;
    eval {
        $res = $com eq 'COMMIT' ? $dbh->commit() :
            $com =~ /^SELECT/ ? $dbh->selectall_arrayref($com) : $dbh->do($com);
    };
    if ($@) {
        chomp $@;
        printf "DB $db [%0.3f] {$type$run} ERROR: $@\n", tv_interval($t0);
        $dbh->rollback();
    }
}

__DATA__

Output:

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"
DB 1 [0.120] {M1} RUN: INSERT INTO foo VALUES (1)
DB 1 [0.124] {M2} RUN: INSERT INTO bar VALUES (1,1)
DB 1 [0.126] {M3} RUN: COMMIT
DB 1 [0.162] {M4} RUN: DELETE FROM foo
DB 2 [0.165] {F5} RUN: INSERT INTO bar VALUES (3,1);
DB 2 [0.170] {F6} RUN: COMMIT
DB 1 [1.168] {M5} RUN: INSERT INTO foo VALUES (1)
DB 1 [1.169] {M6} RUN: COMMIT
DB 2 [1.183] {F6} ERROR: DBD::Pg::db commit failed: ERROR:  insert or update on table "bar" violates foreign key
constraint"bar_ref_foo" 
DETAIL:  Key (foo_id)=(1) is not present in table "foo".




- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200710111804
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHDp4IvJuQZxSWSsgRAz9RAKD0HzqNlVrcM5/m+IZY5+D4W2ZfsgCgyXt8
sqioJN8iHhIo+RQWcH3p3E8=
=YbhB
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Came across an odd bug while dealing with deferred foreign keys.

I'm not convinced this is a bug.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Tom Lane wrote:
>> Came across an odd bug while dealing with deferred foreign keys.

> I'm not convinced this is a bug.

Can you elaborate on this? Am I doing something wrong in my app?
Someone on irc pointed out that this affects more than deferred
fk, but for my purposes, here's what's happening:

Table A has a primary key.
Table B references that primary key.

Process A periodically updates the table by doing
(basically) a delete all/insert new data, inside of
a transaction.

Process B is adding entries to table B.

If Process B happens in the "middle" of Process A,
the insert to B fails as it claims that the corresponding
row in table A does not exist.

Short of Process A grabbing an exclusive lock on the table, I
can't see a way around this. Feel free to punt this to
general if this is the expected behavior.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200710151809
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHE+VUvJuQZxSWSsgRAzyGAKCveD8q0a8O2XFEkD1g5f08Z58mbgCgvHUF
z4bBO7MJ0gWow1fPHJY09is=
=ohAQ
-----END PGP SIGNATURE-----