pypgsql-devel — List for discussion of pyPgSQL development.

[Pypgsql-devel] [ pypgsql-Bugs-1672884 ] "where x in %s" failes with single-element tuple

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

Bugs item #1672884, was opened at 2007-03-03 21:34
Message generated for change (Comment added) made by andrewmcnamara
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1672884&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Larry Hastings (lhastings)
Assigned to: Nobody/Anonymous (nobody)
Summary: "where x in %s" failes with single-element tuple

Initial Comment:

This:
db.execute("delete from table where index in %s", ((1, 2, 3),) )
works fine.

But this:
db.execute("delete from table where index in %s", ((1,),) )
fails with a syntax error.

It's because PyPgSQL renders the arguments into the command, so the command fed into PostgreSQL becomes
delete from table where index in (1,);
and PostgreSQL's parser doesn't like a one-element tuple like that.

I suspect the fix is: notice when rendering a one-element tuple, and somehow remove or prevent the comma.

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

Comment By: Andrew McNamara (andrewmcnamara)
Date: 2007-05-01 16:04

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

In PgSQL.py, the function _quote() is responsible for this. For example:

>>> from pyPgSQL import PgSQL
>>> PgSQL._quote((1,))
'(1)'


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

Comment By: Larry Hastings (lhastings)
Date: 2007-05-01 15:38

Message:
Logged In: YES 
user_id=364875
Originator: YES

I'm using 2.5.1, and I definitely saw the problem.  Where in the code does
it handle this case?

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

Comment By: Andrew McNamara (andrewmcnamara)
Date: 2007-04-30 18:04

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

You might want to check that you're using an up-to-date version of pyPgSQL
- I seem to remember this being fixed in 2.4. I use IN queries extensively
in my apps, and I haven't had a complaint about this problem for some time.

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1672884 ] "where x in %s" failes with single-element tuple

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

Bugs item #1672884, was opened at 2007-03-03 10:34
Message generated for change (Comment added) made by lhastings
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1672884&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Larry Hastings (lhastings)
Assigned to: Nobody/Anonymous (nobody)
Summary: "where x in %s" failes with single-element tuple

Initial Comment:

This:
db.execute("delete from table where index in %s", ((1, 2, 3),) )
works fine.

But this:
db.execute("delete from table where index in %s", ((1,),) )
fails with a syntax error.

It's because PyPgSQL renders the arguments into the command, so the command fed into PostgreSQL becomes
delete from table where index in (1,);
and PostgreSQL's parser doesn't like a one-element tuple like that.

I suspect the fix is: notice when rendering a one-element tuple, and somehow remove or prevent the comma.

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

>Comment By: Larry Hastings (lhastings)
Date: 2007-05-01 05:38

Message:
Logged In: YES 
user_id=364875
Originator: YES

I'm using 2.5.1, and I definitely saw the problem.  Where in the code does
it handle this case?

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

Comment By: Andrew McNamara (andrewmcnamara)
Date: 2007-04-30 08:04

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

You might want to check that you're using an up-to-date version of pyPgSQL
- I seem to remember this being fixed in 2.4. I use IN queries extensively
in my apps, and I haven't had a complaint about this problem for some time.

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

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

[Pypgsql-devel] [ pypgsql-Patches-1710329 ] Add nested savepoints

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

Patches item #1710329, was opened at 2007-05-01 01:27
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1710329&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Markus Schiltknecht (schiltknecht)
Assigned to: Nobody/Anonymous (nobody)
Summary: Add nested savepoints

Initial Comment:
This patch is derived from #1511984, implementing nested savepoints.

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1669810 ] large OID not supported?

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

Bugs item #1669810, was opened at 2007-02-27 20:32
Message generated for change (Comment added) made by andrewmcnamara
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1669810&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: None
Priority: 5
Private: No
Submitted By: petit_vic (petit_vic)
Assigned to: Nobody/Anonymous (nobody)
Summary: large OID not supported?

Initial Comment:
I am working on system tables and at some point I need to get the "oid" value from pg_class table. I noticed that when I try to get this field for records with too high value, I don't get the value but 2147483647 = 2 ** 31 - 1. 

For example if I try to get the max(oid) from pg_class which is 4169345710, I get 2147483647.

Can somebody help on this?

Thank you very much,

petit_vic

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

Comment By: Andrew McNamara (andrewmcnamara)
Date: 2007-05-01 00:48

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

Some random thoughts from a passer-by: if I had to guess, I would say this
is a bug in the handling of the PG_OID type in pgresult.c - strtol(3)
returns LONG_MIN on under or overflow. The logic in libPQgetvalue for the
PG_OID case is too subtle to for me to immediately see what the problem is
- I'm not sure what the oidCache is being used for.

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1672884 ] "where x in %s" failes with single-element tuple

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

Bugs item #1672884, was opened at 2007-03-03 21:34
Message generated for change (Comment added) made by andrewmcnamara
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1672884&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Larry Hastings (lhastings)
Assigned to: Nobody/Anonymous (nobody)
Summary: "where x in %s" failes with single-element tuple

Initial Comment:

This:
db.execute("delete from table where index in %s", ((1, 2, 3),) )
works fine.

But this:
db.execute("delete from table where index in %s", ((1,),) )
fails with a syntax error.

It's because PyPgSQL renders the arguments into the command, so the command fed into PostgreSQL becomes
delete from table where index in (1,);
and PostgreSQL's parser doesn't like a one-element tuple like that.

I suspect the fix is: notice when rendering a one-element tuple, and somehow remove or prevent the comma.

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

Comment By: Andrew McNamara (andrewmcnamara)
Date: 2007-04-30 18:04

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

You might want to check that you're using an up-to-date version of pyPgSQL
- I seem to remember this being fixed in 2.4. I use IN queries extensively
in my apps, and I haven't had a complaint about this problem for some time.

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

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

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

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

Bugs item #1694437, was opened at 2007-04-05 03:30
Message generated for change (Comment added) made by andrewmcnamara
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: Andrew McNamara (andrewmcnamara)
Date: 2007-04-30 17:56

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

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

I would rather see multiple queries per execute disallowed - it's a
classic vector for SQL injection attacks, and the newer libpc
PQexecParams() API doesn't allow it either.

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

Comment By: longj (longj)
Date: 2007-04-06 04:41

Message:
Logged In: YES 
user_id=1761244
Originator: YES

Another thing I noticed is if you run
>>> cur.execute('select f()')
BEGIN WORK
DECLARE "PgSQL_081B414C" CURSOR FOR select f()
FETCH 1 FROM "PgSQL_081B414C"

It will run fetch one in this case. But won't in multi-select case. 
Seems this if statement was bypassed:
	elif _qstr[:8] == 'DECLARE ':
	    # Ok -- we've created a cursor, we will pre-fetch the first row in
	    # order to make the description array.  Note: the first call to
	    # fetchXXX will return the pre-fetched record.
	    self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
...

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

Comment By: Billy G. Allie (ballie01)
Date: 2007-04-05 14: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

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

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

Bugs item #1694437, was opened at 2007-04-04 10:30
Message generated for change (Comment added) made by longj
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: longj (longj)
Date: 2007-04-05 11:41

Message:
Logged In: YES 
user_id=1761244
Originator: YES

Another thing I noticed is if you run
>>> cur.execute('select f()')
BEGIN WORK
DECLARE "PgSQL_081B414C" CURSOR FOR select f()
FETCH 1 FROM "PgSQL_081B414C"

It will run fetch one in this case. But won't in multi-select case. 
Seems this if statement was bypassed:
	elif _qstr[:8] == 'DECLARE ':
	    # Ok -- we've created a cursor, we will pre-fetch the first row in
	    # order to make the description array.  Note: the first call to
	    # fetchXXX will return the pre-fetched record.
	    self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
...

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

Comment By: Billy G. Allie (ballie01)
Date: 2007-04-04 21: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

[Pypgsql-devel] [ pypgsql-Bugs-1409818 ] Problem using SAVEPOINT

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

Bugs item #1409818, was opened at 2006-01-19 06:03
Message generated for change (Comment added) made by ballie01
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1409818&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: libpq
Group: None
Status: Open
>Resolution: Later
Priority: 5
Private: No
Submitted By: Peer (peergriebel)
>Assigned to: Billy G. Allie (ballie01)
Summary: Problem using SAVEPOINT

Initial Comment:
We are using PostgreSQL 8.1.1 with
pyPgSQL-2.4.win32-py2.3.exe.

Executing the following code in e.g. pgadmin works as
expected:

SAVEPOINT APS_SAVEPOINT2;
SELECT persons.oid FROM persons WHERE person_id = '9';
ROLLBACK TO APS_SAVEPOINT2;
SELECT persons.oid FROM persons WHERE person_id = '9'

Executing the code inside of the python interface to
PostgreSQL using:


from pyPgSQL import PgSQL

connection = PgSQL.connect(...)
cursor=connection.cursor()
cursor.execute("SAVEPOINT APS_SAVEPOINT2")
cursor.execute("SELECT persons.oid FROM persons WHERE
person_id = '9'")
cursor.execute("ROLLBACK TRANSACTION TO SAVEPOINT
APS_SAVEPOINT2")
cursor.execute("SAVEPOINT APS_SAVEPOINT3")
cursor.execute("SELECT persons.oid FROM persons WHERE
person_id = '9'")


does not work.  An exception is raised:

libpq.OperationalError: ERROR:  current transaction is
aborted, commands ignored until end of transaction block

I can't see the reason.  But I think it lies somewhere
in libpq.

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

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

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

The problem is that pyPgSQL does not understand savepoints.  When the
cursor sees a second SELECT without a connection.rollback() or
connection.commit(), it assumes that the programmer is finished with the
first SELECT and resets the cursor object back to it's initial state
(rolling back the transaction if one exists).

The workaround is to turn on autocommit and start (and end) the
transaction yourself.

The fix is to add support for savepoints to pyPgSQL.

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

Comment By: Gerhard Häring (ghaering)
Date: 2006-06-02 09:55

Message:
Logged In: YES 
user_id=163326

I believe it has to do with portals. If you close the cursor
and create a new one before the "ROLLBACK TRANSACTION", you
don't get this error message.

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1524586 ] Don't use transactions by default

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

Bugs item #1524586, was opened at 2006-07-18 11:01
Message generated for change (Comment added) made by ballie01
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1524586&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: Closed
Resolution: None
Priority: 5
Private: No
Submitted By: BjLindqvist (sonderblade)
>Assigned to: Billy G. Allie (ballie01)
Summary: Don't use transactions by default

Initial Comment:
Today I spent alot of time tracking down why my updates
to the database didn't seem to stick. It appears the
problem was that pyPgSQL uses transactions by default.
Wrapping the queries in BEGIN WORK/COMMIT WORK
statements fixed the problem. 

To prevent others from running into the same annoyance
I suggest that transactions should be off by default.

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

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

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

The DB-API standard specifies that transaction be enabled by default
(autocommit off).  You can enable autocommit on a connection by executing
'connection.autocommit = True' before you execute a query.

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

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

[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

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

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

Bugs item #1694437, was opened at 2007-04-04 10:30
Message generated for change (Tracker Item Submitted) made by Item Submitter
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: None
Priority: 5
Private: No
Submitted By: longj (longj)
Assigned to: Nobody/Anonymous (nobody)
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)


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

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

[Pypgsql-devel] [ pypgsql-Bugs-1672884 ] "where x in %s" failes with single-element tuple

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

Bugs item #1672884, was opened at 2007-03-03 10:34
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1672884&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Larry Hastings (lhastings)
Assigned to: Nobody/Anonymous (nobody)
Summary: "where x in %s" failes with single-element tuple

Initial Comment:

This:
db.execute("delete from table where index in %s", ((1, 2, 3),) )
works fine.

But this:
db.execute("delete from table where index in %s", ((1,),) )
fails with a syntax error.

It's because PyPgSQL renders the arguments into the command, so the command fed into PostgreSQL becomes
delete from table where index in (1,);
and PostgreSQL's parser doesn't like a one-element tuple like that.

I suspect the fix is: notice when rendering a one-element tuple, and somehow remove or prevent the comma.

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1669810 ] large OID not supported?

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

Bugs item #1669810, was opened at 2007-02-27 18:32
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1669810&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: None
Priority: 5
Private: No
Submitted By: petit_vic (petit_vic)
Assigned to: Nobody/Anonymous (nobody)
Summary: large OID not supported?

Initial Comment:
I am working on system tables and at some point I need to get the "oid" value from pg_class table. I noticed that when I try to get this field for records with too high value, I don't get the value but 2147483647 = 2 ** 31 - 1. 

For example if I try to get the max(oid) from pg_class which is 4169345710, I get 2147483647.

Can somebody help on this?

Thank you very much,

petit_vic

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

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

[Pypgsql-devel] [ pypgsql-Patches-1618304 ] toggleShowQuery => segfault

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

Patches item #1618304, was opened at 2006-12-18 13:41
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1618304&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Ken Lalonde (kenlalonde)
Assigned to: Nobody/Anonymous (nobody)
Summary: toggleShowQuery => segfault

Initial Comment:
Configuration: pyPgSQL-2.5.1, python 2.5, freebsd 6.0.

Use of the magic toggleShowQuery attribute causes a
seg fault in strlen().  This is caused by messed up reference counts.  The attached patch fixes the problem.

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

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

[Pypgsql-devel] [ pypgsql-Patches-1609701 ] Interval parsing bug

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

Patches item #1609701, was opened at 2006-12-05 18:59
Message generated for change (Comment added) made by kenlalonde
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1609701&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Ken Lalonde (kenlalonde)
Assigned to: Nobody/Anonymous (nobody)
Summary: Interval parsing bug

Initial Comment:
PgSQL.py 2.5.1 uses int() to parse the time part of a postgres interval string.  When the seconds part contains a decimal, int() will throw a ValueError.

The attached patch just discards the decimal part, since mx.DateTime.RelativeTime stores seconds as an integer.

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

>Comment By: Ken Lalonde (kenlalonde)
Date: 2006-12-06 15:46

Message:
Logged In: YES 
user_id=203927
Originator: YES

To be clear, here's a simple example that demonstrates the bug.
We assume Db credentials are in the environment:

from pyPgSQL import PgSQL
db = PgSQL.connect()
cur = db.cursor()
cur.execute("select interval '2 years 3 days 12:34:56.78'")
cur.fetchall()


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

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

[Pypgsql-devel] [ pypgsql-Patches-1609701 ] Interval parsing bug

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

Patches item #1609701, was opened at 2006-12-05 18:59
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1609701&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Ken Lalonde (kenlalonde)
Assigned to: Nobody/Anonymous (nobody)
Summary: Interval parsing bug

Initial Comment:
PgSQL.py 2.5.1 uses int() to parse the time part of a postgres interval string.  When the seconds part contains a decimal, int() will throw a ValueError.

The attached patch just discards the decimal part, since mx.DateTime.RelativeTime stores seconds as an integer.

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

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

[Pypgsql-devel] [ pypgsql-Patches-1583325 ] Incorrect handling of TIMESTAMP columns

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

Patches item #1583325, was opened at 2006-10-24 04:48
Message generated for change (Comment added) made by davecole
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1583325&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Dave Cole (davecole)
Assigned to: Nobody/Anonymous (nobody)
Summary: Incorrect handling of TIMESTAMP columns

Initial Comment:
The 2.5.1 module incorrectly applies the timezone
offset to TIMESTAMP columns.  I get correct results
querying TIMESTAMP columns using pqsl but pyPgSQL seems
to be double applying the timezone offset.

The attached patch Works For Me (TM) by only doing the
localtime() conversion for TIMESTAMP WITH TIME ZONE
columns.


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

>Comment By: Dave Cole (davecole)
Date: 2006-10-24 04:51

Message:
Logged In: YES 
user_id=28658

And here is the small test program I used to see the before
and after behaviour.

$ python test-timestamp DATABASE-NAME

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

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

[Pypgsql-devel] [ pypgsql-Patches-1583325 ] Incorrect handling of TIMESTAMP columns

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

Patches item #1583325, was opened at 2006-10-24 04:48
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1583325&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Dave Cole (davecole)
Assigned to: Nobody/Anonymous (nobody)
Summary: Incorrect handling of TIMESTAMP columns

Initial Comment:
The 2.5.1 module incorrectly applies the timezone
offset to TIMESTAMP columns.  I get correct results
querying TIMESTAMP columns using pqsl but pyPgSQL seems
to be double applying the timezone offset.

The attached patch Works For Me (TM) by only doing the
localtime() conversion for TIMESTAMP WITH TIME ZONE
columns.


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

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

[Pypgsql-devel] [ pypgsql-Patches-1511984 ] Add savepoint method to Connection

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

Patches item #1511984, was opened at 2006-06-25 03:34
Message generated for change (Comment added) made by schiltknecht
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1511984&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Clarence (clarence1126)
Assigned to: Gerhard Häring (ghaering)
Summary: Add savepoint method to Connection

Initial Comment:
Adds savepoint() and release() methods to Connection, 
and modifies rollback() to optionally rollback only to 
the savepoint.

Deals only with one, unnamed from the user's point of 
view, savepoint.

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

Comment By: Markus Schiltknecht (schiltknecht)
Date: 2006-10-06 12:43

Message:
Logged In: YES 
user_id=1614584

I'm trying to upload an update to the patch.

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

Comment By: Clarence (clarence1126)
Date: 2006-06-26 22:31

Message:
Logged In: YES 
user_id=1537139

Oh, you have to check that silly box or it ignores your 
upload :(
Hopefully, here's the patch without whitespace issues

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

Comment By: Clarence (clarence1126)
Date: 2006-06-26 22:25

Message:
Logged In: YES 
user_id=1537139

Beats me how SourceForge works. Here's the file again.

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

Comment By: Clarence (clarence1126)
Date: 2006-06-26 22:24

Message:
Logged In: YES 
user_id=1537139

Here is a patch without whitespace issues

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1531468 ] Some problems with date/time support

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

Bugs item #1531468, was opened at 2006-07-31 02:44
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1531468&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: PgResult
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Christoph Zwerschke (cito)
Assigned to: Nobody/Anonymous (nobody)
Summary: Some problems with date/time support

Initial Comment:
Since I am currently improving date/time support in
PyGreSQL, I had a quick look how you do it in PyPgSQL.
Particularly, I found no easy solution to handle the
cases when datestyle is set to 'sql,european' or
'postgres,european', since the output in this format
cannot be distinguished from the US variant. I saw that
PyPgSQL cannot handle this case either. Probably we can
only add this as a caveat to the documentation of the
modules.

I also observed the following minor problems with PyPgSQL :

- ValueError for intervals with microseconds
- negative dates (years BC) are not handled correctly
- timestamps outside the Unix epoch are not handled
correctly

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

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

[Pypgsql-devel] [ pypgsql-Bugs-1524586 ] Don't use transactions by default

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

Bugs item #1524586, was opened at 2006-07-18 17:01
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1524586&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: None
Priority: 5
Submitted By: Björn Lindqvist (sonderblade)
Assigned to: Nobody/Anonymous (nobody)
Summary: Don't use transactions by default

Initial Comment:
Today I spent alot of time tracking down why my updates
to the database didn't seem to stick. It appears the
problem was that pyPgSQL uses transactions by default.
Wrapping the queries in BEGIN WORK/COMMIT WORK
statements fixed the problem. 

To prevent others from running into the same annoyance
I suggest that transactions should be off by default.

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

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

[Pypgsql-devel] [ pypgsql-Patches-1511984 ] Add savepoint method to Connection

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

Patches item #1511984, was opened at 2006-06-24 18:34
Message generated for change (Comment added) made by clarence1126
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1511984&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Clarence (clarence1126)
Assigned to: Gerhard Häring (ghaering)
Summary: Add savepoint method to Connection

Initial Comment:
Adds savepoint() and release() methods to Connection, 
and modifies rollback() to optionally rollback only to 
the savepoint.

Deals only with one, unnamed from the user's point of 
view, savepoint.

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

>Comment By: Clarence (clarence1126)
Date: 2006-06-26 13:31

Message:
Logged In: YES 
user_id=1537139

Oh, you have to check that silly box or it ignores your 
upload :(
Hopefully, here's the patch without whitespace issues

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

Comment By: Clarence (clarence1126)
Date: 2006-06-26 13:25

Message:
Logged In: YES 
user_id=1537139

Beats me how SourceForge works. Here's the file again.

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

Comment By: Clarence (clarence1126)
Date: 2006-06-26 13:24

Message:
Logged In: YES 
user_id=1537139

Here is a patch without whitespace issues

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

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

[Pypgsql-devel] [ pypgsql-Patches-1511984 ] Add savepoint method to Connection

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

Patches item #1511984, was opened at 2006-06-24 18:34
Message generated for change (Comment added) made by clarence1126
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1511984&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Clarence (clarence1126)
Assigned to: Gerhard Häring (ghaering)
Summary: Add savepoint method to Connection

Initial Comment:
Adds savepoint() and release() methods to Connection, 
and modifies rollback() to optionally rollback only to 
the savepoint.

Deals only with one, unnamed from the user's point of 
view, savepoint.

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

>Comment By: Clarence (clarence1126)
Date: 2006-06-26 13:25

Message:
Logged In: YES 
user_id=1537139

Beats me how SourceForge works. Here's the file again.

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

Comment By: Clarence (clarence1126)
Date: 2006-06-26 13:24

Message:
Logged In: YES 
user_id=1537139

Here is a patch without whitespace issues

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

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

[Pypgsql-devel] [ pypgsql-Patches-1511984 ] Add savepoint method to Connection

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

Patches item #1511984, was opened at 2006-06-24 18:34
Message generated for change (Comment added) made by clarence1126
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1511984&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Clarence (clarence1126)
Assigned to: Gerhard Häring (ghaering)
Summary: Add savepoint method to Connection

Initial Comment:
Adds savepoint() and release() methods to Connection, 
and modifies rollback() to optionally rollback only to 
the savepoint.

Deals only with one, unnamed from the user's point of 
view, savepoint.

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

>Comment By: Clarence (clarence1126)
Date: 2006-06-26 13:24

Message:
Logged In: YES 
user_id=1537139

Here is a patch without whitespace issues

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

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

[Pypgsql-devel] [ pypgsql-Patches-1511984 ] Add savepoint method to Connection

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

Patches item #1511984, was opened at 2006-06-25 03:34
Message generated for change (Settings changed) made by ghaering
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=316528&aid=1511984&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Clarence (clarence1126)
>Assigned to: Gerhard Häring (ghaering)
Summary: Add savepoint method to Connection

Initial Comment:
Adds savepoint() and release() methods to Connection, 
and modifies rollback() to optionally rollback only to 
the savepoint.

Deals only with one, unnamed from the user's point of 
view, savepoint.

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

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