Re: NOT NULL NOT ENFORCED

On Thu, Sep 25, 2025 at 5:46 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
> >
> > CREATE TABLE tx3 (x int not null not enforced);
> >
> > can be dumped as:
> >
> > CREATE TABLE public.tx3 (x integer);
> > ALTER TABLE public.tx3 ADD CONSTRAINT tx3_x_not_null NOT NULL x NOT ENFORCED;
> > ---------------
> > note: currently not enforced check constraint is dumped separately.
>
> Hmm, I wonder what's the reason for this. Seems quite useless. Why
> wouldn't we dump unenforced constraint together with the table? The
> case is different from invalid constraints, which have to be created
> after data is loaded.
>
> --
> Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/

hi.

If NOT ENFORCED NOT NULL is dumped as separate ALTER TABLE entry:
ALTER TABLE ... ADD CONSTRAINT ... NOT NULL NOT ENFORCED;

then the dump behavior is effectively the same as NOT NULL NOT VALID.

However, if we intend to emit NOT ENFORCED together with other column
attributes during dump, we must distinguish between NOT VALID ENFORCED and
NOT VALID NOT ENFORCED.
For example, that would require refactoring the code snippet below in
the `flagInhAttrs()` function.

/*
* Keep track of whether all the parents that have a
* not-null constraint on this column have it as NOT
* VALID; if they all are, arrange to have it printed for
* this column. If at least one parent has it as valid,
* there's no need.
*/
if (fout->remoteVersion >= 180000 &&
parent->notnull_constrs[inhAttrInd] &&
!parent->notnull_invalid[inhAttrInd])
allNotNullsInvalid = false;

But I feel like such refactoring is unappealing; refactoring it would also
require more comments to explain it, All of this would introduce additional
complexity.

Given the relatively limited benefit,
NOT NULL NOT ENFORCED constraints as a separate ALTER TABLE statements in
pg_dump output should be fine, IMHO.

--
jian
https://www.enterprisedb.com/