On Mon May 24 20:46:50 2010, SAMV wrote:
Show quoted text> OK, after a bit of investigation I'm more convinced that the code is
> unnecessary. In the event that an $sth goes out of scope during an
> aborted transaction, the DEALLOCATE statement will simply fail, a
> warning will be issued by the enclosing code, but nothing disastrous
> should result of this. Even if you explicitly re-used a prepared
> statement name using pg_prepare_name, it would fail, and then pick a
> different name. I'll see if I can prove this with a test case.
Continuing my monologue, after producing a test case (attached) I can see
that the exact drawback I describe is not correct, leaving me with just
the general grumble that the driver is resetting my transaction state
without telling me.
#!perl
## Test savepoint functionality
use 5.006;
use strict;
use warnings;
use Test::More;
use DBI ':sql_types';
use lib 't','.';
require 'dbdpg_test_setup.pl';
select(($|=1,select(STDERR),$|=1)[1]);
my $dbh = connect_database();
if (!defined $dbh) {
plan skip_all => 'Connection to database failed, cannot continue testing';
}
plan "no_plan";
isnt ($dbh, undef, 'Connect to database for savepoint testing');
my $pgversion = $dbh->{pg_server_version};
my $t;
my $str = 'Savepoint Test';
my $ids = sub {
my $ids = $dbh->selectcol_arrayref('SELECT id FROM dbd_pg_test WHERE pname = ? order by id asc',undef,$str);
return $ids;
};
SKIP: {
skip ('Cannot test savepoints on pre-8.0 servers', 2) if $pgversion < 80000;
my $sth = $dbh->prepare('INSERT INTO dbd_pg_test (id,pname) VALUES (?,?)');
## Create 500 without a savepoint
$sth->execute(500,$str);
## Create 501 inside a savepoint and roll it back
$dbh->pg_savepoint('dbd_pg_test_savepoint');
$sth->execute(501,$str);
$dbh->pg_rollback_to('dbd_pg_test_savepoint');
## savepoints can be re-used. Make 505 and roll it back.
$sth->execute(502,$str);
$dbh->pg_rollback_to('dbd_pg_test_savepoint');
## Create 502 after the rollback:
$sth->execute(503,$str);
$dbh->commit;
$t='pg_savepoint and pg_rollback_to - re-using a savepoint';
is_deeply($ids->(), [500, 503], $t);
## however, re-using a savepoint name also stacks them.
$dbh->pg_savepoint('dbd_pg_test_savepoint');
$sth->execute(504,$str);
$dbh->pg_savepoint('dbd_pg_test_savepoint');
$sth->execute(505,$str);
$dbh->pg_rollback_to('dbd_pg_test_savepoint');
$dbh->commit;
$t='duplicate savepoint name behaviour';
is_deeply($ids->(), [500, 503, 504], $t);
## we can also 'release' savepoints
$dbh->pg_savepoint('dbd_pg_test_savepoint');
$sth->execute(506,$str);
$dbh->pg_release('dbd_pg_test_savepoint');
# these ones should fail
eval { $dbh->pg_rollback_to('dbd_pg_test_savepoint') };
$t = "exception by rollback to released savepoint";
like($@, qr/no such savepoint/, $t);
eval { $sth->execute(507,$str) };
$t = "exception from trying to execute stuff";
like($@, qr/current.*aborted/, $t);
## this will be a forced rollback.
$dbh->commit;
$t = 'pg_release to release savepoint';
is_deeply($ids->(), [500, 503, 504], $t);
## test rollback with an actual exception
$dbh->pg_savepoint('dbd_pg_test_savepoint');
$sth->execute(508,$str);
$dbh->pg_savepoint('dbd_pg_test_savepoint');
$sth->execute(509,$str);
# we do something bad,
eval { $dbh->selectall_arrayref('gibbons') };
# say we detect the error and roll back,
$dbh->pg_rollback_to('dbd_pg_test_savepoint');
$sth->execute(510,$str);
$dbh->commit;
$t = 'no implicit rollback on statement deallocate';
is_deeply($ids->(), [500, 503, 504, 508, 510], $t);
# test what happens if you re-use savepoints and roll back twice
$dbh->do('SAVEPOINT dbd_pg_test_savepoint');
$sth->execute(511,$str);
$dbh->do('SAVEPOINT dbd_pg_test_savepoint');
$sth->execute(512,$str);
# we do something bad,
eval { $dbh->selectall_arrayref('gibbons') };
# the statement handle rolls back for us,
$dbh->do('ROLLBACK TO dbd_pg_test_savepoint');
# we detect the error and roll back as before,
$dbh->do('ROLLBACK TO dbd_pg_test_savepoint');
$sth->execute(513,$str);
$dbh->commit;
# ah, it's safe - because the first rollback didn't *release*
# the savepoint.
$t = 'rollback to savepoint - re-using rollbacks in the presence of stacking';
is_deeply($ids->(), [500, 503, 504, 508, 510, 511, 513], $t);
}
$dbh->do('DELETE FROM dbd_pg_test');
$dbh->commit();
cleanup_database($dbh,'test');
$dbh->disconnect();
