Waiting for 9.3 – Provide database object names as separate fields in error messages.

Yet another missed thing for “Waiting for 9.3". Sorry about that.

On 29th of January, Tom Lane committed patch:

Provide database object names as separate fields in error messages.
 
This patch addresses the problem that applications currently have to
extract object names from possibly-localized textual error messages,
if they want to know for example which index caused a UNIQUE_VIOLATION
failure.  It adds new error message fields to the wire protocol, which
can carry the name of a table, table column, data type, or constraint
associated with the error.  (Since the protocol spec has always instructed
clients to ignore unrecognized field types, this should not create any
compatibility problem.)
 
Support for providing these new fields has been added to just a limited set
of error reports (mainly, those in the "integrity constraint violation"
SQLSTATE class), but we will doubtless add them to more calls in future.
 
Pavel Stehule, reviewed and extensively revised by Peter Geoghegan, with
additional hacking by Tom Lane.

This is pretty cool in cases where you are doing many things using single query. For example you're calling function that does multiple writes, or you use triggers, or writable CTE.

The thing is that in such cases, when there is constraint violation you get not much information:

$ CREATE TABLE t (i INT4 PRIMARY KEY);
psql:z.sql:2: NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "t_pkey" FOR TABLE "t"
CREATE TABLE
 
$ CREATE TABLE t2 (i INT4 PRIMARY KEY);
psql:z.sql:3: NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "t2_pkey" FOR TABLE "t2"
CREATE TABLE
 
$ CREATE OR REPLACE FUNCTION copy_value() RETURNS TRIGGER AS
    $BODY$
    DECLARE
    BEGIN
        INSERT INTO t2 (i) VALUES (NEW.i);
        RETURN NEW;
    END;
    $BODY$
    LANGUAGE 'plpgsql';
CREATE FUNCTION
 
$ CREATE TRIGGER copy_value AFTER INSERT ON t FOR EACH ROW EXECUTE PROCEDURE copy_value();
CREATE TRIGGER
 
$ INSERT INTO t2 (i) VALUES (1);
INSERT 0 1
 
$ INSERT INTO t (i) VALUES (1);
psql:z.sql:15: ERROR:  duplicate KEY VALUE violates UNIQUE CONSTRAINT "t2_pkey"
DETAIL:  KEY (i)=(1) already EXISTS.
CONTEXT:  SQL statement "INSERT INTO t2 (i) VALUES (NEW.i)"
PL/pgSQL FUNCTION copy_value() line 4 at SQL statement

Now, it is possible to find out what exactly is the problem, but the constraint name is “hidden" in a error message string, so we'd have to parse it out. And the table/column names are hidden in another strings, also requiring some parsing.

In 9.3, when run in psql, it will look very similar:

$ INSERT INTO t (i) VALUES (1);
psql:z.sql:15: ERROR:  duplicate KEY VALUE violates UNIQUE CONSTRAINT "t2_pkey"
DETAIL:  KEY (i)=(1) already EXISTS.
CONTEXT:  SQL statement "INSERT INTO t2 (i) VALUES (NEW.i)"
PL/pgSQL FUNCTION copy_value() line 4 at SQL statement

But now, in verbose mode, we will see:

$ INSERT INTO t (i) VALUES (1);
psql:z.sql:16: ERROR:  23505: duplicate KEY VALUE violates UNIQUE CONSTRAINT "t2_pkey"
DETAIL:  KEY (i)=(1) already EXISTS.
CONTEXT:  SQL statement "INSERT INTO t2 (i) VALUES (NEW.i)"
PL/pgSQL FUNCTION copy_value() line 4 at SQL statement
SCHEMA NAME:  public
TABLE NAME:  t2
CONSTRAINT NAME:  t2_pkey
LOCATION:  _bt_check_unique, nbtinsert.c:398

Schema, table name, and constraint name all ready to be used, without any parsing necessary.

How to get to this information will depend on your language/library. In Perls DBD::Pg, you just set “pg_errorlevel" option to 2, and you will get them in errstr.

Cool stuff, thanks Pavel.