Skip to content

Commit b96f6b1

Browse files
alvherrealvherre
committed
pg_partition_ancestors
Adds another introspection feature for partitioning, necessary for further psql patches. Reviewed-by: Michaël Paquier Discussion: https://postgr.es/m/20190226222757.GA31622@alvherre.pgsql
1 parent d12fbe2 commit b96f6b1

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+
Oid relid = PG_GETARG_OID(0);
215+
FuncCallContext *funcctx;
216+
ListCell **next;
217+
218+
if (SRF_IS_FIRSTCALL())
219+
{
220+
MemoryContext oldcxt;
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+
Oid relid = 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-
#define CATALOG_VERSION_NO 201902161
56+
#define CATALOG_VERSION_NO 201903041
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 c ON (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 c ON (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
DROP TABLE ptif_test;
7987

8088
-- Table that is not part of any partition tree is not listed.
8189
CREATE TABLE ptif_normal_table(a int);
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
DROP TABLE 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)