Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> 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.
> Presumably, the field pg_class.relchecks already keeps a count of the
> number of constraints, so it should be possible to assign numbers easily.
But pg_class.relchecks is per-relation --- how does it help you assign a
globally unique number?
After much thought I am coming around to the conclusion that we should
name constraints within-schemas (ie, there will be a schema OID column
in pg_constraint), but *not require these names to be unique*. DROP
CONSTRAINT, SET CONSTRAINTS, etc will act on all constraints matching
the target name, as they do now. This will create the minimum risk of
breaking existing database schemas, while still allowing us to move
some of the way towards SQL compliance --- in particular, SET
CONSTRAINTS with a schema-qualified constraint name would work as the
spec expects.
We would still take care to generate unique-within-a-relation names for
nameless constraints, using the same code that exists now, but we'd not
enforce this by means of a unique index on pg_constraint.
A compromise between that and exact SQL semantics would be to enforce
uniqueness of conname + connamespace + conrelid + contypid (the last
being a column that links to pg_type for domain constraints; conrelid
and contypid are each zero if not relevant). This would have the effect
of making relation constraint names unique per-relation, and domain
constraint names separately unique per-domain, and also allowing global
assertion names that are unique per-schema as in SQL92. This seems a
little baroque to me, but maybe it will appeal to others.
Comments?
regards, tom lane