[ruby-dbi-users] dbd_pg and deallocating prepared statements

[Erik Hollensbe]

On Monday 01 September 2008 22:39:09 KUBO Takehiro wrote:
> The following patch will release prepared statements in GC even though they
> are not finished explicitly.
>    sth = dbh.prepare('select * from names')
>    sth.execute
>      ...
>    sth = nil # forget to finish the statement handle.
>    GC.start # <= sth's prepared statement is deallocated by GC.

Sorry this is so long. KUBO, If you could be so kind as to post this patch on 
the rubyforge patches tracker, it will make it much easier for me to track, as 
I can't integrate it this moment (for more than just the reasons described 
here). 

This is actually a patch I've been wanting to do for a while, but really had 
no good grasp on how to do it. Ruby finalizers are still a bit of a black art 
to me since they aren't really a destructor, but this patch definitely tries 
to use them like one.

I'll read up on finalizers.

However, my biggest concern right now is with the database handle itself, as 
it seems that the dbh instance becomes co-dependent with the statement handle 
for garbage collection. While I certainly don't have a better solution to this 
problem at the moment, the way this is done circumvents the normal chain of 
events:

f.e.:

dbh is created, sth is created. sth does not finish. dbh is disconnected. 
program continues.

at this point there is still a dbh instance (which in every case holds an 
instance of the underlying database driver) that is inoperable.

The statement handle, even when gc'd, would not clean up it's postgresql level 
statement because it would be unable to do so, since the database is 
disconnected. Now, PostgreSQL may do this for us automatically, but I do think 
it's worth considering that it doesn't, or a similar pattern in another DBD 
would render this problem, at least for the sake of this argument.

I should clarify that one of my long-term goals with Ruby/DBI is to enforce 
policy more in DBI itself and alleviate DBDs from having to implement policy 
unless absolutely necessary; one of the largest sources of bug reports is DBI 
*saying* one thing and the DBD *doing* something else, which DBI does not 
properly prevent.

So, while I think a finalizer used in this fashion would be a good way to 
handle this problem, I think there are higher-level concerns. 0.4.0 currently 
expects the user to finish all their statement handles, and I don't think 
changing this functionality in one DBD solves the problem.

So, that brings us to 0.6.0. 

One thing that *all* DBDs would benefit from is a DatabaseHandle modification 
to #prepare (and friends) that stores the newly created StatementHandle before 
passing it off. At this point, we have all the information required to finish 
handles before disconnection happens. (In the event of premature termination, 
we can't do much anyways,)

Also, if we did this finalizer at a higher level (StatementHandle or maybe 
even BaseStatement) all DBDs would be able to leverage this functionality, 
allowing DBI to manage the process up to the point that the DBD needs to get 
involved. 

It also leaves open the opportunity for DatabaseHandle finalizers that operate 
in a similar fashion, and preserves the chain of creation and destruction 
without side effects. It's also a consistent process that benefits all DBI 
users.

So, to boil this down I think your idea is splendid, and I want to expand on 
it, but I don't think it's the full solution, nor is it something that can be 
reasonably considered for a patchlevel release. What do you think?

-Erik