Re: TR: redundant constraint_schema - Mailing list pgsql-general

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