Re: Difference in dump from original and restored database due to NOT NULL constraints on children

Hello Ashutosh,

On 2024-Nov-14, Ashutosh Bapat wrote:

> Because of the test I am developing under discussion at [1], I noticed
> that the DDLs dumped for inheritance children with NOT NULL
> constraints defer between original database and database restored from
> a dump of original database. I investigated those differences, but can
> not decide whether they are expected and whether they are intentional
> or not. Look something related to
> 14e87ffa5c543b5f30ead7413084c25f7735039f.

It does and it is, thanks.

So there are two differences:

1.

CREATE TABLE generated_stored_tests.gtest1 (
a integer NOT NULL,
b integer GENERATED ALWAYS AS ((a * 2)) STORED
);

CREATE TABLE generated_stored_tests.gtestxx_4 (
- a integer,
- b integer NOT NULL
+ a integer NOT NULL,
+ b integer
)
INHERITS (generated_stored_tests.gtest1);

In this case, I think it's wrong to add NOT NULL to gtestxx_4.a because
the constraint was not local originally and it should be acquired by the
inheritance. I'm not sure about gtestxx_4.b ... the GENERATED
constraint should induce a not-null if I recall correctly, so an
explicit not-null marking in the child might not be necessary. But I'll
have a look.

2.

CREATE TABLE public.notnull_tbl4 (
a integer NOT NULL
);

CREATE TABLE public.notnull_tbl4_cld2 (
)
INHERITS (public.notnull_tbl4);
-ALTER TABLE ONLY public.notnull_tbl4_cld2 ALTER COLUMN a SET NOT NULL;
+ALTER TABLE ONLY public.notnull_tbl4_cld2 ADD CONSTRAINT notnull_tbl4_a_not_null NOT NULL a;

CREATE TABLE public.notnull_tbl4_cld3 (
)
INHERITS (public.notnull_tbl4);
-ALTER TABLE ONLY public.notnull_tbl4_cld3 ADD CONSTRAINT a_nn NOT NULL a;
+ALTER TABLE ONLY public.notnull_tbl4_cld3 ADD CONSTRAINT notnull_tbl4_a_not_null NOT NULL a;

For notnull_tbl4_cld2 we try to set the column not-null, but it's
already not-null due to inheritance ... so why are we doing that?
Weird. These lines should just go away in both dumps.

In notnull_tbl4_cld3's case we have the same, but we also want to set a
constraint name, but the constraint already exists, so that doesn't
work, and that's the reason why the next dump uses the standard name.
Maybe we could dump the name change as ALTER TABLE RENAME CONSTRAINT in
that case instead, _if_ we can obtain the original name (should be
doable, because we can see it's a nonstandard name.)

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/