From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
cc: =?utf-8?B?QW5kcsOpIEjDpG5zZWw=?= <andre@webkr.de>,
        pgsql-bugs@postgresql.org
Subject: Re: Two constraints with the same name not always allowed
In-reply-to: <87in3ovy0r.fsf@news-spur.riddles.org.uk>
References: <0c1001d4428f$0942b430$1bc81c90$@webkr.de>
 <87in3ovy0r.fsf@news-spur.riddles.org.uk>
Comments: In-reply-to Andrew Gierth <andrew@tao11.riddles.org.uk>
	message dated "Sun, 02 Sep 2018 14:22:56 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <10109.1535907645.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sun, 02 Sep 2018 13:00:45 -0400
Message-ID: <10110.1535907645@sss.pgh.pa.us>
Precedence: bulk

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "André" == André Hänsel <andre@webkr.de> writes:
>  André> Case 2:
>  André> CREATE TABLE t(c integer);
>  André> ALTER TABLE t ADD CONSTRAINT foo CHECK(c > 1);
>  André> ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c);
>  André>  -> Creates two constraints, both called "foo".

> I'd call _that_ a bug, myself - having two constraints on a table with
> the same name potentially messes up a lot of automated maintenance
> operations.

Agreed.  We must have missed a check for constraint-exists someplace.

This also points up the lack of a suitable unique index on pg_constraint.
It's sort of difficult to figure out what that should look like given that
pg_constraint contains two quasi-independent collections of constraints,
but maybe UNIQUE(conrelid,contypid,conname) would serve given the
reasonable assumption that exactly one of conrelid and contypid is zero.

Potentially we could drop pg_constraint_conrelid_index and
pg_constraint_contypid_index, replacing scans on those with
scans on this new unique index.

			regards, tom lane