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).
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 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