Re: pg_dump, ATTACH, and independently restorable child partitions

On Fri, Oct 23, 2020 at 12:29:40AM -0500, Justin Pryzby wrote:
> 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.
...
> 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

The easy fix is to add an explicit begin/commit.

--
Justin