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

The following bug has been logged on the website:

Bug reference:      16767
Logged by:          Matthias vd Meent
Email address:      boekewurm+postgres@gmail.com
PostgreSQL version: 12.5
Operating system:   Debian Stretch (9.13)
Description:

Hi,

I've just noticed that equivalent unique constraints that are specified in
the same statement only generate one constraint; but if they are specified
in different statements they generate the correct constraints. E.g.:

CREATE TABLE test (
  id bigint,
  val test,
  CONSTRAINT u_id UNIQUE (id),
  CONSTRAINT u_id2 UNIQUE (id),
  CONSTRAINT pk_id PRIMARY KEY (id),
  CONSTRAINT u_val UNIQUE (val)
);

vs

CREATE TABLE test (
  id bigint,
  val test
);

ALTER TABLE test ADD CONSTRAINT u_id UNIQUE (id);
ALTER TABLE test ADD CONSTRAINT u_id2 UNIQUE (id);
ALTER TABLE test ADD CONSTRAINT pk_id PRIMARY KEY (id);
ALTER TABLE test ADD CONSTRAINT u_val UNIQUE (val);

The first only results in a primary key on (id), and unique(val), the second
(correctly?) generates 4 constraints on the test table. 

This unexpected and undocumented behaviour also exists at least in pg10.15
and pg11.10

-Matthias

PG Bug reporting form <noreply@postgresql.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

On Tue, 8 Dec 2020 at 17:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.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

On 2020-12-08 17:48, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.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

It's nonetheless inconsistent that you can create redundant unique 
constraints via ALTER TABLE, but doing it in CREATE TABLE results in 
different behavior.

This all seems a bit dubious to me.  We should either allow it or 
prohibit it, not silently do something else in some cases.