Re: enhanced error fields

On 29 December 2012 18:37, Stephen Frost <sfrost(at)snowman(dot)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