From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: David Rowley <drowley@postgresql.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: pg_dump, ATTACH, and independently restorable child partitions
Date: Fri, 23 Oct 2020 00:29:40 -0500
Message-ID: <20201023052940.GE9241@telsasoft.com> (raw)
Since this commit, pg_dump CREATEs tables and then ATTACHes them:
|commit 33a53130a89447e171a8268ae0b221bb48af6468
|Author: Alvaro Herrera <alvherre@alvh.no-ip.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, alvherre@alvh.no-ip.org, drowley@postgresql.org, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_dump, ATTACH, and independently restorable child partitions
In-Reply-To: <20201023052940.GE9241@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox