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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 15670
> Logged by: Michael Binder
> Email address: michael(at)mibi(dot)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