2012/12/29 Peter Geoghegan <pe...@2ndquadrant.com>: > On 29 December 2012 18:37, Stephen Frost <sfr...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
