[Locale Incompatibility] transaction rollback fails after DBAPI originated error in pg9 environment

[Phazorx]

Using combinations of following:

• py-postgresql: 1.0.2, 1.0.3
• libpg: 8.4.7, 8.4.12, 9.1.4
• PostgreSQL: 9.1.1, 9.1.3

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 and does not emit rollback statement to server side).

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

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))

results in:

Got exception 1 (while executing):
ошибка синтаксиса в значении типа integer: "a"
  CODE: 22P02
  LOCATION: File 'numutils.c', line 62, in pg_atoi from SERVER
  POSITION: 8
STATEMENT: [parsing]
  LINE:
    SELECT 'a'=1;
           ^ [line 1, character 8]
  statement_id: py:0x2e90890
  string: SELECT 'a'=1;
CONNECTION: [closed]
  client_address: 192.168.1.160/32
  client_port: 53660
  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: [Host] pq://aust:***@aust.aa: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
STATEMENT: [parsing]
  LINE:
    SELECT 'a'=1;
           ^ [line 1, character 8]
  statement_id: py:0x2e90890
  string: SELECT 'a'=1;
CONNECTION: [closed]
  client_address: 192.168.1.160/32
  client_port: 53660
  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: [Host] pq://aust:***@aust.aa:5432/aust
  category: None
DRIVER: postgresql.driver.pq3.Driver

at same time when serverside is 8.4.*, the output is as expected:

Got exception 1 (while executing):
invalid input syntax for integer: "a"
  CODE: 22P02
  LOCATION: File 'numutils.c', line 64, in pg_atoi from SERVER
  POSITION: 8
STATEMENT: [parsing]
  LINE:
    SELECT 'a'=1;
           ^ [line 1, character 8]
  statement_id: py:0x2c6ded0
  string: SELECT 'a'=1;
CONNECTION: [failed block]
  client_address: 127.0.0.1/32
  client_port: 34153
  version:
    PostgreSQL 8.4.12 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo Hardened 4.6.2 p1.0, pie-0.4.5) 4.6.2, 64-bit
CONNECTOR: [Host] pq://pkolla:***@localhost:5432/aust
  category: None
DRIVER: postgresql.driver.pq3.Driver

[elprans]

That's a known issue. It's due to the localization of severity field in errors. Below is a patch that helps on Russian locale, which you are apparently using.

 postgresql/protocol/xact3.py |    3 ++-
 1 files changed, 2 insertions(+), 1 deletions(-)

diff --git a/postgresql/protocol/xact3.py b/postgresql/protocol/xact3.py
index 8064b61..190742d 100644
--- a/postgresql/protocol/xact3.py
+++ b/postgresql/protocol/xact3.py
@@ -492,7 +492,8 @@ class Instruction(Transaction):
                if x[0] == ERROR_TYPE:
                    em = ERROR_PARSE(x[1])
                    # Is it fatal?
-                   self.fatal = fatal = em[b'S'].upper() != b'ERROR'
+                   errors = {b'ERROR', b'\xd0\x9e\xd0\xa8\xd0\x98\xd0\x91\xd0\x9a\xd0\x90'}
+                   self.fatal = fatal = em[b'S'].upper() not in errors
                    self.error_message = em
                    if fatal is True:
                        # Can't sync up if the session is closed.
-- 
1.7.8.6

[jwp]

I think I might apply that patch. Maybe write a script to scrape the translations for ERROR from the Postgres source.

[jwp]

I find myself thinking about this issue from time to time and it's starting to get on my nerves. =\

[diogobaeder]

Happened to me as well, and, if I change the PG locale to en_US.UTF-8 (instead of my system's pt_BR.utf8), then it works fine. I think the library should rely on more stable error information than descriptions that might change according to locality. Relying on textual description to change application behavior is very error-prone.

[jwp]

I believe localization of the severity field is relatively recent--after the software was written, and Postgres provides no other indicator to distinguish between fatal and non-fatal errors. Potentially, looking at the error code would be appropriate, but we'd have to track down every possible fatal code, which is not likely to be future-proof. I guess we could force the localization to en_US.utf-8 until a better solution is found. Well, at the very least a warning would be appropriate.

[jwp]

Is this still an issue?

[jwp]

Appears irrelevant now. Adjusting the gotcha regarding locales.