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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers