Thread: 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.