Skip to content

Commit d5eec4e

Browse files
michaelpqmichaelpq
committed
Add pg_partition_tree to display information about partitions
This new function is useful to display a full tree of partitions with a partitioned table given in output, and avoids the need of any complex WITH RECURSIVE query when looking at partition trees which are deep multiple levels. It returns a set of records, one for each partition, containing the partition's name, its immediate parent's name, a boolean value telling if the relation is a leaf in the tree and an integer telling its level in the partition tree with given table considered as root, beginning at zero for the root, and incrementing by one each time the scan goes one level down. Author: Amit Langote Reviewed-by: Jesper Pedersen, Michael Paquier, Robert Haas Discussion: https://postgr.es/m/8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp
1 parent 56c0484 commit d5eec4e

File tree

9 files changed

+393
-4
lines changed

9 files changed

+393
-4
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20216,6 +20216,49 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2021620216
The function returns the number of new collation objects it created.
2021720217
</para>
2021820218

20219+
<table id="functions-info-partition">
20220+
<title>Partitioning Information Functions</title>
20221+
<tgroup cols="3">
20222+
<thead>
20223+
<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
20224+
</thead>
20225+
20226+
<tbody>
20227+
<row>
20228+
<entry><literal><function>pg_partition_tree(<type>regclass</type>)</function></literal></entry>
20229+
<entry><type>setof record</type></entry>
20230+
<entry>
20231+
List information about tables or indexes in a partition tree for a
20232+
given partitioned table or partitioned index, with one row for each
20233+
partition. Information provided includes the name of the partition,
20234+
the name of its immediate parent, a boolean value telling if the
20235+
partition is a leaf, and an integer telling its level in the hierarchy.
20236+
The value of level begins at <literal>0</literal> for the input table
20237+
or index in its role as the root of the partition tree,
20238+
<literal>1</literal> for its partitions, <literal>2</literal> for
20239+
their partitions, and so on.
20240+
</entry>
20241+
</row>
20242+
</tbody>
20243+
</tgroup>
20244+
</table>
20245+
20246+
<para>
20247+
To check the total size of the data contained in
20248+
<structname>measurement</structname> table described in
20249+
<xref linkend="ddl-partitioning-declarative-example"/>, one could use the
20250+
following query:
20251+
</para>
20252+
20253+
<programlisting>
20254+
=# SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
20255+
FROM pg_partition_tree('measurement');
20256+
total_size
20257+
------------
20258+
24 kB
20259+
(1 row)
20260+
</programlisting>
20261+
2021920262
</sect2>
2022020263

2022120264
<sect2 id="functions-admin-index">

‎src/backend/utils/adt/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -20,8 +20,8 @@ OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \
2020
jsonfuncs.o like.o lockfuncs.o mac.o mac8.o misc.o name.o \
2121
network.o network_gist.o network_selfuncs.o network_spgist.o \
2222
numeric.o numutils.o oid.o oracle_compat.o \
23-
orderedsetaggs.o pg_locale.o pg_lsn.o pg_upgrade_support.o \
24-
pgstatfuncs.o \
23+
orderedsetaggs.o partitionfuncs.o pg_locale.o pg_lsn.o \
24+
pg_upgrade_support.o pgstatfuncs.o \
2525
pseudotypes.o quote.o rangetypes.o rangetypes_gist.o \
2626
rangetypes_selfuncs.o rangetypes_spgist.o rangetypes_typanalyze.o \
2727
regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \

‎src/backend/utils/adt/partitionfuncs.c

Lines changed: 154 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,154 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* partitionfuncs.c
4+
* Functions for accessing partition-related metadata
5+
*
6+
* Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
7+
* Portions Copyright (c) 1994, Regents of the University of California
8+
*
9+
*
10+
* IDENTIFICATION
11+
* src/backend/utils/adt/partitionfuncs.c
12+
*
13+
*-------------------------------------------------------------------------
14+
*/
15+
16+
#include "postgres.h"
17+
18+
#include "access/htup_details.h"
19+
#include "catalog/partition.h"
20+
#include "catalog/pg_class.h"
21+
#include "catalog/pg_inherits.h"
22+
#include "catalog/pg_type.h"
23+
#include "funcapi.h"
24+
#include "utils/fmgrprotos.h"
25+
#include "utils/lsyscache.h"
26+
27+
28+
/*
29+
* pg_partition_tree
30+
*
31+
* Produce a view with one row per member of a partition tree, beginning
32+
* from the top-most parent given by the caller. This gives information
33+
* about each partition, its immediate partitioned parent, if it is
34+
* a leaf partition and its level in the hierarchy.
35+
*/
36+
Datum
37+
pg_partition_tree(PG_FUNCTION_ARGS)
38+
{
39+
#define PG_PARTITION_TREE_COLS 4
40+
Oid rootrelid = PG_GETARG_OID(0);
41+
char relkind = get_rel_relkind(rootrelid);
42+
FuncCallContext *funcctx;
43+
ListCell **next;
44+
45+
/* Only allow relation types that can appear in partition trees. */
46+
if (relkind != RELKIND_RELATION &&
47+
relkind != RELKIND_FOREIGN_TABLE &&
48+
relkind != RELKIND_INDEX &&
49+
relkind != RELKIND_PARTITIONED_TABLE &&
50+
relkind != RELKIND_PARTITIONED_INDEX)
51+
ereport(ERROR,
52+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
53+
errmsg("\"%s\" is not a table, a foreign table, or an index",
54+
get_rel_name(rootrelid))));
55+
56+
/* stuff done only on the first call of the function */
57+
if (SRF_IS_FIRSTCALL())
58+
{
59+
MemoryContext oldcxt;
60+
TupleDesc tupdesc;
61+
List *partitions;
62+
63+
/* create a function context for cross-call persistence */
64+
funcctx = SRF_FIRSTCALL_INIT();
65+
66+
/* switch to memory context appropriate for multiple function calls */
67+
oldcxt = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
68+
69+
/*
70+
* Find all members of inheritance set. We only need AccessShareLock
71+
* on the children for the partition information lookup.
72+
*/
73+
partitions = find_all_inheritors(rootrelid, AccessShareLock, NULL);
74+
75+
tupdesc = CreateTemplateTupleDesc(PG_PARTITION_TREE_COLS, false);
76+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid",
77+
REGCLASSOID, -1, 0);
78+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid",
79+
REGCLASSOID, -1, 0);
80+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "isleaf",
81+
BOOLOID, -1, 0);
82+
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "level",
83+
INT4OID, -1, 0);
84+
85+
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
86+
87+
/* allocate memory for user context */
88+
next = (ListCell **) palloc(sizeof(ListCell *));
89+
*next = list_head(partitions);
90+
funcctx->user_fctx = (void *) next;
91+
92+
MemoryContextSwitchTo(oldcxt);
93+
}
94+
95+
/* stuff done on every call of the function */
96+
funcctx = SRF_PERCALL_SETUP();
97+
next = (ListCell **) funcctx->user_fctx;
98+
99+
if (*next != NULL)
100+
{
101+
Datum result;
102+
Datum values[PG_PARTITION_TREE_COLS];
103+
bool nulls[PG_PARTITION_TREE_COLS];
104+
HeapTuple tuple;
105+
Oid parentid = InvalidOid;
106+
Oid relid = lfirst_oid(*next);
107+
char relkind = get_rel_relkind(relid);
108+
int level = 0;
109+
List *ancestors = get_partition_ancestors(lfirst_oid(*next));
110+
ListCell *lc;
111+
112+
/*
113+
* Form tuple with appropriate data.
114+
*/
115+
MemSet(nulls, 0, sizeof(nulls));
116+
MemSet(values, 0, sizeof(values));
117+
118+
/* relid */
119+
values[0] = ObjectIdGetDatum(relid);
120+
121+
/* parentid */
122+
if (ancestors != NIL)
123+
parentid = linitial_oid(ancestors);
124+
if (OidIsValid(parentid))
125+
values[1] = ObjectIdGetDatum(parentid);
126+
else
127+
nulls[1] = true;
128+
129+
/* isleaf */
130+
values[2] = BoolGetDatum(relkind != RELKIND_PARTITIONED_TABLE &&
131+
relkind != RELKIND_PARTITIONED_INDEX);
132+
133+
/* level */
134+
if (relid != rootrelid)
135+
{
136+
foreach(lc, ancestors)
137+
{
138+
level++;
139+
if (lfirst_oid(lc) == rootrelid)
140+
break;
141+
}
142+
}
143+
values[3] = Int32GetDatum(level);
144+
145+
*next = lnext(*next);
146+
147+
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
148+
result = HeapTupleGetDatum(tuple);
149+
SRF_RETURN_NEXT(funcctx, result);
150+
}
151+
152+
/* done when there are no more elements left */
153+
SRF_RETURN_DONE(funcctx);
154+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201810251
56+
#define CATALOG_VERSION_NO 201810301
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10029,4 +10029,13 @@
1002910029
proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any',
1003010030
proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' },
1003110031

10032+
# information about a partition tree
10033+
{ oid => '3423', descr => 'view partition tree tables',
10034+
proname => 'pg_partition_tree', prorows => '1000', proretset => 't',
10035+
provolatile => 'v', prorettype => 'record', proargtypes => 'regclass',
10036+
proallargtypes => '{regclass,regclass,regclass,bool,int4}',
10037+
proargmodes => '{i,o,o,o,o}',
10038+
proargnames => '{rootrelid,relid,parentrelid,isleaf,level}',
10039+
prosrc => 'pg_partition_tree' }
10040+
1003210041
]

‎src/test/regress/expected/partition_info.out

Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,114 @@
1+
--
2+
-- Tests for pg_partition_tree
3+
--
4+
SELECT * FROM pg_partition_tree(NULL);
5+
relid | parentrelid | isleaf | level
6+
-------+-------------+--------+-------
7+
(0 rows)
8+
9+
-- Test table partition trees
10+
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
11+
CREATE TABLE ptif_test0 PARTITION OF ptif_test
12+
FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
13+
CREATE TABLE ptif_test01 PARTITION OF ptif_test0 FOR VALUES IN (1);
14+
CREATE TABLE ptif_test1 PARTITION OF ptif_test
15+
FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
16+
CREATE TABLE ptif_test11 PARTITION OF ptif_test1 FOR VALUES IN (1);
17+
CREATE TABLE ptif_test2 PARTITION OF ptif_test
18+
FOR VALUES FROM (100) TO (maxvalue);
19+
-- Test index partition tree
20+
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
21+
CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
22+
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index;
23+
CREATE INDEX ptif_test01_index ON ptif_test01 (a);
24+
ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index;
25+
CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a);
26+
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index;
27+
CREATE INDEX ptif_test11_index ON ptif_test11 (a);
28+
ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index;
29+
CREATE INDEX ptif_test2_index ON ptif_test2 (a);
30+
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
31+
-- List all tables members of the tree
32+
SELECT relid, parentrelid, level, isleaf
33+
FROM pg_partition_tree('ptif_test');
34+
relid | parentrelid | level | isleaf
35+
-------------+-------------+-------+--------
36+
ptif_test | | 0 | f
37+
ptif_test0 | ptif_test | 1 | f
38+
ptif_test1 | ptif_test | 1 | f
39+
ptif_test2 | ptif_test | 1 | t
40+
ptif_test01 | ptif_test0 | 2 | t
41+
ptif_test11 | ptif_test1 | 2 | t
42+
(6 rows)
43+
44+
-- List tables from an intermediate level
45+
SELECT relid, parentrelid, level, isleaf
46+
FROM pg_partition_tree('ptif_test0') p
47+
JOIN pg_class c ON (p.relid = c.oid);
48+
relid | parentrelid | level | isleaf
49+
-------------+-------------+-------+--------
50+
ptif_test0 | ptif_test | 0 | f
51+
ptif_test01 | ptif_test0 | 1 | t
52+
(2 rows)
53+
54+
-- List from leaf table
55+
SELECT relid, parentrelid, level, isleaf
56+
FROM pg_partition_tree('ptif_test01') p
57+
JOIN pg_class c ON (p.relid = c.oid);
58+
relid | parentrelid | level | isleaf
59+
-------------+-------------+-------+--------
60+
ptif_test01 | ptif_test0 | 0 | t
61+
(1 row)
62+
63+
-- List all indexes members of the tree
64+
SELECT relid, parentrelid, level, isleaf
65+
FROM pg_partition_tree('ptif_test_index');
66+
relid | parentrelid | level | isleaf
67+
-------------------+------------------+-------+--------
68+
ptif_test_index | | 0 | f
69+
ptif_test0_index | ptif_test_index | 1 | f
70+
ptif_test1_index | ptif_test_index | 1 | f
71+
ptif_test2_index | ptif_test_index | 1 | t
72+
ptif_test01_index | ptif_test0_index | 2 | t
73+
ptif_test11_index | ptif_test1_index | 2 | t
74+
(6 rows)
75+
76+
-- List indexes from an intermediate level
77+
SELECT relid, parentrelid, level, isleaf
78+
FROM pg_partition_tree('ptif_test0_index') p
79+
JOIN pg_class c ON (p.relid = c.oid);
80+
relid | parentrelid | level | isleaf
81+
-------------------+------------------+-------+--------
82+
ptif_test0_index | ptif_test_index | 0 | f
83+
ptif_test01_index | ptif_test0_index | 1 | t
84+
(2 rows)
85+
86+
-- List from leaf index
87+
SELECT relid, parentrelid, level, isleaf
88+
FROM pg_partition_tree('ptif_test01_index') p
89+
JOIN pg_class c ON (p.relid = c.oid);
90+
relid | parentrelid | level | isleaf
91+
-------------------+------------------+-------+--------
92+
ptif_test01_index | ptif_test0_index | 0 | t
93+
(1 row)
94+
95+
DROP TABLE ptif_test;
96+
-- A table not part of a partition tree works is the only member listed.
97+
CREATE TABLE ptif_normal_table(a int);
98+
SELECT relid, parentrelid, level, isleaf
99+
FROM pg_partition_tree('ptif_normal_table');
100+
relid | parentrelid | level | isleaf
101+
-------------------+-------------+-------+--------
102+
ptif_normal_table | | 0 | t
103+
(1 row)
104+
105+
DROP TABLE ptif_normal_table;
106+
-- Views and materialized viewS cannot be part of a partition tree.
107+
CREATE VIEW ptif_test_view AS SELECT 1;
108+
CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
109+
SELECT * FROM pg_partition_tree('ptif_test_view');
110+
ERROR: "ptif_test_view" is not a table, a foreign table, or an index
111+
SELECT * FROM pg_partition_tree('ptif_test_matview');
112+
ERROR: "ptif_test_matview" is not a table, a foreign table, or an index
113+
DROP VIEW ptif_test_view;
114+
DROP MATERIALIZED VIEW ptif_test_matview;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c
116116
# ----------
117117
# Another group of parallel tests
118118
# ----------
119-
test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate
119+
test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info
120120

121121
# event triggers cannot run concurrently with any test that runs DDL
122122
test: event_trigger

‎src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -185,6 +185,7 @@ test: reloptions
185185
test: hash_part
186186
test: indexing
187187
test: partition_aggregate
188+
test: partition_info
188189
test: event_trigger
189190
test: fast_default
190191
test: stats

0 commit comments

Comments
 (0)