Re: pg_dump emits ALTER TABLE ONLY partitioned_table

On Fri, Feb 17, 2017 at 3:23 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE
> command for those schema elements of a table that could not be included
> directly in the CREATE TABLE command for the table.
>
> For example:
>
> create table p (a int, b int) partition by range (a);
> create table p1 partition of p for values from (1) to (10) partition by
> range (b);
> create table p11 partition of p1 for values from (1) to (10);
>
> pg_dump -s gives:
>
> CREATE TABLE p (
> a integer NOT NULL,
> b integer
> )
> PARTITION BY RANGE (a);
>
> CREATE TABLE p1 PARTITION OF p
> FOR VALUES FROM (1) TO (10)
> PARTITION BY RANGE (b);
> ALTER TABLE ONLY p1 ALTER COLUMN a SET NOT NULL;
> ALTER TABLE ONLY p1 ALTER COLUMN b SET NOT NULL;
>
> <snip>
>
> Note the ONLY in the above emitted command. Now if I run the above
> commands in another database, the following error occurs:
>
> ERROR: constraint must be added to child tables too
>
> That's because specifying ONLY for the AT commands on partitioned tables
> that must recurse causes an error.
>
> Attached patch fixes that - it prevents emitting ONLY for those ALTER
> TABLE commands, which if run, would cause an error like the one above.

Isn't it bogus that this is generating ALTER TABLE .. SET NOT NULL
columns at all? You didn't say anything like that when setting up the
database, so why should it be there when dumping?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company