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
Date: Mon, 25 Mar 2024 12:11:09 -0400
Message-ID: <2497244.1711383069@sss.pgh.pa.us> (raw)
In-Reply-To: <18405-4ce10e802e8d3174@postgresql.org>
References: <18405-4ce10e802e8d3174@postgresql.org>
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
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: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott_ribe@elevated-dev.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18405: flaw in dump of inherited/dropped constraints
In-Reply-To: <2497244.1711383069@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox