Re: [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

On 03/30/2016 10:21 AM, Tom Lane wrote:
> Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> On 2016/03/30 15:16, Harshal Dhumal wrote:
>>> If we create two different type of constrains (lets say primary key and
>>> foreign key) on same table with same name (lets say 'key' ) then its shows
>>> same drop query for both constrains.
> I have a vague recollection that non-uniqueness of constraint names may
> have been intentional at some point. But yeah, the existence of the
> ALTER TABLE DROP CONSTRAINT syntax seems to make that a pretty bad idea.
>
>> It seems that, whereas name uniqueness check occurs when creating a named
>> FK constraint, the same does not occur when creating a named PK constraint
>> or any index-based constraint for that matter (they are handled by
>> different code paths - in the latter's case, name conflicts with existing
>> relations is checked for when creating the constraint index)
> I think that if we want to ensure uniqueness of constraint names, this
> is really approaching it the wrong way, as it still fails to provide
> any guarantees (consider concurrent index creation, for example).
> What we need is a unique index on pg_constraint.
>
> The problem with that is that pg_constraint contains both table-related
> and type (domain) related constraints; but it strikes me that we could
> probably create a unique index on (conrelid, contypid, conname). Given
> the convention that conrelid is zero in a type constraint and contypid
> is zero in a table constraint, this should work to enforce per-table
> or per-type constraint name uniqueness. The cost of an extra index
> is a bit annoying, but we could probably make it help pay for itself
> by speeding up assorted searches.
>
>

+1, but does that mean people will have to change constraint names to be
compliant before running pg_upgrade?

cheers

andrew