Обсуждение: persevere NO INHERIT when Dump not-null constraints on inherited columns

Hi.

Related: https://git.postgresql.org/cgit/postgresql.git/commit/?id=fd41ba93e4630921a72ed5127cd0d552a8f3f8fc

create table p1(f1 int);
create table p1_c1() inherits(p1);
alter table p1_c1 add constraint p1c1_a_nn not null f1 no inherit;

pg_dump --table-and-children=p1* --clean --if-exists --no-data
output:

---------------<<<<<<
DROP TABLE IF EXISTS public.p1_c1;
DROP TABLE IF EXISTS public.p1;
CREATE TABLE public.p1 (
    f1 integer
);
CREATE TABLE public.p1_c1 (
    CONSTRAINT p1c1_a_nn NOT NULL f1
)
INHERITS (public.p1);
---------------<<<<<<
for p1_c1, it should be

CREATE TABLE public.p1_c1 (
    CONSTRAINT p1c1_a_nn NOT NULL f1 NO INHERIT
)
INHERITS (public.p1);

add
```
if (tbinfo->notnull_noinh[j])
    appendPQExpBufferStr(q, " NO INHERIT");
```
at the end of IF loop
```
if (!shouldPrintColumn(dopt, tbinfo, j) &&
    !tbinfo->attisdropped[j] &&
    tbinfo->notnull_constrs[j] != NULL &&
    tbinfo->notnull_islocal[j])
{
}
```
will fix this problem.



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

On 2026-Feb-25, jian he wrote:

> create table p1(f1 int);
> create table p1_c1() inherits(p1);
> alter table p1_c1 add constraint p1c1_a_nn not null f1 no inherit;

Ugh, you're right, this is broken and your patch fixes it.

I'm gonna write a quick test case for this and push as soon as I'm able.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)

On 2026-Feb-25, Álvaro Herrera wrote:

> Ugh, you're right, this is broken and your patch fixes it.
> 
> I'm gonna write a quick test case for this and push as soon as I'm able.

Here it is.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

On 2026-Feb-25, Álvaro Herrera wrote:

> On 2026-Feb-25, Álvaro Herrera wrote:
> 
> > Ugh, you're right, this is broken and your patch fixes it.
> > 
> > I'm gonna write a quick test case for this and push as soon as I'm able.
> 
> Here it is.

Pushed.  I also added a test for the standard case of NO INHERIT in a
not-null constraint.

Thanks for reporting this.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)

On Thu, Feb 26, 2026 at 4:22 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2026-Feb-25, Álvaro Herrera wrote:
>
> > On 2026-Feb-25, Álvaro Herrera wrote:
> >
> > > Ugh, you're right, this is broken and your patch fixes it.
> > >
> > > I'm gonna write a quick test case for this and push as soon as I'm able.
> >
> > Here it is.
>
> Pushed.  I also added a test for the standard case of NO INHERIT in a
> not-null constraint.
>
> Thanks for reporting this.

This could have been caught by 002_pg_upgrade's regression
dump/restore test, if we had a NO INHERIT on child being tested in
regression test and not dropped. I browsed through regression tests
testing NO INHERIT. I found some adding NO INHERIT check constraint on
child table but no NOT NULL constraint on child table. But I didn't
look very closely. If that's true, we may not have enough coverage to
check whether NO INHERIT on a child table is honoured or not when a
grandchild is added. I did find a test which tests that NO INHERIT NOT
NULL on the child table is not merged with normal NOT NULL constraint.

--
Best Wishes,
Ashutosh Bapat

On 2026-Feb-26, Ashutosh Bapat wrote:

> This could have been caught by 002_pg_upgrade's regression
> dump/restore test, if we had a NO INHERIT on child being tested in
> regression test and not dropped. I browsed through regression tests
> testing NO INHERIT. I found some adding NO INHERIT check constraint on
> child table but no NOT NULL constraint on child table. But I didn't
> look very closely. If that's true, we may not have enough coverage to
> check whether NO INHERIT on a child table is honoured or not when a
> grandchild is added. I did find a test which tests that NO INHERIT NOT
> NULL on the child table is not merged with normal NOT NULL constraint.

Hmm, yeah, feel free to propose a change so that those tables are not
dropped, so that this is exercised better in the
PG_TEST_EXTRA=regress_dump_restore case.  I guess the whole gamut of
NOT ENFORCED, NOT VALID etc ought to be covered that way.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/