Re: BUG #16767: Silent dropping of CONSTRAINT... UNIQUE

On Tue, 8 Dec 2020 at 17:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > I've just noticed that equivalent unique constraints that are specified in
> > the same statement only generate one constraint;
>
> Yeah, that's intentional. Per the source code comments:
>
> * Scan the index list and remove any redundant index specifications. This
> * can happen if, for instance, the user writes UNIQUE PRIMARY KEY. A
> * strict reading of SQL would suggest raising an error instead, but that
> * strikes me as too anal-retentive. - tgl 2001-02-14
>
> The CREATE TABLE man page does explain this with respect to primary keys:
>
> The primary key constraint should name a set of columns that is
> different from the set of columns named by any unique
> constraint defined for the same table. (Otherwise, the unique
> constraint is redundant and will be discarded.)
>
> However, I see that there's not similar wording under UNIQUE; that says
>
> Each unique table constraint must name a set of columns that is
> different from the set of columns named by any other unique or
> primary key constraint defined for the table. (Otherwise it
> would just be the same constraint listed twice.)
>
> That implies that such a constraint is redundant, but it doesn't actually
> say it in so many words. We should probably use wording more like the
> PRIMARY KEY text.
>
> regards, tom lane

Although I fully agree that redundant unique-constraint definitions could be
discarded (and understand the reasons why you would choose to do so), the
current behaviour is doing so silently even if the user specified explicit
names for those constraints (implying that those constraints are not redundant
to the user). In those cases, at the very least I'd expect a NOTICE that these
redundant definitions were dropped, similar to how ADD CONSTRAINT USING INDEX
gives a notice that it renames the index.

Additionally, the docs are not consistently applied, as (e.g.) UNIQUE (col2,
col1) is not dropped in favour of PRIMARY KEY (col1, col2), which would be
the behaviour that is described in the docs (the sets of columns in the
constraints are equal).

Finally, if a part of the solution would be 'update the docs', then an update
to the ADD EXCLUDE-docs would be required as well, as that touches the same
code path, and is similarly undocumented.

-Matthias