Re: DBI, savepoints, transactions, and AutoCommit -- NOT A BUG

On Wed, Nov 02, 2011 at 06:05:56PM -0400, Daniel Staal wrote:

> Also, do you have any example code that shows the problem?

Well shiver me timbers! I wrote a sample program ... and it works. I
could have sworn I was logging all SQL executed, but it looks like
something is slipping by. I've attached the program just to be
thorough.

Sorry for not discovering before I posted. You guys get enough noise.

================
#!/usr/bin/perl -w

use strict;

use DBI;
use DBD::Pg;

my $connect = "dbi:Pg:dbname=$ENV{LOGNAME}";
my $schema = 'xyzzy';
my $svname = 'plugh';

die $DBI::errstr unless my $dbh = DBI->connect($connect, undef, undef, { RaiseError => 0, PrintError => 0, AutoCommit => 0 });
die $DBI::errstr unless $dbh->do("CREATE SCHEMA $schema");
die $DBI::errstr unless $dbh->do("SET search_path = $schema");
die $DBI::errstr unless $dbh->do("SET client_min_messages='warning'");
die $DBI::errstr unless $dbh->do("CREATE TABLE teste (kolum INT UNIQUE)");
die $DBI::errstr unless $dbh->do("COMMIT");
die $DBI::errstr unless $dbh->do("INSERT INTO teste (kolum) VALUES (1)");
die $DBI::errstr unless $dbh->pg_savepoint($svname);
die $DBI::errstr unless $dbh->do("INSERT INTO teste (kolum) VALUES (2)");
die "Dup insert did not fail" if $dbh->do("INSERT INTO teste (kolum) VALUES (2)");
print STDERR "$DBI::errstr\n";
die $DBI::errstr unless $dbh->pg_rollback_to($svname);
die $DBI::errstr unless $dbh->do("INSERT INTO teste (kolum) VALUES (3)");
die $DBI::errstr unless $dbh->pg_release($svname);
die $DBI::errstr unless $dbh->do("COMMIT");
#die $DBI::errstr unless my $rows = $dbh->selectall_arrayref("SELECT kolum FROM teste ORDER BY kolum");
die $DBI::errstr unless my $sth = $dbh->prepare("SELECT kolum FROM teste ORDER BY kolum");
die $DBI::errstr unless $sth->execute();
die $DBI::errstr unless my $rows = $sth->fetchall_arrayref();
print STDERR join("\t", 'FETCHED', @$_), "\n" foreach @$rows;
die $DBI::errstr unless $dbh->disconnect();
================

--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix(at)crowfix(dot)com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o