Обсуждение: BUG #15670: alter table .. add column if not exists ... references ...; adds a FK constraint on each execution

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 end up with 3 FK constrains:

select 
    constraint_name
from information_schema.key_column_usage 
where table_name='test2' 
and position_in_unique_constraint is not null;
--
test2_test1_fk_fkey
test2_test1_fk_fkey1
test2_test1_fk_fkey2


best regards,
Michael Binder

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,

https://www.postgresql.org/message-id/flat/152509815280.19803.16118194452213577808%40wrigleys.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