BUG #15361: Add column if not exists create duplicate constraint

The following bug has been logged on the website:

Bug reference: 15361
Logged by: Olivier Lepretre
Email address: postgresql(at)noetika(dot)com
PostgreSQL version: 9.6.2
Operating system: Windows 10
Description:

I have a patching script that is supposed to add column if not existing :

ALTER TABLE myschem.table1
ADD COLUMN IF NOT EXISTS col1 VARCHAR(254) REFERENCES
myschem.table2(col2)

When col1 already exists, I expected that nothing would happen. But, when
applying the previous query and then querying :

select constraint_name from information_schema.key_column_usage where
constraint_schema='myschem'

I notice that a new constraint "table1_col2_fkeyxxx" is created each time
the previous ALTER TABLE ADD COLUMN is called (with xxx being a new number
each time)

It seems strange to have second part of statement executed (references) when
first part (add column) was not. Would it be possible that this sort of
query executes "references" first ?

Following are comments from pgsql-general list

Problem occurs on 9.6, 10.5 and 11beta1

On 09/01/2018 09:27 AM, Andreas Kretschmer wrote:
>
>
> Am 01.09.2018 um 17:50 schrieb Olivier Leprêtre:
>> I notice that a new constraint "table1_col2_fkeyxxx" is created each
>> time the previous ALTER TABLE ADD COLUMN is called
>
> smells like a bug.

Yeah, a quick test on a database where I have an event trigger:

create table fk_parent(col2 varchar primary key);
NOTICE: Table public.fk_parent created
NOTICE: caught CREATE TABLE event on 'public.fk_parent'
NOTICE: caught CREATE INDEX event on 'public.fk_parent_pkey'
create table fk_child(col1 varchar references fk_parent(col2));
NOTICE: Table public.fk_child created
NOTICE: caught CREATE TABLE event on 'public.fk_child'
NOTICE: caught ALTER TABLE event on 'public.fk_child'
\d fk_child
Table "public.fk_child"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
col1 | character varying | | |
Foreign-key constraints:
"fk_child_col1_fkey" FOREIGN KEY (col1) REFERENCES fk_parent(col2)

alter table fk_child add column if not exists col1 varchar references
fk_parent(col2);

NOTICE: column "col1" of relation "fk_child" already exists, skipping

NOTICE: caught ALTER TABLE event on 'public.fk_child'

ALTER TABLE

\d fk_child
Table "public.fk_child"

Column | Type | Collation | Nullable | Default

--------+-------------------+-----------+----------+---------

col1 | character varying | | |

Foreign-key constraints:

"fk_child_col1_fkey" FOREIGN KEY (col1) REFERENCES fk_parent(col2)

"fk_child_col1_fkey1" FOREIGN KEY (col1) REFERENCES fk_parent(col2)

>
> Regards, Andreas
>
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com