help: pypgsql: this code works in Fedora core 1, Not in FC2

[J Dubal]

Hello good people,

Following works in FC1 (python-2.2.3-7, postgresql-7.3.4-11,
kernel-2.4.22-1.2194.nptl, pyPgSQL-2.4)

from pyPgSQL import PgSQL
conn = PgSQL.connect('localhost',database='rop')
loccur = conn.cursor()
loccur.execute("DECLARE itemcursor CURSOR FOR \
SELECT * FROM locmf")
loccur.execute("FETCH ALL FROM itemcursor")
rec=loccur.fetchall()
print rec

However, following errors are produced in FC2 (python-2.3.3-6,
postgresql-7.4.2-1, kernel-2.6.8-1.521, pyPgSQL-2.4):

/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py:2644: FutureWarning:
%u/%o/%x/%X of negative int will return a signed string in Python 2.4
and up
name = "PgSQL_%08X" % id(self)
Traceback (most recent call last):
File "tt1.py", line 5, in ?
loccur.execute("DECLARE itemcursor CURSOR FOR \
File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3091,
in execute
self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
libpq.OperationalError: ERROR: cursor "PgSQL_F6F9906C" does not exist

Out of this, the first error is produced by conn.cursor() statement.

We have to use the "declare cursor" statement because we have to use
"fetch prior" statement later in the program. Is there a better way of
achiving this?

Something seems to have changed either python or in postgresql that is
causing this error. Can someone provide a solution/workaround?

Thanks in advance.
J Dubal.

 

[Cliff Wells]

Hello good people,

Following works in FC1 (python-2.2.3-7, postgresql-7.3.4-11,
kernel-2.4.22-1.2194.nptl, pyPgSQL-2.4)

from pyPgSQL import PgSQL
conn = PgSQL.connect('localhost',database='rop')
loccur = conn.cursor()
loccur.execute("DECLARE itemcursor CURSOR FOR \
SELECT * FROM locmf")
loccur.execute("FETCH ALL FROM itemcursor")
rec=loccur.fetchall()
print rec

However, following errors are produced in FC2 (python-2.3.3-6,
postgresql-7.4.2-1, kernel-2.6.8-1.521, pyPgSQL-2.4):

/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py:2644: FutureWarning:
%u/%o/%x/%X of negative int will return a signed string in Python 2.4
and up
name = "PgSQL_%08X" % id(self)
Traceback (most recent call last):
File "tt1.py", line 5, in ?
loccur.execute("DECLARE itemcursor CURSOR FOR \
File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3091,
in execute
self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
libpq.OperationalError: ERROR: cursor "PgSQL_F6F9906C" does not exist

Out of this, the first error is produced by conn.cursor() statement.

We have to use the "declare cursor" statement because we have to use
"fetch prior" statement later in the program. Is there a better way of
achiving this?

Something seems to have changed either python or in postgresql that is
causing this error. Can someone provide a solution/workaround?

Don't know if anything changed between the versions of the software you
are using, but see

http://www.postgresql.org/docs/current/static/sql-declare.html

particularly the section on "WITH HOLD/WITHOUT HOLD" and the first
paragraph in "Notes". I'm guessing that perhaps you aren't starting a
new transaction before declaring the cursor and that perhaps pyPgSQL is
autocommitting the DECLARE statement which would result in your declared
cursor going out of scope. Try adding WITH HOLD or put your stuff
inside of "BEGIN/COMMIT" statements.

Regards,
Cliff

 

[J Dubal]

Don't know if anything changed between the versions of the software you
are using, but see

http://www.postgresql.org/docs/current/static/sql-declare.html

particularly the section on "WITH HOLD/WITHOUT HOLD" and the first
paragraph in "Notes". I'm guessing that perhaps you aren't starting a
new transaction before declaring the cursor and that perhaps pyPgSQL is
autocommitting the DECLARE statement which would result in your declared
cursor going out of scope. Try adding WITH HOLD or put your stuff
inside of "BEGIN/COMMIT" statements.

Regards,
Cliff

Many thanks for your response. We tried a few things and found that
although the above code does not work on FC2, it works if we change
this statement:
conn = PgSQL.connect('localhost',database='rop')
to this:
conn = PgSQL.connect('192.168.1.5',database='rop')
192.168.1.5 happens to be another host running redhat 8.0 and
postgresql-server-7.3.4-1PGDG. It appears that the older (7.3.4)
engine was allowing the above syntax but the newer (7.4.2) is not
allowing.

Then we tried conn.conn.toggleShowQuery. This shows what sql
statements are executed. Based on its output we worked out the
following solution. Relace these lines:
loccur.execute("DECLARE itemcursor CURSOR FOR \
SELECT * FROM locmf")
loccur.execute("FETCH ALL FROM itemcursor")
by these lines:
loccur.execute("SELECT * FROM locmf")
curname=loccur.name
loccur.execute('FETCH ALL FROM "%s"' %curname)

Then everything works as expected. Even with the old engine.
Thanks and regards.
J Dubal.