Re: enhanced error fields

Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com> writes:
> On 26 January 2013 22:36, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> BTW, one thing that struck me in a quick look-through is that the
>> ERRCODE_FOREIGN_KEY_VIOLATION patches seem to inconsistently send
>> either the PK or FK rel as the "errtable". Is this really per spec?
>> I'd have sort of expected that the reported table ought to be the one
>> that the constraint belongs to, namely the FK table.

> Personally, on the face of it I'd expect the "inconsistency" to simply
> reflect the fact that the error related to the referencing table or
> referenced table.

I looked in the spec a bit, and what I found seems to support my
recollection about this. In SQL99, it's 19.1 <get diagnostics
statement> that defines the usage of these fields, and I see

f) If the value of RETURNED_SQLSTATE corresponds to integrity
constraint violation, transaction rollback - integrity
constraint violation, or a triggered data change violation
that was caused by a violation of a referential constraint,
then:

i) The values of CONSTRAINT_CATALOG and CONSTRAINT_SCHEMA are
the <catalog name> and the <unqualified schema name> of the
<schema name> of the schema containing the constraint or
assertion. The value of CONSTRAINT_NAME is the <qualified
identifier> of the constraint or assertion.

ii) Case:

1) If the violated integrity constraint is a table
constraint, then the values of CATALOG_NAME, SCHEMA_
NAME, and TABLE_NAME are the <catalog name>, the
<unqualified schema name> of the <schema name>, and
the <qualified identifier> or <local table name>,
respectively, of the table in which the table constraint
is contained.

The notion of a constraint being "contained" in a table is a bit weird;
I guess they mean contained in the table's schema description. Anyway
it seems fairly clear to me that it's supposed to be the table that the
constraint belongs to, and that has to be the FK table not the PK table.

regards, tom lane