Re: Scope of constraint names - Mailing list pgsql-hackers

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

Hmmm...there must be some sort of schema that can do both in one table?
Even something nastly like:

refid Oid of relation or domain
type 'r' for relation and 'd' for domain
...

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

If we do go over to SQL92, what kind of problems will people have reloading
their old schema?  Should <unnamed> be excluded from the uniqueness
check...?

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

Surely adding a foreign key is what you'd call a 'rare' event in a database,
occurring once once for millions or queries?  Hence, we shouldn't worry
about it too much?

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

Surely you do the ol' loop and test sort of thing...?

> 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?

If they're both equally evil, then maybe we should consider going the SQL92
way, for compatibilities sake?

Chris