On 2017/04/24 13:16, Amit Langote wrote: > On 2017/04/22 3:40, buschm...@nidsa.net wrote: >> The following bug has been logged on the website: >> >> Bug reference: 14629 >> Logged by: Hans Buschmann >> Email address: buschm...@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
>From 3687032a63906a7f71d97459fd362c2f9138b5b2 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Mon, 24 Apr 2017 11:33:05 +0900 Subject: [PATCH] Fix VALIDATE CONSTRAINT to consider NO INHERIT attribute Currently, trying to validate a NO INHERIT constraint on the parent will search for the constraint in child tables (where it is not supposed to exist), wrongly causing a "constraint does not exist" error. --- src/backend/commands/tablecmds.c | 5 +++-- src/test/regress/expected/alter_table.out | 20 ++++++++++++++++++++ src/test/regress/sql/alter_table.sql | 15 +++++++++++++++ 3 files changed, 38 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index a02904c85c..626928658b 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7680,9 +7680,10 @@ ATExecValidateConstraint(Relation rel, char *constrName, bool recurse, /* * If we're recursing, the parent has already done this, so skip - * it. + * it. Also, if the constraint is a NO INHERIT constraint, we + * shouldn't try to look for it in the children. */ - if (!recursing) + if (!recursing && !con->connoinherit) children = find_all_inheritors(RelationGetRelid(rel), lockmode, NULL); diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 883a5c9864..aed6964724 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -367,6 +367,26 @@ NOTICE: merging constraint "identity" with inherited definition ALTER TABLE tmp3 VALIDATE CONSTRAINT identity; NOTICE: boo: 16 NOTICE: boo: 20 +-- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT +create table parent_noinh_convalid (a int); +create table child_noinh_convalid () inherits (parent_noinh_convalid); +insert into parent_noinh_convalid values (1); +insert into child_noinh_convalid values (1); +alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid; +-- fail, because of the row in parent +alter table parent_noinh_convalid validate constraint check_a_is_2; +ERROR: check constraint "check_a_is_2" is violated by some row +delete from only parent_noinh_convalid; +-- ok (parent itself contains no violating rows) +alter table parent_noinh_convalid validate constraint check_a_is_2; +select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2'; + convalidated +-------------- + t +(1 row) + +-- cleanup +drop table parent_noinh_convalid, child_noinh_convalid; -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on -- tmp4 is a,b ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index eb1b4b536f..8ec0cf0f61 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -307,6 +307,21 @@ ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b)); ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID; ALTER TABLE tmp3 VALIDATE CONSTRAINT identity; +-- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT +create table parent_noinh_convalid (a int); +create table child_noinh_convalid () inherits (parent_noinh_convalid); +insert into parent_noinh_convalid values (1); +insert into child_noinh_convalid values (1); +alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid; +-- fail, because of the row in parent +alter table parent_noinh_convalid validate constraint check_a_is_2; +delete from only parent_noinh_convalid; +-- ok (parent itself contains no violating rows) +alter table parent_noinh_convalid validate constraint check_a_is_2; +select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2'; +-- cleanup +drop table parent_noinh_convalid, child_noinh_convalid; + -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on -- tmp4 is a,b -- 2.11.0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
