agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 4/9] psql and pg_dump support for partitions. 13+ messages / 2 participants [nested] [flat]
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 46 ++++++++++++- src/test/regress/expected/create_table.out | 23 +++++++ src/test/regress/sql/create_table.sql | 6 ++ 7 files changed, 340 insertions(+), 9 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 77ce807..5d90413 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8405,6 +8405,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionListSpec: + { + PartitionListSpec *list_spec = (PartitionListSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionRangeSpec: + { + PartitionRangeSpec *range_spec = (PartitionRangeSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c418ba7..626fa00 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6125,6 +6125,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15302,6 +15359,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15330,8 +15398,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15360,7 +15431,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15425,15 +15497,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15603,6 +15682,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 10d924a..9554f5e 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition Of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,15 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ + /* print child tables (exclude, if parent is a partitioned table) */ if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s' AND c.relkind != 'P')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 2b12276..5162c82 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -592,6 +592,29 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (b); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition Of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Of: parted FOR VALUES IN ('c') +Partition Key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index c38312e..b29ac79 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -546,6 +546,12 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------954458C17BBEB1BF6FC758E7 Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-2.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 82 +++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 118 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 +++ src/bin/psql/describe.c | 85 +++++++++++++++++--- src/test/regress/expected/create_table.out | 40 ++++++++++ src/test/regress/sql/create_table.sql | 12 +++ 7 files changed, 415 insertions(+), 20 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9004878..99add8e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8447,6 +8447,88 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundSpec: + { + PartitionBoundSpec *spec = (PartitionBoundSpec *) node; + ListCell *cell; + char *sep; + + switch (spec->strategy) + { + case PARTITION_STRATEGY_LIST: + Assert(spec->listdatums != NIL); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, spec->listdatums) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + break; + + case PARTITION_STRATEGY_RANGE: + Assert(spec->lowerdatums != NIL && + spec->upperdatums != NIL && + list_length(spec->lowerdatums) == + list_length(spec->upperdatums)); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " FROM"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->lowerdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + + appendStringInfoString(buf, " TO"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->upperdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + break; + + default: + elog(ERROR, "unrecognized partition strategy: %d", + (int) spec->strategy); + break; + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 3e20f02..22f1806 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -323,6 +338,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -924,6 +976,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index fb92e7f..57e626c 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5608,9 +5608,16 @@ getInherits(Archive *fout, int *numInherits) /* Make sure we are in proper schema */ selectSourceSchema(fout, "pg_catalog"); - /* find all the inheritance information */ - - appendPQExpBufferStr(query, "SELECT inhrelid, inhparent FROM pg_inherits"); + /* + * Find all the inheritance information, excluding implicit inheritance + * via partitioning. We handle that case using getPartitions(), because + * we want more information about partitions than just the parent-child + * relationship. + */ + appendPQExpBufferStr(query, + "SELECT inhrelid, inhparent " + "FROM pg_inherits " + "WHERE inhparent NOT IN (SELECT oid FROM pg_class WHERE relkind = 'P')"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -5637,6 +5644,70 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Before version 10, there are no partitions */ + if (fout->remoteVersion < 100000) + { + *numPartitions = 0; + return NULL; + } + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find the inheritance and boundary information about partitions */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -14168,6 +14239,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -14196,8 +14278,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -14226,7 +14311,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -14284,15 +14370,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -14462,6 +14555,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index f33f86d..dd5ad8f 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -321,6 +321,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -461,6 +463,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9b08bae..0d34927 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1774,6 +1774,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2535,8 +2563,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2565,9 +2597,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2582,24 +2628,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 01124e1..1f56bcb 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -591,6 +591,46 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 1 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_a" CHECK (length(a) > 0) + "part_b_b_check" CHECK (b >= 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition key: LIST (a) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partitions cannot be dropped directly DROP TABLE part_a; -- need to specify CASCADE to drop partitions along with the parent diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 683b852..c28b7b3 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -562,6 +562,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partitions cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------1C84B3F970C18697850BA485 Content-Type: text/x-diff; name="0005-Teach-a-few-places-to-use-partition-check-quals-15.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-15.patc"; filename*1="h" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 85 +++++++++++++++++++++---- src/test/regress/expected/create_table.out | 39 +++++++++++ src/test/regress/sql/create_table.sql | 12 ++++ 7 files changed, 393 insertions(+), 17 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 51e175e..c76ebb1 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8405,6 +8405,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundList: + { + PartitionBoundList *list_spec = (PartitionBoundList *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionBoundRange: + { + PartitionBoundRange *range_spec = (PartitionBoundRange *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index e310b63..4788018 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6141,6 +6141,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15321,6 +15378,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15349,8 +15417,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15379,7 +15450,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15444,15 +15516,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15622,6 +15701,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index db6dc5c..9834599 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2606,24 +2652,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index e0181c4..19257d6 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -595,6 +595,45 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition key: LIST (a) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 7255690..fecae9b 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -551,6 +551,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------5A7B27F4D7359D1CD914EDC6 Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-7.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-7.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 85 +++++++++++++++++++++---- src/test/regress/expected/create_table.out | 39 +++++++++++ src/test/regress/sql/create_table.sql | 12 ++++ 7 files changed, 393 insertions(+), 17 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 82f03ea..6e2bdde 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8411,6 +8411,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundList: + { + PartitionBoundList *list_spec = (PartitionBoundList *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionBoundRange: + { + PartitionBoundRange *range_spec = (PartitionBoundRange *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 8aa615b..9ec4323 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6243,6 +6243,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15423,6 +15480,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15451,8 +15519,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15481,7 +15552,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15546,15 +15618,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15724,6 +15803,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index e57d78e..cae1b45 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2606,24 +2652,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index e0181c4..19257d6 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -595,6 +595,45 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition key: LIST (a) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 7255690..fecae9b 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -551,6 +551,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------89A1FA8649051FBDB5697BF9 Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-8.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-8.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 46 ++++++++++++- src/test/regress/expected/create_table.out | 23 +++++++ src/test/regress/sql/create_table.sql | 6 ++ 7 files changed, 340 insertions(+), 9 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 77ce807..5d90413 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8405,6 +8405,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionListSpec: + { + PartitionListSpec *list_spec = (PartitionListSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionRangeSpec: + { + PartitionRangeSpec *range_spec = (PartitionRangeSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c418ba7..626fa00 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6125,6 +6125,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15302,6 +15359,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15330,8 +15398,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15360,7 +15431,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15425,15 +15497,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15603,6 +15682,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 10d924a..9554f5e 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition Of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,15 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ + /* print child tables (exclude, if parent is a partitioned table) */ if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s' AND c.relkind != 'P')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 2b12276..5162c82 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -592,6 +592,29 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (b); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition Of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Of: parted FOR VALUES IN ('c') +Partition Key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index c38312e..b29ac79 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -546,6 +546,12 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------6EB5B318125CB7DB1A4FD229 Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-3.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-3.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/7] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 82 ++++++++++++++++++++ src/bin/pg_dump/common.c | 86 +++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 118 +++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 +++ src/bin/psql/describe.c | 85 ++++++++++++++++++--- src/test/regress/expected/create_table.out | 40 ++++++++++ src/test/regress/sql/create_table.sql | 12 +++ 7 files changed, 415 insertions(+), 20 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 60fe794816..4e2ba19d1b 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8450,6 +8450,88 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundSpec: + { + PartitionBoundSpec *spec = (PartitionBoundSpec *) node; + ListCell *cell; + char *sep; + + switch (spec->strategy) + { + case PARTITION_STRATEGY_LIST: + Assert(spec->listdatums != NIL); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, spec->listdatums) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + break; + + case PARTITION_STRATEGY_RANGE: + Assert(spec->lowerdatums != NIL && + spec->upperdatums != NIL && + list_length(spec->lowerdatums) == + list_length(spec->upperdatums)); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " FROM"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->lowerdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + + appendStringInfoString(buf, " TO"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->upperdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + break; + + default: + elog(ERROR, "unrecognized partition strategy: %d", + (int) spec->strategy); + break; + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 3e20f028c8..22f1806eca 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -323,6 +338,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -924,6 +976,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 2fb6d5dcc4..b43d152e77 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5642,9 +5642,16 @@ getInherits(Archive *fout, int *numInherits) /* Make sure we are in proper schema */ selectSourceSchema(fout, "pg_catalog"); - /* find all the inheritance information */ - - appendPQExpBufferStr(query, "SELECT inhrelid, inhparent FROM pg_inherits"); + /* + * Find all the inheritance information, excluding implicit inheritance + * via partitioning. We handle that case using getPartitions(), because + * we want more information about partitions than just the parent-child + * relationship. + */ + appendPQExpBufferStr(query, + "SELECT inhrelid, inhparent " + "FROM pg_inherits " + "WHERE inhparent NOT IN (SELECT oid FROM pg_class WHERE relkind = 'P')"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -5671,6 +5678,70 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Before version 10, there are no partitions */ + if (fout->remoteVersion < 100000) + { + *numPartitions = 0; + return NULL; + } + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find the inheritance and boundary information about partitions */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -14249,6 +14320,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -14277,8 +14359,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -14307,7 +14392,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -14365,15 +14451,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -14543,6 +14636,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index f47e535c24..395a9f3288 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -322,6 +322,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index baa5e859d7..f0d955be4f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1808,6 +1808,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2587,8 +2615,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2617,9 +2649,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2634,24 +2680,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index c3afca687f..b40a18aec2 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -601,6 +601,46 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 1 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_a" CHECK (length(a) > 0) + "part_b_b_check" CHECK (b >= 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition key: LIST (a) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partitions cannot be dropped directly DROP TABLE part_a; -- need to specify CASCADE to drop partitions along with the parent diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 7818bc01f6..69848e3094 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -565,6 +565,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partitions cannot be dropped directly DROP TABLE part_a; -- 2.11.0 --------------E1933196A000B12EC95A8B6E Content-Type: text/x-diff; name="0005-Teach-a-few-places-to-use-partition-check-quals-20.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-20.patc"; filename*1="h" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 82 +++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 118 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 +++ src/bin/psql/describe.c | 85 +++++++++++++++++--- src/test/regress/expected/create_table.out | 40 ++++++++++ src/test/regress/sql/create_table.sql | 12 +++ 7 files changed, 415 insertions(+), 20 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9004878..99add8e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8447,6 +8447,88 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundSpec: + { + PartitionBoundSpec *spec = (PartitionBoundSpec *) node; + ListCell *cell; + char *sep; + + switch (spec->strategy) + { + case PARTITION_STRATEGY_LIST: + Assert(spec->listdatums != NIL); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, spec->listdatums) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + break; + + case PARTITION_STRATEGY_RANGE: + Assert(spec->lowerdatums != NIL && + spec->upperdatums != NIL && + list_length(spec->lowerdatums) == + list_length(spec->upperdatums)); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " FROM"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->lowerdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + + appendStringInfoString(buf, " TO"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->upperdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + break; + + default: + elog(ERROR, "unrecognized partition strategy: %d", + (int) spec->strategy); + break; + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 3e20f02..22f1806 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -323,6 +338,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -924,6 +976,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d32d0ff..467c243 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5610,9 +5610,16 @@ getInherits(Archive *fout, int *numInherits) /* Make sure we are in proper schema */ selectSourceSchema(fout, "pg_catalog"); - /* find all the inheritance information */ - - appendPQExpBufferStr(query, "SELECT inhrelid, inhparent FROM pg_inherits"); + /* + * Find all the inheritance information, excluding implicit inheritance + * via partitioning. We handle that case using getPartitions(), because + * we want more information about partitions than just the parent-child + * relationship. + */ + appendPQExpBufferStr(query, + "SELECT inhrelid, inhparent " + "FROM pg_inherits " + "WHERE inhparent NOT IN (SELECT oid FROM pg_class WHERE relkind = 'P')"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -5639,6 +5646,70 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Before version 10, there are no partitions */ + if (fout->remoteVersion < 100000) + { + *numPartitions = 0; + return NULL; + } + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find the inheritance and boundary information about partitions */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -14217,6 +14288,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -14245,8 +14327,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -14275,7 +14360,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -14333,15 +14419,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -14511,6 +14604,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index e9849ef..a7cb00a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -322,6 +322,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -625,6 +636,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9b08bae..0d34927 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1774,6 +1774,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2535,8 +2563,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2565,9 +2597,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2582,24 +2628,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 01124e1..1f56bcb 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -591,6 +591,46 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 1 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_a" CHECK (length(a) > 0) + "part_b_b_check" CHECK (b >= 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition key: LIST (a) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partitions cannot be dropped directly DROP TABLE part_a; -- need to specify CASCADE to drop partitions along with the parent diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 683b852..c28b7b3 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -562,6 +562,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partitions cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------38C0D572AC793CAA7276C062 Content-Type: text/x-diff; name="0005-Teach-a-few-places-to-use-partition-check-quals-16.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-16.patc"; filename*1="h" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/7] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 82 +++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 118 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 +++ src/bin/psql/describe.c | 85 +++++++++++++++++--- src/test/regress/expected/create_table.out | 40 ++++++++++ src/test/regress/sql/create_table.sql | 12 +++ 7 files changed, 415 insertions(+), 20 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9004878..99add8e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8447,6 +8447,88 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundSpec: + { + PartitionBoundSpec *spec = (PartitionBoundSpec *) node; + ListCell *cell; + char *sep; + + switch (spec->strategy) + { + case PARTITION_STRATEGY_LIST: + Assert(spec->listdatums != NIL); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, spec->listdatums) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + break; + + case PARTITION_STRATEGY_RANGE: + Assert(spec->lowerdatums != NIL && + spec->upperdatums != NIL && + list_length(spec->lowerdatums) == + list_length(spec->upperdatums)); + + appendStringInfoString(buf, "FOR VALUES"); + appendStringInfoString(buf, " FROM"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->lowerdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + + appendStringInfoString(buf, " TO"); + appendStringInfoString(buf, " ("); + sep = ""; + foreach (cell, spec->upperdatums) + { + PartitionRangeDatum *datum = lfirst(cell); + Const *val; + + appendStringInfoString(buf, sep); + if (datum->infinite) + appendStringInfoString(buf, "UNBOUNDED"); + else + { + val = (Const *) datum->value; + get_const_expr(val, context, -1); + } + sep = ", "; + } + appendStringInfoString(buf, ")"); + break; + + default: + elog(ERROR, "unrecognized partition strategy: %d", + (int) spec->strategy); + break; + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 3e20f02..22f1806 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -323,6 +338,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -924,6 +976,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d32d0ff..467c243 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5610,9 +5610,16 @@ getInherits(Archive *fout, int *numInherits) /* Make sure we are in proper schema */ selectSourceSchema(fout, "pg_catalog"); - /* find all the inheritance information */ - - appendPQExpBufferStr(query, "SELECT inhrelid, inhparent FROM pg_inherits"); + /* + * Find all the inheritance information, excluding implicit inheritance + * via partitioning. We handle that case using getPartitions(), because + * we want more information about partitions than just the parent-child + * relationship. + */ + appendPQExpBufferStr(query, + "SELECT inhrelid, inhparent " + "FROM pg_inherits " + "WHERE inhparent NOT IN (SELECT oid FROM pg_class WHERE relkind = 'P')"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -5639,6 +5646,70 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Before version 10, there are no partitions */ + if (fout->remoteVersion < 100000) + { + *numPartitions = 0; + return NULL; + } + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find the inheritance and boundary information about partitions */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -14217,6 +14288,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -14245,8 +14327,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -14275,7 +14360,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -14333,15 +14419,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -14511,6 +14604,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index e9849ef..a7cb00a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -322,6 +322,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -625,6 +636,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9b08bae..0d34927 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1774,6 +1774,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2535,8 +2563,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2565,9 +2597,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2582,24 +2628,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index bbb7e28..b5f6d06 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -591,6 +591,46 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 1 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_a" CHECK (length(a) > 0) + "part_b_b_check" CHECK (b >= 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition key: LIST (a) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partitions cannot be dropped directly DROP TABLE part_a; -- need to specify CASCADE to drop partitions along with the parent diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 683b852..c28b7b3 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -562,6 +562,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a level-2 partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partitions cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------F7EE68175C11124DB3D461AC Content-Type: text/x-diff; name="0005-Teach-a-few-places-to-use-partition-check-quals-17.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-17.patc"; filename*1="h" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++ src/bin/pg_dump/common.c | 86 +++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 105 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 +++ src/bin/psql/describe.c | 85 +++++++++++++++++++--- src/test/regress/expected/create_table.out | 39 ++++++++++ src/test/regress/sql/create_table.sql | 12 +++ 7 files changed, 400 insertions(+), 17 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 82f03ea..6e2bdde 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8411,6 +8411,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionBoundList: + { + PartitionBoundList *list_spec = (PartitionBoundList *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionBoundRange: + { + PartitionBoundRange *range_spec = (PartitionBoundRange *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 8aa615b..8ece47a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6243,6 +6243,70 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Before version 10, there are no partitions */ + if (fout->remoteVersion < 100000) + { + *numPartitions = 0; + return NULL; + } + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15423,6 +15487,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15451,8 +15526,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15481,7 +15559,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15546,15 +15625,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15724,6 +15810,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index e57d78e..cae1b45 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2606,24 +2652,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 580a872..0578389 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -595,6 +595,45 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition key: LIST (a) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 2610222..3a5301e 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -551,6 +551,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------F4EE744316C41AC08CF3C467 Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-9.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-9.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 85 +++++++++++++++++++++---- src/test/regress/expected/create_table.out | 39 +++++++++++ src/test/regress/sql/create_table.sql | 12 ++++ 7 files changed, 393 insertions(+), 17 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 03be202..4bc4e91 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8405,6 +8405,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionListSpec: + { + PartitionListSpec *list_spec = (PartitionListSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionRangeSpec: + { + PartitionRangeSpec *range_spec = (PartitionRangeSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c805a84..40ab7cc 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6134,6 +6134,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15311,6 +15368,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15339,8 +15407,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15369,7 +15440,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15434,15 +15506,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15612,6 +15691,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 10d924a..0b763ee 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition Of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2606,24 +2652,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 2b12276..732bf80 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -592,6 +592,45 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (b); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition Of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Of: parted FOR VALUES IN ('c') +Partition Key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Key: LIST (a) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index c38312e..8f52a85 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -546,6 +546,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------4012470FC48539D6F6714CEA Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-4.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-4.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/9] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 85 +++++++++++++++++++++---- src/test/regress/expected/create_table.out | 39 +++++++++++ src/test/regress/sql/create_table.sql | 12 ++++ 7 files changed, 393 insertions(+), 17 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 03be202..4bc4e91 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8405,6 +8405,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionListSpec: + { + PartitionListSpec *list_spec = (PartitionListSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionRangeSpec: + { + PartitionRangeSpec *range_spec = (PartitionRangeSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c805a84..40ab7cc 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6134,6 +6134,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT inhrelid as partrelid, inhparent AS partparent," + " pg_get_expr(relpartbound, inhrelid) AS partbound" + " FROM pg_class c, pg_inherits" + " WHERE c.oid = inhrelid AND c.relispartition"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15311,6 +15368,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15339,8 +15407,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15369,7 +15440,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15434,15 +15506,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15612,6 +15691,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 10d924a..0b763ee 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1798,6 +1798,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + " FROM pg_catalog.pg_class c" + " JOIN pg_catalog.pg_inherits" + " ON c.oid = inhrelid" + " WHERE c.oid = '%s' AND c.relispartition;", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition Of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2559,8 +2587,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2589,9 +2621,23 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ - if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + /* print child tables (with additional info if partitions) */ + if (pset.sversion >= 100000) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); + else if (pset.sversion >= 80300) + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); @@ -2606,24 +2652,39 @@ describeOneTableDetails(const char *schemaname, /* print the number of child tables, if any */ if (tuples > 0) { - printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + if (tableinfo.relkind != 'P') + printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); + else + printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); printTableAddFooter(&cont, buf.data); } } else { /* display the list of child tables */ - const char *ct = _("Child tables"); + const char *ct = tableinfo.relkind != 'P' ? _("Child tables") : _("Partitions"); int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); for (i = 0; i < tuples; i++) { - if (i == 0) - printfPQExpBuffer(&buf, "%s: %s", - ct, PQgetvalue(result, i, 0)); + if (tableinfo.relkind != 'P') + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", + ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", + ctw, "", PQgetvalue(result, i, 0)); + } else - printfPQExpBuffer(&buf, "%*s %s", - ctw, "", PQgetvalue(result, i, 0)); + { + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s %s", + ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + else + printfPQExpBuffer(&buf, "%*s %s %s", + ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1)); + } if (i < tuples - 1) appendPQExpBufferChar(&buf, ','); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index b3e987d..fac621b 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -598,6 +598,45 @@ ERROR: cannot use column or expression from ancestor partition key CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b)); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition Of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Of: parted FOR VALUES IN ('c') +Partition Key: RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + Table "public.parted" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Key: LIST (a) +Check constraints: + "check_b" CHECK (b > 0) +Number of partitions: 3 (Use \d+ to list them.) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 733c073..5e45014 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -552,6 +552,18 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- Show partition count in the parent's describe output +-- Tempted to include \d+ output listing partitions with bound info but +-- output could vary depending on the order in which partition oids are +-- returned. +\d parted + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------D008BA677674C4836D7B65AD Content-Type: text/x-diff; name="0005-Refactor-optimizer-s-inheritance-set-expansion-code-5.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Refactor-optimizer-s-inheritance-set-expansion-code-5.p"; filename*1="atch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions. @ 2016-07-12 08:50 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 13+ messages in thread From: amit @ 2016-07-12 08:50 UTC (permalink / raw) Takes care of both the partition bound deparse stuff and handling parent-partition relationship (filtering pg_inherits entries pertaining to partitions and handling appropriately). --- src/backend/utils/adt/ruleutils.c | 78 ++++++++++++++++++++++ src/bin/pg_dump/common.c | 86 ++++++++++++++++++++++++ src/bin/pg_dump/pg_dump.c | 98 ++++++++++++++++++++++++++-- src/bin/pg_dump/pg_dump.h | 12 ++++ src/bin/psql/describe.c | 46 ++++++++++++- src/test/regress/expected/create_table.out | 23 +++++++ src/test/regress/sql/create_table.sql | 6 ++ 7 files changed, 340 insertions(+), 9 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 4691b5b..b29fec1 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8350,6 +8350,84 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_PartitionListSpec: + { + PartitionListSpec *list_spec = (PartitionListSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach (cell, list_spec->values) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + + appendStringInfoString(buf, ")"); + } + break; + + case T_PartitionRangeSpec: + { + PartitionRangeSpec *range_spec = (PartitionRangeSpec *) node; + ListCell *cell; + char *sep; + + appendStringInfoString(buf, "FOR VALUES"); + + appendStringInfoString(buf, " START"); + if (!range_spec->lower) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->lower) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (!range_spec->lowerinc) + appendStringInfoString(buf, " EXCLUSIVE"); + } + + appendStringInfoString(buf, " END"); + + if (!range_spec->upper) + appendStringInfoString(buf, " UNBOUNDED"); + else + { + appendStringInfoString(buf, " ("); + + sep = ""; + foreach (cell, range_spec->upper) + { + Const *val = lfirst(cell); + + appendStringInfoString(buf, sep); + get_const_expr(val, context, -1); + sep = ", "; + } + appendStringInfoString(buf, ")"); + + if (range_spec->upperinc) + appendStringInfoString(buf, " INCLUSIVE"); + } + } + break; + case T_List: { char *sep; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1cbb987..c8e56bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -68,6 +68,8 @@ static int numextmembers; static void flagInhTables(TableInfo *tbinfo, int numTables, InhInfo *inhinfo, int numInherits); +static void flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions); static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables); static DumpableObject **buildIndexArray(void *objArray, int numObjs, Size objSize); @@ -75,6 +77,8 @@ static int DOCatalogIdCompare(const void *p1, const void *p2); static int ExtensionMemberIdCompare(const void *p1, const void *p2); static void findParentsByOid(TableInfo *self, InhInfo *inhinfo, int numInherits); +static void findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions); static int strInArray(const char *pattern, char **arr, int arr_size); @@ -93,8 +97,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) NamespaceInfo *nspinfo; ExtensionInfo *extinfo; InhInfo *inhinfo; + PartInfo *partinfo; int numAggregates; int numInherits; + int numPartitions; int numRules; int numProcLangs; int numCasts; @@ -232,6 +238,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, &numInherits); if (g_verbose) + write_msg(NULL, "reading partition information\n"); + partinfo = getPartitions(fout, &numPartitions); + + if (g_verbose) write_msg(NULL, "reading event triggers\n"); getEventTriggers(fout, &numEventTriggers); @@ -245,6 +255,11 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, "finding inheritance relationships\n"); flagInhTables(tblinfo, numTables, inhinfo, numInherits); + /* Link tables to partition parents, mark parents as interesting */ + if (g_verbose) + write_msg(NULL, "finding partition relationships\n"); + flagPartitions(tblinfo, numTables, partinfo, numPartitions); + if (g_verbose) write_msg(NULL, "reading column info for interesting tables\n"); getTableAttrs(fout, tblinfo, numTables); @@ -319,6 +334,43 @@ flagInhTables(TableInfo *tblinfo, int numTables, } } +/* flagPartitions - + * Fill in parent link fields of every target table that is partition, + * and mark parents of partitions as interesting + * + * modifies tblinfo + */ +static void +flagPartitions(TableInfo *tblinfo, int numTables, + PartInfo *partinfo, int numPartitions) +{ + int i; + + for (i = 0; i < numTables; i++) + { + /* Some kinds are never partitions */ + if (tblinfo[i].relkind == RELKIND_SEQUENCE || + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) + continue; + + /* Don't bother computing anything for non-target tables, either */ + if (!tblinfo[i].dobj.dump) + continue; + + /* Find the parent TableInfo and save */ + findPartitionParentByOid(&tblinfo[i], partinfo, numPartitions); + + /* Mark the parent as interesting for getTableAttrs */ + if (tblinfo[i].partitionOf) + { + tblinfo[i].partitionOf->interesting = true; + addObjectDependency(&tblinfo[i].dobj, + tblinfo[i].partitionOf->dobj.dumpId); + } + } +} + /* flagInhAttrs - * for each dumpable table in tblinfo, flag its inherited attributes * @@ -920,6 +972,40 @@ findParentsByOid(TableInfo *self, } /* + * findPartitionParentByOid + * find a partition's parent in tblinfo[] + */ +static void +findPartitionParentByOid(TableInfo *self, PartInfo *partinfo, + int numPartitions) +{ + Oid oid = self->dobj.catId.oid; + int i; + + for (i = 0; i < numPartitions; i++) + { + if (partinfo[i].partrelid == oid) + { + TableInfo *parent; + + parent = findTableByOid(partinfo[i].partparent); + if (parent == NULL) + { + write_msg(NULL, "failed sanity check, parent OID %u of table \"%s\" (OID %u) not found\n", + partinfo[i].partparent, + self->dobj.name, + oid); + exit_nicely(1); + } + self->partitionOf = parent; + + /* While we're at it, also save the partdef */ + self->partitiondef = partinfo[i].partdef; + } + } +} + +/* * parseOidArray * parse a string of numbers delimited by spaces into a character array * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index a127672..8e4231e 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6124,6 +6124,63 @@ getInherits(Archive *fout, int *numInherits) } /* + * getPartitions + * read all the partition inheritance and partition bound information + * from the system catalogs return them in the PartInfo* structure + * + * numPartitions is set to the number of pairs read in + */ +PartInfo * +getPartitions(Archive *fout, int *numPartitions) +{ + PGresult *res; + int ntups; + int i; + PQExpBuffer query = createPQExpBuffer(); + PartInfo *partinfo; + + int i_partrelid; + int i_partparent; + int i_partbound; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + /* find all the inheritance information */ + + appendPQExpBufferStr(query, + "SELECT partrelid, inhparent AS partparent, " + " pg_get_expr(partbound, partrelid) AS partbound " + "FROM pg_partition, pg_inherits " + "WHERE partrelid = inhrelid"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + *numPartitions = ntups; + + partinfo = (PartInfo *) pg_malloc(ntups * sizeof(PartInfo)); + + i_partrelid = PQfnumber(res, "partrelid"); + i_partparent = PQfnumber(res, "partparent"); + i_partbound = PQfnumber(res, "partbound"); + + for (i = 0; i < ntups; i++) + { + partinfo[i].partrelid = atooid(PQgetvalue(res, i, i_partrelid)); + partinfo[i].partparent = atooid(PQgetvalue(res, i, i_partparent)); + partinfo[i].partdef = pg_strdup(PQgetvalue(res, i, i_partbound)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return partinfo; +} + +/* * getIndexes * get information about every index on a dumpable table * @@ -15301,6 +15358,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !dopt->binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->partitionOf && !dopt->binary_upgrade) + { + TableInfo *parentRel = tbinfo->partitionOf; + + appendPQExpBuffer(q, " PARTITION OF "); + if (parentRel->dobj.namespace != tbinfo->dobj.namespace) + appendPQExpBuffer(q, "%s.", + fmtId(parentRel->dobj.namespace->dobj.name)); + appendPQExpBufferStr(q, fmtId(parentRel->dobj.name)); + } + if (tbinfo->relkind != RELKIND_MATVIEW) { /* Dump the attributes */ @@ -15329,8 +15397,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) (!tbinfo->inhNotNull[j] || dopt->binary_upgrade)); - /* Skip column if fully defined by reloftype */ - if (tbinfo->reloftype && + /* + * Skip column if fully defined by reloftype or the + * partition parent. + */ + if ((tbinfo->reloftype || tbinfo->partitionOf) && !has_default && !has_notnull && !dopt->binary_upgrade) continue; @@ -15359,7 +15430,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (tbinfo->reloftype && !dopt->binary_upgrade) + if ((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade) { appendPQExpBufferStr(q, " WITH OPTIONS"); } @@ -15424,15 +15496,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (actual_atts) appendPQExpBufferStr(q, "\n)"); - else if (!(tbinfo->reloftype && !dopt->binary_upgrade)) + else if (!((tbinfo->reloftype || tbinfo->partitionOf) && + !dopt->binary_upgrade)) { /* * We must have a parenthesized attribute list, even though - * empty, when not using the OF TYPE syntax. + * empty, when not using the OF TYPE or PARTITION OF syntax. */ appendPQExpBufferStr(q, " (\n)"); } + if (tbinfo->partitiondef && !dopt->binary_upgrade) + { + appendPQExpBufferStr(q, "\n"); + appendPQExpBufferStr(q, tbinfo->partitiondef); + } + if (numParents > 0 && !dopt->binary_upgrade) { appendPQExpBufferStr(q, "\nINHERITS ("); @@ -15602,6 +15681,15 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->reloftype); } + if (tbinfo->partitionOf) + { + appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n"); + appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n", + fmtId(tbinfo->partitionOf->dobj.name), + tbinfo->dobj.name, + tbinfo->partitiondef); + } + appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u', relminmxid = '%u'\n" diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0292859..760067a 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -320,6 +320,8 @@ typedef struct _tableInfo struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */ int numTriggers; /* number of triggers for table */ struct _triggerInfo *triggers; /* array of TriggerInfo structs */ + struct _tableInfo *partitionOf; /* TableInfo for the partition parent */ + char *partitiondef; /* partition key definition */ } TableInfo; typedef struct _attrDefInfo @@ -460,6 +462,15 @@ typedef struct _inhInfo Oid inhparent; /* OID of its parent */ } InhInfo; +/* PartInfo isn't a DumpableObject, just temporary state */ +typedef struct _partInfo +{ + Oid partrelid; /* OID of a partition */ + Oid partparent; /* OID of its parent */ + char *partdef; /* partition bound definition */ +} PartInfo; + + typedef struct _prsInfo { DumpableObject dobj; @@ -626,6 +637,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions); extern TableInfo *getTables(Archive *fout, int *numTables); extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables); extern InhInfo *getInherits(Archive *fout, int *numInherits); +extern PartInfo *getPartitions(Archive *fout, int *numPartitions); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); extern RuleInfo *getRules(Archive *fout, int *numRules); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 3ba8a90..32b84ca 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1786,6 +1786,34 @@ describeOneTableDetails(const char *schemaname, } /* Make footers */ + if (pset.sversion >= 90600) + { + /* Get the partition information */ + PGresult *result; + char *parent_name; + char *partdef; + + printfPQExpBuffer(&buf, + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(partbound, partrelid)" + " FROM pg_catalog.pg_partition p" + " JOIN pg_catalog.pg_inherits i" + " ON p.partrelid = i.inhrelid" + " WHERE p.partrelid = '%s';", oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + parent_name = PQgetvalue(result, 0, 0); + partdef = PQgetvalue(result, 0, 1); + printfPQExpBuffer(&tmpbuf, _("Partition Of: %s %s"), parent_name, + partdef); + printTableAddFooter(&cont, tmpbuf.data); + PQclear(result); + } + } + if (pset.sversion >= 90600 && tableinfo.relkind == 'P') { /* Get the partition key information */ @@ -2547,8 +2575,12 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print inherited tables */ - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); + /* print inherited tables (exclude, if parent is a partitioned table) */ + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" + " AND c.relkind != 'P' ORDER BY inhseqno;", oid); result = PSQLexec(buf.data); if (!result) @@ -2577,9 +2609,15 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print child tables */ + /* print child tables (exclude, if parent is a partitioned table) */ if (pset.sversion >= 80300) - printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); + printfPQExpBuffer(&buf, + "SELECT c.oid::pg_catalog.regclass" + " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhrelid AND" + " i.inhparent = '%s' AND" + " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s' AND c.relkind != 'P')" + " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid); else printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 9295093..31bd1d8 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -609,6 +609,29 @@ ERROR: column "c" named in partition key does not exist CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (b); -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Modifiers +--------+---------+--------------------- + a | text | + b | integer | not null default 10 +Partition Of: parted FOR VALUES IN ('b') +Check constraints: + "check_b" CHECK (b > 0) + +-- Both partition bound and partition key in describe output +\d part_c + Partitioned table "public.part_c" + Column | Type | Modifiers +--------+---------+-------------------- + a | text | + b | integer | not null default 1 +Partition Of: parted FOR VALUES IN ('c') +Partition Key: PARTITION BY RANGE (b) +Check constraints: + "check_b" CHECK (b > 0) + -- partition cannot be dropped directly DROP TABLE part_a; ERROR: "part_a" is a partition of "parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 1e14c4a..d920abb 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -545,6 +545,12 @@ CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ( -- create a partition of partition CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES START (1) END (10); +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + -- partition cannot be dropped directly DROP TABLE part_a; -- 1.7.1 --------------8C4C48E640631A327D3D2826 Content-Type: text/x-diff; name="0005-Teach-a-few-places-to-use-partition-check-constraint.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Teach-a-few-places-to-use-partition-check-constraint.pa"; filename*1="tch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH v2 4/9] use Buffer instead of buffer ID for startup's buffer pin wait @ 2026-07-22 15:32 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 13+ messages in thread From: Nathan Bossart @ 2026-07-22 15:32 UTC (permalink / raw) --- src/backend/storage/buffer/bufmgr.c | 16 ++++++++-------- src/backend/storage/lmgr/proc.c | 20 ++++++++++---------- src/include/storage/proc.h | 9 +++++---- 3 files changed, 23 insertions(+), 22 deletions(-) diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 3908529872a..a85547fa492 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6806,12 +6806,12 @@ LockBufferForCleanup(Buffer buffer) if (log_recovery_conflict_waits && waitStart == 0) waitStart = GetCurrentTimestamp(); - /* Publish the bufid that Startup process waits on */ - SetStartupBufferPinWaitBufId(buffer - 1); + /* Publish the buffer that Startup process waits on */ + SetStartupBufferPinWaitBuf(buffer); /* Set alarm and then wait to be signaled by UnpinBuffer() */ ResolveRecoveryConflictWithBufferPin(); - /* Reset the published bufid */ - SetStartupBufferPinWaitBufId(-1); + /* Reset the published buffer */ + SetStartupBufferPinWaitBuf(InvalidBuffer); } else ProcWaitForSignal(WAIT_EVENT_BUFFER_CLEANUP); @@ -6865,18 +6865,18 @@ cleanup_lock_acquired: bool HoldingBufferPinThatDelaysRecovery(void) { - int bufid = GetStartupBufferPinWaitBufId(); + Buffer buffer = GetStartupBufferPinWaitBuf(); /* * If we get woken slowly then it's possible that the Startup process was * already woken by other backends before we got here. Also possible that * we get here by multiple interrupts or interrupts at inappropriate - * times, so make sure we do nothing if the bufid is not set. + * times, so make sure we do nothing if the buffer is not set. */ - if (bufid < 0) + if (buffer == InvalidBuffer) return false; - if (GetPrivateRefCount(bufid + 1) > 0) + if (GetPrivateRefCount(buffer) > 0) return true; return false; diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 9d6e69175a5..f973494abb1 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -239,7 +239,7 @@ ProcGlobalShmemInit(void *arg) dlist_init(&ProcGlobal->autovacFreeProcs); dlist_init(&ProcGlobal->bgworkerFreeProcs); dlist_init(&ProcGlobal->walsenderFreeProcs); - ProcGlobal->startupBufferPinWaitBufId = -1; + ProcGlobal->startupBufferPinWaitBuf = InvalidBuffer; pg_atomic_init_u32(&ProcGlobal->avLauncherProc, INVALID_PROC_NUMBER); pg_atomic_init_u32(&ProcGlobal->walwriterProc, INVALID_PROC_NUMBER); pg_atomic_init_u32(&ProcGlobal->checkpointerProc, INVALID_PROC_NUMBER); @@ -760,30 +760,30 @@ InitAuxiliaryProcess(void) /* * Used from bufmgr to share the value of the buffer that Startup waits on, - * or to reset the value to "not waiting" (-1). This allows processing - * of recovery conflicts for buffer pins. Set is made before backends look - * at this value, so locking not required, especially since the set is - * an atomic integer set operation. + * or to reset the value to "not waiting" (InvalidBuffer). This allows + * processing of recovery conflicts for buffer pins. Set is made before + * backends look at this value, so locking not required, especially since + * the set is an atomic integer set operation. */ void -SetStartupBufferPinWaitBufId(int bufid) +SetStartupBufferPinWaitBuf(Buffer buffer) { /* use volatile pointer to prevent code rearrangement */ volatile PROC_HDR *procglobal = ProcGlobal; - procglobal->startupBufferPinWaitBufId = bufid; + procglobal->startupBufferPinWaitBuf = buffer; } /* * Used by backends when they receive a request to check for buffer pin waits. */ -int -GetStartupBufferPinWaitBufId(void) +Buffer +GetStartupBufferPinWaitBuf(void) { /* use volatile pointer to prevent code rearrangement */ volatile PROC_HDR *procglobal = ProcGlobal; - return procglobal->startupBufferPinWaitBufId; + return procglobal->startupBufferPinWaitBuf; } /* diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 03a1a466fa8..4c3f431b4eb 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -17,6 +17,7 @@ #include "access/xlogdefs.h" #include "lib/ilist.h" #include "miscadmin.h" +#include "storage/buf.h" #include "storage/latch.h" #include "storage/lock.h" #include "storage/pg_sema.h" @@ -498,8 +499,8 @@ typedef struct PROC_HDR /* Current shared estimate of appropriate spins_per_delay value */ int spins_per_delay; - /* Buffer id of the buffer that Startup process waits for pin on, or -1 */ - int startupBufferPinWaitBufId; + /* Buffer that Startup process waits for pin on, or InvalidBuffer */ + Buffer startupBufferPinWaitBuf; } PROC_HDR; extern PGDLLIMPORT PROC_HDR *ProcGlobal; @@ -558,8 +559,8 @@ extern void InitProcess(void); extern void InitProcessPhase2(void); extern void InitAuxiliaryProcess(void); -extern void SetStartupBufferPinWaitBufId(int bufid); -extern int GetStartupBufferPinWaitBufId(void); +extern void SetStartupBufferPinWaitBuf(Buffer buffer); +extern Buffer GetStartupBufferPinWaitBuf(void); extern bool HaveNFreeProcs(int n, int *nfree); extern void ProcReleaseLocks(bool isCommit); -- 2.50.1 (Apple Git-155) --Xf36GNcW+0xZNily Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v2-0005-convert-PROC_HDR-startupBufferPinWaitBuf-to-an-at.patch ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-07-22 15:32 UTC | newest] Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/7] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/9] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2016-07-12 08:50 [PATCH 4/7] psql and pg_dump support for partitions. amit <amitlangote09@gmail.com> 2026-07-22 15:32 [PATCH v2 4/9] use Buffer instead of buffer ID for startup's buffer pin wait Nathan Bossart <nathan@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox