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

[Erik Hollensbe]

On Wednesday 03 September 2008 23:18:46 KUBO Takehiro wrote:
> PostgreSQL does it automatically.
>
>   http://www.postgresql.org/docs/8.3/static/sql-deallocate.html
>     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
> name.

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

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

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.

-Erik