pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDBI driver and transactions
16+ messages / 6 participants
[nested] [flat]
* DBI driver and transactions
@ 2003-02-03 02:18 Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 21:28 ` Re: [INTERFACES] DBI driver and transactions Rudy Lippan <rlippan@remotelinux.com>
0 siblings, 1 reply; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-03 02:18 UTC (permalink / raw)
To: pgsql-general; +Cc: pgsql-interfaces@postgresql.org
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?
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...
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.
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?
and
d) I've said it before but what's the point of DBI if it's not to make life
easier?
This on a linux debian potato with:
Pg 7.2.3
Perl 5.8.0
DBI 1.30
DBD::Pg 1.13
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [INTERFACES] DBI driver and transactions
2003-02-03 02:18 DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-04 21:28 ` Rudy Lippan <rlippan@remotelinux.com>
2003-02-04 22:28 ` Re: [INTERFACES] DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 1 reply; 16+ messages in thread
From: Rudy Lippan @ 2003-02-04 21:28 UTC (permalink / raw)
To: Nigel J. Andrews <nandrews@investsystems.co.uk>; +Cc: pgsql-general@postgresql.org, <pgsql-interfaces@postgresql.org>
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.
$dbi->commit();
}; if (my $e = $@)
$dbi->rollback();
}
# $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.
-r
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [INTERFACES] DBI driver and transactions
2003-02-03 02:18 DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 21:28 ` Re: [INTERFACES] DBI driver and transactions Rudy Lippan <rlippan@remotelinux.com>
@ 2003-02-04 22:28 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-05 01:39 ` Re: [INTERFACES] DBI driver and transactions Rudy Lippan <rlippan@remotelinux.com>
0 siblings, 1 reply; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-04 22:28 UTC (permalink / raw)
To: Rudy Lippan <rlippan@remotelinux.com>; +Cc: pgsql-general; pgsql-interfaces@postgresql.org
On Tue, 4 Feb 2003, Rudy Lippan wrote:
> On Mon, 3 Feb 2003, Nigel J. Andrews wrote:
>
> > 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.
Yep, it was a disconnect that was doing that. What I wasn't expecting was a
disconnect on a cached connection (with others still open) to be rolling
back.
It sort of makes sense to do that on the assumption that each connection is
opened for a particular transaction and others are idle. However, there is only
one connection to the backend and to make that assumption the driver should
also not send any requests for a particular cached connection to the backend
until it sees a commit/rollback. Why? Well because it's silly to make the
assumption that a single cached connection close is terminating a transaction
when there can be another one interleaving queries with it. For example take
the situation:
Object or Library 1:
dbh1 = DBI->connect_cached
Object or Library 2:
dbh2 = DBI->connect_cached
main:
do_selects_in_lib1();
do_inserts_in_lib2();
close_lib1_without_commit();
...all of a sudden here we find lib2's inserts have been rolled back.
It may be that I've taken an odd design route and that a more usual one is to
create a connection and pass that around instead of letting subsystems create
their own (it's not as though I don't need to pass the information to make the
connection around).
My point, however, is that the connect_cached method is multiplexing request
channels to a single processing unit. It is not normal for one such channel to
invalidate all others, or indeed validate (commit), all others. On the one hand
DBI is saying each channel is a separate entity, otherwise it wouldn't rollback
when only one closes, but on the other hand all channels are one, otherwise it
wouldn't allow query interleaving, i.e. would have locking on the channel to
the server.
> > 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.
> $dbi->commit();
> }; if (my $e = $@)
> $dbi->rollback();
> }
> # $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.
Interesting. Just wondering though, why the comment on exit calling
commit? Within your eval block you've already done the commit and dbi won't
issue another one until another statement is executed.
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [INTERFACES] DBI driver and transactions
2003-02-03 02:18 DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 21:28 ` Re: [INTERFACES] DBI driver and transactions Rudy Lippan <rlippan@remotelinux.com>
2003-02-04 22:28 ` Re: [INTERFACES] DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-05 01:39 ` Rudy Lippan <rlippan@remotelinux.com>
2003-02-05 01:53 ` Re: [INTERFACES] DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 1 reply; 16+ messages in thread
From: Rudy Lippan @ 2003-02-05 01:39 UTC (permalink / raw)
To: Nigel J. Andrews <nandrews@investsystems.co.uk>; +Cc: pgsql-general@postgresql.org, <pgsql-interfaces@postgresql.org>
On Tue, 4 Feb 2003, Nigel J. Andrews wrote:
> > IIRC, It will do a rollback for you on disconnect.
>
> Yep, it was a disconnect that was doing that. What I wasn't expecting was a
> disconnect on a cached connection (with others still open) to be rolling
> back.
>
I do not think that connect_cached() is doing what you expect of it. When
you call disconnect() on your db handle it disconnects from the databse.
You were probably thinking of something like Apache::DBI which overloads
disconnect() so that it does not actually disconnect from the db.
> It sort of makes sense to do that on the assumption that each connection is
> opened for a particular transaction and others are idle. However, there is only
> one connection to the backend and to make that assumption the driver should
> also not send any requests for a particular cached connection to the backend
> until it sees a commit/rollback. Why? Well because it's silly to make the
> assumption that a single cached connection close is terminating a transaction
> when there can be another one interleaving queries with it. For example take
> the situation:
>
I think you are missing the point behind connect_cached()
> Object or Library 1:
> dbh1 = DBI->connect_cached
>
# DBI creates a connection to the database, and returns it. remembering
# that you connected with @stuff;
$dbh1 = DBI=>connect_cahced(@stuff);
> Object or Library 2:
> dbh2 = DBI->connect_cached
>
#DBI looks up sees that you already connected with @suff and returns
# a reference to handle that it already created (ie. $db1 == $dbh2)
$dbh2 = DBI->connect_cached(@stuff);
# now if you connect with @fooo -- you would get a different handle.
>
> main:
>
> do_selects_in_lib1();
> do_inserts_in_lib2();
> close_lib1_without_commit();
> ...all of a sudden here we find lib2's inserts have been rolled back.
>
because dbh1 == dbh2.
> It may be that I've taken an odd design route and that a more usual one is to
> create a connection and pass that around instead of letting subsystems create
> their own (it's not as though I don't need to pass the information to make the
> connection around).
If I can get away with it, I put $dbh in a global/package global.
>
> My point, however, is that the connect_cached method is multiplexing request
> channels to a single processing unit. It is not normal for one such channel to
> invalidate all others, or indeed validate (commit), all others. On the one hand
> DBI is saying each channel is a separate entity, otherwise it wouldn't rollback
> when only one closes, but on the other hand all channels are one, otherwise it
> wouldn't allow query interleaving, i.e. would have locking on the channel to
> the server.
>
connect_cached() is not multiplexing anything. It just returns a cached
copy of a valid database handle. Everything is going over one connection
to the backend because there is only one object talking to the backend.
If you were to drop the connect_cached() and do two connect calls you
would get the behavior that you expect. Well, Except (maybe) for the
transaction behavour (depending on how you are using transactions) because
then you will be looking at to different data base connexions with
separate transactions -- And I don't think you want to go there.
>
> > sub stuff {
> > local($dbi->{AutoCommit}) = 0;
> >
> > eval {
> > #do transaction stuff.
> > $dbi->commit();
> > }; if (my $e = $@)
> > $dbi->rollback();
> > }
> > # $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.
>
> Interesting. Just wondering though, why the comment on exit calling
> commit? Within your eval block you've already done the commit and dbi won't
> issue another one until another statement is executed.
>
I ment on exit from the sub, or more correctly the exit from the scope in
which local() was called. The reason for the commit is that
$dbi->{AutoCommit} gets changed from a value of 0 to a value of 1
(assuming that the value of $dbi->{AutoCommit} was 1 when you entered the
sub, of course). When autocommit is turned on DBD::Pg issues a commit.
And remember you are in transaction because the $dbi->commit/rollback()
automatically started another transaction for you.
-r
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [INTERFACES] DBI driver and transactions
2003-02-03 02:18 DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 21:28 ` Re: [INTERFACES] DBI driver and transactions Rudy Lippan <rlippan@remotelinux.com>
2003-02-04 22:28 ` Re: [INTERFACES] DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-05 01:39 ` Re: [INTERFACES] DBI driver and transactions Rudy Lippan <rlippan@remotelinux.com>
@ 2003-02-05 01:53 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 0 replies; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-05 01:53 UTC (permalink / raw)
To: Rudy Lippan <rlippan@remotelinux.com>; +Cc: pgsql-general; pgsql-interfaces@postgresql.org
On Tue, 4 Feb 2003, Rudy Lippan wrote:
> On Tue, 4 Feb 2003, Nigel J. Andrews wrote:
>
> > Object or Library 1:
> > dbh1 = DBI->connect_cached
> >
> # DBI creates a connection to the database, and returns it. remembering
> # that you connected with @stuff;
>
> $dbh1 = DBI=>connect_cahced(@stuff);
>
> > Object or Library 2:
> > dbh2 = DBI->connect_cached
> >
> #DBI looks up sees that you already connected with @suff and returns
> # a reference to handle that it already created (ie. $db1 == $dbh2)
> $dbh2 = DBI->connect_cached(@stuff);
>
>
> # now if you connect with @fooo -- you would get a different handle.
> >
> > main:
> >
> > do_selects_in_lib1();
> > do_inserts_in_lib2();
> > close_lib1_without_commit();
> > ...all of a sudden here we find lib2's inserts have been rolled back.
> >
> because dbh1 == dbh2.
>
> ...
>
> >
> > My point, however, is that the connect_cached method is multiplexing request
> > channels to a single processing unit. It is not normal for one such channel to
> > invalidate all others, or indeed validate (commit), all others. On the one hand
> > DBI is saying each channel is a separate entity, otherwise it wouldn't rollback
> > when only one closes, but on the other hand all channels are one, otherwise it
> > wouldn't allow query interleaving, i.e. would have locking on the channel to
> > the server.
> >
> connect_cached() is not multiplexing anything. It just returns a cached
> copy of a valid database handle. Everything is going over one connection
> to the backend because there is only one object talking to the backend.
> If you were to drop the connect_cached() and do two connect calls you
> would get the behavior that you expect. Well, Except (maybe) for the
> transaction behavour (depending on how you are using transactions) because
> then you will be looking at to different data base connexions with
> separate transactions -- And I don't think you want to go there.
Bingo. Thanks for the explanation. You are indeed correct I was
misunderstanding the cached connection stuff was doing.
I thought it was doing something more like how I changed my usage to use
connect() and a reference count. My connection only gets closed when there the
reference count drops back to zero. To me that's much more sensible a scheme.
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
@ 2003-02-03 03:37 Lincoln Yeoh <lyeoh@pop.jaring.my>
0 siblings, 0 replies; 16+ messages in thread
From: Lincoln Yeoh @ 2003-02-03 03:37 UTC (permalink / raw)
To: Nigel J. Andrews <nandrews@investsystems.co.uk>; pgsql-general; +Cc: pgsql-interfaces@postgresql.org
Do you want autocommit on or off?
With autocommit off, use $dbh->rollback to do ROLLBACK followed by BEGIN
and use $dbh->commit to do COMMIT followed by BEGIN.
Don't use the $dbh->do stuff for that sort of thing.
It could be you, or could be DBI, depends on what you really want to do
(can't tell from your message). If you are trying to catch asynchronous
notices with autocommit off, DBI might not be a good choice.
But if you're trying to do run of the mill stuff, it's probably you :).
Link.
At 02:18 AM 2/3/03 +0000, 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?
>
>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...
>
>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.
>
>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?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
@ 2003-02-03 08:06 Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 1 reply; 16+ messages in thread
From: Christoph Dalitz @ 2003-02-03 08:06 UTC (permalink / raw)
To: nandrews@investsystems.co.uk; +Cc: pgsql-general
> 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.
Christoph Dalitz
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
@ 2003-02-03 09:13 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 09:31 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
0 siblings, 2 replies; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-03 09:13 UTC (permalink / raw)
To: Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>; +Cc: 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 them before 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-03 09:31 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
1 sibling, 0 replies; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-03 09:31 UTC (permalink / raw)
To: Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>; +Cc: pgsql-general
On Mon, 3 Feb 2003, Nigel J. Andrews wrote:
> 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.
>
> [lots of my blurb edited out]
>
> 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 them before 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
>
> [lots of my blurb edited out]
>
> 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?
>
One that did also occur to me is that I don't call finish before methods which
have done prepare/execute and that many times I don't use a loop to retrieve
data until one of the fetchrow_...() methods says there isn't any left. Could
this be the cause of my problems? Somehow messing with DBI's mind and making it
run out of statment handles? It does say finish or destroy handle and the
handles should be destroyed when they drop out of scope shouldn't they?
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-03 10:27 ` Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` Re: DBI driver and transactions greg@turnstep.com
1 sibling, 1 reply; 16+ messages in thread
From: Lincoln Yeoh @ 2003-02-03 10:27 UTC (permalink / raw)
To: Nigel J. Andrews <nandrews@investsystems.co.uk>; Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>; +Cc: pgsql-general
At 09:31 AM 2/3/03 +0000, Nigel J. Andrews wrote:
>One that did also occur to me is that I don't call finish before methods which
>have done prepare/execute and that many times I don't use a loop to retrieve
>data until one of the fetchrow_...() methods says there isn't any left. Could
>this be the cause of my problems? Somehow messing with DBI's mind and
>making it
>run out of statment handles? It does say finish or destroy handle and the
>handles should be destroyed when they drop out of scope shouldn't they?
Yep before you disconnect/quit, you're supposed to finish active
statements. e.g. prepare, execute, use up results, or call finish if you
don't need the rest of the results.
DBI by default buffers results, so if you don't call finish the buffered
results hang around, and if you just quit abruptly without tidying stuff
up, it grumbles (but still should clean up).
HTH,
Link.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
@ 2003-02-03 14:15 ` greg@turnstep.com
2003-02-03 14:49 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 1 reply; 16+ messages in thread
From: greg@turnstep.com @ 2003-02-03 14:15 UTC (permalink / raw)
To: pgsql-general
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Yep before you disconnect/quit, you're supposed to finish active
> statements. e.g. prepare, execute, use up results, or call finish if you
> don't need the rest of the results.
Exactly. The error only appears after you have done a prepare *and* a
select, with no concomitant finish or fetching. Here is a code sample:
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
{AutoCommit=>0, RaiseError=>1, PrintError=>0});
my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");
my $count = $sth->execute(120);
## Exiting here will cause the warning described
if ($count eq "0E0") {
$sth->finish();
}
else {
my $info = $sth->fetchall_arrayref({});
## Do something with info...
}
## Exiting is now safe: commit and disconnect are separate issues...
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302030912
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+PnomvJuQZxSWSsgRAhTMAKDLEzXWlGdEQJWKZ72qafkkBL9PRQCfVuh3
uFGqo2u41jwQFONCT9VBAks=
=axjF
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` Re: DBI driver and transactions greg@turnstep.com
@ 2003-02-03 14:49 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 16:58 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 11:00 ` Re: DBI driver and transactions Peter Haworth <pmh@edison.ioppublishing.com>
0 siblings, 2 replies; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-03 14:49 UTC (permalink / raw)
To: greg@turnstep.com; +Cc: pgsql-general
On Mon, 3 Feb 2003 greg@turnstep.com wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > Yep before you disconnect/quit, you're supposed to finish active
> > statements. e.g. prepare, execute, use up results, or call finish if you
> > don't need the rest of the results.
>
> Exactly. The error only appears after you have done a prepare *and* a
> select, with no concomitant finish or fetching. Here is a code sample:
>
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
> {AutoCommit=>0, RaiseError=>1, PrintError=>0});
>
> my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");
>
> my $count = $sth->execute(120);
>
> ## Exiting here will cause the warning described
>
> if ($count eq "0E0") {
> $sth->finish();
> }
> else {
> my $info = $sth->fetchall_arrayref({});
> ## Do something with info...
> }
>
> ## Exiting is now safe: commit and disconnect are separate issues...
Thanks for that clarification. I hadn't been worrying about it in my code.
With the database design I either expect 1 or 0 rows from queries in which I
treat which ever is appropiate as a problem or I am expecting 0 <= n in which
case I do fetch all the results.
I presume that the issue here is that in the 1 or 0 rows returned case where 1
row indicates I have a problem then I am not fetching from or finishing that
statement. Although, in the debugger I've seen the Active flag still set after
doing a single fetch on a 1 row resultset.
Now I'm just going to added finishes everywhere. Nasty, time consuming and
probably excesive but also probably less time consuming that trying to be
selective and missing one that's necessary.
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` Re: DBI driver and transactions greg@turnstep.com
2003-02-03 14:49 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-03 16:58 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 19:26 ` Re: DBI driver and transactions greg@turnstep.com
1 sibling, 1 reply; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-03 16:58 UTC (permalink / raw)
To: greg@turnstep.com; +Cc: pgsql-general
Ok, bug found, and I didn't even have to go through all my code inserting
finish() calls. Reusing Greg's example code, the problem was in a method
(actually a constructor) like:
> On Mon, 3 Feb 2003 greg@turnstep.com wrote:
> >
> > > Yep before you disconnect/quit, you're supposed to finish active
> > > statements. e.g. prepare, execute, use up results, or call finish if you
> > > don't need the rest of the results.
> >
> > Exactly. The error only appears after you have done a prepare *and* a
> > select, with no concomitant finish or fetching. Here is a code sample:
> >
package blah;
use DBI;
sub new {
my $self = {};
my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
{AutoCommit=>0, RaiseError=>1, PrintError=>0});
$self->{dbh} = $dbh;
my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");
my $count = $sth->execute(120);
## Exiting here will cause the warning described
return undef unless $count;
if ($count eq "0E0") {
$sth->finish();
}
else {
my $info = $sth->fetchall_arrayref({});
## Do something with info...
}
## Exiting is now safe: commit and disconnect are separate issues...
return bless($self,'blah');
}
So, the question is where's the error there?
I might well completely have the wrong idea about perl and what happens with
lexically scoped variables but to me that says $sth gets destroyed because it
drops out of scope, even if the first exit from the function is taken. If not
then that says all lexically scoped variables remain allocated until explicitly
destroyed. So I could have a function creating and storing huge amounts of data
in 'my' variables and that data will still be stored, adding to the processes
memory footprint, until my process exits. That could be years.
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` Re: DBI driver and transactions greg@turnstep.com
2003-02-03 14:49 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 16:58 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-03 19:26 ` greg@turnstep.com
2003-02-03 19:57 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 1 reply; 16+ messages in thread
From: greg@turnstep.com @ 2003-02-03 19:26 UTC (permalink / raw)
To: pgsql-general
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> So, the question is where's the error there?
>
> I might well completely have the wrong idea about perl and what happens with
> lexically scoped variables but to me that says $sth gets destroyed because it
> drops out of scope, even if the first exit from the function is taken. If not
> then that says all lexically scoped variables remain allocated until explicitly
> destroyed. So I could have a function creating and storing huge amounts of data
> in 'my' variables and that data will still be stored, adding to the processes
> memory footprint, until my process exits. That could be years.
You first assumption is correct: if a statement handle goes out of scope, it
no longer can be "Active" or "Inactive". What happens is this: when the
disconnect method is called, it polls all the current statement handles to
see if any of them are still have the Active flag set. If they do, it throws
the error you see. However, destroying a statement handle (e.g. by leaving the
scope in which its variable is declared) does not do any checking, and the
Active flag is ignored. In other words:
my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
$sth->execute("Mustard","library","wrench");
$dbh->disconnect();
This throws the error. DBI is saying: Hey! You just went through all the
trouble of making this query, don't you want to see all the results?
{
my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
$sth->execute("Peacock","kitchen","candlestick");
}
## $sth is gone
$dbh->disconnect();
No error is thrown in this case, as the variable $sth fails to exist at the
end of the unnamed block, so the disconnect method has no way of knowing anything
about the statement handle you created. In practice, finish() is not called
very often, as you usually want to fetch everything you asked for. It's a nice
short-circuit as in my first example, however, as it is more efficient than
calling a fetch method if you know there is nothing there.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302031426
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+PsKkvJuQZxSWSsgRArYYAKDDXvaJOerEOoHJoMtn+oxj71JzcACfSSV4
wGMsZL0nl3LUHW8Mrdn5Xu8=
=RWel
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` Re: DBI driver and transactions greg@turnstep.com
2003-02-03 14:49 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 16:58 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 19:26 ` Re: DBI driver and transactions greg@turnstep.com
@ 2003-02-03 19:57 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 0 replies; 16+ messages in thread
From: Nigel J. Andrews @ 2003-02-03 19:57 UTC (permalink / raw)
To: greg@turnstep.com; +Cc: pgsql-general
On Mon, 3 Feb 2003 greg@turnstep.com wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > So, the question is where's the error there?
> >
> > I might well completely have the wrong idea about perl and what happens with
> > lexically scoped variables but to me that says $sth gets destroyed because it
> > drops out of scope, even if the first exit from the function is taken. If not
> > then that says all lexically scoped variables remain allocated until explicitly
> > destroyed. So I could have a function creating and storing huge amounts of data
> > in 'my' variables and that data will still be stored, adding to the processes
> > memory footprint, until my process exits. That could be years.
>
> You first assumption is correct: if a statement handle goes out of scope, it
> no longer can be "Active" or "Inactive". What happens is this: when the
> disconnect method is called, it polls all the current statement handles to
> see if any of them are still have the Active flag set. If they do, it throws
> the error you see.
Funny you should say that because one of the things I did to try and fix the
problem was basically that only I did a finish instead of throwing an error.
> However, destroying a statement handle (e.g. by leaving the
> scope in which its variable is declared) does not do any checking, and the
> Active flag is ignored. In other words:
>
> my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
> $sth->execute("Mustard","library","wrench");
> $dbh->disconnect();
>
> This throws the error. DBI is saying: Hey! You just went through all the
> trouble of making this query, don't you want to see all the results?
>
> {
> my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
> $sth->execute("Peacock","kitchen","candlestick");
> }
> ## $sth is gone
> $dbh->disconnect();
>
>
> No error is thrown in this case, as the variable $sth fails to exist at the
> end of the unnamed block, so the disconnect method has no way of knowing anything
> about the statement handle you created. In practice, finish() is not called
> very often, as you usually want to fetch everything you asked for. It's a nice
> short-circuit as in my first example, however, as it is more efficient than
> calling a fetch method if you know there is nothing there.
It doesn't explain why in your previous example that I changed slightly I had
to add an explicit call to finish before returning early in order to avoid the
error being raised. Indeed in the application the only reason I have a database
connection available in the level I am commiting, which is where I came across
the problem, is to do the transaction control. All other work is done in object
methods and all statement handles are scoped within the methods only and so out
of scope at commit time.
Unfortunately, there might also have been some other effects in play that
changed as the app. changed data (in auto commit mode) as I've now reverted to
not scanning statement handles myself before disconnecting and I've got errors
again. However, this is just one script out of two I've currently done towards
the application. Both use many of the same classes and the first does not
generate this error.
Anyway, I still think there is something very odd going on with this stuff/perl
but accept that that is how it is and am living with it, going through bloating
the code by adding finishes all over the place and being very paranoid about
where exactly I'm putting them. I will just remember for next time I use DBI
that it's got these idioscracies.
Thanks for the input on this.
--
Nigel J. Andrews
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: DBI driver and transactions
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` Re: DBI driver and transactions greg@turnstep.com
2003-02-03 14:49 ` Re: DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
@ 2003-02-04 11:00 ` Peter Haworth <pmh@edison.ioppublishing.com>
1 sibling, 0 replies; 16+ messages in thread
From: Peter Haworth @ 2003-02-04 11:00 UTC (permalink / raw)
To: Nigel J. Andrews <nandrews@investsystems.co.uk>; greg@turnstep.com; +Cc: pgsql-general
On Mon, 3 Feb 2003 14:49:39 +0000 (GMT), Nigel J. Andrews wrote:
> I presume that the issue here is that in the 1 or 0 rows returned case
> where 1 row indicates I have a problem then I am not fetching from or
> finishing that statement. Although, in the debugger I've seen the Active
> flag still set after doing a single fetch on a 1 row resultset.
The problem is that the statement doesn't get marked inactive until you have
fetched the row after the last one:
# There are some rows to fetch here
$sth1->execute; # Active
...
$sth1->fetch; # Last row returned, still active
$sth1->fetch; # undef returned, now it's inactive
# No rows to fetch in this case
$sth0->execute; # Active
$sth0->fetch; # undef returned, now it's inactive
As you can see, this behaviour is necessary in order that empty result sets
can be recognised. Otherwise you'd be fetching on an inactive handle.
--
Peter Haworth pmh@edison.ioppublishing.com
Hi, this is Ken. What's the root password?
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2003-02-05 01:53 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-03 02:18 DBI driver and transactions Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 21:28 ` Rudy Lippan <rlippan@remotelinux.com>
2003-02-04 22:28 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-05 01:39 ` Rudy Lippan <rlippan@remotelinux.com>
2003-02-05 01:53 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 03:37 Re: DBI driver and transactions Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 08:06 Re: DBI driver and transactions Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
2003-02-03 09:13 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 09:31 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 10:27 ` Lincoln Yeoh <lyeoh@pop.jaring.my>
2003-02-03 14:15 ` greg@turnstep.com
2003-02-03 14:49 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 16:58 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-03 19:26 ` greg@turnstep.com
2003-02-03 19:57 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-02-04 11:00 ` Peter Haworth <pmh@edison.ioppublishing.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