Hi,
Consider the following test scenario:
create table test ( c1 serial, c2 int not null ) partition by list (c2);
create table test_p1 partition of test for values in ( 1);
create table test_p2 partition of test for values in ( 2);
rushabh@rushabh:postgresql$ ./db/bin/pg_dump db1 > dump.sql
While restoring above dump it's throwing a below error:
CREATE TABLE
psql:dump.sql:66: ERROR: column "c1" in child table must be marked NOT NULL
ALTER TABLE
CREATE TABLE
psql:dump.sql:79: ERROR: column "c1" in child table must be marked NOT NULL
ALTER TABLE
Problem got introduced with below commit:
commit 3b23552ad8bbb1384381b67f860019d14d5b680e
Author: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Wed Apr 24 15:30:37 2019 -0400
Make pg_dump emit ATTACH PARTITION instead of PARTITION OF
Above commit use ATTACH PARTITION instead of PARTITION OF, that
means CREATE TABLE get build with attributes for each child. I found
that NOT NULL constraints not getting dump for the child table and that
is the reason restore end up with above error.
Looking at code found the below code which skip the NULL NULL
constraint for the inherited table - and which is the reason it also
it end up not emitting the NOT NULL constraint for child table:
/*
* Not Null constraint --- suppress if inherited, except
* in binary-upgrade case where that won't work.
*/
bool has_notnull = (tbinfo->notnull[j] &&
(!tbinfo->inhNotNull[j] ||
dopt->binary_upgrade));
PFA patch to fix the issue, which allow to dump the NOT NULL
for partition table.
PS: we also need to backport this to v11.
Thanks,
--
Rushabh Lathia
www.EnterpriseDB.com
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index db8ca40..8aac3f5 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15595,7 +15595,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* in binary-upgrade case where that won't work.
*/
bool has_notnull = (tbinfo->notnull[j] &&
- (!tbinfo->inhNotNull[j] ||
+ (tbinfo->ispartition ||
+ !tbinfo->inhNotNull[j] ||
dopt->binary_upgrade));
/* Skip column if fully defined by reloftype */
