Thread Analysis: Adjust error message to reflect the inheritance relationship

Core Problem

The thread raises a minor but legitimate user-experience issue in PostgreSQL's DDL error reporting for inheritance hierarchies. The reporter demonstrates the following scenario:

CREATE TABLE t0(c0 boolean);
CREATE TABLE t3(c0 boolean) INHERITS(t0);
ALTER TABLE ONLY t0 ADD CONSTRAINT Z PRIMARY KEY(c0);
-- ERROR:  column "c0" of table "t3" is not marked NOT NULL

The user issues ALTER TABLE ONLY t0 ... intending to operate only on the parent, yet the error message references the child table t3. At first glance this is confusing: why is PostgreSQL complaining about t3 when the user explicitly said ONLY t0?

Why This Behavior Is Architecturally Correct

The reporter correctly traces the logic into ATPrepAddPrimaryKey() in src/backend/commands/tablecmds.c. The comment there explains the design rationale:

For the case where we're asked not to recurse, we verify that a not-null constraint exists on each column of each (direct) child table, throwing an error if not. Not throwing an error would also work, because a not-null constraint would be created anyway, but it'd cause a silent scan of the child table to verify absence of nulls.

This reflects a deliberate PostgreSQL design decision about inheritance + primary keys:

1. A PRIMARY KEY implies NOT NULL on each key column.
2. In PostgreSQL's inheritance model, NOT NULL constraints on a parent must also be enforced on all descendants — a tuple in the child is conceptually visible through the parent, so a NULL in the child would violate the parent's PK semantics when queried via the parent.
3. Therefore, even when the user says ONLY t0, the system must verify that every child column is already NOT NULL. If it isn't, PostgreSQL refuses rather than silently doing an expensive full-table scan of each child to prove null-absence.

So the error is not a bug — it's intentional defensive behavior to avoid hidden O(N) work across an inheritance tree.

Proposed Change

The reporter proposes a pure wording/clarity patch to the error message in tablecmds.c:

• Before: column "%s" of table "%s" is not marked NOT NULL
• After: column "%s" in child table "%s" is not marked NOT NULL

Justification: the phrase "child table" already appears elsewhere in tablecmds.c for analogous situations, so this aligns the diagnostic vocabulary across the file and immediately tells the user why a table they didn't name is appearing in the error.

Technical Assessment of the Patch

Strengths

• Zero functional risk: it is a errmsg() string change only — no behavioral, catalog, or protocol impact.
• Consistency: aligns with the existing "child table" phrasing used elsewhere in inheritance-related error paths.
• Better diagnosability: the current wording misleads users into thinking there is something wrong with t3 as a standalone object, when the real issue is the inheritance relationship forcing constraint propagation checks.

Considerations / Tradeoffs

• Translation churn: every errmsg() change invalidates the corresponding entries in all .po translation catalogs. Committers are typically conservative about cosmetic message changes for this reason, particularly after a release freeze. Whether this is accepted often depends on how clearly the new wording improves comprehension.
• Partitioning vs. legacy inheritance: "child table" is the right term for classical inheritance (INHERITS). For declarative partitioning the preferred term is "partition". The code path here is reached via ATPrepAddPrimaryKey for ONLY on a partitioned parent as well — if the same message is emitted when the descendant is a partition, the word "child" may be slightly less precise than ideal. A variant that distinguishes relkind == RELKIND_PARTITIONED_TABLE/RELKIND_RELATION descendants could be considered, but that adds complexity for minor gain.
• Alternative wording: some projects prefer framing the error as an instruction, e.g., "child table "%s" must have column "%s" marked NOT NULL before a primary key can be added to the parent" — more verbose but fully self-explanatory. The reporter's minimal edit preserves errmsg brevity.

Key Technical Insights

The deeper lesson buried in this small thread is about NOT NULL propagation in inheritance and why ALTER TABLE ONLY ... ADD PRIMARY KEY isn't fully "only". Since work by Alvaro Herrera (pg16/17 era) to make NOT NULL a proper catalogued constraint (pg_constraint rows with contype = 'n'), the interplay between PK addition and child NOT NULL state has become more visible. The ATPrepAddPrimaryKey validation exists precisely because:

• Adding a PK to the parent cannot make the parent's NOT NULL invariant hold over its descendants unless those descendants already carry the constraint.
• Recursing to fix the children would contradict ONLY semantics.
• Silently verifying by scanning would be an undocumented performance cliff.

The error is the correct resolution of that trilemma, and improving its wording is exactly the kind of polish that helps users understand this non-obvious design.

Status

As of the posted message, this is a single-message proposal seeking feedback ("Any thoughts?"). No patch file was attached in-thread, no reviewers have weighed in, and no committer has responded yet. The change is small enough that it would likely be handled as a trivial cleanup patch if a committer picks it up; the main gating question is simply whether the improved clarity is judged worth the translation re-work.