pg_dump emits ALTER TABLE ONLY partitioned_table

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_dump emits ALTER TABLE ONLY partitioned_table
Date: 2017-02-17 08:23:43
Message-ID: 7682253a-6f79-6a92-00aa-267c4c412870@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Thanks,
Amit

Attachment Content-Type Size
0001-pg_dump-do-not-emit-ALTER-TABLE-ONLY-for-partitioned.patch text/x-diff 3.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2017-02-17 08:54:36 Re: Help text for pg_basebackup -R
Previous Message Thomas Munro 2017-02-17 07:45:19 Re: Measuring replay lag