pgsql: Reject enforcing a parent CHECK constraint over a NOT VALID chil

Поиск
Список
Период
Сортировка
Искать

pgsql: Reject enforcing a parent CHECK constraint over a NOT VALID chil

От:
Andrew Dunstan <andrew@dunslane.net>
Дата:
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 
Author: Shihao Zhong 
Author: Jian He 
Reviewed-by: Chao Li 
Reviewed-by: Andrew Dunstan 
Discussion: https://postgr.es/m/CAGRkXqSGE=fQrjCoNQsyiCZRKfQ4SrB015CAR=3pXYB5tB=ieg@mail.gmail.com
Backpatch-through: 19

Branch
------
REL_19_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/8356d8e4dba6dc36c84d4a3f15f10b9c89c2cd83

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(+)

FAQ