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.