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 problem for 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
0001-PL-Python-SQL-error-code-pass-through.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
