[Pypgsql-devel] Strange cursor retention errors with upgrade to PostgreSQL 7.4

[Mike C. F. <mcf...@ro...>]

I just upgraded a database to PostgreSQL 7.4 and am now encountering a 
weird set of errors:

    OperationalError: (SQLQuery) ERROR:  cursor "PgSQL_01AA9B48" already 
exists

I'm assuming the cursor name is being auto-generated from somewhere.  
I'm also assuming that the problem is something like my not explicitly 
closing cursors (I never close any cursors, always trusting that the 
system will de-allocate them).  The same code was working fine with 
PostgreSQL 7.3, so I can only imagine that its a change in the semantics 
of 7.3 vs 7.4 cursors.  Another possibility I could imagine would be 
something like the names being based on Python object ids, and a re-used 
object id causing a name collision.  That seems a little stretched, 
though.  Alternately, could be that PyPgSQL is declaring "HOLD" on all 
cursors?

I see the following in the 7.4 changelog:

   Cursors conform more closely to the SQL standard
          The commands "FETCH" and "MOVE" have been overhauled to conform
          more closely to the SQL standard.

   Cursors can exist outside transactions
          These cursors are also called holdable cursors.

     * Allow cursors outside transactions using WITH HOLD (Neil)
       In previous releases, cursors were removed at the end of the
       transaction that created them. Cursors can now be created with the
       WITH HOLD option, which allows them to continue to be accessed
       after the creating transaction has committed.

My question then is: has anyone successfully used PyPgSQL with 
PostgreSQL 7.4 for more than trivial operations, and if so, did you 
encounter the above behaviour, and if so, what did you do to eliminate it?

I'll have to look into this more deeply tomorrow (I'm dead at the 
moment), so any hints would be helpful.

Have fun all,
Mike

_______________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://members.rogers.com/mcfletch/