Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).
Amul Sul <sulamul@gmail.com>
From: Amul Sul <sulamul@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2023-09-20T12:58:36Z
Lists: pgsql-hackers
Hi,
On the latest master head, I can see a $subject bug that seems to be related
commit #b0e96f311985:
Here is the table definition:
create table foo(i int, j int, CONSTRAINT pk PRIMARY KEY(i) DEFERRABLE);
And after restore from the dump, it shows a descriptor where column 'i' not
marked NOT NULL:
=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
j | integer | | |
Indexes:
"pk" PRIMARY KEY, btree (i) DEFERRABLE
The pg_attribute entry:
=# select attname, attnotnull from pg_attribute
where attrelid = 'foo'::regclass and attnum > 0;
attname | attnotnull
---------+------------
i | f
j | f
(2 rows)
--
Regards,
Amul Sul
EDB: http://www.enterprisedb.com
Commits
-
Revert structural changes to not-null constraints
- 6f8bb7c1e961 17.0 cited
-
Admit deferrable PKs into rd_pkindex, but flag them as such
- 270af6f0df76 17.0 landed
-
Add the notion of REPLICA IDENTITY for a table.
- 07cacba983ef 9.4.0 cited