Skip to content

Commit 3677a0b

Browse files
michaelpqmichaelpq
committed
Add pg_partition_root to display top-most parent of a partition tree
This is useful when looking at partition trees with multiple layers, and combined with pg_partition_tree, it provides the possibility to show up an entire tree by just knowing one member at any level. Author: Michael Paquier Reviewed-by: Álvaro Herrera, Amit Langote Discussion: https://postgr.es/m/20181207014015.GP2407@paquier.xyz
1 parent 34ea1ab commit 3677a0b

File tree

6 files changed

+153
-11
lines changed

6 files changed

+153
-11
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20274,6 +20274,17 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2027420274
their partitions, and so on.
2027520275
</entry>
2027620276
</row>
20277+
<row>
20278+
<entry>
20279+
<indexterm><primary>pg_partition_root</primary></indexterm>
20280+
<literal><function>pg_partition_root(<type>regclass</type>)</function></literal>
20281+
</entry>
20282+
<entry><type>regclass</type></entry>
20283+
<entry>
20284+
Return the top-most parent of a partition tree to which the given
20285+
relation belongs.
20286+
</entry>
20287+
</row>
2027720288
</tbody>
2027820289
</tgroup>
2027920290
</table>

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

Lines changed: 65 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,33 @@
2525
#include "utils/lsyscache.h"
2626
#include "utils/syscache.h"
2727

28+
/*
29+
* Checks if a given relation can be part of a partition tree. Returns
30+
* false if the relation cannot be processed, in which case it is up to
31+
* the caller to decide what to do, by either raising an error or doing
32+
* something else.
33+
*/
34+
static bool
35+
check_rel_can_be_partition(Oid relid)
36+
{
37+
char relkind;
38+
39+
/* Check if relation exists */
40+
if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(relid)))
41+
return false;
42+
43+
relkind = get_rel_relkind(relid);
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+
return false;
52+
53+
return true;
54+
}
2855

2956
/*
3057
* pg_partition_tree
@@ -39,19 +66,10 @@ pg_partition_tree(PG_FUNCTION_ARGS)
3966
{
4067
#define PG_PARTITION_TREE_COLS 4
4168
Oid rootrelid = PG_GETARG_OID(0);
42-
char relkind = get_rel_relkind(rootrelid);
4369
FuncCallContext *funcctx;
4470
ListCell **next;
4571

46-
if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(rootrelid)))
47-
PG_RETURN_NULL();
48-
49-
/* Return NULL for relation types that cannot appear in partition trees */
50-
if (relkind != RELKIND_RELATION &&
51-
relkind != RELKIND_FOREIGN_TABLE &&
52-
relkind != RELKIND_INDEX &&
53-
relkind != RELKIND_PARTITIONED_TABLE &&
54-
relkind != RELKIND_PARTITIONED_INDEX)
72+
if (!check_rel_can_be_partition(rootrelid))
5573
PG_RETURN_NULL();
5674

5775
/* stuff done only on the first call of the function */
@@ -153,3 +171,40 @@ pg_partition_tree(PG_FUNCTION_ARGS)
153171
/* done when there are no more elements left */
154172
SRF_RETURN_DONE(funcctx);
155173
}
174+
175+
/*
176+
* pg_partition_root
177+
*
178+
* Returns the top-most parent of the partition tree to which a given
179+
* relation belongs, or NULL if it's not (or cannot be) part of any
180+
* partition tree.
181+
*/
182+
Datum
183+
pg_partition_root(PG_FUNCTION_ARGS)
184+
{
185+
Oid relid = PG_GETARG_OID(0);
186+
Oid rootrelid;
187+
List *ancestors;
188+
189+
if (!check_rel_can_be_partition(relid))
190+
PG_RETURN_NULL();
191+
192+
/*
193+
* If the relation is not a partition (it may be the partition parent),
194+
* return itself as a result.
195+
*/
196+
if (!get_rel_relispartition(relid))
197+
PG_RETURN_OID(relid);
198+
199+
/* Fetch the top-most parent */
200+
ancestors = get_partition_ancestors(relid);
201+
rootrelid = llast_oid(ancestors);
202+
list_free(ancestors);
203+
204+
/*
205+
* "rootrelid" must contain a valid OID, given that the input relation is
206+
* a valid partition tree member as checked above.
207+
*/
208+
Assert(OidIsValid(rootrelid));
209+
PG_RETURN_OID(rootrelid);
210+
}

‎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 201902071
56+
#define CATALOG_VERSION_NO 201902081
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10509,4 +10509,9 @@
1050910509
proargnames => '{rootrelid,relid,parentrelid,isleaf,level}',
1051010510
prosrc => 'pg_partition_tree' },
1051110511

10512+
# function to get the top-most partition root parent
10513+
{ oid => '3424', descr => 'get top-most partition root parent',
10514+
proname => 'pg_partition_root', prorettype => 'regclass',
10515+
proargtypes => 'regclass', prosrc => 'pg_partition_root' },
10516+
1051210517
]

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

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,18 @@ SELECT * FROM pg_partition_tree(0);
1212
| | |
1313
(1 row)
1414

15+
SELECT pg_partition_root(NULL);
16+
pg_partition_root
17+
-------------------
18+
19+
(1 row)
20+
21+
SELECT pg_partition_root(0);
22+
pg_partition_root
23+
-------------------
24+
25+
(1 row)
26+
1527
-- Test table partition trees
1628
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
1729
CREATE TABLE ptif_test0 PARTITION OF ptif_test
@@ -66,6 +78,20 @@ SELECT relid, parentrelid, level, isleaf
6678
ptif_test01 | ptif_test0 | 0 | t
6779
(1 row)
6880

81+
-- List all members using pg_partition_root with leaf table reference
82+
SELECT relid, parentrelid, level, isleaf
83+
FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
84+
JOIN pg_class c ON (p.relid = c.oid);
85+
relid | parentrelid | level | isleaf
86+
-------------+-------------+-------+--------
87+
ptif_test | | 0 | f
88+
ptif_test0 | ptif_test | 1 | f
89+
ptif_test1 | ptif_test | 1 | f
90+
ptif_test2 | ptif_test | 1 | t
91+
ptif_test01 | ptif_test0 | 2 | t
92+
ptif_test11 | ptif_test1 | 2 | t
93+
(6 rows)
94+
6995
-- List all indexes members of the tree
7096
SELECT relid, parentrelid, level, isleaf
7197
FROM pg_partition_tree('ptif_test_index');
@@ -98,6 +124,20 @@ SELECT relid, parentrelid, level, isleaf
98124
ptif_test01_index | ptif_test0_index | 0 | t
99125
(1 row)
100126

127+
-- List all members using pg_partition_root with leaf index reference
128+
SELECT relid, parentrelid, level, isleaf
129+
FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
130+
JOIN pg_class c ON (p.relid = c.oid);
131+
relid | parentrelid | level | isleaf
132+
-------------------+------------------+-------+--------
133+
ptif_test_index | | 0 | f
134+
ptif_test0_index | ptif_test_index | 1 | f
135+
ptif_test1_index | ptif_test_index | 1 | f
136+
ptif_test2_index | ptif_test_index | 1 | t
137+
ptif_test01_index | ptif_test0_index | 2 | t
138+
ptif_test11_index | ptif_test1_index | 2 | t
139+
(6 rows)
140+
101141
DROP TABLE ptif_test;
102142
-- Table that is not part of any partition tree is the only member listed.
103143
CREATE TABLE ptif_normal_table(a int);
@@ -108,6 +148,12 @@ SELECT relid, parentrelid, level, isleaf
108148
ptif_normal_table | | 0 | t
109149
(1 row)
110150

151+
SELECT pg_partition_root('ptif_normal_table');
152+
pg_partition_root
153+
-------------------
154+
ptif_normal_table
155+
(1 row)
156+
111157
DROP TABLE ptif_normal_table;
112158
-- Various partitioning-related functions return NULL if passed relations
113159
-- of types that cannot be part of a partition tree; for example, views,
@@ -126,5 +172,17 @@ SELECT * FROM pg_partition_tree('ptif_test_matview');
126172
| | |
127173
(1 row)
128174

175+
SELECT pg_partition_root('ptif_test_view');
176+
pg_partition_root
177+
-------------------
178+
179+
(1 row)
180+
181+
SELECT pg_partition_root('ptif_test_matview');
182+
pg_partition_root
183+
-------------------
184+
185+
(1 row)
186+
129187
DROP VIEW ptif_test_view;
130188
DROP MATERIALIZED VIEW ptif_test_matview;

‎src/test/regress/sql/partition_info.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,8 @@
33
--
44
SELECT * FROM pg_partition_tree(NULL);
55
SELECT * FROM pg_partition_tree(0);
6+
SELECT pg_partition_root(NULL);
7+
SELECT pg_partition_root(0);
68

79
-- Test table partition trees
810
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
@@ -39,6 +41,10 @@ SELECT relid, parentrelid, level, isleaf
3941
SELECT relid, parentrelid, level, isleaf
4042
FROM pg_partition_tree('ptif_test01') p
4143
JOIN pg_class c ON (p.relid = c.oid);
44+
-- List all members using pg_partition_root with leaf table reference
45+
SELECT relid, parentrelid, level, isleaf
46+
FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
47+
JOIN pg_class c ON (p.relid = c.oid);
4248

4349
-- List all indexes members of the tree
4450
SELECT relid, parentrelid, level, isleaf
@@ -51,13 +57,18 @@ SELECT relid, parentrelid, level, isleaf
5157
SELECT relid, parentrelid, level, isleaf
5258
FROM pg_partition_tree('ptif_test01_index') p
5359
JOIN pg_class c ON (p.relid = c.oid);
60+
-- List all members using pg_partition_root with leaf index reference
61+
SELECT relid, parentrelid, level, isleaf
62+
FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
63+
JOIN pg_class c ON (p.relid = c.oid);
5464

5565
DROP TABLE ptif_test;
5666

5767
-- Table that is not part of any partition tree is the only member listed.
5868
CREATE TABLE ptif_normal_table(a int);
5969
SELECT relid, parentrelid, level, isleaf
6070
FROM pg_partition_tree('ptif_normal_table');
71+
SELECT pg_partition_root('ptif_normal_table');
6172
DROP TABLE ptif_normal_table;
6273

6374
-- Various partitioning-related functions return NULL if passed relations
@@ -67,5 +78,7 @@ CREATE VIEW ptif_test_view AS SELECT 1;
6778
CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
6879
SELECT * FROM pg_partition_tree('ptif_test_view');
6980
SELECT * FROM pg_partition_tree('ptif_test_matview');
81+
SELECT pg_partition_root('ptif_test_view');
82+
SELECT pg_partition_root('ptif_test_matview');
7083
DROP VIEW ptif_test_view;
7184
DROP MATERIALIZED VIEW ptif_test_matview;

0 commit comments

Comments
 (0)