Transactions: DBI vs. SQL

[John Siracusa]

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