agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH 2/7] psql and pg_dump support for partitioned tables. 13+ messages / 4 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ 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; 13+ 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] 13+ messages in thread
* [PATCH v7 2/3] Remove "dead" flag from catcache tuple @ 2020-11-18 07:57 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Kyotaro Horiguchi @ 2020-11-18 07:57 UTC (permalink / raw) --- src/backend/utils/cache/catcache.c | 43 +++++++++++++----------------- src/include/utils/catcache.h | 10 ------- 2 files changed, 18 insertions(+), 35 deletions(-) diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c index 644d92dd9a..611b65168d 100644 --- a/src/backend/utils/cache/catcache.c +++ b/src/backend/utils/cache/catcache.c @@ -480,6 +480,13 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct) Assert(ct->refcount == 0); Assert(ct->my_cache == cache); + /* delink from linked list if not yet */ + if (ct->cache_elem.prev) + { + dlist_delete(&ct->cache_elem); + ct->cache_elem.prev = NULL; + } + if (ct->c_list) { /* @@ -487,14 +494,10 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct) * which will recurse back to me, and the recursive call will do the * work. Set the "dead" flag to make sure it does recurse. */ - ct->dead = true; CatCacheRemoveCList(cache, ct->c_list); return; /* nothing left to do */ } - /* delink from linked list */ - dlist_delete(&ct->cache_elem); - /* * Free keys when we're dealing with a negative entry, normal entries just * point into tuple, allocated together with the CatCTup. @@ -534,7 +537,7 @@ CatCacheRemoveCList(CatCache *cache, CatCList *cl) /* if the member is dead and now has no references, remove it */ if ( #ifndef CATCACHE_FORCE_RELEASE - ct->dead && + ct->cache_elem.prev == NULL && #endif ct->refcount == 0) CatCacheRemoveCTup(cache, ct); @@ -609,7 +612,9 @@ CatCacheInvalidate(CatCache *cache, uint32 hashValue) if (ct->refcount > 0 || (ct->c_list && ct->c_list->refcount > 0)) { - ct->dead = true; + dlist_delete(&ct->cache_elem); + ct->cache_elem.prev = NULL; + /* list, if any, was marked dead above */ Assert(ct->c_list == NULL || ct->c_list->dead); } @@ -688,7 +693,8 @@ ResetCatalogCache(CatCache *cache) if (ct->refcount > 0 || (ct->c_list && ct->c_list->refcount > 0)) { - ct->dead = true; + dlist_delete(&ct->cache_elem); + ct->cache_elem.prev = NULL; /* list, if any, was marked dead above */ Assert(ct->c_list == NULL || ct->c_list->dead); } @@ -1268,9 +1274,6 @@ SearchCatCacheInternal(CatCache *cache, { ct = dlist_container(CatCTup, cache_elem, iter.cur); - if (ct->dead) - continue; /* ignore dead entries */ - if (ct->hash_value != hashValue) continue; /* quickly skip entry if wrong hash val */ @@ -1522,7 +1525,6 @@ ReleaseCatCache(HeapTuple tuple) offsetof(CatCTup, tuple)); /* Safety checks to ensure we were handed a cache entry */ - Assert(ct->ct_magic == CT_MAGIC); Assert(ct->refcount > 0); ct->refcount--; @@ -1530,7 +1532,7 @@ ReleaseCatCache(HeapTuple tuple) if ( #ifndef CATCACHE_FORCE_RELEASE - ct->dead && + ct->cache_elem.prev == NULL && #endif ct->refcount == 0 && (ct->c_list == NULL || ct->c_list->refcount == 0)) @@ -1737,8 +1739,8 @@ SearchCatCacheList(CatCache *cache, { ct = dlist_container(CatCTup, cache_elem, iter.cur); - if (ct->dead || ct->negative) - continue; /* ignore dead and negative entries */ + if (ct->negative) + continue; /* ignore negative entries */ if (ct->hash_value != hashValue) continue; /* quickly skip entry if wrong hash val */ @@ -1799,14 +1801,13 @@ SearchCatCacheList(CatCache *cache, { foreach(ctlist_item, ctlist) { + Assert (ct->cache_elem.prev != NULL); + ct = (CatCTup *) lfirst(ctlist_item); Assert(ct->c_list == NULL); Assert(ct->refcount > 0); ct->refcount--; if ( -#ifndef CATCACHE_FORCE_RELEASE - ct->dead && -#endif ct->refcount == 0 && (ct->c_list == NULL || ct->c_list->refcount == 0)) CatCacheRemoveCTup(cache, ct); @@ -1834,9 +1835,6 @@ SearchCatCacheList(CatCache *cache, /* release the temporary refcount on the member */ Assert(ct->refcount > 0); ct->refcount--; - /* mark list dead if any members already dead */ - if (ct->dead) - cl->dead = true; } Assert(i == nmembers); @@ -1960,11 +1958,9 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments, * Finish initializing the CatCTup header, and add it to the cache's * linked list and counts. */ - ct->ct_magic = CT_MAGIC; ct->my_cache = cache; ct->c_list = NULL; ct->refcount = 0; /* for the moment */ - ct->dead = false; ct->negative = negative; ct->hash_value = hashValue; ct->lastaccess = catcacheclock; @@ -2158,9 +2154,6 @@ PrintCatCacheLeakWarning(HeapTuple tuple) CatCTup *ct = (CatCTup *) (((char *) tuple) - offsetof(CatCTup, tuple)); - /* Safety check to ensure we were handed a cache entry */ - Assert(ct->ct_magic == CT_MAGIC); - elog(WARNING, "cache reference leak: cache %s (%d), tuple %u/%u has count %d", ct->my_cache->cc_relname, ct->my_cache->id, ItemPointerGetBlockNumber(&(tuple->t_self)), diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h index 291e857e38..53b0bf31eb 100644 --- a/src/include/utils/catcache.h +++ b/src/include/utils/catcache.h @@ -87,9 +87,6 @@ typedef struct catcache typedef struct catctup { - int ct_magic; /* for identifying CatCTup entries */ -#define CT_MAGIC 0x57261502 - uint32 hash_value; /* hash value for this tuple's keys */ /* @@ -106,19 +103,12 @@ typedef struct catctup dlist_node cache_elem; /* list member of per-bucket list */ /* - * A tuple marked "dead" must not be returned by subsequent searches. - * However, it won't be physically deleted from the cache until its - * refcount goes to zero. (If it's a member of a CatCList, the list's - * refcount must go to zero, too; also, remember to mark the list dead at - * the same time the tuple is marked.) - * * A negative cache entry is an assertion that there is no tuple matching * a particular key. This is just as useful as a normal entry so far as * avoiding catalog searches is concerned. Management of positive and * negative entries is identical. */ int refcount; /* number of active references */ - bool dead; /* dead but not yet removed? */ bool negative; /* negative cache entry? */ HeapTupleData tuple; /* tuple management header */ uint64 lastaccess; /* timestamp in us of the last usage */ -- 2.27.0 ----Next_Part(Wed_Jan_27_10_13_08_2021_482)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v7-0003-catcachebench.patch" ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Idea Feedback: psql \h misses -> Offers Links? @ 2024-05-02 17:50 Andrey M. Borodin <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Andrey M. Borodin @ 2024-05-02 17:50 UTC (permalink / raw) To: Kirk Wolak <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]> > On 17 Apr 2024, at 22:47, Kirk Wolak <[email protected]> wrote: > > Thoughts? Today we had a hacking session with Nik and Kirk. We produced a patch to assess how these links might look like. Also we needed a url_encode() and found none in a codebase. It would be nice to have this as an SQL-callable function. Thanks! Best regards, Andrey Borodin. Attachments: [application/octet-stream] v1-0001-Add-URLs-to-h-in-psql-when-there-is-no-match.patch (4.4K, ../../[email protected]/2-v1-0001-Add-URLs-to-h-in-psql-when-there-is-no-match.patch) download | inline diff: From 199b87deb39c393562d96dec56c565e25ae76edc Mon Sep 17 00:00:00 2001 From: "Andrey M. Borodin" <[email protected]> Date: Thu, 2 May 2024 22:28:15 +0500 Subject: [PATCH v1] Add URLs to \h in psql when there is no match --- src/bin/psql/help.c | 44 ++++++++++++++++++++++++++++++++++++++++- src/bin/psql/settings.h | 3 +++ src/bin/psql/startup.c | 11 +++++++++++ 3 files changed, 57 insertions(+), 1 deletion(-) diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 4e79a819d8..703597289e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -422,6 +422,8 @@ helpVariables(unsigned short int pager) " specifies the prompt used when a statement continues from a previous line\n"); HELP0(" PROMPT3\n" " specifies the prompt used during COPY ... FROM STDIN\n"); + HELP0(" HELP_URLS\n" + " specifies |-separated array of URLs with \%s\n"); HELP0(" QUIET\n" " run quietly (same as -q option)\n"); HELP0(" ROW_COUNT\n" @@ -558,6 +560,30 @@ helpVariables(unsigned short int pager) } +static char *url_encode(const char *input) { + /* Allocate enough space for the encoded string */ + char *output = calloc(strlen(input) * 3 + 1, sizeof(char)); + int output_pos = 0; + + for (int i = 0; input[i]; i++) { + unsigned char c = input[i]; + if (c == ' ') { + output[output_pos++] = '%'; + output[output_pos++] = '2'; + output[output_pos++] = '0'; + } else if ((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || (c >= '0' && c <= '9') || c == '-' || c == '_' || c == '.' || c == '~') { + output[output_pos++] = c; + } else { + output[output_pos++] = '%'; + output[output_pos++] = "0123456789ABCDEF"[c >> 4]; + output[output_pos++] = "0123456789ABCDEF"[c & 0xF]; + } + } + + return output; +} + + /* * helpSQL -- help with SQL commands * @@ -713,10 +739,26 @@ helpSQL(const char *topic, unsigned short int pager) /* If we never found anything, report that */ if (!output) { + char* urls; + char* url; + char* encoded_topic; output = PageOutput(2, pager ? &(pset.popt.topt) : NULL); fprintf(output, _("No help available for \"%s\".\n" - "Try \\h with no arguments to see available help.\n"), + "Try \\h with no arguments to see available help or search online:\n"), topic); + /* TODO: URL encode topic*/ + encoded_topic = url_encode(topic); + urls = pstrdup(pset.help_urls); + url = strtok(urls,"|"); + while (url != NULL) + { + fprintf(output, "\nURL: "); + fprintf(output, url, encoded_topic); + fprintf(output, "\n"); + url = strtok(NULL, "|"); + } + fprintf(output, "\n"); + free(encoded_topic); } ClosePager(output); diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h index 505f99d8e4..0be1300738 100644 --- a/src/bin/psql/settings.h +++ b/src/bin/psql/settings.h @@ -27,6 +27,8 @@ #define DEFAULT_PROMPT2 "%/%R%x%# " #define DEFAULT_PROMPT3 ">> " +#define DEFAULT_HELP_URLS "https://www.postgresql.org/search/?q=%s" + /* * Note: these enums should generally be chosen so that zero corresponds * to the default behavior. @@ -151,6 +153,7 @@ typedef struct _psqlSettings const char *prompt1; const char *prompt2; const char *prompt3; + const char *help_urls; PGVerbosity verbosity; /* current error verbosity level */ bool show_all_results; PGContextVisibility show_context; /* current context display level */ diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c index 036caaec2f..824297fd57 100644 --- a/src/bin/psql/startup.c +++ b/src/bin/psql/startup.c @@ -203,6 +203,7 @@ main(int argc, char *argv[]) SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1); SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2); SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3); + SetVariable(pset.vars, "HELP_URLS", DEFAULT_HELP_URLS); SetVariableBool(pset.vars, "SHOW_ALL_RESULTS"); parse_psql_options(argc, argv, &options); @@ -1115,6 +1116,13 @@ prompt3_hook(const char *newval) return true; } +static bool +help_urls_hook(const char *newval) +{ + pset.help_urls = newval ? newval : ""; + return true; +} + static char * verbosity_substitute_hook(char *newval) { @@ -1250,6 +1258,9 @@ EstablishVariableSpace(void) SetVariableHooks(pset.vars, "PROMPT3", NULL, prompt3_hook); + SetVariableHooks(pset.vars, "HELP_URLS", + NULL, + help_urls_hook); SetVariableHooks(pset.vars, "VERBOSITY", verbosity_substitute_hook, verbosity_hook); -- 2.37.1 (Apple Git-137.1) ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Idea Feedback: psql \h misses -> Offers Links? @ 2024-05-02 18:24 Pavel Stehule <[email protected]> parent: Andrey M. Borodin <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Pavel Stehule @ 2024-05-02 18:24 UTC (permalink / raw) To: Andrey M. Borodin <[email protected]>; +Cc: Kirk Wolak <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]> čt 2. 5. 2024 v 19:50 odesílatel Andrey M. Borodin <[email protected]> napsal: > > > > On 17 Apr 2024, at 22:47, Kirk Wolak <[email protected]> wrote: > > > > Thoughts? > > Today we had a hacking session with Nik and Kirk. We produced a patch to > assess how these links might look like. > > Also we needed a url_encode() and found none in a codebase. It would be > nice to have this as an SQL-callable function. > +1 it was requested more times Pavel > Thanks! > > > Best regards, Andrey Borodin. > > ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-05-02 18:24 UTC | newest] Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-07-12 08: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]> 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]> 2020-11-18 07:57 [PATCH v7 2/3] Remove "dead" flag from catcache tuple Kyotaro Horiguchi <[email protected]> 2024-05-02 17:50 Re: Idea Feedback: psql \h misses -> Offers Links? Andrey M. Borodin <[email protected]> 2024-05-02 18:24 ` Re: Idea Feedback: psql \h misses -> Offers Links? Pavel Stehule <[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