public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 2/7] psql and pg_dump support for partitioned tables.
14+ messages / 5 participants
[nested] [flat]
* [PATCH 2/7] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 68 +++++++++++--
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 302 insertions(+), 27 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index fecee85..60fe794 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1415,6 +1417,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9f59f53..6d5a9ed 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1228,9 +1228,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2087,6 +2088,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4961,7 +4965,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4975,7 +4979,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5503,7 +5508,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6902,6 +6909,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14311,6 +14359,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14371,7 +14422,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
- tbinfo->relkind == RELKIND_FOREIGN_TABLE))
+ tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
{
@@ -14389,7 +14441,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14606,6 +14659,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f3e5977..e9849ef 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -312,6 +312,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..bc44ac5 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -865,6 +865,7 @@ permissionsList(const char *pattern)
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
" WHEN 'f' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" END as \"%s\",\n"
" ",
gettext_noop("Schema"),
@@ -874,6 +875,7 @@ permissionsList(const char *pattern)
gettext_noop("materialized view"),
gettext_noop("sequence"),
gettext_noop("foreign table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("Type"));
printACLColumn(&buf, "c.relacl");
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2999,6 +3028,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" WHEN 'S' THEN '%s'"
" WHEN 's' THEN '%s'"
" WHEN 'f' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" END as \"%s\",\n"
" pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
gettext_noop("Schema"),
@@ -3010,6 +3040,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("sequence"),
gettext_noop("special"),
gettext_noop("foreign table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("Type"),
gettext_noop("Owner"));
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 047a1ce..96e77ec 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1979,6 +1979,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 410d96b..02e0720 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -417,7 +417,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | integer | | |
+ c | text | | not null |
+ d | text | | not null |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index b9489fc..2af3214 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -411,7 +411,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------96825311778D254EC2B71375
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-18.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-18.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/8] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..319531c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partitioning key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partitioning key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partitioning key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..c12c490 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partitioning key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4da297f..c393264 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1216,9 +1216,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2074,6 +2075,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4946,7 +4950,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4960,7 +4964,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5488,7 +5493,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6897,6 +6904,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partitioning key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partitioning key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14249,6 +14297,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14309,6 +14360,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14327,7 +14379,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14544,6 +14597,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index a60cf95..a75e79c 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -310,6 +310,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partitioning key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..6d44378 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partitioning key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partitioning key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..5e49475 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1977,6 +1977,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partitioning key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 0d70cc9..bec5655 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partitioning key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partitioning key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------294A99728041A5608B577083
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-11.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-11.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/8] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..9004878 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ee1f673..fb92e7f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1226,9 +1226,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2085,6 +2086,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4959,7 +4963,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4973,7 +4977,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5501,7 +5506,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6910,6 +6917,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14262,6 +14310,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14322,6 +14373,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14340,7 +14392,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14557,6 +14610,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 642c4d5..f33f86d 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -311,6 +311,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -649,5 +650,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..9b08bae 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..74d9447 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1977,6 +1977,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index e555076..0f15c98 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------30A9D66B3BEA4523792AC3CB
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-14.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-14.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/8] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..9004878 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9f59f53..d32d0ff 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1228,9 +1228,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2087,6 +2088,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4961,7 +4965,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4975,7 +4979,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5503,7 +5508,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6902,6 +6909,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14311,6 +14359,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14371,6 +14422,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14389,7 +14441,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14606,6 +14659,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f3e5977..e9849ef 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -312,6 +312,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..9b08bae 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..74d9447 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1977,6 +1977,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index e555076..0f15c98 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------38C0D572AC793CAA7276C062
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-16.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-16.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/7] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..9004878 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9f59f53..d32d0ff 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1228,9 +1228,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2087,6 +2088,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4961,7 +4965,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4975,7 +4979,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5503,7 +5508,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6902,6 +6909,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14311,6 +14359,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14371,6 +14422,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14389,7 +14441,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14606,6 +14659,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f3e5977..e9849ef 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -312,6 +312,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..9b08bae 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 047a1ce..96e77ec 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1979,6 +1979,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index e555076..0f15c98 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------F7EE68175C11124DB3D461AC
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-17.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-17.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/7] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 68 +++++++++++--
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 302 insertions(+), 27 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index fecee85..60fe794 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1415,6 +1417,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 2ff60b9..7e2a65d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1239,9 +1239,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2098,6 +2099,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4972,7 +4976,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4986,7 +4990,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5514,7 +5519,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6913,6 +6920,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14322,6 +14370,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14382,7 +14433,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
- tbinfo->relkind == RELKIND_FOREIGN_TABLE))
+ tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
{
@@ -14400,7 +14452,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14617,6 +14670,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f3e5977..e9849ef 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -312,6 +312,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..bc44ac5 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -865,6 +865,7 @@ permissionsList(const char *pattern)
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
" WHEN 'f' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" END as \"%s\",\n"
" ",
gettext_noop("Schema"),
@@ -874,6 +875,7 @@ permissionsList(const char *pattern)
gettext_noop("materialized view"),
gettext_noop("sequence"),
gettext_noop("foreign table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("Type"));
printACLColumn(&buf, "c.relacl");
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2999,6 +3028,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" WHEN 'S' THEN '%s'"
" WHEN 's' THEN '%s'"
" WHEN 'f' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" END as \"%s\",\n"
" pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
gettext_noop("Schema"),
@@ -3010,6 +3040,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("sequence"),
gettext_noop("special"),
gettext_noop("foreign table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("Type"),
gettext_noop("Owner"));
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6aa3f20..2765f4c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 047a1ce..96e77ec 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1979,6 +1979,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 410d96b..02e0720 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -417,7 +417,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | integer | | |
+ c | text | | not null |
+ d | text | | not null |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index b9489fc..2af3214 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -411,7 +411,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------AEEF86BC59A941B2E83AF069
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-19.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-19.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/8] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..9004878 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4da297f..c443735 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1216,9 +1216,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2074,6 +2075,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4946,7 +4950,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4960,7 +4964,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5488,7 +5493,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6897,6 +6904,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14249,6 +14297,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14309,6 +14360,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14327,7 +14379,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14544,6 +14597,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index a60cf95..527d6ad 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -310,6 +310,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..9b08bae 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..74d9447 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1977,6 +1977,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index e555076..0f15c98 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------B737FB31608A9E2BB1F071B4
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-12.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-12.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/8] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..9004878 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4da297f..c443735 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1216,9 +1216,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2074,6 +2075,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4946,7 +4950,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4960,7 +4964,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5488,7 +5493,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6897,6 +6904,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14249,6 +14297,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14309,6 +14360,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14327,7 +14379,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14544,6 +14597,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index a60cf95..527d6ad 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -310,6 +310,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -648,5 +649,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..9b08bae 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..74d9447 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1977,6 +1977,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index e555076..0f15c98 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------20C4AAD7DBE3F2B5281ABBA6
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-13.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-13.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/8] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 ++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 66 +++++++++++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 ++++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 301 insertions(+), 26 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a3a4174..9004878 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1412,6 +1414,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb987..3e20f02 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ee1f673..fb92e7f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1226,9 +1226,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2085,6 +2086,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4959,7 +4963,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -4973,7 +4977,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5501,7 +5506,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6910,6 +6917,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14262,6 +14310,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14322,6 +14373,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
@@ -14340,7 +14392,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14557,6 +14610,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 642c4d5..f33f86d 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -311,6 +311,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -649,5 +650,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1632104..9b08bae 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -861,6 +861,7 @@ permissionsList(const char *pattern)
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
@@ -870,6 +871,7 @@ permissionsList(const char *pattern)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
@@ -920,7 +922,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1566,8 +1568,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1632,6 +1634,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1645,8 +1655,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1667,12 +1677,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1748,7 +1758,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1756,14 +1766,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1902,7 +1931,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2461,7 +2490,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2665,7 +2694,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -2993,6 +3022,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" c.relname as \"%s\",\n"
" CASE c.relkind"
" WHEN 'r' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" WHEN 'v' THEN '%s'"
" WHEN 'm' THEN '%s'"
" WHEN 'i' THEN '%s'"
@@ -3004,6 +3034,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("table"),
+ gettext_noop("table"),
gettext_noop("view"),
gettext_noop("materialized view"),
gettext_noop("index"),
@@ -3048,7 +3079,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..9938695 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..74d9447 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1977,6 +1977,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132..7ed1623 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index e555076..0f15c98 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -407,7 +407,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e24ff3f..f100498 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -408,7 +408,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
1.7.1
--------------1C84B3F970C18697850BA485
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-15.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-15.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/7] psql and pg_dump support for partitioned tables.
@ 2016-07-12 08:20 amit <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: amit @ 2016-07-12 08:20 UTC (permalink / raw)
Takes care of both the partition key deparse stuff and the new relkind.
---
src/backend/utils/adt/ruleutils.c | 159 +++++++++++++++++++++++++++++
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_dump.c | 68 ++++++++++--
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 61 ++++++++---
src/bin/psql/tab-complete.c | 6 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_table.out | 20 +++-
src/test/regress/sql/create_table.sql | 6 +-
10 files changed, 302 insertions(+), 27 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index fecee85e5b..60fe794816 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -315,6 +316,7 @@ static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
bool attrsOnly, bool showTblSpc,
int prettyFlags, bool missing_ok);
+static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
@@ -1415,6 +1417,163 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
return buf.data;
}
+/*
+ * pg_get_partkeydef
+ *
+ * Returns the partition key specification, ie, the following:
+ *
+ * PARTITION BY { RANGE | LIST } (column opt_collation opt_opclass [, ...])
+ */
+Datum
+pg_get_partkeydef(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(string_to_text(pg_get_partkeydef_worker(relid,
+ PRETTYFLAG_INDENT)));
+}
+
+/*
+ * Internal workhorse to decompile a partition key definition.
+ */
+static char *
+pg_get_partkeydef_worker(Oid relid, int prettyFlags)
+{
+ Form_pg_partitioned_table form;
+ HeapTuple tuple;
+ oidvector *partclass;
+ oidvector *partcollation;
+ List *partexprs;
+ ListCell *partexpr_item;
+ List *context;
+ Datum datum;
+ bool isnull;
+ StringInfoData buf;
+ int keyno;
+ char *str;
+ char *sep;
+
+ tuple = SearchSysCache1(PARTRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for partition key of %u", relid);
+
+ form = (Form_pg_partitioned_table) GETSTRUCT(tuple);
+
+ Assert(form->partrelid == relid);
+
+ /* Must get partclass and partcollation the hard way */
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partclass, &isnull);
+ Assert(!isnull);
+ partclass = (oidvector *) DatumGetPointer(datum);
+
+ datum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partcollation, &isnull);
+ Assert(!isnull);
+ partcollation = (oidvector *) DatumGetPointer(datum);
+
+
+ /*
+ * Get the expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions, because we want to display non-const-folded
+ * expressions.)
+ */
+ if (!heap_attisnull(tuple, Anum_pg_partitioned_table_partexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(PARTRELID, tuple,
+ Anum_pg_partitioned_table_partexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ partexprs = (List *) stringToNode(exprsString);
+
+ if (!IsA(partexprs, List))
+ elog(ERROR, "unexpected node type found in partexprs: %d",
+ (int) nodeTag(partexprs));
+
+ pfree(exprsString);
+ }
+ else
+ partexprs = NIL;
+
+ partexpr_item = list_head(partexprs);
+ context = deparse_context_for(get_relation_name(relid), relid);
+
+ initStringInfo(&buf);
+
+ switch (form->partstrat)
+ {
+ case PARTITION_STRATEGY_LIST:
+ appendStringInfo(&buf, "LIST");
+ break;
+ case PARTITION_STRATEGY_RANGE:
+ appendStringInfo(&buf, "RANGE");
+ break;
+ default:
+ elog(ERROR, "unexpected partition strategy: %d",
+ (int) form->partstrat);
+ }
+
+ appendStringInfo(&buf, " (");
+ sep = "";
+ for (keyno = 0; keyno < form->partnatts; keyno++)
+ {
+ AttrNumber attnum = form->partattrs.values[keyno];
+ Oid keycoltype;
+ Oid keycolcollation;
+ Oid partcoll;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+ if (attnum != 0)
+ {
+ /* Simple attribute reference */
+ char *attname;
+ int32 keycoltypmod;
+
+ attname = get_relid_attribute_name(relid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ get_atttypetypmodcoll(relid, attnum,
+ &keycoltype, &keycoltypmod,
+ &keycolcollation);
+ }
+ else
+ {
+ /* Expression */
+ Node *partkey;
+
+ if (partexpr_item == NULL)
+ elog(ERROR, "too few entries in partexprs list");
+ partkey = (Node *) lfirst(partexpr_item);
+ partexpr_item = lnext(partexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(partkey, context, false, false,
+ 0, 0);
+
+ appendStringInfoString(&buf, str);
+ keycoltype = exprType(partkey);
+ keycolcollation = exprCollation(partkey);
+ }
+
+ /* Add collation, if not default for column */
+ partcoll = partcollation->values[keyno];
+ if (OidIsValid(partcoll) && partcoll != keycolcollation)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name((partcoll)));
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(partclass->values[keyno], keycoltype, &buf);
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /* Clean up */
+ ReleaseSysCache(tuple);
+
+ return buf.data;
+}
/*
* pg_get_constraintdef
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb9874f3..3e20f028c8 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -273,6 +273,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading policies\n");
getPolicies(fout, tblinfo, numTables);
+ if (g_verbose)
+ write_msg(NULL, "reading partition key information for interesting tables\n");
+ getTablePartitionKeyInfo(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 42873bb32a..2fb6d5dcc4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1239,9 +1239,10 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2098,6 +2099,9 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
return;
+ /* Skip partitioned tables (data in partitions) */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ return;
/* Don't dump data in unlogged tables, if so requested */
if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
@@ -4993,7 +4997,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -5007,7 +5011,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -5535,7 +5540,9 @@ getTables(Archive *fout, int *numTables)
* We only need to lock the table for certain components; see
* pg_dump.h
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ if (tblinfo[i].dobj.dump &&
+ (tblinfo[i].relkind == RELKIND_RELATION ||
+ tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
@@ -6934,6 +6941,47 @@ getTransforms(Archive *fout, int *numTransforms)
}
/*
+ * getTablePartitionKeyInfo -
+ * for each interesting partitioned table, read information about its
+ * partition key
+ *
+ * modifies tblinfo
+ */
+void
+getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ int i,
+ ntups;
+ PGresult *res;
+
+ /* No partitioned tables before 10 */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ for (i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &(tblinfo[i]);
+
+ /* Only partitioned tables have partition key */
+ if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /* Don't bother computing anything for non-target tables, either */
+ if (!tbinfo->dobj.dump)
+ continue;
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q, "SELECT pg_catalog.pg_get_partkeydef('%u'::pg_catalog.oid)",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ Assert(ntups == 1);
+ tbinfo->partkeydef = pg_strdup(PQgetvalue(res, 0, 0));
+ }
+}
+
+/*
* getTableAttrs -
* for each interesting table, read info about its attributes
* (names, types, default values, CHECK constraints, etc)
@@ -14343,6 +14391,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferChar(q, ')');
}
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "\nPARTITION BY %s", tbinfo->partkeydef);
+
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname));
}
@@ -14403,7 +14454,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*/
if (dopt->binary_upgrade &&
(tbinfo->relkind == RELKIND_RELATION ||
- tbinfo->relkind == RELKIND_FOREIGN_TABLE))
+ tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
{
@@ -14421,7 +14473,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION)
+ if (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
@@ -14638,6 +14691,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* dump properties we only have ALTER TABLE syntax for
*/
if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_PARTITIONED_TABLE ||
tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 7df9066cd7..f47e535c24 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -312,6 +312,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ char *partkeydef; /* partition key definition */
/*
* Stuff computed only for dumpable tables.
@@ -649,5 +650,6 @@ extern void processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
extern void getPolicies(Archive *fout, TableInfo tblinfo[], int numTables);
+extern void getTablePartitionKeyInfo(Archive *fout, TableInfo *tblinfo, int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 931c6887f9..baa5e859d7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -865,6 +865,7 @@ permissionsList(const char *pattern)
" WHEN 'm' THEN '%s'"
" WHEN 'S' THEN '%s'"
" WHEN 'f' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" END as \"%s\",\n"
" ",
gettext_noop("Schema"),
@@ -874,6 +875,7 @@ permissionsList(const char *pattern)
gettext_noop("materialized view"),
gettext_noop("sequence"),
gettext_noop("foreign table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("Type"));
printACLColumn(&buf, "c.relacl");
@@ -954,7 +956,7 @@ permissionsList(const char *pattern)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
- "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
+ "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f', 'P')\n");
/*
* Unless a schema pattern is specified, we suppress system and temp
@@ -1600,8 +1602,8 @@ describeOneTableDetails(const char *schemaname,
* types, and foreign tables (c.f. CommentObject() in comment.c).
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
}
@@ -1666,6 +1668,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
schemaname, relationname);
break;
+ case 'P':
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Table \"%s.%s\""),
+ schemaname, relationname);
+ break;
default:
/* untranslated unknown relkind */
printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
@@ -1679,8 +1689,8 @@ describeOneTableDetails(const char *schemaname,
cols = 2;
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'f' ||
+ tableinfo.relkind == 'c' || tableinfo.relkind == 'P')
{
headers[cols++] = gettext_noop("Collation");
headers[cols++] = gettext_noop("Nullable");
@@ -1701,12 +1711,12 @@ describeOneTableDetails(const char *schemaname,
{
headers[cols++] = gettext_noop("Storage");
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Stats target");
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
headers[cols++] = gettext_noop("Description");
}
@@ -1782,7 +1792,7 @@ describeOneTableDetails(const char *schemaname,
/* Statistics target, if the relkind supports this feature */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
false, false);
@@ -1790,14 +1800,33 @@ describeOneTableDetails(const char *schemaname,
/* Column comments, if the relkind supports this feature. */
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
- tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
+ tableinfo.relkind == 'm' || tableinfo.relkind == 'c' ||
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
false, false);
}
}
/* Make footers */
+ if (tableinfo.relkind == 'P')
+ {
+ /* Get the partition key information */
+ PGresult *result;
+ char *partkeydef;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result || PQntuples(result) != 1)
+ goto error_return;
+
+ partkeydef = PQgetvalue(result, 0, 0);
+ printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
+ printTableAddFooter(&cont, tmpbuf.data);
+ PQclear(result);
+ }
+
if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
@@ -1936,7 +1965,7 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
/* Footer information about a table */
PGresult *result = NULL;
@@ -2513,7 +2542,7 @@ describeOneTableDetails(const char *schemaname,
* Finish printing the footer information about a table.
*/
if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
- tableinfo.relkind == 'f')
+ tableinfo.relkind == 'f' || tableinfo.relkind == 'P')
{
PGresult *result;
int tuples;
@@ -2717,7 +2746,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline)
{
/* relkinds for which we support tablespaces */
- if (relkind == 'r' || relkind == 'm' || relkind == 'i')
+ if (relkind == 'r' || relkind == 'm' || relkind == 'i' || relkind == 'P')
{
/*
* We ignore the database default tablespace so that users not using
@@ -3051,6 +3080,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" WHEN 'S' THEN '%s'"
" WHEN 's' THEN '%s'"
" WHEN 'f' THEN '%s'"
+ " WHEN 'P' THEN '%s'"
" END as \"%s\",\n"
" pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
gettext_noop("Schema"),
@@ -3062,6 +3092,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("sequence"),
gettext_noop("special"),
gettext_noop("foreign table"),
+ gettext_noop("table"), /* partitioned table */
gettext_noop("Type"),
gettext_noop("Owner"));
@@ -3100,7 +3131,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
- appendPQExpBufferStr(&buf, "'r',");
+ appendPQExpBufferStr(&buf, "'r', 'P',");
if (showViews)
appendPQExpBufferStr(&buf, "'v',");
if (showMatViews)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6b95052a67..cd64c39b7f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -452,7 +452,7 @@ static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r')",
+ "c.relkind IN ('r', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -483,7 +483,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'f', 'v')",
+ "c.relkind IN ('r', 'f', 'v', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
@@ -513,7 +513,7 @@ static const SchemaQuery Query_for_list_of_tsvmf = {
/* catname */
"pg_catalog.pg_class c",
/* selcondition */
- "c.relkind IN ('r', 'S', 'v', 'm', 'f')",
+ "c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P')",
/* viscondition */
"pg_catalog.pg_table_is_visible(c.oid)",
/* namespace */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 047a1ce71c..96e77ec437 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1979,6 +1979,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("role name by OID (with fallback)");
DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_indexdef _null_ _null_ _null_ ));
DESCR("index description");
+DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
+DESCR("partition key description");
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
DESCR("trigger description");
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 90f5132b03..7ed162322c 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -728,6 +728,7 @@ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
+extern Datum pg_get_partkeydef(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef(PG_FUNCTION_ARGS);
extern Datum pg_get_triggerdef_ext(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 410d96b0b6..02e0720eec 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -417,7 +417,25 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | integer | | |
+ c | text | | not null |
+ d | text | | not null |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US")
+
+\d partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST ((a + 1))
+
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index b9489fc171..2af3214b19 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -411,7 +411,11 @@ DROP FUNCTION plusone(int);
-- partitioned table cannot partiticipate in regular inheritance
CREATE TABLE partitioned2 (
a int
-) PARTITION BY RANGE (a);
+) PARTITION BY LIST ((a+1));
CREATE TABLE fail () INHERITS (partitioned2);
+-- Partition key in describe output
+\d partitioned
+\d partitioned2
+
DROP TABLE partitioned, partitioned2;
--
2.11.0
--------------E1933196A000B12EC95A8B6E
Content-Type: text/x-diff;
name="0003-Catalog-and-DDL-for-partitions-20.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Catalog-and-DDL-for-partitions-20.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 08/10] Default to zstd..
@ 2021-03-12 21:35 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Justin Pryzby @ 2021-03-12 21:35 UTC (permalink / raw)
for CI, not for merge
---
configure | 6 ++++--
configure.ac | 2 +-
src/backend/access/transam/xlog.c | 2 +-
src/backend/utils/misc/guc.c | 2 +-
4 files changed, 7 insertions(+), 5 deletions(-)
diff --git a/configure b/configure
index 81e23418b2..253f028fc4 100755
--- a/configure
+++ b/configure
@@ -1582,7 +1582,7 @@ Optional Packages:
use system time zone data in DIR
--without-zlib do not use Zlib
--without-lz4 build without LZ4 support
- --with-zstd build with Zstd compression library
+ --without-zstd build without Zstd compression library
--with-gnu-ld assume the C compiler uses GNU ld [default=no]
--with-ssl=LIB use LIB for SSL/TLS support (openssl)
--with-openssl obsolete spelling of --with-ssl=openssl
@@ -8740,7 +8740,9 @@ $as_echo "#define USE_ZSTD 1" >>confdefs.h
esac
else
- with_zstd=no
+ with_zstd=yes
+
+$as_echo "#define USE_ZSTD 1" >>confdefs.h
fi
diff --git a/configure.ac b/configure.ac
index d6f6349067..8d72710fa7 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1005,7 +1005,7 @@ fi
# ZSTD
#
AC_MSG_CHECKING([whether to build with zstd support])
-PGAC_ARG_BOOL(with, zstd, no, [build with Zstd compression library],
+PGAC_ARG_BOOL(with, zstd, yes, [build without Zstd compression library],
[AC_DEFINE([USE_ZSTD], 1, [Define to 1 to build with zstd support. (--with-zstd)])])
AC_MSG_RESULT([$with_zstd])
AC_SUBST(with_zstd)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 307eee6626..92023de9f5 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -99,7 +99,7 @@ bool EnableHotStandby = false;
bool fullPageWrites = true;
bool wal_log_hints = false;
bool wal_compression = false;
-int wal_compression_method = WAL_COMPRESSION_LZ4;
+int wal_compression_method = WAL_COMPRESSION_ZSTD;
char *wal_consistency_checking_string = NULL;
bool *wal_consistency_checking = NULL;
bool wal_init_zero = true;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 52f9cd0242..8031e027aa 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -4728,7 +4728,7 @@ static struct config_enum ConfigureNamesEnum[] =
NULL
},
&wal_compression_method,
- WAL_COMPRESSION_LZ4, wal_compression_options,
+ WAL_COMPRESSION_ZSTD, wal_compression_options,
NULL, NULL, NULL
},
--
2.17.0
--jozmn01XJZjDjM3N
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0009-Add-zstd-compression-levels.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* Follow-up on OpenSSL "engines" and "providers"
@ 2026-01-29 17:15 Bear Giles <[email protected]>
2026-02-13 11:42 ` Re: Follow-up on OpenSSL "engines" and "providers" Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Bear Giles @ 2026-01-29 17:15 UTC (permalink / raw)
To: pgsql-hackers
--000000000000f3d50906498a0174
Content-Type: text/plain; charset="UTF-8"
I've been doing a lot of thought about how my prior and current work could
be useful, not just intellectual exercises that might be useful to some
people, and keep coming back to the idea that my focus should probably be
other server-side extensions and not the SQL user. They won't be excluded -
far from it - but the emphasis should be providing an open source solution
that can handle much of the encryption and authentication required by other
extensions.
Most importantly - giving them a way to avoid knowing their private keys.
They can't expose what they don't have.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Follow-up on OpenSSL "engines" and "providers"
2026-01-29 17:15 Follow-up on OpenSSL "engines" and "providers" Bear Giles <[email protected]>
@ 2026-02-13 11:42 ` Daniel Gustafsson <[email protected]>
2026-02-16 11:12 ` Re: Follow-up on OpenSSL "engines" and "providers" =?UTF-8?Q?Anders_=C3=85strand?= <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Gustafsson @ 2026-02-13 11:42 UTC (permalink / raw)
To: Bear Giles <[email protected]>; +Cc: pgsql-hackers
> On 29 Jan 2026, at 18:15, Bear Giles <[email protected]> wrote:
> Most importantly - giving them a way to avoid knowing their private keys. They can't expose what they don't have.
FWIW there has been discussion among those of us who regularly dip our toes in
the OpenSSL support code to add some form of integration with vaults (like
vault from Hashicorp, ipa/idm from Redhat, Keychain from Apple etc) for storing
secrets. AFAIK there are no concrete patches to look at (yet?), but there is
interest and it will most likely be discussed at PGConf.dev in case you are
thinking of attending.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Follow-up on OpenSSL "engines" and "providers"
2026-01-29 17:15 Follow-up on OpenSSL "engines" and "providers" Bear Giles <[email protected]>
2026-02-13 11:42 ` Re: Follow-up on OpenSSL "engines" and "providers" Daniel Gustafsson <[email protected]>
@ 2026-02-16 11:12 ` =?UTF-8?Q?Anders_=C3=85strand?= <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: =?UTF-8?Q?Anders_=C3=85strand?= @ 2026-02-16 11:12 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; Bear Giles <[email protected]>; +Cc: pgsql-hackers
> FWIW there has been discussion among those of us who regularly dip our toes in
> the OpenSSL support code to add some form of integration with vaults (like
> vault from Hashicorp, ipa/idm from Redhat, Keychain from Apple etc) for storing
> secrets. AFAIK there are no concrete patches to look at (yet?), but there is
> interest and it will most likely be discussed at PGConf.dev in case you are
> thinking of attending.
I've been toying with the idea of building a key manager extension that could be used by others to get hold of secrets or use encryption keys without necessarily having to know where those secrets would be stored. It could use loadable shared libraries for the different providers similar to how we do it for OAuth.
I don't believe this code would have to start out in core at all, but maybe we will want to integrate it later for reasons.
--
Anders Åstrand
Percona
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2026-02-16 11:12 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-07-12 08:20 [PATCH 2/7] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/8] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/8] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/8] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/7] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/7] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/8] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/8] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/8] psql and pg_dump support for partitioned tables. amit <[email protected]>
2016-07-12 08:20 [PATCH 2/7] psql and pg_dump support for partitioned tables. amit <[email protected]>
2021-03-12 21:35 [PATCH 08/10] Default to zstd.. Justin Pryzby <[email protected]>
2026-01-29 17:15 Follow-up on OpenSSL "engines" and "providers" Bear Giles <[email protected]>
2026-02-13 11:42 ` Re: Follow-up on OpenSSL "engines" and "providers" Daniel Gustafsson <[email protected]>
2026-02-16 11:12 ` Re: Follow-up on OpenSSL "engines" and "providers" =?UTF-8?Q?Anders_=C3=85strand?= <[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