Thread: BUG #18405: flaw in dump of inherited/dropped constraints

The following bug has been logged on the website:

Bug reference:      18405
Logged by:          sribe
Email address:      scott_ribe@elevated-dev.com
PostgreSQL version: 16.2
Operating system:   Linux & macOS
Description:

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;

------ 
Check it:

test=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c      | boolean |           |          |
Inherits: t1

------ 
Dump it: 

❯ pg_dump -s -t t1 -t t2 test
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.2
-- Dumped by pg_dump version 16.2

SET ...

CREATE TABLE public.t1 (
    c boolean NOT NULL
);

ALTER TABLE public.t1 OWNER TO sribe;

CREATE TABLE public.t2 (
    c boolean
)
INHERITS (public.t1);


ALTER TABLE public.t2 OWNER TO sribe;

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

But it's actually a bit weirder than that. I have been using:

pg_dump -Fc -Z0 mydb | bzip2 > <filename>

followed by:

bunzip2 -kc <filename> | pg_restore -v -d mydb

to load up a testing copy for a very long time. This worked from pretty old
versions all the way through 15, but broke in 16 because of the erroneous
not null constraint on a table. Yet going back to 14.2 (I don't currently
have 15 installed locally), the plain format dump has the problem of not
dropping the constraint. So it looks like prior to 16, plain dumps had this
problem, but custom format dumps did not.

One more thing, making the custom format dump using pg_dump 14, then
restoring with pg_restore 16, gets the erroneous not null constraint. (Dump
with 16 and restore with 14, is of course not possible.)

PG builds are nothing exotic. Linux: ./configure --with-openssl
--with-systemd --with-llvm --prefix=..., macOS: ./configure --without-icu
--with-llvm --prefix=...

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

> 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+.)

I'd call that an acceptable resolution. My main concern is dump/restore not being able to dump & restore an existing
database,and this v17 change fixes this case. (For background, this odd inheritance wasn't a deliberate design, it was
a(supposedly temporary) workaround for a client bug.) 

>> ... 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?

I will re-run tests when I get a bit of time--it is possible I confused versions or schemas somewhere along the line of
switchingback and forth. 

> On Mar 25, 2024, at 10:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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?

Retried exactly what I though I had done previously. Nope, you are right. I must have screwed up a step.