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

PG Bug reporting form <noreply(at)postgresql(dot)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