https://www.postgresql.org/message-id/CA%2BTgmoZFn7TJ7QBsFat
nuEE%3DGYGdZSNXqr9489n5JBsdy5rFfA%40mail.gmail.com
Above thread, it's been pointed out as important consideration
about adding reload-through-the-top-parent switch the partition
table. One small step toward making use of hash function was
adding a switch into pg_dump which reload through the top
parent for the partition table.
Attach patch does the implementation for the same:
- Added switch reload-through-root: (Open for suggestion for the switch
name)
- Fix dumpTableData to COPY to the Root table with the reload-through-root
switch.
- Fix dumpTableData_insert - to generate the proper insert statement with
the reload-through-root switch.
- Added switch reload-through-root into pg_dumpall
Testing:
- Performed test with multi level partition + different schema combination.
- Tested a case with parent and child attributes in different order.
Attaching the pg_dump output for below test with --reload-through-root for
COPY and INSERTS.
Testcase:
create schema a;
create schema b;
create schema c;
create table a.t1 ( a int, b int ) partition by list(a);
create table b.t1_p1 partition of a.t1 FOR VALUES in ( 1, 2, 3) partition
by list(b);
create table c.t1_p1_p1 partition of b.t1_p1 FOR VALUES in (10, 20 );
insert into a.t1 values ( 2 , 10 );
insert into a.t1 values ( 1 , 20 );
My colleague Robert and I had doubt about the order in of TABLE
and TABLE_DATA. We thought earlier that reload-thought-root might
might not solve the purpose which has been discussed in the above
mentioned thread. But later looking into code I realize the sort order
for DO_TABLE and DO_TABLE_DATA are different, so we don't need
to worry about that issue.
TODOs:
- Update documentation for pg_dump & pg_dumpall
Thanks,
Rushabh Lathia
www.EnterpriseDB.com
--
-- PostgreSQL database dump
--
-- Dumped from database version 10beta2
-- Dumped by pg_dump version 10beta2
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: rushabh
--
COMMENT ON DATABASE postgres IS 'default administrative connection database';
--
-- Name: a; Type: SCHEMA; Schema: -; Owner: rushabh
--
CREATE SCHEMA a;
ALTER SCHEMA a OWNER TO rushabh;
--
-- Name: b; Type: SCHEMA; Schema: -; Owner: rushabh
--
CREATE SCHEMA b;
ALTER SCHEMA b OWNER TO rushabh;
--
-- Name: c; Type: SCHEMA; Schema: -; Owner: rushabh
--
CREATE SCHEMA c;
ALTER SCHEMA c OWNER TO rushabh;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = a, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: t1; Type: TABLE; Schema: a; Owner: rushabh
--
CREATE TABLE t1 (
a integer,
b integer
)
PARTITION BY LIST (a);
ALTER TABLE t1 OWNER TO rushabh;
SET search_path = b, pg_catalog;
--
-- Name: t1_p1; Type: TABLE; Schema: b; Owner: rushabh
--
CREATE TABLE t1_p1 PARTITION OF a.t1
FOR VALUES IN (1, 2, 3)
PARTITION BY LIST (b);
ALTER TABLE t1_p1 OWNER TO rushabh;
SET search_path = c, pg_catalog;
--
-- Name: t1_p1_p1; Type: TABLE; Schema: c; Owner: rushabh
--
CREATE TABLE t1_p1_p1 PARTITION OF b.t1_p1
FOR VALUES IN (10, 20);
ALTER TABLE t1_p1_p1 OWNER TO rushabh;
--
-- Data for Name: t1_p1_p1; Type: TABLE DATA; Schema: c; Owner: rushabh
--
INSERT INTO a.t1 VALUES (1, 20);
INSERT INTO a.t1 VALUES (2, 10);
--
-- PostgreSQL database dump complete
--
--
-- PostgreSQL database dump
--
-- Dumped from database version 10beta2
-- Dumped by pg_dump version 10beta2
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: rushabh
--
COMMENT ON DATABASE postgres IS 'default administrative connection database';
--
-- Name: a; Type: SCHEMA; Schema: -; Owner: rushabh
--
CREATE SCHEMA a;
ALTER SCHEMA a OWNER TO rushabh;
--
-- Name: b; Type: SCHEMA; Schema: -; Owner: rushabh
--
CREATE SCHEMA b;
ALTER SCHEMA b OWNER TO rushabh;
--
-- Name: c; Type: SCHEMA; Schema: -; Owner: rushabh
--
CREATE SCHEMA c;
ALTER SCHEMA c OWNER TO rushabh;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = a, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: t1; Type: TABLE; Schema: a; Owner: rushabh
--
CREATE TABLE t1 (
a integer,
b integer
)
PARTITION BY LIST (a);
ALTER TABLE t1 OWNER TO rushabh;
SET search_path = b, pg_catalog;
--
-- Name: t1_p1; Type: TABLE; Schema: b; Owner: rushabh
--
CREATE TABLE t1_p1 PARTITION OF a.t1
FOR VALUES IN (1, 2, 3)
PARTITION BY LIST (b);
ALTER TABLE t1_p1 OWNER TO rushabh;
SET search_path = c, pg_catalog;
--
-- Name: t1_p1_p1; Type: TABLE; Schema: c; Owner: rushabh
--
CREATE TABLE t1_p1_p1 PARTITION OF b.t1_p1
FOR VALUES IN (10, 20);
ALTER TABLE t1_p1_p1 OWNER TO rushabh;
--
-- Data for Name: t1_p1_p1; Type: TABLE DATA; Schema: c; Owner: rushabh
--
COPY a.t1 (a, b) FROM stdin;
1 20
2 10
\.
--
-- PostgreSQL database dump complete
--
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 144068a..bfd49a8 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -157,6 +157,7 @@ typedef struct _dumpOptions
int outputNoTablespaces;
int use_setsessauth;
int enable_row_security;
+ int reload_through_root;
/* default, if no "inclusion" switches appear, is to dump everything */
bool include_everything;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index fb964d0..f0eacd2 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -269,6 +269,7 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
const char *prefix, Archive *fout);
static char *get_synchronized_snapshot(Archive *fout);
static void setupDumpWorker(Archive *AHX);
+static TableInfo *getRootTableInfo(TableInfo *tbinfo);
int
@@ -345,6 +346,7 @@ main(int argc, char **argv)
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
{"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
+ {"reload-through-root", no_argument, &dopt.reload_through_root, 1},
{"role", required_argument, NULL, 3},
{"section", required_argument, NULL, 5},
{"serializable-deferrable", no_argument, &dopt.serializable_deferrable, 1},
@@ -959,6 +961,7 @@ help(const char *progname)
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
+ printf(_(" --reload-through-root reload partition table through the root relation\n"));
printf(_(" --section=SECTION dump named section (pre-data, data, or post-data)\n"));
printf(_(" --serializable-deferrable wait until the dump can run without anomalies\n"));
printf(_(" --snapshot=SNAPSHOT use given snapshot for the dump\n"));
@@ -1902,8 +1905,32 @@ dumpTableData_insert(Archive *fout, void *dcontext)
if (insertStmt == NULL)
{
insertStmt = createPQExpBuffer();
+
+ /*
+ * When reload-through-root is set, get the root relation name
+ * for the partition table, so that we can reload data through
+ * the root table.
+ */
+ if (dopt->reload_through_root && tbinfo->ispartition)
+ {
+ TableInfo *parentTbinfo;
+
+ parentTbinfo = getRootTableInfo(tbinfo);
+
+ /*
+ * When we loading data through the root, we will qualify
+ * the table name. This is needed because earlier
+ * search_path will be set for the partition table.
+ */
+ classname = (char *) fmtQualifiedId(fout->remoteVersion,
+ parentTbinfo->dobj.namespace->dobj.name,
+ parentTbinfo->dobj.name);
+ }
+ else
+ classname = fmtId(tbinfo->dobj.name);
+
appendPQExpBuffer(insertStmt, "INSERT INTO %s ",
- fmtId(classname));
+ classname);
/* corner case for zero-column table */
if (nfields == 0)
@@ -2025,6 +2052,20 @@ dumpTableData_insert(Archive *fout, void *dcontext)
return 1;
}
+/*
+ * getRootTableInfo:
+ * get the root TableInfo for the given partition table.
+ */
+static TableInfo *
+getRootTableInfo(TableInfo *tbinfo)
+{
+ Assert(tbinfo->ispartition);
+ Assert(tbinfo->numParents == 1);
+ if (tbinfo->parents[0]->ispartition)
+ return getRootTableInfo(tbinfo->parents[0]);
+
+ return tbinfo->parents[0];
+}
/*
* dumpTableData -
@@ -2041,14 +2082,37 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
PQExpBuffer clistBuf = createPQExpBuffer();
DataDumperPtr dumpFn;
char *copyStmt;
+ const char *copyFrom;
if (!dopt->dump_inserts)
{
/* Dump/restore using COPY */
dumpFn = dumpTableData_copy;
- /* must use 2 steps here 'cause fmtId is nonreentrant */
+
+ /*
+ * When reload-through-root is set, get the root relation name for the
+ * partition table, so that we can reload data through the root table.
+ */
+ if (dopt->reload_through_root && tbinfo->ispartition)
+ {
+ TableInfo *parentTbinfo;
+
+ parentTbinfo = getRootTableInfo(tbinfo);
+
+ /*
+ * When we loading data through the root, we will qualify the
+ * table name. This is needed because earlier search_path will be
+ * set for the partition table.
+ */
+ copyFrom = fmtQualifiedId(fout->remoteVersion,
+ parentTbinfo->dobj.namespace->dobj.name,
+ parentTbinfo->dobj.name);
+ }
+ else
+ copyFrom = fmtId(tbinfo->dobj.name);
+
appendPQExpBuffer(copyBuf, "COPY %s ",
- fmtId(tbinfo->dobj.name));
+ copyFrom);
appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
fmtCopyColumnList(tbinfo, clistBuf),
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index b14bb8e..1849581 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -80,6 +80,7 @@ static int no_subscriptions = 0;
static int no_unlogged_table_data = 0;
static int no_role_passwords = 0;
static int server_version;
+static int reload_through_root = 0;
static char role_catalog[10];
#define PG_AUTHID "pg_authid"
@@ -128,6 +129,7 @@ main(int argc, char *argv[])
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &no_tablespaces, 1},
{"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
+ {"reload-through-root", no_argument, &reload_through_root, 1},
{"role", required_argument, NULL, 3},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-publications", no_argument, &no_publications, 1},
@@ -385,6 +387,8 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --no-tablespaces");
if (quote_all_identifiers)
appendPQExpBufferStr(pgdumpopts, " --quote-all-identifiers");
+ if (reload_through_root)
+ appendPQExpBufferStr(pgdumpopts, " --reload-through-root");
if (use_setsessauth)
appendPQExpBufferStr(pgdumpopts, " --use-set-session-authorization");
if (no_publications)
@@ -606,6 +610,7 @@ help(void)
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
+ printf(_(" --reload-through-root reload partition table through the root relation\n"));
printf(_(" --use-set-session-authorization\n"
" use SET SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
