Re: DBI driver and transactions - Mailing list pgsql-general

On Mon, 3 Feb 2003, Christoph Dalitz wrote:

> > Date: Mon, 3 Feb 2003 02:18:11 +0000 (GMT)
> > From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
> >
> > With AutoCommit => 0 I see a BEGIN logged right before the
> > first query I send...
> >
> This is in compliance to the SQL2 standard, which says that
> transactions implicitly begin with the first DML command after
> the last commit/rollback. As Postgres has explicit transaction
> beginning, the DBD-Pg driver must emulate the SQL2 behaviour by
> automatically issuing a BEGIN statement.
>
> DBI provides the methods begin(), commit() and rollback() for
> transaction management; begin() usually does nothing and thus
> does no harm when you call it.

Thanks for the replies folks.

Indeed I understand that. I wasn't surprised to see the begin withheld until
the first real query. What I was completely baffled by and probably didn't
explain clearly in the next paragraph was why there was a rollback issued later
but before I had even considered issuing commit/rollback in my code.

So just to clarify. To use transactions with DBI:

$dbh = DBI->connect_cached("blah",  "more blah", "secret",
                           { AutoCommit => 0, Raise Error => 1 });
...
$dbh->prepare("SELECT...");
...
$dbh->prepare("INSERT...");
...
$dbh->prepare("INSERT...");

$dbh->commit;

...more stuff

?

So any ideas what this message on that commit is?

disconnect(DBI::db=HASH(0x87af40c)) invalidates 1 active statement. Either destroy statement handles or call finish on
thembefore disconnecting. at /home/nandrews/src/www/lib/LtS/perl/DB.pm line 128. 
        DB::disconnect('Delegate=HASH(0x87afeb0)') called at /my/directory/modulesA/DB.pm line 144
        DB::DESTROY('Delegate=HASH(0x87afeb0)') called at /my/directory/modules/BookingSummaryPage.pm line 142
        eval {...} called at /my/directory/modules/BookingSummaryPage.pm line 142

Sorry for the formating, I didn't want to add line breaks where the original
message doesn't have them. The way this just refuses to work for me I wouldn't
be surprised if such things were critically important.

DB is a wrapper class I have which uses DBI and DB::disconnect calls
DBI::disconnect. Delegate is one of my classes and happens to be the last one
which issued an insert or update down the connection. However, the commit call
is made after that particular operation has completed, i.e. the commit occurs
after calling a method of a different class which does an insert. Delegate
inherits from DB, via another intermdiary class.

I fail to see where I'm going wrong and what that error message is really
saying. I'm going to switch to using normal connects to see if that makes any
difference, although I'm pretty sure I was getting similar messages at an
earlier stage of development before I switched away from my own connection
caching to use DBI's.

BTW, is there a better list for discussing such DBI questions?


--
Nigel J. Andrews