agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH 4/7] psql and pg_dump support for partitions.
12+ messages / 3 participants
[nested] [flat]
* [PATCH 4/7] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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] 12+ messages in thread
* [PATCH 4/7] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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 60fe794..4e2ba19 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 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 6d5a9ed..5c85b8b 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 bc44ac5..def1d50 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 783143d..d504b05 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 8696a85..ab77f99 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;
--
1.7.1
--------------96825311778D254EC2B71375
Content-Type: text/x-diff;
name="0005-Teach-a-few-places-to-use-partition-check-quals-18.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-18.patc";
filename*1="h"
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH 4/7] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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 60fe794..4e2ba19 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 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 7e2a65d..281ab4f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5621,9 +5621,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);
@@ -5650,6 +5657,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
*
@@ -14228,6 +14299,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 */
@@ -14256,8 +14338,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;
@@ -14286,7 +14371,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");
}
@@ -14344,15 +14430,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 (");
@@ -14522,6 +14615,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 bc44ac5..def1d50 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 c3afca6..b40a18a 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 7818bc0..69848e3 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;
--
1.7.1
--------------AEEF86BC59A941B2E83AF069
Content-Type: text/x-diff;
name="0005-Teach-a-few-places-to-use-partition-check-quals-19.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-19.patc";
filename*1="h"
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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 c443735..9deb035 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5595,9 +5595,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);
@@ -5624,6 +5631,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
*
@@ -14155,6 +14226,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 */
@@ -14183,8 +14265,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;
@@ -14213,7 +14298,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");
}
@@ -14271,15 +14357,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 (");
@@ -14449,6 +14542,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 527d6ad..3f3c777 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;
@@ -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
--------------B737FB31608A9E2BB1F071B4
Content-Type: text/x-diff;
name="0005-Teach-a-few-places-to-use-partition-check-quals-12.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-12.patc";
filename*1="h"
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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 | 86 ++++++++++++++++++++
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, 419 insertions(+), 20 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 319531c..5662bad 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8447,6 +8447,92 @@ 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, " START");
+ 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, ")");
+ if (!spec->lowerinc)
+ appendStringInfoString(buf, " EXCLUSIVE");
+
+ appendStringInfoString(buf, " END");
+ 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, ")");
+ if (spec->upperinc)
+ appendStringInfoString(buf, " INCLUSIVE");
+ 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 c12c490..b1c4457 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 c393264..834e50d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5595,9 +5595,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);
@@ -5624,6 +5631,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
*
@@ -14155,6 +14226,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 */
@@ -14183,8 +14265,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;
@@ -14213,7 +14298,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");
}
@@ -14271,15 +14357,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 (");
@@ -14449,6 +14542,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 a75e79c..fc0a345 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;
@@ -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 6d44378..ca8ff5f 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 partitioning 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 fbdfaea..829d05e 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -592,6 +592,46 @@ ERROR: column "c" named in partitioning 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 START (1) END (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')
+Partitioning 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
+Partitioning 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 1d03720..4c0deb6 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 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
+
-- partitions cannot be dropped directly
DROP TABLE part_a;
--
1.7.1
--------------294A99728041A5608B577083
Content-Type: text/x-diff;
name="0005-Teach-a-few-places-to-use-partition-check-quals-11.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-11.patc";
filename*1="h"
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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 c443735..9deb035 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5595,9 +5595,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);
@@ -5624,6 +5631,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
*
@@ -14155,6 +14226,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 */
@@ -14183,8 +14265,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;
@@ -14213,7 +14298,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");
}
@@ -14271,15 +14357,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 (");
@@ -14449,6 +14542,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 527d6ad..3f3c777 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;
@@ -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
--------------20C4AAD7DBE3F2B5281ABBA6
Content-Type: text/x-diff;
name="0005-Teach-a-few-places-to-use-partition-check-quals-13.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-13.patc";
filename*1="h"
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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
--------------30A9D66B3BEA4523792AC3CB
Content-Type: text/x-diff;
name="0005-Teach-a-few-places-to-use-partition-check-quals-14.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0005-Teach-a-few-places-to-use-partition-check-quals-14.patc";
filename*1="h"
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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] 12+ messages in thread
* [PATCH 4/8] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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] 12+ messages in thread
* [PATCH 4/7] psql and pg_dump support for partitions.
@ 2016-07-12 08:50 amit <[email protected]>
0 siblings, 0 replies; 12+ 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] 12+ messages in thread
* [PATCH 3/6] Optimize allocations in bringetbitmap
@ 2020-09-13 10:12 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Tomas Vondra @ 2020-09-13 10:12 UTC (permalink / raw)
The bringetbitmap function allocates memory for various purposes, which
may be quite expensive, depending on the number of scan keys. Instead of
allocating them separately, allocate one bit chunk of memory an carve it
into smaller pieces as needed - all the pieces have the same lifespan,
and it saves quite a bit of CPU and memory overhead.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/access/brin/brin.c | 62 +++++++++++++++++++++++++++-------
1 file changed, 49 insertions(+), 13 deletions(-)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 55851376d8..8d23f2864b 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -372,6 +372,9 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
int *nkeys,
*nnullkeys;
int keyno;
+ char *ptr;
+ Size len;
+ char *tmp PG_USED_FOR_ASSERTS_ONLY;
opaque = (BrinOpaque *) scan->opaque;
bdesc = opaque->bo_bdesc;
@@ -397,11 +400,50 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
* Make room for per-attribute lists of scan keys that we'll pass to the
* consistent support procedure. We keep null and regular keys separate,
* so that we can easily pass regular keys to the consistent function.
+ *
+ * To reduce the allocation overhead, we allocate one big chunk and then
+ * carve it into smaller arrays ourselves. All the pieces have exactly
+ * the same lifetime, so that's OK.
*/
- keys = palloc0(sizeof(ScanKey *) * bdesc->bd_tupdesc->natts);
- nullkeys = palloc0(sizeof(ScanKey *) * bdesc->bd_tupdesc->natts);
- nkeys = palloc0(sizeof(int) * bdesc->bd_tupdesc->natts);
- nnullkeys = palloc0(sizeof(int) * bdesc->bd_tupdesc->natts);
+ len =
+ /* regular keys */
+ MAXALIGN(sizeof(ScanKey *) * bdesc->bd_tupdesc->natts) +
+ MAXALIGN(sizeof(ScanKey) * scan->numberOfKeys * bdesc->bd_tupdesc->natts) +
+ MAXALIGN(sizeof(int) * bdesc->bd_tupdesc->natts) +
+ /* NULL keys */
+ MAXALIGN(sizeof(ScanKey *) * bdesc->bd_tupdesc->natts) +
+ MAXALIGN(sizeof(ScanKey) * scan->numberOfKeys * bdesc->bd_tupdesc->natts) +
+ MAXALIGN(sizeof(int) * bdesc->bd_tupdesc->natts);
+
+ ptr = palloc(len);
+ tmp = ptr;
+
+ keys = (ScanKey **) ptr;
+ ptr += MAXALIGN(sizeof(ScanKey *) * bdesc->bd_tupdesc->natts);
+
+ nullkeys = (ScanKey **) ptr;
+ ptr += MAXALIGN(sizeof(ScanKey *) * bdesc->bd_tupdesc->natts);
+
+ nkeys = (int *) ptr;
+ ptr += MAXALIGN(sizeof(int) * bdesc->bd_tupdesc->natts);
+
+ nnullkeys = (int *) ptr;
+ ptr += MAXALIGN(sizeof(int) * bdesc->bd_tupdesc->natts);
+
+ for (int i = 0; i < bdesc->bd_tupdesc->natts; i++)
+ {
+ keys[i] = (ScanKey *) ptr;
+ ptr += MAXALIGN(sizeof(ScanKey) * scan->numberOfKeys);
+
+ nullkeys[i] = (ScanKey *) ptr;
+ ptr += MAXALIGN(sizeof(ScanKey) * scan->numberOfKeys);
+ }
+
+ Assert(tmp + len == ptr);
+
+ /* zero the number of keys */
+ memset(nkeys, 0, sizeof(int) * bdesc->bd_tupdesc->natts);
+ memset(nnullkeys, 0, sizeof(int) * bdesc->bd_tupdesc->natts);
/*
* Preprocess the scan keys - split them into per-attribute arrays.
@@ -437,9 +479,9 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
{
FmgrInfo *tmp;
- /* No key/null arrays for this attribute. */
- Assert((keys[keyattno - 1] == NULL) && (nkeys[keyattno - 1] == 0));
- Assert((nullkeys[keyattno - 1] == NULL) && (nnullkeys[keyattno - 1] == 0));
+ /* First time we see this attribute, so no key/null keys. */
+ Assert(nkeys[keyattno - 1] == 0);
+ Assert(nnullkeys[keyattno - 1] == 0);
tmp = index_getprocinfo(idxRel, keyattno,
BRIN_PROCNUM_CONSISTENT);
@@ -450,17 +492,11 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
/* Add key to the proper per-attribute array. */
if (key->sk_flags & SK_ISNULL)
{
- if (!nullkeys[keyattno - 1])
- nullkeys[keyattno - 1] = palloc0(sizeof(ScanKey) * scan->numberOfKeys);
-
nullkeys[keyattno - 1][nnullkeys[keyattno - 1]] = key;
nnullkeys[keyattno - 1]++;
}
else
{
- if (!keys[keyattno - 1])
- keys[keyattno - 1] = palloc0(sizeof(ScanKey) * scan->numberOfKeys);
-
keys[keyattno - 1][nkeys[keyattno - 1]] = key;
nkeys[keyattno - 1]++;
}
--
2.26.2
--------------D21F32E0F9E7408E9BE1AF2E
Content-Type: text/x-patch; charset=UTF-8;
name="0004-BRIN-bloom-indexes-20210114.patch"
Content-Transfer-Encoding: 8bit
Content-Disposition: attachment;
filename="0004-BRIN-bloom-indexes-20210114.patch"
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Column Filtering in Logical Replication
@ 2022-01-11 00:28 Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Alvaro Herrera @ 2022-01-11 00:28 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Tomas Vondra <[email protected]>; Amit Kapila <[email protected]>; [email protected] <[email protected]>; Rahila Syed <[email protected]>; Peter Smith <[email protected]>; pgsql-hackers
On 2022-Jan-07, Peter Eisentraut wrote:
> ATExecReplicaIdentity(): Regarding the question of how to handle
> REPLICA_IDENTITY_NOTHING: I see two ways to do this. Right now, the
> approach is that the user can set the replica identity freely, and we
> decide later based on that what we can replicate (e.g., no updates).
> For this patch, that would mean we don't restrict what columns can be
> in the column list, but we check what actions we can replicate based
> on the column list. The alternative is that we require the column
> list to include the replica identity, as the patch is currently doing,
> which would mean that REPLICA_IDENTITY_NOTHING can be allowed since
> it's essentially a set of zero columns.
>
> I find the current behavior a bit weird on reflection. If a user
> wants to replicate on some columns and only INSERTs, that should be
> allowed regardless of what the replica identity columns are.
Hmm. So you're saying that we should only raise errors about the column
list if we are publishing UPDATE or DELETE, but otherwise let the
replica identity be anything. OK, I'll see if I can come up with a
reasonable set of rules ...
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2022-01-11 00:28 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-07-12 08:50 [PATCH 4/7] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/7] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/7] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/7] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <[email protected]>
2016-07-12 08:50 [PATCH 4/8] psql and pg_dump support for partitions. amit <[email protected]>
2020-09-13 10:12 [PATCH 3/6] Optimize allocations in bringetbitmap Tomas Vondra <[email protected]>
2022-01-11 00:28 Re: Column Filtering in Logical Replication Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox