pg_dump: fail to restore partition table with serial type

From: Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_dump: fail to restore partition table with serial type
Date: 2019-05-06 05:43:51
Message-ID: CAGPqQf0iQV=PPOv2Btog9J9AwOQp6HmuVd6SbGTR_v3Zp2XT1w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
dump_partition_serial_column.patch text/x-patch 562 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-05-06 07:01:05 Re: REINDEX INDEX results in a crash for an index of pg_class since 9.6
Previous Message Thomas Munro 2019-05-06 04:47:40 Re: Fixing order of resowner cleanup in 12, for Windows