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: Re: pg_dump, ATTACH, and independently restorable child partitions
Date: Thu, 29 Oct 2020 12:00:20 -0500
Message-ID: <20201029170020.GA31710@telsasoft.com> (raw)
In-Reply-To: <20201024195949.GL9241@telsasoft.com>
References: <20201023052940.GE9241@telsasoft.com>
<20201024195949.GL9241@telsasoft.com>
On Sat, Oct 24, 2020 at 02:59:49PM -0500, Justin Pryzby wrote:
> 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.
Now with updated test script.
--
Justin
Attachments:v2-0001-pg_dump-Allow-child-partitions-to-be-independentl.patch (text/x-diff, 2.1K) download
From 8e34426bf7d863e3e8878a2e31c64ca999ec4464 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 24 Oct 2020 14:51:18 -0500
Subject: [PATCH v2] 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 ++++++++
src/bin/pg_dump/t/002_pg_dump.pl | 2 ++
2 files changed, 10 insertions(+)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex 03f9d4d9e8..4767418849 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -15651,6 +15651,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 " : "",
@@ -15871,6 +15878,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)
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.plindex ec63662060..90dc2c5ae9 100644--- a/src/bin/pg_dump/t/002_pg_dump.pl+++ b/src/bin/pg_dump/t/002_pg_dump.pl@@ -2343,6 +2343,7 @@ my %tests = (
regexp => qr/^
\Q-- Name: measurement;\E.*\n
\Q--\E\n\n
+ \Qbegin;\E\n
\QCREATE TABLE dump_test.measurement (\E\n
\s+\Qcity_id integer NOT NULL,\E\n
\s+\Qlogdate date NOT NULL,\E\n
@@ -2351,6 +2352,7 @@ my %tests = (
\s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer))\E\n
\)\n
\QPARTITION BY RANGE (logdate);\E\n
+ \Qcommit;\E\n
/xm,
like =>
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
--
2.17.0
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: <20201029170020.GA31710@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