2026-05-22 · claude-opus-4-6

Prevent Setting NO INHERIT on Partitioned Not-Null Constraints

Core Problem

PostgreSQL's partitioning system requires that NOT NULL constraints on partitioned tables are always inherited by child partitions. This is a fundamental invariant: if a parent partitioned table declares a column as NOT NULL, all partitions must enforce that constraint — otherwise the semantic guarantee of the constraint would be violated when data is routed to partitions.

The system correctly prevents creating a NOT NULL constraint with the NO INHERIT property on a partitioned table at CREATE TABLE time (enforced in parse_utilcmd.c). However, a gap exists in the ALTER TABLE ... ALTER CONSTRAINT ... NO INHERIT code path that was introduced in PostgreSQL v18. This allows users to retroactively set NO INHERIT on an existing not-null constraint of a partitioned table, circumventing the creation-time check.

This is a constraint enforcement bug: it breaks the invariant that partitioned table NOT NULL constraints always propagate to partitions. The practical consequence is that child partitions could potentially drop or not inherit the constraint, allowing NULL values in columns that the parent table schema declares as NOT NULL.

Proposed Solution

The fix is straightforward: add a check in the ALTER CONSTRAINT code path that raises an error when a user attempts to set NO INHERIT on a not-null constraint belonging to a partitioned table. This mirrors the existing check in parse_utilcmd.c that prevents this at table creation time.

Patch Details (v2)

The patch adds a conditional check in the ALTER CONSTRAINT execution path:

If the target relation is partitioned (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
And the constraint being altered is a not-null constraint
And the requested change is to set NO INHERIT

Then raise an error with:

errcode(ERRCODE_FEATURE_NOT_SUPPORTED) — consistent with the existing check in parse_utilcmd.c
errmsg("not-null constraint \"%s\" on partitioned table \"%s\" cannot be NO INHERIT")

Version History

v1: Correct logic but used ERRCODE_WRONG_OBJECT_TYPE as the error code (copy-paste error from nearby code that checks different constraint types).
v2: Corrected to use ERRCODE_FEATURE_NOT_SUPPORTED to be consistent with the equivalent check in parse_utilcmd.c.

Key Design Considerations

Consistency of error codes: The thread highlights the importance of using consistent error codes across different code paths that enforce the same logical rule. ERRCODE_FEATURE_NOT_SUPPORTED is the correct choice because the restriction exists due to a semantic limitation of the partitioning model, not because the object type is wrong.
Backpatch target: The bug should be backpatched to v18, since that's where ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT was introduced. Prior versions don't have this code path and are unaffected.
Defense in depth: This is a classic case where a new feature (ALTER CONSTRAINT ... [NO] INHERIT) introduced a bypass for an existing restriction. The original CREATE TABLE check was sufficient before v18, but the new ALTER path needed its own guard.

Architectural Context

In PostgreSQL's partitioning implementation, constraints on partitioned tables serve as templates that are propagated to partitions. The NO INHERIT flag on a constraint means it applies only to the table where it's defined and is not propagated to children. For partitioned tables specifically, NOT NULL constraints must propagate because:

Tuple routing relies on partition constraints being at least as restrictive as the parent
Query planning may make assumptions about NOT NULL based on the parent table's constraints
The partition hierarchy's data integrity guarantees depend on constraint inheritance

The fix is minimal and surgical — exactly the right approach for a constraint enforcement gap.

Prevent setting NO INHERIT on partitioned not-null constraints