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

Commitb96f6b1

Browse files
committed
pg_partition_ancestors
Adds another introspection feature for partitioning, necessary forfurther psql patches.Reviewed-by: Michaël PaquierDiscussion:https://postgr.es/m/20190226222757.GA31622@alvherre.pgsql
1 parentd12fbe2 commitb96f6b1

File tree

6 files changed

+144
-1
lines changed

6 files changed

+144
-1
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_ancestors</primary></indexterm>
20280+
<literal><function>pg_partition_ancestors(<type>regclass</type>)</function></literal>
20281+
</entry>
20282+
<entry><type>setof regclass</type></entry>
20283+
<entry>
20284+
List the ancestor relations of the given partition,
20285+
including the partition itself.
20286+
</entry>
20287+
</row>
2027720288
<row>
2027820289
<entry>
2027920290
<indexterm><primary>pg_partition_root</primary></indexterm>

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

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,3 +201,52 @@ pg_partition_root(PG_FUNCTION_ARGS)
201201
Assert(OidIsValid(rootrelid));
202202
PG_RETURN_OID(rootrelid);
203203
}
204+
205+
/*
206+
* pg_partition_ancestors
207+
*
208+
* Produces a view with one row per ancestor of the given partition,
209+
* including the input relation itself.
210+
*/
211+
Datum
212+
pg_partition_ancestors(PG_FUNCTION_ARGS)
213+
{
214+
Oidrelid=PG_GETARG_OID(0);
215+
FuncCallContext*funcctx;
216+
ListCell**next;
217+
218+
if (SRF_IS_FIRSTCALL())
219+
{
220+
MemoryContextoldcxt;
221+
List*ancestors;
222+
223+
funcctx=SRF_FIRSTCALL_INIT();
224+
225+
if (!check_rel_can_be_partition(relid))
226+
SRF_RETURN_DONE(funcctx);
227+
228+
oldcxt=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
229+
230+
ancestors=get_partition_ancestors(relid);
231+
ancestors=lcons_oid(relid,ancestors);
232+
233+
next= (ListCell**)palloc(sizeof(ListCell*));
234+
*next=list_head(ancestors);
235+
funcctx->user_fctx= (void*)next;
236+
237+
MemoryContextSwitchTo(oldcxt);
238+
}
239+
240+
funcctx=SRF_PERCALL_SETUP();
241+
next= (ListCell**)funcctx->user_fctx;
242+
243+
if (*next!=NULL)
244+
{
245+
Oidrelid=lfirst_oid(*next);
246+
247+
*next=lnext(*next);
248+
SRF_RETURN_NEXT(funcctx,ObjectIdGetDatum(relid));
249+
}
250+
251+
SRF_RETURN_DONE(funcctx);
252+
}

‎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_NO201902161
56+
#defineCATALOG_VERSION_NO201903041
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10528,6 +10528,11 @@
1052810528
proargmodes => '{i,o,o,o,o}',
1052910529
proargnames => '{rootrelid,relid,parentrelid,isleaf,level}',
1053010530
prosrc => 'pg_partition_tree' },
10531+
{ oid => '3425', descr => 'view ancestors of the partition',
10532+
proname => 'pg_partition_ancestors', prorows => '10', proretset => 't',
10533+
provolatile => 'v', prorettype => 'regclass', proargtypes => 'regclass',
10534+
proallargtypes => '{regclass,regclass}', proargmodes => '{i,o}',
10535+
proargnames => '{partitionid,relid}', prosrc => 'pg_partition_ancestors' },
1053110536

1053210537
# function to get the top-most partition root parent
1053310538
{ oid => '3424', descr => 'get top-most partition root parent',

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

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,16 @@ SELECT * FROM pg_partition_tree(0);
1111
-------+-------------+--------+-------
1212
(0 rows)
1313

14+
SELECT * FROM pg_partition_ancestors(NULL);
15+
relid
16+
-------
17+
(0 rows)
18+
19+
SELECT * FROM pg_partition_ancestors(0);
20+
relid
21+
-------
22+
(0 rows)
23+
1424
SELECT pg_partition_root(NULL);
1525
pg_partition_root
1626
-------------------
@@ -92,6 +102,21 @@ SELECT relid, parentrelid, level, isleaf
92102
ptif_test3 | ptif_test | 0 | f
93103
(1 row)
94104

105+
-- List all ancestors of root and leaf tables
106+
SELECT * FROM pg_partition_ancestors('ptif_test01');
107+
relid
108+
-------------
109+
ptif_test01
110+
ptif_test0
111+
ptif_test
112+
(3 rows)
113+
114+
SELECT * FROM pg_partition_ancestors('ptif_test');
115+
relid
116+
-----------
117+
ptif_test
118+
(1 row)
119+
95120
-- List all members using pg_partition_root with leaf table reference
96121
SELECT relid, parentrelid, level, isleaf
97122
FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
@@ -164,6 +189,21 @@ SELECT relid, parentrelid, level, isleaf
164189
ptif_test11_index | ptif_test1_index | 2 | t
165190
(7 rows)
166191

192+
-- List all ancestors of root and leaf indexes
193+
SELECT * FROM pg_partition_ancestors('ptif_test01_index');
194+
relid
195+
-------------------
196+
ptif_test01_index
197+
ptif_test0_index
198+
ptif_test_index
199+
(3 rows)
200+
201+
SELECT * FROM pg_partition_ancestors('ptif_test_index');
202+
relid
203+
-----------------
204+
ptif_test_index
205+
(1 row)
206+
167207
DROP TABLE ptif_test;
168208
-- Table that is not part of any partition tree is not listed.
169209
CREATE TABLE ptif_normal_table(a int);
@@ -173,6 +213,11 @@ SELECT relid, parentrelid, level, isleaf
173213
-------+-------------+-------+--------
174214
(0 rows)
175215

216+
SELECT * FROM pg_partition_ancestors('ptif_normal_table');
217+
relid
218+
-------
219+
(0 rows)
220+
176221
SELECT pg_partition_root('ptif_normal_table');
177222
pg_partition_root
178223
-------------------
@@ -207,6 +252,26 @@ SELECT * FROM pg_partition_tree('ptif_li_child');
207252
-------+-------------+--------+-------
208253
(0 rows)
209254

255+
SELECT * FROM pg_partition_ancestors('ptif_test_view');
256+
relid
257+
-------
258+
(0 rows)
259+
260+
SELECT * FROM pg_partition_ancestors('ptif_test_matview');
261+
relid
262+
-------
263+
(0 rows)
264+
265+
SELECT * FROM pg_partition_ancestors('ptif_li_parent');
266+
relid
267+
-------
268+
(0 rows)
269+
270+
SELECT * FROM pg_partition_ancestors('ptif_li_child');
271+
relid
272+
-------
273+
(0 rows)
274+
210275
SELECT pg_partition_root('ptif_test_view');
211276
pg_partition_root
212277
-------------------

‎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*FROM pg_partition_ancestors(NULL);
7+
SELECT*FROM pg_partition_ancestors(0);
68
SELECT pg_partition_root(NULL);
79
SELECT pg_partition_root(0);
810

@@ -50,6 +52,9 @@ SELECT relid, parentrelid, level, isleaf
5052
SELECT relid, parentrelid, level, isleaf
5153
FROM pg_partition_tree('ptif_test3') p
5254
JOIN pg_class cON (p.relid=c.oid);
55+
-- List all ancestors of root and leaf tables
56+
SELECT*FROM pg_partition_ancestors('ptif_test01');
57+
SELECT*FROM pg_partition_ancestors('ptif_test');
5358
-- List all members using pg_partition_root with leaf table reference
5459
SELECT relid, parentrelid, level, isleaf
5560
FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
@@ -74,13 +79,17 @@ SELECT relid, parentrelid, level, isleaf
7479
SELECT relid, parentrelid, level, isleaf
7580
FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
7681
JOIN pg_class cON (p.relid=c.oid);
82+
-- List all ancestors of root and leaf indexes
83+
SELECT*FROM pg_partition_ancestors('ptif_test01_index');
84+
SELECT*FROM pg_partition_ancestors('ptif_test_index');
7785

7886
DROPTABLE ptif_test;
7987

8088
-- Table that is not part of any partition tree is not listed.
8189
CREATETABLEptif_normal_table(aint);
8290
SELECT relid, parentrelid, level, isleaf
8391
FROM pg_partition_tree('ptif_normal_table');
92+
SELECT*FROM pg_partition_ancestors('ptif_normal_table');
8493
SELECT pg_partition_root('ptif_normal_table');
8594
DROPTABLE ptif_normal_table;
8695

@@ -95,6 +104,10 @@ SELECT * FROM pg_partition_tree('ptif_test_view');
95104
SELECT*FROM pg_partition_tree('ptif_test_matview');
96105
SELECT*FROM pg_partition_tree('ptif_li_parent');
97106
SELECT*FROM pg_partition_tree('ptif_li_child');
107+
SELECT*FROM pg_partition_ancestors('ptif_test_view');
108+
SELECT*FROM pg_partition_ancestors('ptif_test_matview');
109+
SELECT*FROM pg_partition_ancestors('ptif_li_parent');
110+
SELECT*FROM pg_partition_ancestors('ptif_li_child');
98111
SELECT pg_partition_root('ptif_test_view');
99112
SELECT pg_partition_root('ptif_test_matview');
100113
SELECT pg_partition_root('ptif_li_parent');

0 commit comments

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