pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedperl dbd libraries keeping transactions open?
4+ messages / 4 participants
[nested] [flat]
* perl dbd libraries keeping transactions open?
@ 2003-02-25 18:46 Adam Haberlach <adam@newsnipple.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adam Haberlach @ 2003-02-25 18:46 UTC (permalink / raw)
To: pgsql-interfaces
I'm not sure if this is the right place, but I've got
a question and potential issue.
We've got a system consisting of several daemons written
in perl and a web interface, and we are having issues with
the nightly vacuums not doing anything. It seems that this
is happening because there is always a transaction held open
by the libraries.
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?
If this isn't the right place, or if anyone knows a
better place to ask this, let me know...
--
Adam Haberlach | "Because manholes are round."
adam@mediariffic.com |
http://mediariffic.com |
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: perl dbd libraries keeping transactions open?
@ 2003-03-02 17:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam Haberlach <adam@newsnipple.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-03-02 17:55 UTC (permalink / raw)
To: Adam Haberlach <adam@newsnipple.com>; +Cc: pgsql-interfaces
Adam Haberlach <adam@newsnipple.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.
If that hasn't been fixed yet in the dbd driver, I would recommend
fixing it there.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: perl dbd libraries keeping transactions open?
@ 2003-03-02 19:02 Ian Barwick <barwick@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Ian Barwick @ 2003-03-02 19:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Adam Haberlach <adam@newsnipple.com>; +Cc: pgsql-interfaces
On Sunday 02 March 2003 18:55, Tom Lane wrote:
> Adam Haberlach <adam@newsnipple.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.
>
> If that hasn't been fixed yet in the dbd driver, I would recommend
> fixing it there.
The default DBD::Pg behaviour is AutoCommit On, and AFAIK
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.
perldoc DBD::Pg contains more info.
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: perl dbd libraries keeping transactions open?
@ 2003-03-03 02:10 Rudy Lippan <rlippan@remotelinux.com>
parent: Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Rudy Lippan @ 2003-03-03 02:10 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Adam Haberlach <adam@newsnipple.com>; pgsql-interfaces
On Sun, 2 Mar 2003, Ian Barwick wrote:
> Date: Sun, 2 Mar 2003 20:02:39 +0100
> From: Ian Barwick <barwick@gmx.net>
> To: Tom Lane <tgl@sss.pgh.pa.us>, Adam Haberlach <adam@newsnipple.com>
> Cc: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] perl dbd libraries keeping transactions open?
>
> On Sunday 02 March 2003 18:55, Tom Lane wrote:
> > Adam Haberlach <adam@newsnipple.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-03 02:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-25 18:46 perl dbd libraries keeping transactions open? Adam Haberlach <adam@newsnipple.com>
2003-03-02 17:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-02 19:02 ` Ian Barwick <barwick@gmx.net>
2003-03-03 02:10 ` Rudy Lippan <rlippan@remotelinux.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox