Date: Sat, 24 Oct 2020 14:59:49 -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: Re: pg_dump, ATTACH, and independently restorable child partitions
Message-ID: <20201024195949.GL9241@telsasoft.com>
References: <20201023052940.GE9241@telsasoft.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="McpcKDxJRrEJVmOH"
Content-Disposition: inline
In-Reply-To: <20201023052940.GE9241@telsasoft.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--McpcKDxJRrEJVmOH
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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@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.
...
> 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

--McpcKDxJRrEJVmOH
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-pg_dump-Allow-child-partitions-to-be-independently-r.patch"

From 4ca8cf7de0e575b0175fbc3a5797b75857bd2fab Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 24 Oct 2020 14:51:18 -0500
Subject: [PATCH] pg_dump: Allow child partitions to be independently restored

..even if the parent doesn't exist, or has missing/incompatible columns

This seems to have been intended by commit 33a53130a
---
 src/bin/pg_dump/pg_dump.c | 8 ++++++++
 1 file changed, 8 insertions(+)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 40844fa1e7..8a783f1d3e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15676,6 +15676,13 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
 			binary_upgrade_set_pg_class_oids(fout, q,
 											 tbinfo->dobj.catId.oid, false);
 
+		/*
+		 * Use explicit transaction commands so that failure in a
+		 * following command in the same txn doesn't cause ROLLBACK of
+		 * the "CREATE TABLE".
+		 */
+		appendPQExpBufferStr(q, "begin;\n");
+
 		appendPQExpBuffer(q, "CREATE %s%s %s",
 						  tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
 						  "UNLOGGED " : "",
@@ -15896,6 +15903,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
 		}
 		else
 			appendPQExpBufferStr(q, ";\n");
+		appendPQExpBufferStr(q, "commit;\n");
 
 		/* Materialized views can depend on extensions */
 		if (tbinfo->relkind == RELKIND_MATVIEW)
-- 
2.17.0


--McpcKDxJRrEJVmOH--