Re: Transactions: DBI vs. SQL

[Tom Mornini]

You need to turn AutoCommit off with:

    my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', '...',

{ PrintError => 1, RaiseError => 0, AutoCommit => 0 });

--
-- Tom Mornini

On Mar 25, 2006, at 3: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.

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