Re: Hm, table constraints aren't so unique as all that - Mailing list pgsql-hackers

I wrote:
> Over in the thread about enhanced error fields, I claimed that
> "constraints are uniquely named among those associated with a table,
> or with a domain".  But it turns out that that ain't necessarily so,
> because the code path for index constraints doesn't pay any attention
> to pre-existing check constraints: ...
> I think we need to tighten this down by having index-constraint creation
> check for conflicts with other constraint types.  It also seems like it
> might be a good idea to put in a unique index to enforce the intended
> lack of conflicts --- note that the existing index on (conname,
> connamespace) isn't unique.  It's a bit problematic that pg_constraint
> contains both table-related constraints and domain-related constraints,
> but it strikes me that we could get close enough by changing
> pg_constraint_conname_nsp_index to be a unique index on
> (conname, connamespace, conrelid, contypid).

I experimented with changing pg_constraint's index that way.  It doesn't
seem to break anything, but it turns out not to fix the problem
completely either, because if you use CREATE INDEX syntax to create an
index then no pg_constraint entry is made at all.  So it's still
possible to have an index with the same name as some non-index
constraint on the same table.

If we wanted to pursue this, we could think about decreeing that every
index must have a pg_constraint entry.  That would have some attraction
from the standpoint of catalog-entry uniformity, but there are
considerable practical problems in the way as well.  Notably, what would
we do for the conkey field in pg_constraint for an expression index?
(Failing to set that up as expected might well break client-side code.)
Also, I think we'd end up with the pg_depend entry between the index and
the constraint pointing in opposite directions depending on whether the
index was made using CONSTRAINT syntax or CREATE INDEX syntax.  There's
some precedent for that with the linkage between pg_class entries and
their pg_type rowtype entries, but that's a mess that I'd rather not
replicate.

Or we could leave the catalogs alone and just add more pre-creation
checking for conflicts.  That doesn't seem very bulletproof though
because of possible race conditions.  I think that right now it'd
be safe enough because of the table-level locks taken by ALTER TABLE
and CREATE INDEX --- but if the project to reduce ALTER TABLE's locking
level ever gets resurrected, we'd be at serious risk of introducing
a problem there.

Or on the third hand, we could just say it's okay if there are conflicts
between index names and check-constraint names.  Any given SQLSTATE
would only be mentioning one of these types of constraints, so it's
arguable that there's not going to be any real ambiguity in practice.

At the moment I'm inclined to leave well enough alone.  Thoughts?
        regards, tom lane