Re: Named cursor problem

Hi,

I am using named cursor in this manner:

cursor = conn.cursor(str(os.getpid))
cursor.execute("select * from documents;")
for row in cursor: print do_something(row) # this function also creates
cursors from "conn"

One million rows have to be processed, but after 10-20 thousands
processed rows I got an error:
Traceback (most recent call last):
File "/homes/eva/xr/xrylko00/spinn/spinn3r/db/db2xml.py", line 408,
in <module>
query = query)
File "/homes/eva/xr/xrylko00/spinn/spinn3r/db/db2xml.py", line 351,
in create_xml
for item in self.get_item(column, identifiers, query):
File "/homes/eva/xr/xrylko00/spinn/spinn3r/db/db2xml.py", line 172,
in get_item
for row in cursor:
ProgrammingError: named cursor isn't valid anymore

Where may be problem?

PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit
Python 2.6.5 (r265:79063, Jun 22 2010, 14:35:26)
[GCC 4.3.5] on linux2
>>> import psycopg2
>>> psycopg2.__version__
'2.2.2 (dt dec ext pq3)'

Cheers,
Vojtěch R.

On 30/01/12 17:09, Vojtěch Rylko wrote:
>
> I am using named cursor in this manner:
>
> cursor = conn.cursor(str(os.getpid))
> cursor.execute("select * from documents;")
> for row in cursor: print do_something(row) # this function also creates
> cursors from "conn"
>
> One million rows have to be processed, but after 10-20 thousands
> processed rows I got an error:
> Traceback (most recent call last):
> File "/homes/eva/xr/xrylko00/spinn/spinn3r/db/db2xml.py", line 408,
> in <module>
> query = query)
> File "/homes/eva/xr/xrylko00/spinn/spinn3r/db/db2xml.py", line 351,
> in create_xml
> for item in self.get_item(column, identifiers, query):
> File "/homes/eva/xr/xrylko00/spinn/spinn3r/db/db2xml.py", line 172,
> in get_item
> for row in cursor:
> ProgrammingError: named cursor isn't valid anymore
>
> Where may be problem?

Did you commit or rollback the connection?

federico

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?

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

Dne 30.1.2012 17:27, Federico Di Gregorio napsal(a):
> 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.
> Hope this helps,
> federico

Different connection solved my problem, thank you for your help. Vojtěch R.