Transactions: DBI vs. SQL

I expect the following two pieces of code to give the same result. One is
SQL, fed directly to Postgres using the psql command-line tool, and the
other is Perl code using DBI. Before I file this as a bug in DBD::Pg, I
want to make sure it's not a "feature" of DBI or something.

In the code, note that I'm intentionally sending statements that I know will
fail. This seems to be necessary to reproduce the bug.

First, the SQL:

DROP TABLE t1;
DROP TABLE t2;

CREATE TABLE t2
(
id INT PRIMARY KEY
);

CREATE TABLE t1
(
id INT PRIMARY KEY,
t2_id INT REFERENCES t2 (id) INITIALLY DEFERRED
);

BEGIN;
INSERT INTO t2 (id) VALUES (1);
INSERT INTO t1 (id, t2_id) VALUES (1, 1);
COMMIT;

BEGIN;
INSERT INTO t1 (id, t2_id) VALUES (2, 1);
INSERT INTO t1 (id, t2_id) VALUES (3, 2);
COMMIT;

BEGIN;
INSERT INTO t1 (id, t2_id) VALUES (2, 1);
ROLLBACK;

SELECT * FROM t1;

The final SELECT should find only one row:

id | t2_id
----+-------
1 | 1

It works as expected. Now the Perl code:

use DBI;

my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', '...',
{ PrintError => 1, RaiseError => 0 });

$dbh->do('CREATE TABLE t2
(
id INT PRIMARY KEY
)');

$dbh->do('CREATE TABLE t1
(
id INT PRIMARY KEY,
t2_id INT REFERENCES t2 (id) INITIALLY DEFERRED
)');

$dbh->do('DELETE FROM t1');
$dbh->do('DELETE FROM t2');

$dbh->begin_work;

$dbh->do('INSERT INTO t2 (id) VALUES (1)');
$dbh->do('INSERT INTO t1 (id, t2_id) VALUES (1, 1)');

$dbh->commit;

$dbh->begin_work;

$dbh->do('INSERT INTO t1 (id, t2_id) VALUES (2, 1)');
$dbh->do('INSERT INTO t1 (id, t2_id) VALUES (3, 2)');

$dbh->commit;

$dbh->begin_work;

$dbh->do('INSERT INTO t1 (id, t2_id) VALUES (2, 1)');

$dbh->rollback; # XXX: This doesn't seem to work

my $sth = $dbh->prepare('SELECT * FROM t1');
$sth->execute;

while(my $row = $sth->fetchrow_hashref)
{
print "$row->{'id'}, $row->{'t2_id'}\n";
}

$dbh->do('DROP TABLE t1');
$dbh->do('DROP TABLE t2');

$dbh->disconnect;

Unfortunately, it prints this:

1, 1
2, 1

Basically, the call to rollback() appears to fail. The row inserted is not
rolled back but remains visible. In fact, if you remove the DROP TABLE
statements from the end of the Perl script, row id 2 in the t1 table stays
in the database after the script exits.

So, is this correct behavior or a bug?

-John



.

Relevant Pages

  • Re: Transactions: DBI vs. SQL
    ... other is Perl code using DBI. ... want to make sure it's not a "feature" of DBI or something. ... I filed this as a bug on DBD::Pg and it was just fixed in the newly ... id INT PRIMARY KEY ...
    (perl.dbi.users)
  • Re: Transactions: DBI vs. SQL
    ... You need to turn AutoCommit off with: ... This seems to be necessary to reproduce the bug. ... First, the SQL: ... id INT PRIMARY KEY ...
    (perl.dbi.users)
  • Re: DECODE
    ... with a DECODE statement which gets values from another table? ... SET column1 = DECODE(some value of a select statement....... ... SQL> CREATE TABLE t1 ... id INT PRIMARY KEY, ...
    (comp.databases.oracle.server)