Re: Hm, table constraints aren't so unique as all that

On 1/29/13 6:40 PM, Tom Lane wrote:
> 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?

ISTM that we shouldn't blindly equate indexes and constraints. I'd
actually argue they should in no way be related, except that I've found
it to be extremely useful to create unique indexes that cover scenarios
that you can't handle with an actual unique constraint (ie:
UNIQUE(field_a, field_B) WHERE field_c IS NULL).

Perhaps a good compromise would be that only unique indexes get entries
in pg_constraint.

Something else worth mentioning is that hopefully we'll eventually have
indexes (including unique) that can span multiple tables.