From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
cc: Andrew Gierth <andrew@tao11.riddles.org.uk>,
        =?iso-8859-1?Q?Andr=E9_H=E4nsel?= <andre@webkr.de>,
        pgsql-bugs@postgresql.org
Subject: Re: Two constraints with the same name not always allowed
In-reply-to: <20180902170511.ibjquzhymvnfvkqs@alvherre.pgsql>
References: <20180902170511.ibjquzhymvnfvkqs@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@2ndquadrant.com>
	message dated "Sun, 02 Sep 2018 14:05:11 -0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <11784.1535909727.1@sss.pgh.pa.us>
Date: Sun, 02 Sep 2018 13:35:27 -0400
Message-ID: <11785.1535909727@sss.pgh.pa.us>
Precedence: bulk

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2018-Sep-02, Tom Lane wrote:
>> 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.

> Hmm ... c.f. 7eca575d1c28.  Maybe we should split them out?  Are there
> reasons to have them together at all?

Yeah, I've occasionally thought about replacing pg_constraint with
two separate catalogs; we could keep pg_constraint as a union view
to avoid breaking clients that look at it.  But that'd be kind of
a large project, whereas adjusting the set of indexes for a catalog
is a pretty simple finger exercise in most cases.  (It's also unclear
how smart the planner would be about optimizing queries on such a
view.)

Thinking about the planner angle some more, it seems like probably
the most reasonable proposal is to add UNIQUE(conrelid,contypid,conname)
replacing pg_constraint_conrelid_index, but keep
pg_constraint_contypid_index.  While we could teach relevant parts
of backend/catalog how to use such a unique index to search for the
constraints of a domain, the planner would not know how to optimize
SQL queries with "WHERE contypid = xxx" unless we keep that index.
It would figure out that "WHERE conrelid = xxx" works with the
unique index, though.

			regards, tom lane