> On Jun 3, 2026, at 22:36, Zsolt Parragi <[email protected]> wrote: > > Hello > > After a bit more testing, I think there's still a remaining issue with > the latest patch: > > create table root_t (a int constraint c check (a > 0) enforced); > create table p2 (a int constraint c check (a > 0) enforced); > create table d () inherits (root_t, p2); > create table e () inherits (d); > create table f () inherits (e); > alter table root_t alter constraint c not enforced; > insert into e values (-5); -- succeeds > > d remains enforced as it should, but e and f doesn't. > >
Thanks for your testing. Yeah, inheritance cases are really complicated. find_all_inheritors() returns a list of the root plus its descendants, but it cannot ensure that a parent always appears before its child. For example: ``` create table gp(a int constraint c check (a > 0) enforced); create table d() inherits (gp); create table p1() inherits (gp); alter table d inherit p1; ``` In this case, d is a child of both gp and p1. But because d is created before p1, d has a smaller OID than p1. So even though p1 is a parent of d, d can still appear before p1 in the list, like gp -> d -> p1. That’s why I built the changing_conids list in the implementation. But I wrongly assumed that all constraints in the list would be updated to NOT ENFORCED. Your test case uncovered that this assumption is wrong. So when a parent appears in changing_conids, we have to recurse upward to see if there is a parent outside the current ALTER TABLE that may affect the result. The fix is in ATCheckCheckConstrHasEnforcedParent(). See attached v7 for details. With v7, your test case passes. I have added this test case to the regression tests, and made it even more complicated. ``` evantest=# create table root_t (a int constraint c check (a > 0) enforced); CREATE TABLE evantest=# create table p2 (a int constraint c check (a > 0) enforced); CREATE TABLE evantest=# create table d () inherits (root_t, p2); NOTICE: merging multiple inherited definitions of column "a" CREATE TABLE evantest=# create table e () inherits (d); CREATE TABLE evantest=# create table f () inherits (e); CREATE TABLE evantest=# alter table root_t alter constraint c not enforced; ALTER TABLE evantest=# evantest=# select conrelid::regclass as tbl, conenforced from pg_constraint where conname = 'c'; tbl | conenforced --------+------------- d | YES e | YES f | YES root_t | NO p2 | YES (5 rows) ``` Now, both e and f remain ENFORCE. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/
v7-0001-Prevent-inherited-CHECK-constraints-from-being-we.patch
Description: Binary data
v7-0002-doc-Clarify-inherited-constraint-behavior.patch
Description: Binary data
v7-0003-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch
Description: Binary data
