Re: DBI driver and transactions - Mailing list 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-----