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

PG Bug reporting form <noreply@postgresql.org> writes:

I don't know if this is the expected behavior but when I execute this
script:
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 end up with 3 FK constrains:

Yeah, this is the same problem previously reported at bug #15180,

/messages/by-id/152509815280.19803.16118194452213577808@wrigleys.postgresql.org

We had a sketch for a fix but discussion seems to have trailed off :-(

regards, tom lane

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