Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] DBI driver and transactions

From: Rudy Lippan <rlippan(at)remotelinux(dot)com>
To: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org, <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: [INTERFACES] DBI driver and transactions
Date: 2003-02-04 21:28:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-interfaces
On Mon, 3 Feb 2003, Nigel J. Andrews wrote:

> I'm getting very odd behaviour and am getting quite frustrated at fighting this
> thing. So just so I know for the future does anyone know if there are problems
> with using transactions when using cached dbi handles?
You will have connections that are idle in transaction on the DB
server for the duration of the time that you hold a $dbh's handle  that 
is not AutoCommit=1  :(

> I was using my own caching of the handle(s) but I didn't have time to work out
> who, what, why and where someone was disconnecting it so switched to the DBI
> caching connect. Note, this is NOT persistent/pooled connections.
> However, either there is something wrong (and it's been fixed since I loaded
> the software) or I completely fail to grasp the concept of transactions as
> understood by DBI. 
> With AutoCommit => 0 I see a BEGIN logged right before the
> first query I send...

That is how DBD::Pg starts a transaction. When you do a AutoCommit=0,
DBD::Pg sends a BEGIN.  And when you do a $dbh->commit()/$dbh->rollback(),
DBD::Pg will do a COMMIT and then a BEGIN (there has been some discussion
on this  commit() behavior on dbi-dev)

> Oh I give up. Trying to clarify what happens when I'm now seeing rollbacks
> issued in AutoCommit => mode where I'm not doing any and it's certainly not me
> since I've got a trap on my interface layer to DBI for that and it's not been
> tripped at all.
IIRC, It will do a rollback for you on disconnect.

> So, 
> a) anyone know of any problems,
> b) can I assume it is me and not just give up on this DBI stuff in the future (
> I haven't got the time to rewrite my application now; not that I can't see it
> being a particularly onerous task for what I use from DBI )
> c) can I just issue BEGIN, COMMIT, ROLLBACK when I want to via normal
> $dbh->do(...) without causing myself even more problems?

Yes and no. DBD::Pg will stop you from calling those commands;  
however, I think that you can trick DBD::Pg by make it not look like a
transaction command (eg. $dbh->do(q{/* Trick Pg */ BEGIN}) ); although, I 
have not tried it, and it is not recommended.

I, for the most part, do:

sub stuff {
    local($dbi->{AutoCommit}) = 0;

    eval {
        #do transaction stuff.
    }; if (my $e = $@)
   # $on exit $dbi->{AutoCommit} will go to 1 and DBD::Pg will call 
   # commit() for you

DBI now supports a begin_work method, but DBD::Pg does not have 
support for it at this time.


In response to


pgsql-interfaces by date

Next:From: Nigel J. AndrewsDate: 2003-02-04 22:28:14
Subject: Re: [INTERFACES] DBI driver and transactions
Previous:From: Bruce MomjianDate: 2003-02-04 11:23:13
Subject: Re: MOVE LAST: why?

pgsql-general by date

Next:From: Nigel J. AndrewsDate: 2003-02-04 22:28:14
Subject: Re: [INTERFACES] DBI driver and transactions
Previous:From: Stephan SzaboDate: 2003-02-04 21:05:58
Subject: Re: not exactly a bug report, but surprising behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group