Scope of constraint names - Mailing list pgsql-hackers

SQL92 requires named constraints to have names that are unique within
their schema.  Our past implementation did not require constraint names
to be unique at all; as a compromise I suggested requiring constraint
names to be unique for any given relation.  Rod Taylor's pending
pg_constraint patch implements that approach, but I'm beginning to have
second thoughts about it.

One problem I see is that pg_constraint entries can *only* be associated
with relations; so the table has no way to represent constraints
associated with domains --- not to mention assertions, which aren't
associated with any table at all.  I'm in no hurry to try to implement
assertions, but domain constraints are definitely interesting.  We'd
probably have to put domain constraints into a separate table, which
is possible but not very attractive.

At the SQL level, constraint names seem to be used in only two
contexts: DROP CONSTRAINT subcommands of ALTER TABLE and ALTER DOMAIN
commands, and SET CONSTRAINTS ... IMMEDIATE/DEFERRED.  In the DROP
context there's no real need to identify constraints globally, since
the associated table or domain name is available, but in SET CONSTRAINTS
the syntax doesn't include a table name.

Our current implementation of SET CONSTRAINTS changes the behavior of
all constraints matching the specified name, which is pretty bogus
given the lack of uniqueness.  If we don't go over to the SQL92 approach
then I think we need some other way of handling SET CONSTRAINTS that
allows a more exact specification of the target constraint.

A considerable advantage of per-relation constraint names is that a new
unique name can be assigned for a nameless constraint while holding only
a lock on the target relation.  We'd need a global lock to create unique
constraint names in the SQL92 semantics.  The only way I can see around
that would be to use newoid(), or perhaps a dedicated sequence
generator, to construct constraint names.  The resulting unpredictable
constraint names would be horribly messy to deal with in the regression
tests, so I'm not eager to do this.

Even per-relation uniqueness has some unhappiness: if you have a domain
with a named constraint, and you try to use this domain for two columns
of a relation, you'll get a constraint name conflict.  Inheriting
similar constraint names from two different parent relations is also
troublesome.  We could get around these either by going back to the
old no-uniqueness approach, or by being willing to alter constraint
names to make them unique (eg, by tacking on "_nnn" when needed).
But this doesn't help SET CONSTRAINTS.

At the moment I don't much like any of the alternatives.  Ideas anyone?
        regards, tom lane