Skip site navigation (1) Skip section navigation (2)

PL/Python SQL error code pass-through

From: Mika Eloranta <mel(at)ohmu(dot)fi>
To: pgsql-hackers(at)postgresql(dot)org
Subject: PL/Python SQL error code pass-through
Date: 2011-11-23 16:24:43
Message-ID: 0174B71F-7940-4819-BDC8-95EC8C9BA833@ohmu.fi (view raw or flat)
Thread:
Lists: 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 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

Attachment: 0001-PL-Python-SQL-error-code-pass-through.patch
Description: application/octet-stream (3.2 KB)

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-11-23 16:36:28
Subject: Re: [JDBC] Optimize postgres protocol for fixed size arrays
Previous:From: Simon RiggsDate: 2011-11-23 16:14:04
Subject: Re: Not HOT enough

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group