PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15361
PG Version9.6.2
OSWindows 10
Opened2018-09-01 17:12:10+00
Reported byOlivier Lepretre
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15361
Logged by:          Olivier Lepretre
Email address:      (redacted)
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.klaver@aklaver.com

Messages

DateAuthorSubject
2018-09-01 17:12:10+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15361: Add column if not exists create duplicate constraint
2018-09-01 18:25:02+00Tom LaneRe: BUG #15361: Add column if not exists create duplicate constraint
2018-09-01 18:42:06+00Tom LaneRe: BUG #15361: Add column if not exists create duplicate constraint
2018-09-02 09:20:47+00=?iso-8859-1?Q?Olivier_Lepr=EAtre?=RE: BUG #15361: Add column if not exists create duplicate constraint
2018-09-02 11:45:15+00Stephen FrostRe: BUG #15361: Add column if not exists create duplicate constraint
2018-09-02 16:38:03+00Tom LaneRe: BUG #15361: Add column if not exists create duplicate constraint
2018-09-20 21:27:13+00Jamie StrachanRe: Re: BUG #15361: Add column if not exists create duplicate constraint