Hi Ășt 30. 10. 2018 v 7:52 odesĂlatel Amit Langote < langote_amit...@lab.ntt.co.jp> napsal:
> Hi Mathias, Pavel, > > On 2018/08/17 12:26, Mathias Brossard wrote: > > On Thu, Aug 16, 2018 at 12:46 AM Pavel Stehule <pavel.steh...@gmail.com> > >> > >> This is question - maybe we can support older partitioning based on only > >> inheritance - and the query can be more exact on PostgreSQL 10 and > newer. > >> > >> Please, send any patch. You are welcome. > > > > In my very humble opinion, I would restrict the definition of partitions > to > > declarative partitioning. My justification would be that partitions all > use > > inheritance, but not all inheritance is a partition (how would you handle > > multiple inheritance). > > > > See patch attached that fails (in a way similar to other features) when > > connected to servers with version earlier than 10.0. > > The patch to add the pg_partition_tree() function was just committed: > > Add pg_partition_tree to display information about partitions > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d5eec4eefde70 > > Could one of you please revise the patch to use that function to produce > the output of \dP+? > here it is. It is based on Mathias's patch. Although we can use pg_partition_tree on PostgreSQL, we still should to support PostgreSQL 10, 11 where this function is not available Regards Pavel > Note that pg_partition_tree simply scans the underlying catalog to get all > the tables, so if you pass it a table that's not partitioned (relkind == > 'r'), but has inheritance children, the children will be returned in its > output. So, if you want to limit the output of \dP to partitioned tables, > be sure to include relkind = 'p' condition in the query. > > Thanks, > Amit > >
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index eb9d93a168..89c60aa340 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1635,6 +1635,21 @@ testdb=> </listitem> </varlistentry> + + <varlistentry> + <term><literal>\dP[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + <listitem> + <para> + Lists partitioned tables. If <replaceable class="parameter">pattern</replaceable> is + specified, only entries whose table name or schema name matches + the pattern are listed. If the form <literal>\dP+</literal> + is used, a sum of size of related partitions and a description + are also displayed. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>\drds [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term> <listitem> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 5b4d54a442..9d76bec38e 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -766,6 +766,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'p': success = permissionsList(pattern); break; + case 'P': + success = listPartitions(pattern, show_verbose); + break; case 'T': success = describeTypes(pattern, show_verbose, show_system); break; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4ca0db1d0c..9c69351277 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3635,6 +3635,132 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys return true; } +/* + * listPartitions() + * + * handler for \dP + */ +bool +listPartitions(const char *pattern, bool verbose) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + static const bool translate_columns[] = {false, false, true, false, false, false, false}; + + /* + * Note: Declarative table partitions are only supported as of Pg 10.0. + */ + + if (pset.sversion < 100000) + { + char sverbuf[32]; + + psql_error("The server (version %s) does not support declarative table partitioning.\n", + formatPGVersionNumber(pset.sversion, false, + sverbuf, sizeof(sverbuf))); + return true; + } + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT n.nspname as \"%s\",\n" + " c.relname as \"%s\",\n" + " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Owner")); + + if (verbose) + { + if (pset.sversion < 120000) + { + appendPQExpBuffer(&buf, + ",\n (WITH RECURSIVE d\n" + " AS (SELECT inhrelid AS oid\n" + " FROM pg_inherits\n" + " WHERE inhparent = c.oid\n" + " UNION ALL\n" + " SELECT inhrelid\n" + " FROM pg_inherits i\n" + " JOIN d ON i.inhparent = d.oid)\n" + " SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size(" + "oid))) FROM d) AS \"%s\"" + ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"", + gettext_noop("Size"), + gettext_noop("Description")); + } + else + { + /* PostgreSQL 11 has pg_partition_tree function */ + appendPQExpBuffer(&buf, + ",\n (SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size(" + "relid)))\n" + " FROM pg_partition_tree(c.oid)) AS \"%s\"" + ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"", + gettext_noop("Size"), + gettext_noop("Description")); + } + } + + appendPQExpBufferStr(&buf, + "\nFROM pg_catalog.pg_class c" + "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"); + + appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN ('p')\n"); + + if (!pattern) + appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + /* + * TOAST objects are suppressed unconditionally. Since we don't provide + * any way to select RELKIND_TOASTVALUE above, we would never show toast + * tables in any case; it seems a bit confusing to allow their indexes to + * be shown. Use plain \d if you really need to look at a TOAST + * table/index. + */ + appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "c.relname", NULL, + "pg_catalog.pg_table_is_visible(c.oid)"); + + appendPQExpBufferStr(&buf, "ORDER BY 1,2;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + /* + * Most functions in this file are content to print an empty table when + * there are no matching objects. We intentionally deviate from that + * here, but only in !quiet mode, for historical reasons. + */ + if (PQntuples(res) == 0 && !pset.quiet) + { + if (pattern) + psql_error("Did not find any relation named \"%s\".\n", + pattern); + else + psql_error("Did not find any relations.\n"); + } + else + { + myopt.nullPrint = NULL; + myopt.title = _("List of partitioned tables"); + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + } + + PQclear(res); + return true; +} /* * \dL diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index a4cc5efae0..0fb7c4b91e 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -63,6 +63,9 @@ extern bool listAllDbs(const char *pattern, bool verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); +/* \dP */ +extern bool listPartitions(const char *pattern, bool verbose); + /* \dD */ extern bool listDomains(const char *pattern, bool verbose, bool showSystem); diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 586aebddd3..07d46ef3cf 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -167,7 +167,7 @@ slashUsage(unsigned short int pager) * Use "psql --help=commands | wc" to count correctly. It's okay to count * the USE_READLINE line even in builds without that. */ - output = PageOutput(125, pager ? &(pset.popt.topt) : NULL); + output = PageOutput(126, pager ? &(pset.popt.topt) : NULL); fprintf(output, _("General\n")); fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n")); @@ -254,6 +254,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dRs[+] [PATTERN] list replication subscriptions\n")); fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n")); fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n")); + fprintf(output, _(" \\dP[+] [PATTERN] list partitioned tables\n")); fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n")); fprintf(output, _(" \\du[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\dv[S+] [PATTERN] list views\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index a980f92e11..7b57a79e38 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1332,7 +1332,7 @@ psql_completion(const char *text, int start, int end) "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df", "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", - "\\dm", "\\dn", "\\do", "\\dO", "\\dp", + "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",