Re: BUG #15670: alter table .. add column if not exists ...references ...; adds a FK constraint on each execution

On Tue, Mar 5, 2019 at 11:35 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      15670
> Logged by:          Michael Binder
> Email address:      michael@mibi.io
> PostgreSQL version: 11.2
> Operating system:   Debian 9.8
> Description:
>
> Hi,
>
> I don't know if this is the expected behavior but when I execute this
> script:
>
> create table test1 (
>   id serial primary key,
>   name text
> );
>
> create table test2 (
>   id serial primary key
> );
>
> alter table test2 add column if not exists test1_fk integer not null
> references test1(id);
> alter table test2 add column if not exists test1_fk integer not null
> references test1(id);
> alter table test2 add column if not exists test1_fk integer not null
> references test1(id);

I think the foreign key constraint creation (references test1(id)
part) is executed independently of add column part, so the latter's
no-op semantics due to the "if not exists" clause doesn't apply to
foreign key creation.  You would get duplicate constraints even if you
had instead done the following:

alter table test2 add column if not exists test1_fk integer not null;
alter table test2 add foreign key (test1_fk) references test1(id);
alter table test2 add foreign key (test1_fk) references test1(id);
alter table test2 add foreign key (test1_fk) references test1(id);

\d test2
                             Table "public.test2"
  Column  |  Type   | Collation | Nullable |              Default
----------+---------+-----------+----------+-----------------------------------
 id       | integer |           | not null | nextval('test2_id_seq'::regclass)
 test1_fk | integer |           | not null |
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "test2_test1_fk_fkey" FOREIGN KEY (test1_fk) REFERENCES test1(id)
    "test2_test1_fk_fkey1" FOREIGN KEY (test1_fk) REFERENCES test1(id)
    "test2_test1_fk_fkey2" FOREIGN KEY (test1_fk) REFERENCES test1(id)

In fact same thing happens when adding unnamed check constraints (like
I did above when adding the foreign key constraint):

alter table bar add check (a > 0);
alter table bar add check (a > 0);
alter table bar add check (a > 0);

\d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |
Check constraints:
    "bar_a_check" CHECK (a > 0)
    "bar_a_check1" CHECK (a > 0)
    "bar_a_check2" CHECK (a > 0)
Foreign-key constraints:
    "bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
    "bar_a_fkey1" FOREIGN KEY (a) REFERENCES foo(a)
    "bar_a_fkey2" FOREIGN KEY (a) REFERENCES foo(a)

I don't know why Postgres doesn't try to recognize a duplicate
constraint definition.  Maybe the thinking is that users won't
deliberately add the same constraint, but the resulting behavior as
seen in the OP's example may surprise some.

Thanks,
Amit