Re: enhanced error fields

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