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

Re: enhanced error fields

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>, "anarazel(at)anarazel(dot)de" <andres(at)anarazel(dot)de>, Robert Haas <robertmhaas(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Re: enhanced error fields
Date: 2013-01-29 17:00:46
Message-ID: CAFj8pRCFqsc7F3Zs=XH7L8F+VTnXQ8XnMm+NyZShUhFdk0e=Mw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Christopher BrowneDate: 2013-01-29 17:04:11
Subject: Re: Event Triggers: adding information
Previous:From: Devrim GündüzDate: 2013-01-29 16:56:46
Subject: Re: pgsql: Fast promote mode skips checkpoint at end of recovery.

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