Re: pg_dump: fail to restore partition table with serial type

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 <alvherre@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