Обсуждение: [BUGS] BUG #14629: ALTER TABLE VALIDATE CONSTRAINTS does not obey NO INHERITclause
[BUGS] BUG #14629: ALTER TABLE VALIDATE CONSTRAINTS does not obey NO INHERITclause
От
buschmann@nidsa.net
Дата:
The following bug has been logged on the website: Bug reference: 14629 Logged by: Hans Buschmann Email address: buschmann@nidsa.net PostgreSQL version: 9.6.2 Operating system: Windows x64 Description: Given these both tables (orders_archiv inherits orders) on 9.6.2 Windows x64 (cut/paste from psql) xxxdb=# \d orders_archiv; ... Check constraints: "ck_or_old" CHECK (or_season < 24) NO INHERIT Inherits: orders xxxdb=# \d orders ... Check constraints: "ck_or_new" CHECK (or_season >= 24) NO INHERIT NOT VALID Triggers: tr_orders_insert BEFORE INSERT ON orders FOR EACH ROW WHEN (new.or_season < 24) EXECUTE PROCEDURE fn_orders_insert() Number of child tables: 1 (Use \d+ to list them.) When applying these commands to the parent table, the following errors are returned: xxxdb=# alter table orders validate constraint ck_or_new; ERROR: constraint "ck_or_new" of relation "orders_archiv" does not exist xxxdb=# alter table only orders validate constraint ck_or_new; ERROR: constraint must be validated on child tables too Background: From our original partitioning of quite a lot of tables according to xx_season columns (a season is a half year period) I dropped and recreated the check constraintsin a not valid state. At the end of the script to move the data from 2 seasons into the archiv tables I tried to reenable the check constraints and encountered those two errors. It seems that I can circumvent these errors by recreating the constraints without the not valid clause. Do I miss something here ? Hans Buschmann -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14629: ALTER TABLE VALIDATE CONSTRAINTS does not obeyNO INHERIT clause
От
Amit Langote
Дата:
On 2017/04/22 3:40, buschmann@nidsa.net wrote: > The following bug has been logged on the website: > > Bug reference: 14629 > Logged by: Hans Buschmann > Email address: buschmann@nidsa.net > PostgreSQL version: 9.6.2 > Operating system: Windows x64 > Description: > > > Given these both tables (orders_archiv inherits orders) on 9.6.2 Windows > x64 > (cut/paste from psql) > > xxxdb=# \d orders_archiv; > ... > Check constraints: > "ck_or_old" CHECK (or_season < 24) NO INHERIT > Inherits: orders > > > xxxdb=# \d orders > ... > Check constraints: > "ck_or_new" CHECK (or_season >= 24) NO INHERIT NOT VALID > Triggers: > tr_orders_insert BEFORE INSERT ON orders FOR EACH ROW WHEN > (new.or_season < 24) EXECUTE PROCEDURE fn_orders_insert() > Number of child tables: 1 (Use \d+ to list them.) > > > When applying these commands to the parent table, the following errors are > returned: > > xxxdb=# alter table orders validate constraint ck_or_new; > ERROR: constraint "ck_or_new" of relation "orders_archiv" does not exist > > > xxxdb=# alter table only orders validate constraint ck_or_new; > ERROR: constraint must be validated on child tables too > > Background: > From our original partitioning of quite a lot of tables according to > xx_season columns (a season is a half year period) I dropped and recreated > the check constraintsin a not valid state. > > At the end of the script to move the data from 2 seasons into the archiv > tables I tried to reenable the check constraints and encountered those two > errors. > > It seems that I can circumvent these errors by recreating the constraints > without the not valid clause. > > Do I miss something here ? Looks indeed like a bug to me. Performing VALIDATE CONSTRAINT on what is a non-inheritable constraint shouldn't look for that constraint in the child tables. Attached patch fixes that. Should be applied in all of the supported branches. Thanks, Amit -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Вложения
Re: [BUGS] BUG #14629: ALTER TABLE VALIDATE CONSTRAINTS does not obeyNO INHERIT clause
От
Amit Langote
Дата:
On 2017/04/24 13:16, Amit Langote wrote: > On 2017/04/22 3:40, buschmann@nidsa.net wrote: >> The following bug has been logged on the website: >> >> Bug reference: 14629 >> Logged by: Hans Buschmann >> Email address: buschmann@nidsa.net >> PostgreSQL version: 9.6.2 >> Operating system: Windows x64 >> Description: >> >> >> Given these both tables (orders_archiv inherits orders) on 9.6.2 Windows >> x64 >> (cut/paste from psql) >> >> xxxdb=# \d orders_archiv; >> ... >> Check constraints: >> "ck_or_old" CHECK (or_season < 24) NO INHERIT >> Inherits: orders >> >> >> xxxdb=# \d orders >> ... >> Check constraints: >> "ck_or_new" CHECK (or_season >= 24) NO INHERIT NOT VALID >> Triggers: >> tr_orders_insert BEFORE INSERT ON orders FOR EACH ROW WHEN >> (new.or_season < 24) EXECUTE PROCEDURE fn_orders_insert() >> Number of child tables: 1 (Use \d+ to list them.) >> >> >> When applying these commands to the parent table, the following errors are >> returned: >> >> xxxdb=# alter table orders validate constraint ck_or_new; >> ERROR: constraint "ck_or_new" of relation "orders_archiv" does not exist >> >> >> xxxdb=# alter table only orders validate constraint ck_or_new; >> ERROR: constraint must be validated on child tables too >> >> Background: >> From our original partitioning of quite a lot of tables according to >> xx_season columns (a season is a half year period) I dropped and recreated >> the check constraintsin a not valid state. >> >> At the end of the script to move the data from 2 seasons into the archiv >> tables I tried to reenable the check constraints and encountered those two >> errors. >> >> It seems that I can circumvent these errors by recreating the constraints >> without the not valid clause. >> >> Do I miss something here ? > > Looks indeed like a bug to me. Performing VALIDATE CONSTRAINT on what is > a non-inheritable constraint shouldn't look for that constraint in the > child tables. Attached patch fixes that. Should be applied in all of the > supported branches. Should have included -hackers when posting the patch. Here it is again for -hackers' perusal. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Вложения
On Mon, Apr 24, 2017 at 12:16 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote: > On 2017/04/22 3:40, buschmann@nidsa.net wrote: >> The following bug has been logged on the website: >> >> Bug reference: 14629 >> Logged by: Hans Buschmann >> Email address: buschmann@nidsa.net >> PostgreSQL version: 9.6.2 >> Operating system: Windows x64 >> Description: >> >> >> Given these both tables (orders_archiv inherits orders) on 9.6.2 Windows >> x64 >> (cut/paste from psql) >> >> xxxdb=# \d orders_archiv; >> ... >> Check constraints: >> "ck_or_old" CHECK (or_season < 24) NO INHERIT >> Inherits: orders >> >> >> xxxdb=# \d orders >> ... >> Check constraints: >> "ck_or_new" CHECK (or_season >= 24) NO INHERIT NOT VALID >> Triggers: >> tr_orders_insert BEFORE INSERT ON orders FOR EACH ROW WHEN >> (new.or_season < 24) EXECUTE PROCEDURE fn_orders_insert() >> Number of child tables: 1 (Use \d+ to list them.) >> >> >> When applying these commands to the parent table, the following errors are >> returned: >> >> xxxdb=# alter table orders validate constraint ck_or_new; >> ERROR: constraint "ck_or_new" of relation "orders_archiv" does not exist >> >> >> xxxdb=# alter table only orders validate constraint ck_or_new; >> ERROR: constraint must be validated on child tables too >> >> Background: >> From our original partitioning of quite a lot of tables according to >> xx_season columns (a season is a half year period) I dropped and recreated >> the check constraintsin a not valid state. >> >> At the end of the script to move the data from 2 seasons into the archiv >> tables I tried to reenable the check constraints and encountered those two >> errors. >> >> It seems that I can circumvent these errors by recreating the constraints >> without the not valid clause. >> >> Do I miss something here ? > > Looks indeed like a bug to me. Performing VALIDATE CONSTRAINT on what is > a non-inheritable constraint shouldn't look for that constraint in the > child tables. Attached patch fixes that. Should be applied in all of the > supported branches. Done. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs