Incremental Update: Fix Bug of CHECK Constraint Enforceability Recursion

Summary of New Developments

The thread has progressed from Jian He's simple v1 patch (reject all enforceability changes on inherited constraints) to Evan's more nuanced v2/v3 approach. A new multi-inheritance edge case was identified, and Álvaro confirmed the correct semantics. The patch is now at v3 with a documentation update.

Key New Technical Content

1. Evan's Critique of the v1 Approach (4 Problems Identified)

Evan identified four concrete problems with the simple coninhcount > 0 && !recursing rejection:

1. Over-restriction on ENFORCED→NOT ENFORCED when parent is NOT ENFORCED: If a parent is NOT ENFORCED and a child was previously changed to ENFORCED (which is legal), the child should be able to revert to NOT ENFORCED. The v1 patch blocks this.

2. Unnecessary restriction on no-op operations: If a child is already ENFORCED and the user issues ALTER ... ENFORCED again, PostgreSQL normally allows this idempotent no-op, but v1 raises an error.

3. Over-restriction on child→ENFORCED: Changing a child from NOT ENFORCED to ENFORCED (stricter than parent) should always be allowed, but v1 blocks it.

4. Multi-inheritance inconsistency (NEW BUG DISCOVERED): When a child inherits from two parents (p1 and p2) both with ENFORCED constraint c, altering p1's constraint to NOT ENFORCED recursively sets the child to NOT ENFORCED — even though p2 still has ENFORCED. This violates the "strictest parent wins" invariant.

2. The Multi-Inheritance Edge Case

This is a newly discovered bug in the existing PG19 feature, distinct from the original report:

CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE ch () INHERITS (p1, p2);  -- coninhcount = 2
ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;
-- BUG: ch.c becomes NOT ENFORCED despite p2.c remaining ENFORCED

The existing CREATE TABLE ... INHERITS code correctly applies the "strictest parent wins" rule (child gets ENFORCED if any parent is ENFORCED), but ALTER TABLE ALTER CONSTRAINT recursion doesn't check other parents before propagating NOT ENFORCED to the child.

3. Evan's v2/v3 Design (Refined Fix)

The new design has three rules:

• Directly reject changing an inherited child CHECK constraint to NOT ENFORCED if any equivalent parent constraint remains ENFORCED.
• Allow changing a child to ENFORCED (making it stricter is always safe).
• During recursion, if a child also inherits an equivalent ENFORCED constraint from another parent outside the current ALTER's hierarchy, the child retains the stricter ENFORCED state (don't propagate NOT ENFORCED).

4. Documentation Concern and Resolution

Jian He raised a concern that v2's behavior (child stays ENFORCED despite one parent being altered to NOT ENFORCED) contradicts existing documentation which states "ALTER TABLE will propagate any changes... down the inheritance hierarchy." Álvaro responded that:

• The documentation was written assuming single inheritance and is "borderline obsolete"
• The v2 behavior is correct: the child must preserve the strictest inherited constraint status
• The documentation needs updating rather than the code changing

5. v3 Patch Structure

• v3-0001: Code fix (unchanged from v2) implementing the refined enforceability recursion logic
• v3-0002: Documentation update to the inheritance DDL docs to reflect the new reality of constraint enforceability in multi-inheritance scenarios

Fix Bug of CHECK Constraint Enforceability Recursion

Core Problem

PostgreSQL 19 introduced the ability to ALTER TABLE ... ALTER CONSTRAINT ... [NOT] ENFORCED for CHECK constraints. This new feature has a critical bug in how it handles constraint enforceability propagation through table inheritance and partitioning hierarchies.

The Invariant Being Violated

PostgreSQL maintains a fundamental invariant for CHECK constraints in inheritance/partition hierarchies:

• Parent ENFORCED + Child NOT ENFORCED = INVALID STATE (should be rejected)
• Parent NOT ENFORCED + Child ENFORCED = VALID (allowed)

This invariant is already enforced by MergeConstraintsIntoExisting (for inheritance via CREATE TABLE) and MergeWithExistingConstraint (for ATTACH PARTITION). However, the new ALTER TABLE ALTER CONSTRAINT code path bypasses these guards, allowing users to create the invalid state directly.

The Bug Mechanism

The recursion logic in ATExecAlterCheckConstrEnforceability has a flawed optimization: when a parent constraint is already marked ENFORCED and the user runs ALTER TABLE parent ALTER CONSTRAINT ck ENFORCED, the code short-circuits because "nothing changed" on the parent. However, a child table may have independently been altered to NOT ENFORCED, meaning the recursion to re-enforce children never fires.

Scenario 1 (Inheritance):

CREATE TABLE p(a int CONSTRAINT ck CHECK (a > 0) ENFORCED);
CREATE TABLE c() INHERITS (p);
ALTER TABLE c ALTER CONSTRAINT ck NOT ENFORCED;  -- child diverges
ALTER TABLE p ALTER CONSTRAINT ck ENFORCED;      -- no-op on parent, doesn't recurse to c
INSERT INTO c VALUES (-2);                       -- succeeds incorrectly!

Scenario 2 (Partitioning):

CREATE TABLE p (a int, CONSTRAINT ck CHECK (a > 0) ENFORCED) PARTITION BY RANGE (a);
CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (-100) TO (100);
ALTER TABLE p1 ALTER CONSTRAINT ck NOT ENFORCED; -- partition diverges
ALTER TABLE p ALTER CONSTRAINT ck ENFORCED;      -- doesn't recurse
INSERT INTO p1 VALUES (-2);                      -- succeeds incorrectly!

Architectural Significance

This bug matters because:

1. Data integrity violation: CHECK constraints are a fundamental data integrity mechanism. Allowing invalid data to bypass enforcement undermines the relational model guarantees.

2. Hierarchy consistency: PostgreSQL's inheritance and partitioning rely on parent-child constraint consistency for correct query planning and constraint exclusion. An enforced parent with an unenforced child creates semantic ambiguity about what guarantees hold when querying via the parent.

3. New in PG19: Since PG18 did not support ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED for CHECK constraints, this invalid state was unreachable. PG19 must not introduce a new way to break the invariant.

Proposed Solutions

Initial Approach: Always Recurse (Evan's first patch)

The initial patch proposed always recursing to descendant tables when altering enforceability, regardless of whether the parent constraint itself changed. This ensures children are brought back in line with the parent's enforceability state.

Rationale: Both partitioned tables and inheritance children can currently be altered independently, so we cannot rely on the parent's own state change as the trigger for recursion.

Final Approach: Reject the Inconsistent ALTER (Consensus)

The thread quickly converged on a different, stricter solution: reject ALTER TABLE child ALTER CONSTRAINT ck NOT ENFORCED when the constraint is inherited from an enforced parent. This approach:

1. Prevents the invalid state from being created in the first place
2. Aligns with the existing behavior in MergeConstraintsIntoExisting and MergeWithExistingConstraint
3. Is simpler to reason about — if you want a child NOT ENFORCED, the parent must also be NOT ENFORCED

Implementation (from Jian He's suggestion):

if (currcon->coninhcount > 0 && !recursing)
    ereport(ERROR,
            errcode(ERRCODE_INVALID_TABLE_DEFINITION),
            errmsg("cannot alter inherited constraint \"%s\" of relation \"%s\" enforceability",
                   NameStr(currcon->conname),
                   RelationGetRelationName(rel)));

The check uses coninhcount > 0 to detect inherited constraints and !recursing to distinguish user-initiated alters from system recursion (when the parent propagates a change downward, recursing is true and the alter should proceed).

Key Design Decisions and Tradeoffs

1. Reject vs. Recurse: Rejecting is preferred because it's defensive — it prevents invalid states rather than trying to repair them after the fact. The "always recurse" approach would still allow temporary inconsistency.

2. Both inheritance and partitioning: The fix must handle both cases uniformly. For partitioned tables, there's additional justification since partitions are logically part of the parent and should never diverge in constraint enforcement.

3. FK constraints are not affected: Foreign key constraints on partitions have a valid conparentid, which already prevents direct alteration of a partition's FK constraint. CHECK constraints use a different mechanism (coninhcount) so they need this explicit guard.

4. Backport considerations: Álvaro confirmed this should be fixed in PG19 before release. Since the feature is new in PG19, there's no backport concern to prior versions.