"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> 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.
> 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