Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1c5d927

Browse files
committed
psql \dP: list partitioned tables and indexes
The new command lists partitioned relations (tables and/or indexes),possibly with their sizes, possibly including partitioned partitions;their parents (if not top-level); if indexes show the tables they belongto; and their descriptions.While there are various possible improvements to this, having it in thisform is already a great improvement over not having any way to obtainthis report.Author: Pavel Stěhule, with help from Mathias Brossard, Amit Langote andJustin Pryzby.Reviewed-by: Amit Langote, Mathias Brossard, Melanie Plageman,Michaël Paquier, Álvaro Herrera
1 parent159970b commit1c5d927

File tree

8 files changed

+495
-2
lines changed

8 files changed

+495
-2
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1659,6 +1659,39 @@ testdb=>
16591659
</listitem>
16601660
</varlistentry>
16611661

1662+
1663+
<varlistentry>
1664+
<term><literal>\dP[itn+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
1665+
<listitem>
1666+
<para>
1667+
Lists partitioned relations.
1668+
If <replaceable class="parameter">pattern</replaceable>
1669+
is specified, only entries whose name matches the pattern are listed.
1670+
The modifiers <literal>t</literal> (tables) and <literal>i</literal>
1671+
(indexes) can be appended to the command, filtering the kind of
1672+
relations to list. By default, partitioned tables and indexes are
1673+
listed.
1674+
</para>
1675+
1676+
<para>
1677+
If the modifier <literal>n</literal> (<quote>nested</quote>) is used,
1678+
or a pattern is specified, then non-root partitioned tables are
1679+
included, and a column is shown displaying the parent of each
1680+
partitioned relation.
1681+
</para>
1682+
1683+
<para>
1684+
If <literal>+</literal> is appended to the command, the sum of sizes of
1685+
table's partitions (including that of their indexes) is also displayed,
1686+
along with the associated description.
1687+
If <literal>n</literal> is combined with <literal>+</literal>, two
1688+
sizes are shown: one including the total size of directly-attached
1689+
leaf partitions, and another showing the total size of all partitions,
1690+
including indirectly attached sub-partitions.
1691+
</para>
1692+
</listitem>
1693+
</varlistentry>
1694+
16621695
<varlistentry>
16631696
<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>
16641697
<listitem>

‎src/bin/psql/command.c

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -784,6 +784,23 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
784784
case'p':
785785
success=permissionsList(pattern);
786786
break;
787+
case'P':
788+
{
789+
switch (cmd[2])
790+
{
791+
case'\0':
792+
case'+':
793+
case't':
794+
case'i':
795+
case'n':
796+
success=listPartitionedTables(&cmd[2],pattern,show_verbose);
797+
break;
798+
default:
799+
status=PSQL_CMD_UNKNOWN;
800+
break;
801+
}
802+
}
803+
break;
787804
case'T':
788805
success=describeTypes(pattern,show_verbose,show_system);
789806
break;

‎src/bin/psql/describe.c

Lines changed: 214 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3777,6 +3777,220 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
37773777
return true;
37783778
}
37793779

3780+
/*
3781+
* \dP
3782+
* Takes an optional regexp to select particular relations
3783+
*
3784+
* As with \d, you can specify the kinds of relations you want:
3785+
*
3786+
* t for tables
3787+
* i for indexes
3788+
*
3789+
* And there's additional flags:
3790+
*
3791+
* n to list non-leaf partitioned tables
3792+
*
3793+
* and you can mix and match these in any order.
3794+
*/
3795+
bool
3796+
listPartitionedTables(constchar*reltypes,constchar*pattern,boolverbose)
3797+
{
3798+
boolshowTables=strchr(reltypes,'t')!=NULL;
3799+
boolshowIndexes=strchr(reltypes,'i')!=NULL;
3800+
boolshowNested=strchr(reltypes,'n')!=NULL;
3801+
PQExpBufferDatabuf;
3802+
PQExpBufferDatatitle;
3803+
PGresult*res;
3804+
printQueryOptmyopt=pset.popt;
3805+
booltranslate_columns[]= {false, false, false, false, false, false, false, false, false};
3806+
constchar*tabletitle;
3807+
boolmixed_output= false;
3808+
3809+
/*
3810+
* Note: Declarative table partitioning is only supported as of Pg 10.0.
3811+
*/
3812+
if (pset.sversion<100000)
3813+
{
3814+
charsverbuf[32];
3815+
3816+
pg_log_error("The server (version %s) does not support declarative table partitioning.",
3817+
formatPGVersionNumber(pset.sversion, false,
3818+
sverbuf,sizeof(sverbuf)));
3819+
return true;
3820+
}
3821+
3822+
/* If no relation kind was selected, show them all */
3823+
if (!showTables&& !showIndexes)
3824+
showTables=showIndexes= true;
3825+
3826+
if (showIndexes&& !showTables)
3827+
tabletitle=_("List of partitioned indexes");/* \dPi */
3828+
elseif (showTables&& !showIndexes)
3829+
tabletitle=_("List of partitioned tables");/* \dPt */
3830+
else
3831+
{
3832+
/* show all kinds */
3833+
tabletitle=_("List of partitioned relations");
3834+
mixed_output= true;
3835+
}
3836+
3837+
initPQExpBuffer(&buf);
3838+
3839+
printfPQExpBuffer(&buf,
3840+
"SELECT n.nspname as \"%s\",\n"
3841+
" c.relname as \"%s\",\n"
3842+
" pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
3843+
gettext_noop("Schema"),
3844+
gettext_noop("Name"),
3845+
gettext_noop("Owner"));
3846+
3847+
if (mixed_output)
3848+
{
3849+
appendPQExpBuffer(&buf,
3850+
",\n CASE c.relkind"
3851+
" WHEN "CppAsString2(RELKIND_PARTITIONED_TABLE)" THEN '%s'"
3852+
" WHEN "CppAsString2(RELKIND_PARTITIONED_INDEX)" THEN '%s'"
3853+
" END as \"%s\"",
3854+
gettext_noop("partitioned table"),
3855+
gettext_noop("partitioned index"),
3856+
gettext_noop("Type"));
3857+
3858+
translate_columns[3]= true;
3859+
}
3860+
3861+
if (showNested||pattern)
3862+
appendPQExpBuffer(&buf,
3863+
",\n c3.oid::regclass as \"%s\"",
3864+
gettext_noop("Parent name"));
3865+
3866+
if (showIndexes)
3867+
appendPQExpBuffer(&buf,
3868+
",\n c2.oid::regclass as \"%s\"",
3869+
gettext_noop("On table"));
3870+
3871+
if (verbose)
3872+
{
3873+
if (showNested)
3874+
{
3875+
appendPQExpBuffer(&buf,
3876+
",\n s.dps as \"%s\"",
3877+
gettext_noop("Leaf partition size"));
3878+
appendPQExpBuffer(&buf,
3879+
",\n s.tps as \"%s\"",
3880+
gettext_noop("Total size"));
3881+
}
3882+
else
3883+
/* Sizes of all partitions are considered in this case. */
3884+
appendPQExpBuffer(&buf,
3885+
",\n s.tps as \"%s\"",
3886+
gettext_noop("Total size"));
3887+
3888+
appendPQExpBuffer(&buf,
3889+
",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
3890+
gettext_noop("Description"));
3891+
}
3892+
3893+
appendPQExpBufferStr(&buf,
3894+
"\nFROM pg_catalog.pg_class c"
3895+
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
3896+
3897+
if (showIndexes)
3898+
appendPQExpBufferStr(&buf,
3899+
"\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
3900+
"\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
3901+
3902+
if (showNested||pattern)
3903+
appendPQExpBufferStr(&buf,
3904+
"\n LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid"
3905+
"\n LEFT JOIN pg_catalog.pg_class c3 ON c3.oid = inh.inhparent");
3906+
3907+
if (verbose)
3908+
{
3909+
if (pset.sversion<120000)
3910+
{
3911+
appendPQExpBuffer(&buf,
3912+
",\n LATERAL (WITH RECURSIVE d\n"
3913+
" AS (SELECT inhrelid AS oid, 1 AS level\n"
3914+
" FROM pg_catalog.pg_inherits\n"
3915+
" WHERE inhparent = c.oid\n"
3916+
" UNION ALL\n"
3917+
" SELECT inhrelid, level + 1\n"
3918+
" FROM pg_catalog.pg_inherits i\n"
3919+
" JOIN d ON i.inhparent = d.oid)\n"
3920+
" SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
3921+
"d.oid))) AS tps,\n"
3922+
" pg_catalog.pg_size_pretty(sum("
3923+
"\n CASE WHEN d.level = 1"
3924+
" THEN pg_catalog.pg_table_size(d.oid) ELSE 0 END)) AS dps\n"
3925+
" FROM d) s");
3926+
}
3927+
else
3928+
{
3929+
/* PostgreSQL 12 has pg_partition_tree function */
3930+
appendPQExpBuffer(&buf,
3931+
",\n LATERAL (SELECT pg_catalog.pg_size_pretty(sum("
3932+
"\n CASE WHEN ppt.isleaf AND ppt.level = 1"
3933+
"\n THEN pg_catalog.pg_table_size(ppt.relid)"
3934+
" ELSE 0 END)) AS dps"
3935+
",\n pg_catalog.pg_size_pretty(sum("
3936+
"pg_catalog.pg_table_size(ppt.relid))) AS tps"
3937+
"\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
3938+
}
3939+
}
3940+
3941+
appendPQExpBufferStr(&buf,"\nWHERE c.relkind IN (");
3942+
if (showTables)
3943+
appendPQExpBufferStr(&buf,CppAsString2(RELKIND_PARTITIONED_TABLE)",");
3944+
if (showIndexes)
3945+
appendPQExpBufferStr(&buf,CppAsString2(RELKIND_PARTITIONED_INDEX)",");
3946+
appendPQExpBufferStr(&buf,"''");/* dummy */
3947+
appendPQExpBufferStr(&buf,")\n");
3948+
3949+
appendPQExpBufferStr(&buf, !showNested&& !pattern ?
3950+
" AND NOT c.relispartition\n" :"");
3951+
3952+
if (!pattern)
3953+
appendPQExpBufferStr(&buf," AND n.nspname <> 'pg_catalog'\n"
3954+
" AND n.nspname <> 'information_schema'\n");
3955+
3956+
/*
3957+
* TOAST objects are suppressed unconditionally. Since we don't provide
3958+
* any way to select RELKIND_TOASTVALUE above, we would never show toast
3959+
* tables in any case; it seems a bit confusing to allow their indexes to
3960+
* be shown. Use plain \d if you really need to look at a TOAST
3961+
* table/index.
3962+
*/
3963+
appendPQExpBufferStr(&buf," AND n.nspname !~ '^pg_toast'\n");
3964+
3965+
processSQLNamePattern(pset.db,&buf,pattern, true, false,
3966+
"n.nspname","c.relname",NULL,
3967+
"pg_catalog.pg_table_is_visible(c.oid)");
3968+
3969+
appendPQExpBuffer(&buf,"ORDER BY \"Schema\", %s%s\"Name\";",
3970+
mixed_output ?"\"Type\" DESC, " :"",
3971+
showNested||pattern ?"\"Parent name\" NULLS FIRST, " :"");
3972+
3973+
res=PSQLexec(buf.data);
3974+
termPQExpBuffer(&buf);
3975+
if (!res)
3976+
return false;
3977+
3978+
initPQExpBuffer(&title);
3979+
appendPQExpBuffer(&title,"%s",tabletitle);
3980+
3981+
myopt.nullPrint=NULL;
3982+
myopt.title=title.data;
3983+
myopt.translate_header= true;
3984+
myopt.translate_columns=translate_columns;
3985+
myopt.n_translate_columns=lengthof(translate_columns);
3986+
3987+
printQuery(res,&myopt,pset.queryFout, false,pset.logfile);
3988+
3989+
termPQExpBuffer(&title);
3990+
3991+
PQclear(res);
3992+
return true;
3993+
}
37803994

37813995
/*
37823996
* \dL

‎src/bin/psql/describe.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,9 @@ extern bool listAllDbs(const char *pattern, bool verbose);
6363
/* \dt, \di, \ds, \dS, etc. */
6464
externboollistTables(constchar*tabtypes,constchar*pattern,boolverbose,boolshowSystem);
6565

66+
/* \dP */
67+
externboollistPartitionedTables(constchar*reltypes,constchar*pattern,boolverbose);
68+
6669
/* \dD */
6770
externboollistDomains(constchar*pattern,boolverbose,boolshowSystem);
6871

‎src/bin/psql/help.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -169,7 +169,7 @@ slashUsage(unsigned short int pager)
169169
* Use "psql --help=commands | wc" to count correctly. It's okay to count
170170
* the USE_READLINE line even in builds without that.
171171
*/
172-
output=PageOutput(126,pager ?&(pset.popt.topt) :NULL);
172+
output=PageOutput(127,pager ?&(pset.popt.topt) :NULL);
173173

174174
fprintf(output,_("General\n"));
175175
fprintf(output,_(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -251,6 +251,7 @@ slashUsage(unsigned short int pager)
251251
fprintf(output,_(" \\do[S] [PATTERN] list operators\n"));
252252
fprintf(output,_(" \\dO[S+] [PATTERN] list collations\n"));
253253
fprintf(output,_(" \\dp [PATTERN] list table, view, and sequence access privileges\n"));
254+
fprintf(output,_(" \\dP[tin+] [PATTERN] list [only table/index] partitioned relations\n"));
254255
fprintf(output,_(" \\drds [PATRN1 [PATRN2]] list per-database role settings\n"));
255256
fprintf(output,_(" \\dRp[+] [PATTERN] list replication publications\n"));
256257
fprintf(output,_(" \\dRs[+] [PATTERN] list replication subscriptions\n"));

‎src/bin/psql/tab-complete.c

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -464,6 +464,15 @@ static const SchemaQuery Query_for_list_of_indexes = {
464464
.result="pg_catalog.quote_ident(c.relname)",
465465
};
466466

467+
staticconstSchemaQueryQuery_for_list_of_partitioned_indexes= {
468+
.catname="pg_catalog.pg_class c",
469+
.selcondition="c.relkind = "CppAsString2(RELKIND_PARTITIONED_INDEX),
470+
.viscondition="pg_catalog.pg_table_is_visible(c.oid)",
471+
.namespace="c.relnamespace",
472+
.result="pg_catalog.quote_ident(c.relname)",
473+
};
474+
475+
467476
/* All relations */
468477
staticconstSchemaQueryQuery_for_list_of_relations= {
469478
.catname="pg_catalog.pg_class c",
@@ -472,6 +481,16 @@ static const SchemaQuery Query_for_list_of_relations = {
472481
.result="pg_catalog.quote_ident(c.relname)",
473482
};
474483

484+
/* partitioned relations */
485+
staticconstSchemaQueryQuery_for_list_of_partitioned_relations= {
486+
.catname="pg_catalog.pg_class c",
487+
.selcondition="c.relkind IN ("CppAsString2(RELKIND_PARTITIONED_TABLE)
488+
"," CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
489+
.viscondition="pg_catalog.pg_table_is_visible(c.oid)",
490+
.namespace="c.relnamespace",
491+
.result="pg_catalog.quote_ident(c.relname)",
492+
};
493+
475494
/* Relations supporting INSERT, UPDATE or DELETE */
476495
staticconstSchemaQueryQuery_for_list_of_updatables= {
477496
.catname="pg_catalog.pg_class c",
@@ -1382,7 +1401,7 @@ psql_completion(const char *text, int start, int end)
13821401
"\\d","\\da","\\dA","\\db","\\dc","\\dC","\\dd","\\ddp","\\dD",
13831402
"\\des","\\det","\\deu","\\dew","\\dE","\\df",
13841403
"\\dF","\\dFd","\\dFp","\\dFt","\\dg","\\di","\\dl","\\dL",
1385-
"\\dm","\\dn","\\do","\\dO","\\dp",
1404+
"\\dm","\\dn","\\do","\\dO","\\dp","\\dP","\\dPi","\\dPt",
13861405
"\\drds","\\dRs","\\dRp","\\ds","\\dS",
13871406
"\\dt","\\dT","\\dv","\\du","\\dx","\\dy",
13881407
"\\e","\\echo","\\ef","\\elif","\\else","\\encoding",
@@ -3527,6 +3546,12 @@ psql_completion(const char *text, int start, int end)
35273546
COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
35283547
elseif (TailMatchesCS("\\dp")||TailMatchesCS("\\z"))
35293548
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables,NULL);
3549+
elseif (TailMatchesCS("\\dPi*"))
3550+
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_indexes,NULL);
3551+
elseif (TailMatchesCS("\\dPt*"))
3552+
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_tables,NULL);
3553+
elseif (TailMatchesCS("\\dP*"))
3554+
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_relations,NULL);
35303555
elseif (TailMatchesCS("\\ds*"))
35313556
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences,NULL);
35323557
elseif (TailMatchesCS("\\dt*"))

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp