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