Pg_upgrade Bug with NOT NULL NOT VALID Constraints

Core Problem

This thread identifies a pg_upgrade compatibility bug introduced by PostgreSQL 18's treatment of NOT NULL constraints as first-class catalog objects (rather than just column-level attnotnull flags). The issue arises from a specific interaction between constraint naming conventions across major versions.

The Scenario

In PostgreSQL 17, a user creates a table with an inline NOT NULL constraint:

CREATE TABLE t(i int NOT NULL);

This sets attnotnull = true on column i but does not create a named constraint in pg_constraint. The user then creates a CHECK constraint that happens to use the same naming pattern that PG18 would auto-generate for the NOT NULL constraint:

ALTER TABLE t ADD CONSTRAINT t_i_not_null CHECK ((i IS NOT NULL)) NOT VALID;

When upgrading to PG18 (current master), pg_dump --binary-upgrade attempts to recreate the NOT NULL constraint as a proper catalog entry with the auto-generated name t_i_not_null. This collides with the pre-existing CHECK constraint of the same name, causing pg_restore to fail with:

ERROR: constraint "t_i_not_null" for relation "t" already exists

Why This Matters Architecturally

PostgreSQL 18 changed NOT NULL constraints from being implicit column metadata (pg_attribute.attnotnull) to explicit entries in pg_constraint, giving them proper constraint names following the {table}_{column}_not_null pattern. This was part of the broader work to make NOT NULL constraints fully manageable objects (supporting NOT VALID, inheritance, etc.). The upgrade path must handle the case where a user already occupied that namespace in the source database.

This is conceptually similar to commit 3db61db48ef5 which addressed naming conflicts during pg_upgrade for other auto-generated constraint names.

Proposed Solutions

1. Change the Auto-Generated Name Pattern (Proposer: Kirill Reshke)

Make the mechanically-generated name for NOT NULL constraints less obvious/predictable during CREATE TABLE processing. This would affect all new databases, not just upgrades.

Rejected by Álvaro Herrera because:

• Any mechanical naming scheme can still be chosen by a user for their CHECK constraint
• It degrades the UX of constraint names for all users
• It doesn't provide hard guarantees, just moves the collision window

2. pg_dump --binary-upgrade Name Collision Avoidance (Álvaro Herrera)

Have pg_dump in binary-upgrade mode generate NOT NULL constraint names with full knowledge of all existing constraint names in the target relation, guaranteeing a non-conflicting name.

Tradeoffs:

• Provides a hard guarantee of no collision
• Complex to implement: requires pg_dump to replicate ChooseConstraintName() logic accurately, including edge cases with name truncation for long table/column names

3. pg_upgrade --check Warning (Álvaro Herrera, preferred)

Have pg_upgrade -c (the pre-upgrade check mode) detect and warn about CHECK constraints whose names would conflict with the auto-generated NOT NULL constraint names. The user would then manually rename the conflicting constraint before upgrading.

Tradeoffs:

• Simplest to implement
• Follows existing patterns (pg_upgrade already has many such pre-checks)
• Slightly ugly: needs to closely match ChooseConstraintName() behavior, especially for edge cases involving name truncation with long table/column names
• Current pg_dump-side algorithm for generating NOT NULL constraint names is intentionally naive (inexact for border cases), but for collision detection it must be precise

Key Technical Context

The ChooseConstraintName() function in PostgreSQL generates constraint names by concatenating table name, column name, and a suffix (like _not_null), then truncating to NAMEDATALEN - 1 (typically 63 bytes). When names exceed this limit, truncation can produce non-obvious results. The pg_upgrade check would need to replicate this logic precisely to avoid false negatives (missing a real collision) or false positives (blocking upgrades unnecessarily).

Assessment

This is a real upgrade-blocking bug that affects any PG17 database where a user has created a CHECK constraint named {table}_{column}_not_null on a column that also has an inline NOT NULL constraint. While likely rare in practice, it's a correctness issue that must be fixed before PG18 release. The pg_upgrade --check approach (option 3) appears most likely to be adopted given its simplicity and alignment with existing pg_upgrade patterns.