Re: Transactions: DBI vs. SQL

John,

I've never used Postgres under Perl but that:

dbdpg: Warning: invalid done_begin turned off
<- rollback= 1 at new.pl line 38

looked suspicious. From the Postgres code:

if (PQTRANS_IDLE == tstatus) { /* Not in a transact
if (imp_dbh->done_begin) {
/* We think we ARE in a tranaction but we really are
not */
- if (dbis->debug >= 1) { PerlIO_printf(DBILOGFP,
"Warning: invalid done_begin turned off\n"); }
+ if (dbis->debug >= 3) { PerlIO_printf(DBILOGFP,
"Warning: invalid done_begin turned off\n"); }
imp_dbh->done_begin = 0;ion */

I draw your attention to the comment "We think we ARE in a tranaction but we
really are not". The above code seemed to end up being put in an ifdef PG74
later and then even later in a ifdef PGLIBVERSION >= 70400. So, I'd suggest it
may be Postgres library version related.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com



On 26-Mar-2006 John Siracusa wrote:
On 3/26/06 4:00 AM, Martin J. Evans wrote:
Tom Mornini wrote:
You need to turn AutoCommit off with:

my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', '...',
{ PrintError => 1, RaiseError => 0,
AutoCommit => 0 });

You should not have to turn autocommit off with begin_work:

Exactly. Even with AutoCommit initially on, that doesn't excuse the
behavior where calls to begin_work() and rollback() both appear to work and
give affirmative return values, but actually do nothing. Here's that code
extracted, but with fatal errors attached:

$dbh->begin_work or die "Could not begin work\n";
$dbh->do('INSERT INTO t1 (id, t2_id) VALUES (2, 1)');
$dbh->rollback or die "Could not roll back\n";

It sails right through that code without dying, but it doesn't actually
begin and then roll back a transaction. Here's the DBI trace of that
section of code:

-> begin_work for DBD::Pg::db (DBI::db=HASH(0x1833704)~0x1869bf0)
1 -> FETCH for DBD::Pg::db (DBI::db=HASH(0x1869bf0)~INNER 'AutoCommit')
dbdpg: dbd_db_FETCH (AutoCommit) dbh=25449128
1 <- FETCH= 1 at /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBI.pm
line 1655 via new.pl line 34
1 -> STORE for DBD::Pg::db (DBI::db=HASH(0x1869bf0)~INNER 'AutoCommit' 0)
dbdpg: dbd_db_STORE (AutoCommit) (0)
1 <- STORE= 1 at /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBI.pm
line 1657 via new.pl line 34
1 -> STORE for DBD::Pg::db (DBI::db=HASH(0x1869bf0)~INNER 'BegunWork' 1)
dbdpg: dbd_db_STORE (BegunWork) (1)
STORE DBI::db=HASH(0x1869bf0) 'BegunWork' => 1
1 <- STORE= 1 at /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBI.pm
line 1658 via new.pl line 34
<- begin_work= 1 at new.pl line 34
-> do for DBD::Pg::db (DBI::db=HASH(0x1833704)~0x1869bf0 'INSERT INTO t1
(id, t2_id) VALUES (2, 1)')
dbdpg: pg_quickexec (INSERT INTO t1 (id, t2_id) VALUES (2, 1))
dbdpg: _sqlstate
<- do= 1 at new.pl line 36
-> rollback for DBD::Pg::db (DBI::db=HASH(0x1833704)~0x1869bf0)
dbdpg: dbd_db_rollback (AutoCommit is 0) (BegunWork is 1)
dbdpg: dbd_db_txn_status
dbdpg: Warning: invalid done_begin turned off
<- rollback= 1 at new.pl line 38

-John
.

Quantcast