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

Commit3677a0b

Browse files
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, andcombined with pg_partition_tree, it provides the possibility to show upan entire tree by just knowing one member at any level.Author: Michael PaquierReviewed-by: Álvaro Herrera, Amit LangoteDiscussion:https://postgr.es/m/20181207014015.GP2407@paquier.xyz
1 parent34ea1ab commit3677a0b

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+
staticbool
35+
check_rel_can_be_partition(Oidrelid)
36+
{
37+
charrelkind;
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
#definePG_PARTITION_TREE_COLS4
4168
Oidrootrelid=PG_GETARG_OID(0);
42-
charrelkind=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+
Oidrelid=PG_GETARG_OID(0);
186+
Oidrootrelid;
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-
#defineCATALOG_VERSION_NO201902071
56+
#defineCATALOG_VERSION_NO201902081
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
CREATETABLEptif_test (aint, bint) 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 cON (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 cON (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 cON (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 cON (p.relid=c.oid);
5464

5565
DROPTABLE ptif_test;
5666

5767
-- Table that is not part of any partition tree is the only member listed.
5868
CREATETABLEptif_normal_table(aint);
5969
SELECT relid, parentrelid, level, isleaf
6070
FROM pg_partition_tree('ptif_normal_table');
71+
SELECT pg_partition_root('ptif_normal_table');
6172
DROPTABLE 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_matviewASSELECT1;
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
DROPVIEW ptif_test_view;
7184
DROP MATERIALIZED VIEW ptif_test_matview;

0 commit comments

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