Bug #836: Deadlock during insert w/o autocommit

Alexander Kirpa (postgres(at)bilteks(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Deadlock during insert w/o autocommit

Long Description
Deadlock during insert w/o autocommit in 2 tables with check to table3. Environment Postgres 7.2.3(cygwin) & 7.3(freebsd) Perl. Testing local (freebsd) and over lan in both direction (freebsd client, w2k server and w2k client, freeBSD server).
Result - freeze client, server don't make (psql) some operation related used tables.

Sample Code
DBname - deadlock
----------
--
-- PostgreSQL database dump
--

\connect - postgres
SET search_path = public, pg_catalog;
CREATE TABLE tb2 ( c0 smallint NOT NULL, c1 character(2) NOT NULL) WITHOUT OIDS;
CREATE TABLE tb0 ( c0 smallint NOT NULL, c1 character(2) NOT NULL) WITHOUT OIDS;
CREATE TABLE tb1 ( c0 smallint NOT NULL, c1 character(2) NOT NULL) WITHOUT OIDS;
COPY tb2 (c0, c1) FROM stdin;
96 BN
\.
COPY tb0 (c0, c1) FROM stdin;
\.
COPY tb1 (c0, c1) FROM stdin;
\.
ALTER TABLE ONLY tb2 ADD CONSTRAINT tb2_pk PRIMARY KEY (c0);
ALTER TABLE ONLY tb2 ADD CONSTRAINT tb2_u1 UNIQUE (c1);
ALTER TABLE ONLY tb1 ADD CONSTRAINT tb2_pk PRIMARY KEY (c0);
ALTER TABLE ONLY tb0 ADD CONSTRAINT tb0_fk1 FOREIGN KEY (c1) REFERENCES tb2(c1) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE ONLY tb1 ADD CONSTRAINT tb2_pk PRIMARY KEY (c0);
ALTER TABLE ONLY tb1 ADD CONSTRAINT tb1_fk1 FOREIGN KEY (c1) REFERENCES tb2(c1) ON UPDATE NO ACTION ON DELETE NO ACTION;

-------------

program deadlock.pl
--------------------------
#!/usr/bin/perl
use strict;
use DBI;
my ($sth0,$dbh0);
my ($sth1,$dbh1);

$dbh0=DBI->connect("DBI:Pg:dbname=deadlock;host=192.168.88.4;port=5432;","postgres","", {RaiseError => "on"}) or die "Cannot connect
to DB" . $DBI::errstr;
$dbh1=DBI->connect("DBI:Pg:dbname=deadlock;host=192.168.88.4;port=5432;","postgres","", {RaiseError => "on"}) or die "Cannot connect
to DB" . $DBI::errstr;

$dbh0->{AutoCommit}=0;
$dbh1->{AutoCommit}=0;
$sth0=$dbh0->prepare("INSERT INTO tb0 (c0,c1) VALUES (?,?)");
$sth1=$dbh1->prepare("INSERT INTO tb1 (c0,c1) VALUES (?,?)");
$sth0->execute(1,'BN');
$sth1->execute(1,'BN');
$dbh0->commit;$dbh0->disconnect;
$dbh1->commit;$dbh1->disconnect;

No file was uploaded with this report