From: greg@turnstep.com
To: pgsql-general@postgresql.org
Subject: Re: DBI driver and transactions
In-Reply-To: <Pine.LNX.4.21.0302031636470.20150-100000@ponder.fairway2k.co.uk>
Date: Mon,  3 Feb 2003 19:26:11 -0000
Message-ID: <c90377a5703973d1dfb7944a2bebd792@biglumber.com>


-----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-----