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

Re: enhanced error fields

From: Matthew Woodcraft <matthew(at)woodcraft(dot)me(dot)uk>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Peter Geoghegan <peter(at)2ndquadrant(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Subject: Re: enhanced error fields
Date: 2012-07-03 19:48:55
Message-ID: 87ipe4pqvs.fsf@golux.woodcraft.me.uk (view raw or flat)
Thread:
Lists: pgsql-hackers
Peter Geoghegan <peter(at)2ndquadrant(dot)com> writes:
> So I took a look at the patch eelog-2012-05-09.diff today. All of the
> following remarks apply to it alone.

I've been trying out this patch for my own interest (I'm very pleased to
see work on this feature), and I have a couple of suggestions from a
user's point of view.


First: if a not null constraint is violated, the error report includes
CONSTRAINT NAME 'not_null_violation'. I think I would find it more
useful if CONSTRAINT NAME were left unset rather than given a value that
doesn't correspond to a real constraint. A client program can tell it's
a null constraint violation from the SQLSTATE.


Second: in the case where a foreign-key constraint is violated by a
change in the primary-key table, the error report gives the following
information:

  TABLE NAME:        name of primary-key table
  SCHEMA NAME:       schema of primary-key table
  CONSTRAINT NAME:   name of foreign-key constraint
  CONSTRAINT SCHEMA: schema of foreign-key table

It doesn't include the name of the foreign-key table (except in the
human-readable error message). But in principle you need to know that
table name to reliably identify the constraint that was violated.

I think what's going on is a mismatch between the way the constraint
namespace works in the SQL standard and in PostgreSQL: it looks like the
standard expects constraint names to be unique within a schema, while
PostgreSQL only requires them to be unique within a table. (A similar
issue makes information_schema less useful than the pg_ tables for
foreign key constraints.)

So I think it would be helpful to go beyond the standard in this case
and include the foreign-key table name somewhere in the report.

Possibly the enhanced-error reports could somehow add the table name to
the string in the CONSTRAINT NAME field, so that the interface
PostgreSQL provides looks like the one the standard envisages (which
ought to make it easier to write cross-database client code).

Or it might be simpler to just add a new enhanced-error field; I can
imagine cases where that table name would be the main thing I'd be
interested in.

-M-

In response to

Responses

pgsql-hackers by date

Next:From: Bjorn MunchDate: 2012-07-03 19:58:12
Subject: Re: Solaris docs
Previous:From: Peter EisentrautDate: 2012-07-03 19:47:29
Subject: Re: [PATCH] Make pg_basebackup configure and start standby

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