On WHPG19 (PG18), a column NOT NULL is also represented as a pg_constraint row with contype = 'n'. PR #52 excludes those rows from GetConstraints (backup/queries_shared.go) because the column definition already carries attnotnull, which stops the constraint being emitted twice.
That is correct as far as it goes, but a PG18 not-null constraint can carry three things the column-level NOT NULL cannot reproduce:
- a name (
conname)
NO INHERIT (connoinherit)NOT VALID (convalidated = false, added by a379061a2)
The first two are cosmetic. NOT VALID is not: a table whose not-null constraint has never been validated may legitimately contain NULLs, and gpbackup currently emits an enforced NOT NULL for it — so the restore's COPY fails on rows the source accepted.
pg_dump handles this in the PG18 branch of getTableAttrs, selecting
co.conname AS notnull_name,
co.connoinherit AS notnull_noinherit,
co.conislocal AS notnull_islocal,
NOT co.convalidated AS notnull_invalid
and emitting CONSTRAINT <name> NOT NULL <col> [NO INHERIT] [NOT VALID] in the column list instead of a bare NOT NULL.
Doing the same in gpbackup means carrying those fields on ColumnDefinition and changing how PrintRegularTableCreateStatement renders the not-null part of a column — a heavily tested path, which is why it was left out of #52 rather than rushed in.
Raised from review of #52 (#52 (comment)); the reviewer was happy for it to be a follow-up given it is an edge case.
On WHPG19 (PG18), a column
NOT NULLis also represented as apg_constraintrow withcontype = 'n'. PR #52 excludes those rows fromGetConstraints(backup/queries_shared.go) because the column definition already carriesattnotnull, which stops the constraint being emitted twice.That is correct as far as it goes, but a PG18 not-null constraint can carry three things the column-level
NOT NULLcannot reproduce:conname)NO INHERIT(connoinherit)NOT VALID(convalidated = false, added bya379061a2)The first two are cosmetic.
NOT VALIDis not: a table whose not-null constraint has never been validated may legitimately contain NULLs, and gpbackup currently emits an enforcedNOT NULLfor it — so the restore'sCOPYfails on rows the source accepted.pg_dumphandles this in the PG18 branch ofgetTableAttrs, selectingand emitting
CONSTRAINT <name> NOT NULL <col> [NO INHERIT] [NOT VALID]in the column list instead of a bareNOT NULL.Doing the same in gpbackup means carrying those fields on
ColumnDefinitionand changing howPrintRegularTableCreateStatementrenders the not-null part of a column — a heavily tested path, which is why it was left out of #52 rather than rushed in.Raised from review of #52 (#52 (comment)); the reviewer was happy for it to be a follow-up given it is an edge case.