Reject enforcing a parent CHECK constraint over a NOT VALID child ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED on a parent's NOT ENFORCED CHECK constraint marked the parent's constraint validated, but when it recursed to a child whose matching constraint was already ENFORCED and NOT VALID, it left that child alone, since its enforceability was unchanged. The child's existing rows were therefore never checked, and the parent ended up validated with a child that was not. Constraint exclusion could then skip the child when querying the parent, and so miss rows that violate the constraint.
Fix by raising the same error that ALTER TABLE ... INHERIT already raises when a child has a NOT VALID copy of a parent's constraint, with a hint to validate the child's constraint first. Validating the child implicitly instead was considered, but ALTER CONSTRAINT ... ENFORCED does not validate an ENFORCED NOT VALID constraint when run directly against the table that has it, so doing so only when reached through a parent would be inconsistent. Add regression tests for both regular inheritance and partitioning. Bug introduced in 342051d73b3. Reported-by: Shihao Zhong <[email protected]> Author: Shihao Zhong <[email protected]> Author: Jian He <[email protected]> Reviewed-by: Chao Li <[email protected]> Reviewed-by: Andrew Dunstan <[email protected]> Discussion: https://postgr.es/m/CAGRkXqSGE=fQrjCoNQsyiCZRKfQ4SrB015CAR=3pXYB5tB=i...@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0b12a5a7435a3b50cccea85347d9e3cf8128d88a Modified Files -------------- src/backend/commands/tablecmds.c | 15 +++++++++++++++ src/test/regress/expected/constraints.out | 11 +++++++++++ src/test/regress/expected/inherit.out | 22 ++++++++++++++++++++++ src/test/regress/sql/constraints.sql | 11 +++++++++++ src/test/regress/sql/inherit.sql | 14 ++++++++++++++ 5 files changed, 73 insertions(+)
