transaction rollback fails after DBAPI originated error using py-postgresql and pg9 server

[Former user]

(original reporter: Phazorx) Using combinations of following:

• SQLAlchemy 0.7.6, 0.7.7
• py-postgresql 1.0.2, 1.0.3
• libpg 8.4.7, 8.4.12, 9.1.4

With PostgreSQL 9.1.1 on backend, upon recieving an exception from DBAPI level within some transaction, connection (?) gets stuck in erroneous state and it is not poss possible to rollback the transaction (which results in original exception being returned again).

For example:

from sqlalchemy import create_engine
from sqlalchemy.sql.expression import text

engine = create_engine('postgresql+pypostgresql://user:pass@host/db', echo=True)
connection = engine.connect()

transaction = connection.begin()
connection.execute(text('SELECT 1;'))
transaction.commit()
transaction.close()

transaction = connection.begin()
try:
    connection.execute(text('SELECT \'a\'=1;'))
    transaction.commit()
except Exception as e:
    print ("\n !!! Got exception executing, rolling back\n".format(e))
    try:
        transaction.rollback()
    except Exception as ee:
        print ("\n !!! Got exception rolling back\n {}\n".format(ee))

connection.close()

produces:

2012-09-03 14:41:18,118 INFO sqlalchemy.engine.base.Engine select version()
2012-09-03 14:41:18,118 INFO sqlalchemy.engine.base.Engine {}
2012-09-03 14:41:18,123 INFO sqlalchemy.engine.base.Engine select current_schema()
2012-09-03 14:41:18,123 INFO sqlalchemy.engine.base.Engine {}
2012-09-03 14:41:18,141 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-09-03 14:41:18,141 INFO sqlalchemy.engine.base.Engine SELECT 1;
2012-09-03 14:41:18,141 INFO sqlalchemy.engine.base.Engine {}
2012-09-03 14:41:18,143 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-03 14:41:18,146 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-09-03 14:41:18,147 INFO sqlalchemy.engine.base.Engine SELECT 'a'=1;
2012-09-03 14:41:18,147 INFO sqlalchemy.engine.base.Engine {}

 !!! Got exception executing, rolling back

2012-09-03 14:41:18,153 INFO sqlalchemy.engine.base.Engine ROLLBACK

 !!! Got exception rolling back
 (TextRepresentationError) ошибка синтаксиса в значении типа integer: "a"
  CODE: 22P02
  LOCATION: File 'numutils.c', line 62, in pg_atoi from SERVER
  POSITION: 8
STATEMENT: [parsing](parsing)
  LINE:
    SELECT 'a'=1;
           ^ [1, character 8](line)
  statement_id: py:0x1eb5bd0
  string: SELECT 'a'=1;
CONNECTION: [closed](closed)
  client_address: 192.168.1.160/32
  client_port: 45706
  version:
    PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
CONNECTOR: [IP4](IP4) pq://aust:***@192.168.0.3:5432/aust
  category: None
DRIVER: postgresql.driver.pq3.Driver None None

At same time, with Postgrs 8.4.7 or 8.4.12, and/or psycopg2 used instead of py-postgresql the results are as expected:

2012-09-03 14:41:18,118 INFO sqlalchemy.engine.base.Engine select version()
2012-09-03 14:41:18,118 INFO sqlalchemy.engine.base.Engine {}
2012-09-03 14:41:18,123 INFO sqlalchemy.engine.base.Engine select current_schema()
2012-09-03 14:41:18,123 INFO sqlalchemy.engine.base.Engine {}
2012-09-03 14:41:18,141 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-09-03 14:41:18,141 INFO sqlalchemy.engine.base.Engine SELECT 1;
2012-09-03 14:41:18,141 INFO sqlalchemy.engine.base.Engine {}
2012-09-03 14:41:18,143 INFO sqlalchemy.engine.base.Engine COMMIT
2012-09-03 14:41:18,146 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-09-03 14:41:18,147 INFO sqlalchemy.engine.base.Engine SELECT 'a'=1;
2012-09-03 14:41:18,147 INFO sqlalchemy.engine.base.Engine {}

 !!! Got exception executing, rolling back

2012-09-03 14:41:18,153 INFO sqlalchemy.engine.base.Engine ROLLBACK

 !!! Got exception rolling back (TextRepresentationError) ошибка синтаксиса в значении типа integer: "a"
  CODE: 22P02
  LOCATION: File 'numutils.c', line 62, in pg_atoi from SERVER
  POSITION: 8
STATEMENT: [parsing](parsing)
  LINE:
    SELECT 'a'=1;
           ^ [1, character 8](line)
  statement_id: py:0x1eb5bd0
  string: SELECT 'a'=1;
CONNECTION: [closed](closed)
  client_address: 192.168.1.160/32
  client_port: 45706
  version:
    PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
CONNECTOR: [IP4](IP4) pq://aust:***@192.168.0.3:5432/aust
  category: None
DRIVER: postgresql.driver.pq3.Driver None None

Mopeover, 'raw' pypostgresql w/o sqlalchemy seems to be working fine too:

import postgresql

db = postgresql.open("pq://user:pass@host/db")

transaction = db.xact()
transaction.start()
db.execute('SELECT 1;')
transaction.commit()

transaction = db.xact()
try:
    transaction.start()
    db.execute('SELECT \'a\'=1;')
    transaction.commit()
except Exception as e:
    print ("\n !!! Got exception executing, rolling back\n".format(e))
    try:
        transaction.rollback()
    except Exception as ee:
        print ("\n !!! Got exception rolling back {}\n".format(ee))

db.close()

yields:

 !!! Got exception executing, rolling back

[Mike Bayer]

that last example doesn't look like the DBAPI interface for pypostgresql. I'd have to get around to playing with a py3K build, in the meantime what happens if you create a pypostgresql DBAPI example ? this would be with "from postgresql.driver import dbapi20".

[Former user]

(original author: Phazorx) This does seems to be py-postgres issue rather than SQLA after all:

import postgresql.driver.dbapi20 as driver
connection = driver.connect(user = 'test', host = 'test', port = '5432', database = 'test', password='test')

transaction = connection.xact()
transaction.start()
connection.execute('SELECT 1;')
transaction.commit()

transaction = connection.xact()
try:
    transaction.start()
    connection.execute('SELECT \'a\'=1;')
    transaction.commit()
except Exception as e:
    print ("Got exception 1 (while executing):\n{}\n".format(e))
    try:
        transaction.rollback()
    except Exception as ee:
        print ("Got exception 2 (while rolling back):\n{}\n".format(ee))

get's 2 identical exceptions and (as I confirmed with packet sniffer) does not emit rollback statement:

Got exception 1 (while executing):
ошибка синтаксиса в значении типа integer: "a"
  CODE: 22P02
  LOCATION: File 'numutils.c', line 62, in pg_atoi from SERVER
  POSITION: 8
CONNECTION: [closed](closed)
  client_address: 192.168.1.160/32
  client_port: 50022
  version:
    PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
CONNECTOR: [IP4](IP4) pq://aust:***@192.168.0.3:5432/aust
  category: None
DRIVER: postgresql.driver.pq3.Driver

Got exception 2 (while rolling back):
ошибка синтаксиса в значении типа integer: "a"
  CODE: 22P02
  LOCATION: File 'numutils.c', line 62, in pg_atoi from SERVER
  POSITION: 8
CONNECTION: [closed](closed)
  client_address: 192.168.1.160/32
  client_port: 50022
  version:
    PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
CONNECTOR: [IP4](IP4) pq://aust:***@192.168.0.3:5432/aust
  category: None
DRIVER: postgresql.driver.pq3.Driver

[Mike Bayer]

great. i would note that your test script isn't DBAPI compliant - ideally, it would be fully DBAPI to test this:

import postgresql.driver.dbapi20 as driver
connection = driver.connect(user = 'test', host = 'test', port = '5432', database = 'test', password='test')

# in DBAPI, transaction is implicit

try:
    cursor = connection.cursor()
    cursor.execute('SELECT \'a\'=1;')
    connection.commit()
except Exception as e:
    print ("Got exception 1 (while executing):\n{}\n".format(e))
    try:
        connection.rollback()
    except Exception as ee:
        print ("Got exception 2 (while rolling back):\n{}\n".format(ee))

Make sure that has the same behavior, then report the bug on pypostgresql's tracker. While you're waiting for that, just use psycopg2. It supports Python 3 fully and is by far the best DBAPI among all database drivers, not just Postgresql.

[Mike Bayer]

Removing milestone: 0.7.9 (automated comment)