Thread: [HACKERS] Re: [BUGS] BUG #14629: ALTER TABLE VALIDATE CONSTRAINTS does not obeyNO INHERIT clause
[HACKERS] Re: [BUGS] BUG #14629: ALTER TABLE VALIDATE CONSTRAINTS does not obeyNO INHERIT clause
From
Amit Langote
Date:
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