From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: scott_ribe@elevated-dev.com
Subject: BUG #18405: flaw in dump of inherited/dropped constraints
Date: Sat, 23 Mar 2024 18:09:02 +0000
Message-ID: <18405-4ce10e802e8d3174@postgresql.org> (raw)
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=...
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, scott_ribe@elevated-dev.com
Subject: Re: BUG #18405: flaw in dump of inherited/dropped constraints
In-Reply-To: <18405-4ce10e802e8d3174@postgresql.org>
* 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