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

KUBO Takehiro kubo at jiubao.org
Thu Sep 4 02:18:46 EDT 2008


On Wed, Sep 3, 2008 at 5:37 AM, Erik Hollensbe <erik at hollensbe.org> wrote:
> 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).

Okay. I'll submit it.

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

PostgreSQL does it automatically.

    If you do not explicitly deallocate a prepared statement, it is
    deallocated when the session ends.

IMO, if server side statement resources are not freed even though the
connection is closed, it is a bug of the DBMS. If such DBMS exists, it
should be solved by native database drivers (for example: ruby-oci8,
ruby-pg, MySQL/Ruby, etc.) for who use them directly. Once the native
drivers solve the problem, dbi has no need to care about it.

The only exception is PostgreSQL's server side prepared statement.
As for other native drivers, statement handle instances correspond to
server side statement resources. When a dbi statement handle is GC'd,
the native driver's statement handle will be GC'd in a short time
and the associated server side statement is also freed. But
PostgreSQL's server side prepared statement is identified by a
name. ruby-pg has no way to know whether the name will be used later
or not. ruby-pg users may use another String instance which has same

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

Another reason: It is easy for C extension libraries such as native
database drivers to use a finalizer, but not for pure ruby libraries
such as ruby-dbi. As for C extension libraries, it is just adding a C
callback function. The function called just before the object is
GC'd. It means that the object is alive at the point of time. But a
ruby proc object which is registerd by ObjectSpace.define_finalizer
can only know the GC'd object's id. The object itself has been already
GC'd. What you want to do is very difficult.

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

I think ruby-dbi has no need to use finalizers except dbd_pg statement
handle. It changes dbd_pg only. It is considered for a patchlevel
release. But I have another issue of dbd_pg, which will be posted to a
new thread.

More information about the ruby-dbi-users mailing list