Re: perl dbd libraries keeping transactions open?

On Sun, 2 Mar 2003, Ian Barwick wrote:

> Date: Sun, 2 Mar 2003 20:02:39 +0100
> From: Ian Barwick <barwick(at)gmx(dot)net>
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Adam Haberlach <adam(at)newsnipple(dot)com>
> Cc: pgsql-interfaces(at)postgresql(dot)org
> Subject: Re: [INTERFACES] perl dbd libraries keeping transactions open?
>
> On Sunday 02 March 2003 18:55, Tom Lane wrote:
> > Adam Haberlach <adam(at)newsnipple(dot)com> writes:
> > > It seems that the implementation is for the libs to
> > > do a BEGIN on connect and after every transaction -- is
> > > this normal. Is there a way to keep it from causing
> > > problems with vaccuums?
> >
> > This is horrible practice for a number of reasons, not only its effect
> > on VACUUM. Consider what you will get from now(), for example. A new
> > BEGIN should only be issued when you are ready to issue the first
> > command of the next transaction.

Ah yes, but that begs the question: what is the first command, because
DBD::Pg will talk to the DB independently of the application. So does
this mean that we issue the begin when a user does a $dbh->tables;
$dbh->column_info; $dbh->prepare; $dbh->rollback; $dbh->ping(); What
this means is that you can have commands other than $sth->execute(); that
can just begin a tx on you.

> >
> > If that hasn't been fixed yet in the dbd driver, I would recommend
> > fixing it there.
>

DBD::Pg still has the old autocommit behavour. There is a patch on gborg,
'http://gborg.postgresql.org/project/dbdpg/bugs/bugupdate.php?398' that
suposedly fixes this problem; however, it does not answer all of the
questions that I have as to when to do the BEGIN when in autotx mode, and
I think it might be better to use PostgreSQL's SET AUTOCOMMIT (if avail).

> The default DBD::Pg behaviour is AutoCommit On, and AFAIK

The default DBI behaviour is AutoCommit On.

> has always been that way. The DBD::Pg driver will only
> begin a transaction if AutoCommit was explicitly set to Off
> or $dbh->begin_work() was executed by the application.

I don't think $dbh->begin_work() is supported by DBD::Pg right now and
will not do things as you expect:

(note: I have not tested the following code)

$dbh->begin_work(); # begin tx.
eval {
$sth->execute();
$dbh->commit(); # rollback tx and start a new one.

}; if (my $e = $@) {
$dbh->rollback(); #rollback tx and start a new one.
}

# we are now in tx.