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

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 03/30/2016 10:21 AM, Tom Lane wrote:
>> 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.

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

Yeah, but I think the situation is pretty uncommon, because we already
reject duplicate constraint names in most cases. As far as I could see
in testing it earlier, these cases all fail already:

* create index constraint when same-named index constraint exists already
* create FK constraint when same-named index constraint exists already
* create FK constraint when same-named FK constraint exists already
* create check constraint when same-named check constraint exists already
* create FK constraint when same-named check constraint exists already

I think that the case Amit's patch plugged, namely create index constraint
when same-named FK or check constraint exists already, may be about the
only missing check. I just want a unique index to be sure we are covering
all cases.

Note also that because pg_dump prefers to create indexes before FK
constraints (for obvious reasons), I believe that such a case would
fail to dump/restore or pg_upgrade already.

regards, tom lane