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>
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 calledsmells 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 calledsmells 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 calledsmells 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_schemaOn 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 calledsmells 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