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

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 https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services