pg_dump, ATTACH, and independently restorable child partitions

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: David Rowley <drowley(at)postgresql(dot)org>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: pg_dump, ATTACH, and independently restorable child partitions
Date: 2020-10-23 05:29:40
Message-ID: 20201023052940.GE9241@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Since this commit, pg_dump CREATEs tables and then ATTACHes them:

|commit 33a53130a89447e171a8268ae0b221bb48af6468
|Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
|Date: Mon Jun 10 18:56:23 2019 -0400
|
| Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise)
|...
| This change also has the advantage that the partition is restorable from
| the dump (as a standalone table) even if its parent table isn't
| restored.

I like the idea of child tables being independently restorable, but it doesn't
seem to work.

|psql postgres -c 'DROP TABLE IF EXISTS t' -c 'CREATE TABLE t(i int) PARTITION BY RANGE(i)' -c 'CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1)TO(2)'
|pg_dump postgres -Fc -t t1 >dump.t1
|psql postgres -c 'DROP TABLE t'
|pg_restore -d postgres ./dump.t1
|pg_restore: while PROCESSING TOC:
|pg_restore: from TOC entry 457; 1259 405311409 TABLE t1 pryzbyj
|pg_restore: error: could not execute query: ERROR: relation "public.t" does not exist
|Command was: CREATE TABLE public.t1 (
| i integer
|);
|ALTER TABLE ONLY public.t ATTACH PARTITION public.t1 FOR VALUES FROM (1) TO (2);
|
|pg_restore: error: could not execute query: ERROR: relation "public.t1" does not exist
|Command was: ALTER TABLE public.t1 OWNER TO pryzbyj;
|
|pg_restore: from TOC entry 4728; 0 405311409 TABLE DATA t1 pryzbyj
|pg_restore: error: could not execute query: ERROR: relation "public.t1" does not exist
|Command was: COPY public.t1 (i) FROM stdin;
|pg_restore: warning: errors ignored on restore: 3

Now that I look, it seems like this is calling PQexec(), which sends a single,
"simple" libpq message with:
|CREATE TABLE ..; ALTER TABLE .. ATTACH PARTITION;
..which is transactional, so when the 2nd command fails, the CREATE is rolled back.
https://www.postgresql.org/docs/9.5/libpq-exec.html#LIBPQ-EXEC-MAIN

Telsasoft does a lot of dynamic DDL, so this happens sometimes due to columns
added or promoted. Up to now, when this has come up, I've run:
pg_restore |grep -v 'ATTACH PARTITION' |psql. Am I missing something ?

The idea of being independently restorable maybe originated with Tom's comment
here: https://www.postgresql.org/message-id/30049.1555537881%40sss.pgh.pa.us

--
Justin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Luc Vlaming 2020-10-23 05:51:16 Re: allow partial union-all and improve parallel subquery costing
Previous Message Kyotaro Horiguchi 2020-10-23 05:28:38 Re: Mop-up around psql's \connect behavior