Date: Fri, 23 Oct 2020 00:29:40 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: David Rowley <drowley@postgresql.org>,
	pgsql-hackers@lists.postgresql.org
Subject: pg_dump, ATTACH, and independently restorable child partitions
Message-ID: <20201023052940.GE9241@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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