Re: Named cursor problem

On 30/01/12 17:16, Vojtěch Rylko wrote:
> Dne 30.1.2012 17:13, Federico Di Gregorio napsal(a):
>> Did you commit or rollback the connection?
>>
>> federico
>>
> Yes!, I commit the connection on another table. So solution may be
> second connection?

Yes. If you commit the current transaction is lost and the server-side
cursor isn't valid anymore. You can use a different connection to fetch
the results or create a "withhold" cursor. From psycopg documentation at:

http://www.psycopg.org/psycopg/docs/usage.html#server-side-cursors

[...]
Named cursors are usually created WITHOUT HOLD, meaning they live only
as long as the current transaction. Trying to fetch from a named cursor
after a commit() or to create a named cursor when the connection
transaction isolation level is set to AUTOCOMMIT will result in an
exception. It is possible to create a WITH HOLD cursor by specifying a
True value for the withhold parameter to cursor() or by setting the
withhold attribute to True before calling execute() on the cursor. It is
extremely important to always close() such cursors, otherwise they will
continue to hold server-side resources until the connection will be
eventually closed. Also note that while WITH HOLD cursors lifetime
extends well after commit(), calling rollback() will automatically close
the cursor.
[...]

Hope this helps,
federico