2012/12/29 Peter Geoghegan <peter@2ndquadrant.com>:
> On 29 December 2012 18:37, Stephen Frost <sfrost@snowman.net> wrote:
>> That's exactly what I was getting at also- in order to do a lookup in
>> the catalog, you need to know certain information to avoid potentially
>> getting multiple records back (which would be an error...).
>
> Well, Pavel said that since a constraint is necessarily associated
> with another object, the constraint name doesn't need to be separately
> qualified. That isn't quite the truth, but I think it's close enough.
>
> Note that I've documented a new set of requirements for various errcodes:
>
> Section: Class 23 - Integrity Constraint Violation
> ! Requirement: unused
> 23000 E ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION
> integrity_constraint_violation
> + Requirement: unused
> 23001 E ERRCODE_RESTRICT_VIOLATION
> restrict_violation
> + # Note that requirements for ERRCODE_NOT_NULL do not apply to domains:
> + Requirement: schema_name, table_name
> 23502 E ERRCODE_NOT_NULL_VIOLATION
> not_null_violation
> + Requirement: schema_name, table_name, constraint_name
> 23503 E ERRCODE_FOREIGN_KEY_VIOLATION
> foreign_key_violation
> + Requirement: schema_name, table_name, constraint_name
> 23505 E ERRCODE_UNIQUE_VIOLATION
> unique_violation
> + Requirement: constraint_name
> 23514 E ERRCODE_CHECK_VIOLATION
> check_violation
> + Requirement: schema_name, table_name, constraint_name
> 23P01 E ERRCODE_EXCLUSION_VIOLATION
> exclusion_violation
>
> So, unless someone adds a constraint name outside of these errcodes (I
> doubt that would make sense), there is exactly one case where a
> constraint_name could not have a schema_name (which, as I've said, is
> almost the same thing as constraint_schema, the exception being when
> referencing FKs on *other* tables are involved) - that case is
> ERRCODE_CHECK_VIOLATION.
>
> That's because this SQL could cause ERRCODE_CHECK_VIOLATION:
>
> select '123'::upc_barcode;
>
> What should schema_name be set to now? Surely not the schema of the
> type upc_barcode, since that would be inconsistent with a few other
> ERRCODE_CHECK_VIOLATION sites where we do know schema_name +
> table_name (those two are always either available together or not at
> all).
I forgot on domain :(
this is use case, where CONSTRAINT_SCHEMA has sense
>
> The bottom line is that I'm not promising that you can reliably look
> up the constraint, and I don't think that that should be a blocker, or
> even that it's all that important. You could do it reliably with the
> schema_name + table_name, though I'm not strongly encouraging that you
> do.
so then we probably need a CONSTRAINT_SCHEMA
>
> So I guess we disagree on that, though I'm not *that* strongly opposed
> to adding back in a constraint_schema field if the extra code is
> deemed worth it.
>
> Does anyone else have an opinion? Tom?
>
> --
> Peter Geoghegan http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training and Services