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