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

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump: fail to restore partition table with serial type
Date: 2019-06-07 18:36:41
Message-ID: 20190607183641.GA17371@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2019-May-06, Alvaro Herrera wrote:

> On 2019-May-06, Rushabh Lathia wrote:
> > Found another scenario where check constraint is not getting
> > dump for the child table.
> You're right, the patched code is bogus; I'm reverting it all for
> today's minors. Thanks for reporting.

Here's another version of this patch. This time, I added some real
tests in pg_dump's suite, including a SERIAL column and NOT NULL
constraints. The improved test verifies that the partition is created
separately and later attached, and it includes constraints from the
parent as well as some locally defined ones. I also added tests with
legacy inheritance, which was not considered previously in pg_dump tests
as far as I could see.

I looked for other cases that could have been broken by changing the
partition creation methodology in pg_dump, and didn't find anything.
That part of pg_dump (dumpTableSchema) is pretty spaghettish, though;
the fact that shouldPrintColumn makes some partitioned-related decisions
and then dumpTableSchema make them again is notoriously confusing. I
could have easily missed something.

One weird thing about pg_dump's output of the serial column in a
partitioned table is that it emits the parent table itself first without
a DEFAULT clause, then the sequence and marks it as owned by the column;
then it emits the partition *with* the default clause, and finally it
alters the parent table's column to set the default. Now there is some
method in this madness (the OWNED BY clause for the sequence is mingled
together with the sequence itself), but I think this arrangement makes
a partial restore of the partition fail.

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v1-0001-Make-pg_dump-emit-ATTACH-PARTITION-instead-of-PAR.patch text/x-diff 9.6 KB
v1-0002-Fix-code-to-consider-NOT-NULL-add-tests.patch text/x-diff 5.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-06-07 19:02:31 Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.
Previous Message Alexander Korotkov 2019-06-07 18:27:58 Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly