Re: enhanced error fields

2013/1/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.

+1

>
> regards, tom lane