Re: enhanced error fields

Hello

2012/7/3 Matthew Woodcraft <matthew(at)woodcraft(dot)me(dot)uk>:
> 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.
>

I don't think so generation some special name is good idea. In this
case - important values are in COLUMN_NAME, TABLE_NAME, SCHEMA_NAME

postgres=# create table ff(a int not null);
CREATE TABLE
postgres=# \set VERBOSITY verbose
postgres=# insert into ff values(null);
ERROR: 23502: null value in column "a" violates not-null constraint
DETAIL: Failing row contains (null).
LOCATION: ExecConstraints, execMain.c:1527
COLUMN NAME: a
TABLE NAME: ff
SCHEMA NAME: public
CONSTRAINT NAME: not_null_violation
CONSTRAINT SCHEMA: public

>
> 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
>

postgres=# create table a1(a int primary key);
NOTICE: 00000: CREATE TABLE / PRIMARY KEY will create implicit index
"a1_pkey" for table "a1"
LOCATION: DefineIndex, indexcmds.c:600
CREATE TABLE
postgres=# create table a2(a int references a1(a));
CREATE TABLE
postgres=# insert into a2 values(10);
ERROR: 23503: insert or update on table "a2" violates foreign key
constraint "a2_a_fkey"
DETAIL: Key (a)=(10) is not present in table "a1".
LOCATION: ri_ReportViolation, ri_triggers.c:3228
TABLE NAME: a2
SCHEMA NAME: public
CONSTRAINT NAME: a2_a_fkey
CONSTRAINT SCHEMA: public

postgres=# \d a2
Table "public.a2"
Column │ Type │ Modifiers
────────┼─────────┼───────────
a │ integer │
Foreign-key constraints:
"a2_a_fkey" FOREIGN KEY (a) REFERENCES a1(a)

I agree so access to related table is not simple, but you know
constraint name, and you can take referenced table from constraint
definition.

so any special column is not necessary. It can be done in future, but
for this moment I would add only really necessary fields.

> 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).

same situation is with triggers

I prefer add two new fields CONSTRAINT_TABLE and TRIGGER_TABLE so
NAME, TABLE and SCHEMA is unique

Regards and thank you for comments

Pavel

>
> 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-