2013/1/27 Tom Lane <t...@sss.pgh.pa.us>: > Peter Geoghegan <peter.geoghega...@gmail.com> writes: >> On 26 January 2013 22:36, Tom Lane <t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
