PL/Python SQL error code pass-through - Mailing list pgsql-hackers

Hi all,

Here's a little SQL snippet that exposes an apparent regression in the 9.1.x PL/Python behavior:

---clip---
# cat foo.sql
\set VERBOSITY 'verbose'

CREATE table bar (a INTEGER CONSTRAINT hello CHECK (a > 1));

CREATE OR REPLACE FUNCTION foo ()
  RETURNS integer
AS $$
  plpy.execute("INSERT INTO bar (a) VALUES (2)")
  plpy.execute("INSERT INTO bar (a) VALUES (1)")
  return 123
$$ LANGUAGE plpythonu;

SELECT * FROM foo();
---clip---


PostgreSQL 9.0 behavior:

---clip---
# psql < foo.sql
CREATE TABLE
CREATE FUNCTION
WARNING:  01000: PL/Python: plpy.SPIError: unrecognized error in PLy_spi_execute_query
CONTEXT:  PL/Python function "foo"
LOCATION:  PLy_elog, plpython.c:3532
ERROR:  23514: new row for relation "bar" violates check constraint "hello"
CONTEXT:  SQL statement "INSERT INTO bar (a) VALUES (1)"
PL/Python function "foo"
LOCATION:  ExecConstraints, execMain.c:1330
---clip---

Note the proper 23514 error code.


PostgreSQL 9.1.1 behavior:

---clip---
# psql < foo.sql
ERROR:  42P07: relation "bar" already exists
LOCATION:  heap_create_with_catalog, heap.c:1011
CREATE FUNCTION
ERROR:  XX000: spiexceptions.CheckViolation: new row for relation "bar" violates check constraint "hello"
CONTEXT:  Traceback (most recent call last):
  PL/Python function "foo", line 3, in <module>
    plpy.execute("INSERT INTO bar (a) VALUES (1)")
PL/Python function "foo"
LOCATION:  PLy_elog, plpython.c:4502
---clip---

In fact, all SQL error that occur within PL/Python seem to be returned with the "XX000" error code. This is a bit of a
problemfor client-side logic that detects e.g. constraint violations based on the SQL error code. 

A small patch that includes passing thru the SQL error code is attached.


Test run with PostgreSQL 9.1.1 + patch:

---clip---
# psql < foo.sql
ERROR:  42P07: relation "bar" already exists
LOCATION:  heap_create_with_catalog, heap.c:1011
CREATE FUNCTION
ERROR:  23514: spiexceptions.CheckViolation: new row for relation "bar" violates check constraint "hello"
CONTEXT:  Traceback (most recent call last):
  PL/Python function "foo", line 4, in <module>
    plpy.execute("INSERT INTO bar (a) VALUES (1)")
PL/Python function "foo"
LOCATION:  PLy_elog, plpython.c:4504
---clip---

Cheers!

    - Mika