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

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 14 Nov 2024 16:26:37 +0530

Hi Alvaro,
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.

I have attached four files. Original dumps from source (src_dump.sql)
and restored databases (dest_dump.plain.sql). Dumps adjusted for known
differences (src_dump.sql_adjusted and
dest_dump.plain.sql_adjusted respectively)
 as per AdjustDump.pm module added by 0001 patch attached to [1].

Are these differences safe? Are they intentional?

-- 
Best Wishes,
Ashutosh Bapat

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 14 Nov 2024 13:38:56 +0100

Hello Ashutosh,

> 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/