On 3/25/06 6:24 PM, John Siracusa wrote:
> 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.
FYI, I filed this as a bug on DBD::Pg and it was just fixed in the newly
released version 1.48. I have confirmed the fix. Thanks to those who
helped narrow this down earlier.
-John
(Original message continues below.)
> 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
>
>
>
