From: Tom Lane <tgl@sss.pgh.pa.us>
To: scott_ribe@elevated-dev.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18405: flaw in dump of inherited/dropped constraints
In-reply-to: <18405-4ce10e802e8d3174@postgresql.org>
References: <18405-4ce10e802e8d3174@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sat, 23 Mar 2024 18:09:02 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2497243.1711383069.1@sss.pgh.pa.us>
Date: Mon, 25 Mar 2024 12:11:09 -0400
Message-ID: <2497244.1711383069@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2497244.1711383069%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> Create the following schema:

> create table t1 (c boolean not null);
> create table t2 (c boolean) inherits (t1);
> alter table t2 alter c drop not null;
> ...
> The drop of the not null constraint is not reflected in the dump. Thus
> playing back the dump results in column c of table t2 having the not null
> constraint.

I'm not super excited about doing anything about that in the
back branches.  It's fundamentally bogus that we allowed the
DROP NOT NULL in the first place, because this structure
allows a SELECT from t1 to see null values of c, which is
pretty inconsistent.  As of HEAD, we don't allow it any more:

regression=# create table t1 (c boolean not null);
CREATE TABLE
regression=# create table t2 (c boolean) inherits (t1);
NOTICE:  merging column "c" with inherited definition
CREATE TABLE
regression=# alter table t2 alter c drop not null;
ERROR:  cannot drop inherited constraint "t1_c_not_null" of relation "t2"

thanks to Alvaro's work to treat NOT NULL the same way we've long
treated more general CHECK constraints.  So there's no need to do
anything in v17, and I think changing the behavior in released
branches would draw more complaints than plaudits.  (Also, if pg_dump
did try harder to duplicate this situation, the result would likely be
that the dump would fail to load into v17+.)

> ... So it looks like prior to 16, plain dumps had this
> problem, but custom format dumps did not.

Given the way pg_dump works, that's pretty hard to believe: you
should get bitwise the same result from pg_dump to text versus
pg_dump -Fc | pg_restore.  Can you provide a self-contained test
showing a case where it doesn't?

> One more thing, making the custom format dump using pg_dump 14, then
> restoring with pg_restore 16, gets the erroneous not null
> constraint.

Hmmm ... maybe using a different pg_restore version would affect
this.  But mostly pg_restore just emits what it finds in the dump
file, unless you told it to filter things.  I tried dumping a
situation like this in a v14 database using various pg_dump and
pg_restore versions, and they all produced the same table
definitions.

			regards, tom lane