[ruby-dbi-users] dbd_pg and deallocating prepared statements
erik at hollensbe.org
Thu Sep 4 06:23:24 EDT 2008
On Wednesday 03 September 2008 23:18:46 KUBO Takehiro wrote:
> 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
Even though this is the case, the problem still exists that the statement will
attempt to deallocate on a disconnected database. While that check can be done
in the finalizers, I'd like to see support at a higher level for this kind of
operation; statements shouldn't exist when corresponding database handles
don't, and so on.
The current situation (where Pg is the only DBD distributed with DBI that does
server-side prepared statements) is the result of poor support in the DBDs
that work against databases with prepared statement support.
> 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.
I still have to read up on this; so I apologize, but I'm answering off the
cuff. Is this really DBI's problem at this point, or a poor separation of
powers on the part of the DBD design?
I guess I'm bringing this up because the way most of the DBDs are designed is
to keep at least two layers between the C code and the end-user classes...
BaseDatabase and DatabaseHandle respectively. You can see that in your
solution that spurred this conversation: at no point are any dangling PGresult
handles being cleaned up, or even recognized in the finalizers, and that's OK,
because ruby (and likely ruby-pg) will take care of that for us naturally.
> > 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.
Again, sorry for being a windbag, but I really understand your reasoning and
want to make it undeniably clear why I disagree with the timing and think it's
best to wait.
Using finalizers is not the issue I'm attempting to address here, it's an
implementation detail of the problem, which is that #finish must be called
explicitly right now, and both of us agree that it shouldn't be necessary.
What we disagree on is where this should be handled, or more precisely, what's
the path that's taken to define these handlers.
Obviously, as your patch demonstrates and we both know, the only thing keeping
this behavior from happening with the Pg driver is for me to integrate your
However, DBI claims to have a consistent interface to SQL and in many cases it
doesn't, and a good 90% of the reasons for this is because different DBDs do
the same things different ways, and DBI allows it. If my words don't help,
take a look at the DBD test suite, which grows one test at a time per bug, and
has become decently sized since I built it against the original CVS repository
checkout I started with. DBI proper, in contrast, has remained relatively
untouched; the only test modifications (and bugs) have been related to feature
You can also see this in the bug reports, where most users are expecting
something that DBI claims it does and it clearly does not, because the user is
on MySQL or SQLite and the only place that works properly is on Pg. Normally,
the problem has nothing to do with the databases themselves, but the DBD that
assumes too much interface. I assert this is DBI's problem.
Nothing is requiring DBDs to define finalizers, just if they do to use the
interface provided. The goal is to ensure #finish and #disconnect work
consistently; at that point it's the DBDs problem to define an appropriate
finalizer ("none" may be appropriate) as DBI has met its part of the contract.
If we want to contrast this with Perl's DBI, there are several (simpler) DBDs
that define little more than a few hash entries and a fetch method. That can't
be done right now (or get remotely close to it) in Ruby/DBI, and I consider
that a design fault.
Another way of looking at it: DBDs should have tunnel vision and DBI maintains
the big picture.
That said, I appreciate your input and effort on this, and I can guarantee
that as soon as I can find enough time to move forward on 0.6.0, this will be
the first thing I tackle.
More information about the ruby-dbi-users