Thread: TR: redundant constraint_schema
Hi,
Please find a question that didn't get an answer in the pgsql-sql & pgsql-hackers lists. I hope I'll get an answer here.
Thanks,
Olivier
De : Olivier Leprêtre [mailto:o.lepretre@gmail.com]
Envoyé : mercredi 29 août 2018 15:49
À : 'pgsql-sql@lists.postgresql.org'
Objet : redundant constraint_schema
Hi,
Can someone explain why, when a column is not created (add column if not exists), a redundant constraint is still created from the REFERENCES part ?
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 ?
Thanks,
Olivier
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. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
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
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. > > Regards, Andreas > Forgot to include Postgres version, 10.5. -- Adrian Klaver adrian.klaver@aklaver.com
Am 01.09.2018 um 18:44 schrieb Adrian Klaver: > Forgot to include Postgres version, 10.5. also 9.6 and 11beta1 Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Mine is 9.6 -----Message d'origine----- De : Adrian Klaver [mailto:adrian.klaver@aklaver.com] Envoyé : samedi 1 septembre 2018 18:45 À : Andreas Kretschmer; pgsql-general@lists.postgresql.org Objet : Re: TR: redundant constraint_schema 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. > > Regards, Andreas > Forgot to include Postgres version, 10.5. -- Adrian Klaver adrian.klaver@aklaver.com
On 09/01/2018 09:47 AM, Olivier Leprêtre wrote: > Mine is 9.6 I would submit a bug report here: https://www.postgresql.org/account/login/?next=/account/submitbug/ > > -----Message d'origine----- > De : Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Envoyé : samedi 1 septembre 2018 18:45 > À : Andreas Kretschmer; pgsql-general@lists.postgresql.org > Objet : Re: TR: redundant constraint_schema > > 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. >> >> Regards, Andreas >> > > Forgot to include Postgres version, 10.5. > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > -- Adrian Klaver adrian.klaver@aklaver.com
Am 01.09.2018 um 18:52 schrieb Adrian Klaver: > On 09/01/2018 09:47 AM, Olivier Leprêtre wrote: >> Mine is 9.6 > > I would submit a bug report here: > > https://www.postgresql.org/account/login/?next=/account/submitbug/ Olivier, please do that! Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
id #15361 Done, thanks ! -----Message d'origine----- De : Andreas Kretschmer [mailto:andreas@a-kretschmer.de] Envoyé : samedi 1 septembre 2018 19:01 À : pgsql-general@lists.postgresql.org Objet : Re: TR: redundant constraint_schema Am 01.09.2018 um 18:52 schrieb Adrian Klaver: > On 09/01/2018 09:47 AM, Olivier Leprêtre wrote: >> Mine is 9.6 > > I would submit a bug report here: > > https://www.postgresql.org/account/login/?next=/account/submitbug/ Olivier, please do that! Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com