[Pypgsql-devel] [ pypgsql-Bugs-1694437 ] execute multiple selects as one statement

[SourceForge.net <no...@so...>]

Bugs item #1694437, was opened at 2007-04-04 13:30
Message generated for change (Comment added) made by ballie01
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1694437&group_id=16528

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: PgSQL
Group: None
Status: Open
>Resolution: Later
>Priority: 3
Private: No
Submitted By: longj (longj)
>Assigned to: Billy G. Allie (ballie01)
Summary: execute multiple selects as one statement

Initial Comment:
If your sql string contains multiple selects and using Cursor.execute to execute them, only the last statement was executed. But you can fool Cursor.execute by adding some comments at the beginning.

How to reproduce and get around:
----------In database---------
create table x (id serial);

create function f() returns void as $$
insert into x default values;
$$language sql;

---------In python-----------
from pyPgSQL import PgSQL
db = PgSQL.connect()
c = db.cursor()
sql1 = """select f();
select f();"""
sql2 = """--create table foo(bar int);
select f();
select f();"""
c.execute(sql1)
db.commit()

--------In database---------
select * from x;
 id 
----
  1
(1 row)

-------In python--------------
c.execute(sql2)
db.commit()

-------In database------------
select * from x;
 id 
----
  1
  2
  3
(3 rows)


----------------------------------------------------------------------

>Comment By: Billy G. Allie (ballie01)
Date: 2007-04-05 00:00

Message:
Logged In: YES 
user_id=8500
Originator: NO

PgSQL by default uses a PostgreSQL cursor for select statements (except
SELECT ... INTO and SELECT ... FOR UPDATE).  This causes the following SQL
to be generated and sent to the backend:

>>> cnx.conn.debug='y'
>>> cur.execute('select f(); select f(); select f();')
QUERY: BEGIN WORK
QUERY: DECLARE "PgSQL_-480D93F4" CURSOR FOR select f(); select f(); select
f();
>>> cnx.commit()
QUERY: CLOSE "PgSQL_-480D93F4"
QUERY: COMMIT WORK

This causes one of the select f(); statements to be lost.  This occurs in
psql also when given the sequence of queries shown above.

The work-around is to execute 'PgSQL.noPostgresCursor = True' before you
execute a multi-select query and to execute 'PgSQL.noPostgresCursor =
False' when you are done.  This disables the use of PostgreSQL cursors,
which will allow the statement to function as expected.

Once I figure out a reliable RE to recognize multi-select queries, I will
put that in as a permenant fix.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1694437&group_id=16528