Scope of constraint names
Rod Taylor
> with relations; so the table has no way to represent constraints
> associated with domains --- not to mention assertions, which aren't
It's ugly, but one could make the relid 0, and add a typeid which is
non-zero to represent a constraint against a domain. Relation
constraints have typeid 0 and relid as a normal number.
Obviously I prefer unique constraint names mostly for my users. For
some reason they tend to try to make assumptions about a constraint
given the name and have been fooled about what the constraint actually
is more than once due to 'having seen it before elsewhere'.
Is applying a lock on the pg_constraint table really that bad during
creation? Sure, you could only make one constraint at a time -- but
thats the same with relations, types, and a fair number of other things
that are usually created at the same time (or same transaction) as most
constraints will be.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Tom Lane
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
Tom Lane
>> 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?
I don't buy that argument even for foreign keys --- and remember that
pg_constraint will also hold entries for CHECK, UNIQUE, and PRIMARY KEY
constraints. I don't want to have to take a global lock whenever we
create an index.
>> 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...?
How is a static 'expected' file going to do loop-and-test?
One possible answer to that is to report all unnamed constraints as
"<unnamed>" in error messages, even though they'd have distinct names
internally. I don't much care for that approach though, since it might
make it hard for users to figure out which internal name to mention in
DROP CONSTRAINT. But it'd keep the expected regression output stable.
> If they're both equally evil, then maybe we should consider going the SQL92
> way, for compatibilities sake?
If the spec didn't seem so brain-damaged on this point, I'd be more
eager to follow it. I can't see any advantage in the way they chose
to do it. But yeah, I'd lean to following the spec, if we can think
of a way around the locking and regression testing issues it creates.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly