public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alvaro Herrera <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Difference in dump from original and restored database due to NOT NULL constraints on children
Date: Thu, 14 Nov 2024 13:38:56 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAExHW5tbdgAKDfqjDJ-7Fk6PJtHg8D4zUF6FQ4H2Pq8zK38Nyw@mail.gmail.com>

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/

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Difference in dump from original and restored database due to NOT NULL constraints on children
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox