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: $(at)\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(at)turnstep(dot)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-----