Skip site navigation (1) Skip section navigation (2)

Re: Named cursor problem

From: Federico Di Gregorio <fog(at)dndg(dot)it>
To: Vojtěch Rylko <rylko(at)vojtuv(dot)net>
Cc: psycopg(at)postgresql(dot)org
Subject: Re: Named cursor problem
Date: 2012-01-30 16:27:52
Message-ID: 4F26C508.9010400@dndg.it (view raw or flat)
Thread:
Lists: psycopg
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

In response to

Responses

psycopg by date

Next:From: Vojtěch RylkoDate: 2012-01-30 17:30:58
Subject: Re: Named cursor problem
Previous:From: Vojtěch RylkoDate: 2012-01-30 16:16:06
Subject: Re: Named cursor problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group