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

Found another scenario where check constraint is not getting
dump for the child table.

Testcase:

create table test ( c1 serial, c2 int not null, c3 integer CHECK (c3 > 0))
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);

In the above test, check constraint for column c3 is not getting
dump with CREATE TABLE, and that is the reason ATTACH
PARTITION is failing.

Seems like need to handle NOT NULL and CHECK CONSTRAINT
differently than the inheritance table.

On Mon, May 6, 2019 at 11:13 AM Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
wrote:

> 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(at)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(at)alvh(dot)no-ip(dot)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
>
>

--
Rushabh Lathia