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

[KUBO Takehiro]

Hi,

On Tue, Sep 2, 2008 at 6:11 AM, Erik Hollensbe <erik at hollensbe.org> wrote:
> On Tuesday 26 August 2008 18:56:40 KUBO Takehiro wrote:
>> Pardon me if I'm not correct. I have been interested in how various
>> DBMSs implement prepared statements. But I have not used postgresql
>> for 9 years.
>>
>> dbd_pg uses prepared statements for all queries. But who deallocate the
>> prepared statements? "@stmt.clear if @stmt" doesn't do it.
>> IMO, if a ruby process uses one database session for all SQL statements
>> and issues them periodically, server side (postmaster) memory will be
>> increased as time goes.
>>
>> See: http://www.postgresql.org/docs/8.3/static/libpq-exec.html#AEN30965
>>      http://www.postgresql.org/docs/8.3/static/sql-deallocate.html
>
> This bug (and a type handling bug in mysql) have been fixed in the recent git
> push. If anyone has some spare time and would just like to run the test suite
> which should reveal any issues in either of these changes, it'll ease my mind
> to release it today.
>
> Thanks again for the help in solving this problem.

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, I have not test it. I have no working postgresql database...

diff --git a/lib/dbd/pg/statement.rb b/lib/dbd/pg/statement.rb
index 0271a10..7273e77 100644
--- a/lib/dbd/pg/statement.rb
+++ b/lib/dbd/pg/statement.rb
@@ -10,6 +10,16 @@ class DBI::DBD::Pg::Statement < DBI::BaseStatement

     PG_STMT_NAME_PREFIX = 'ruby-dbi:Pg:'

+    @@id_map = {} # prepared statement's id => db
+
+    @@finalizer_proc = proc do |id|
+        db = @@id_map[id]
+        if db
+            @@id_map.delete(id)
+            db._exec("DEALLOCATE \"#{PG_STMT_NAME_PREFIX + id.to_s}\"")
+        end
+    end
+
     def initialize(db, sql)
         super(db)
         @db  = db
@@ -17,7 +27,7 @@ class DBI::DBD::Pg::Statement < DBI::BaseStatement
         @stmt_name = PG_STMT_NAME_PREFIX + self.object_id.to_s
         @result = nil
         @bindvars = []
-        @prepared = false
+        ObjectSpace.define_finalizer(self, @@finalizer_proc)
     rescue PGError => err
         raise DBI::ProgrammingError.new(err.message)
     end
@@ -125,8 +135,8 @@ class DBI::DBD::Pg::Statement < DBI::BaseStatement
     # finish the statement at a lower level
     def internal_finish
         @result.finish if @result
-        statement_exists = @db._exec("select * from
pg_prepared_statements where name='#{@stmt_name}'")
-        if statement_exists.num_tuples > 0
+        if @@id_map[self.object_id]
+            @@id_map.delete(self.object_id)
             @db._exec("DEALLOCATE \"#{@stmt_name}\"")
         end
     end
@@ -134,14 +144,14 @@ class DBI::DBD::Pg::Statement < DBI::BaseStatement
     # prepare the statement at a lower level.
     def internal_prepare
         if @db["pg_native_binding"]
-            unless @prepared
+            unless @@id_map[self.object_id]
                 @stmt = @db._prepare(@stmt_name, translate_param_markers(@sql))
             end
         else
             internal_finish
             @stmt = @db._prepare(@stmt_name,
DBI::SQL::PreparedStatement.new(DBI::DBD::Pg, @sql).bind(@bindvars))
         end
-        @prepared = true
+        @@id_map[self.object_id] = @db
     end

     # Prepare the given SQL statement, returning its PostgreSQL string