Hi all, Álvaro has given faced a use case where it would be useful to have a function which is able to return the top-most parent of a partition tree: https://postgr.es/m/20181204184159.eue3wlchqrkh4vsc@alvherre.pgsql
This has been mentioned as well on the thread where was discussed pg_partition_tree, but it got shaved from the committed patch as many things happened when discussing the thing. Attached is a patch to do the work, which includes documentation and tests. An argument could be made to include the top-most parent as part of pg_partition_tree, but it feels more natural to me to have a separate function. This makes sure to handle invalid relations by returning NULL, and it generates an error for incorrect relkind. I have included as well a fix for the recent crash on pg_partition_tree I have reported, but let's discuss the crash on its thread here: https://www.postgresql.org/message-id/20181207010406.go2...@paquier.xyz The bug fix would most likely get committed first, and I'll rebase this patch as need be. I am adding this patch to the CF of January. I think that Amit should also be marked as a co-author of this patch, as that's inspired from what has been submitted previously, still I have no reused the code. Thanks, -- Michael
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b3336ea9be..dbec132188 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20270,6 +20270,17 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
their partitions, and so on.
</entry>
</row>
+ <row>
+ <entry>
+ <indexterm><primary>pg_partition_root</primary></indexterm>
+ <literal><function>pg_partition_root(<type>regclass</type>)</function></literal>
+ </entry>
+ <entry><type>regclass</type></entry>
+ <entry>
+ Return the top-most parent of a partition tree for the given
+ partitioned table or partitioned index.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt/partitionfuncs.c
index 78dd2b542b..611a33d0e1 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -23,7 +23,38 @@
#include "funcapi.h"
#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
+/*
+ * Perform several checks on a relation on which is extracted some
+ * information related to its partition tree. Returns false if the
+ * relation cannot be processed, in which case it is up to the caller
+ * to decide what to do instead of an error.
+ */
+static bool
+check_rel_for_partition_info(Oid relid)
+{
+ char relkind;
+
+ /* Check if relation exists */
+ if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(relid)))
+ return false;
+
+ relkind = get_rel_relkind(relid);
+
+ /* Only allow relation types that can appear in partition trees. */
+ if (relkind != RELKIND_RELATION &&
+ relkind != RELKIND_FOREIGN_TABLE &&
+ relkind != RELKIND_INDEX &&
+ relkind != RELKIND_PARTITIONED_TABLE &&
+ relkind != RELKIND_PARTITIONED_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a table, a foreign table, or an index",
+ get_rel_name(relid))));
+
+ return true;
+}
/*
* pg_partition_tree
@@ -38,20 +69,11 @@ pg_partition_tree(PG_FUNCTION_ARGS)
{
#define PG_PARTITION_TREE_COLS 4
Oid rootrelid = PG_GETARG_OID(0);
- char relkind = get_rel_relkind(rootrelid);
FuncCallContext *funcctx;
ListCell **next;
- /* Only allow relation types that can appear in partition trees. */
- if (relkind != RELKIND_RELATION &&
- relkind != RELKIND_FOREIGN_TABLE &&
- relkind != RELKIND_INDEX &&
- relkind != RELKIND_PARTITIONED_TABLE &&
- relkind != RELKIND_PARTITIONED_INDEX)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a table, a foreign table, or an index",
- get_rel_name(rootrelid))));
+ if (!check_rel_for_partition_info(rootrelid))
+ PG_RETURN_NULL();
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -152,3 +174,39 @@ pg_partition_tree(PG_FUNCTION_ARGS)
/* done when there are no more elements left */
SRF_RETURN_DONE(funcctx);
}
+
+/*
+ * pg_partition_root
+ *
+ * For the given relation part of a partition tree, return its top-most
+ * root parent.
+ */
+Datum
+pg_partition_root(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ Oid rootrelid;
+ List *ancestors;
+
+ if (!check_rel_for_partition_info(relid))
+ PG_RETURN_NULL();
+
+ /*
+ * If the relation is not a partition, return itself as a result.
+ */
+ if (!get_rel_relispartition(relid))
+ PG_RETURN_OID(relid);
+
+ /* Fetch the top-most parent */
+ ancestors = get_partition_ancestors(relid);
+ rootrelid = llast_oid(ancestors);
+ list_free(ancestors);
+
+ /*
+ * If the relation is actually a partition, 'rootrelid' has been set to
+ * the OID of the root table in the partition tree. It should be a valid
+ * valid per the previous check for partition leaf above.
+ */
+ Assert(OidIsValid(rootrelid));
+ PG_RETURN_OID(rootrelid);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 034a41eb55..6817a027c2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10048,4 +10048,9 @@
proargnames => '{rootrelid,relid,parentrelid,isleaf,level}',
prosrc => 'pg_partition_tree' },
+# function to get the top-most partition root parent
+{ oid => '3424', descr => 'get top-most partition root parent',
+ proname => 'pg_partition_root', prorettype => 'regclass',
+ proargtypes => 'regclass', prosrc => 'pg_partition_root' },
+
]
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
index 6b116125e6..cee741aefe 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -6,6 +6,24 @@ SELECT * FROM pg_partition_tree(NULL);
-------+-------------+--------+-------
(0 rows)
+SELECT * FROM pg_partition_tree(0);
+ relid | parentrelid | isleaf | level
+-------+-------------+--------+-------
+ | | |
+(1 row)
+
+SELECT pg_partition_root(NULL);
+ pg_partition_root
+-------------------
+
+(1 row)
+
+SELECT pg_partition_root(0);
+ pg_partition_root
+-------------------
+
+(1 row)
+
-- Test table partition trees
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
CREATE TABLE ptif_test0 PARTITION OF ptif_test
@@ -29,67 +47,67 @@ ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index;
CREATE INDEX ptif_test2_index ON ptif_test2 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
-- List all tables members of the tree
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test');
- relid | parentrelid | level | isleaf
--------------+-------------+-------+--------
- ptif_test | | 0 | f
- ptif_test0 | ptif_test | 1 | f
- ptif_test1 | ptif_test | 1 | f
- ptif_test2 | ptif_test | 1 | t
- ptif_test01 | ptif_test0 | 2 | t
- ptif_test11 | ptif_test1 | 2 | t
+ relid | parentrelid | level | isleaf | pg_partition_root
+-------------+-------------+-------+--------+-------------------
+ ptif_test | | 0 | f | ptif_test
+ ptif_test0 | ptif_test | 1 | f | ptif_test
+ ptif_test1 | ptif_test | 1 | f | ptif_test
+ ptif_test2 | ptif_test | 1 | t | ptif_test
+ ptif_test01 | ptif_test0 | 2 | t | ptif_test
+ ptif_test11 | ptif_test1 | 2 | t | ptif_test
(6 rows)
-- List tables from an intermediate level
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test0') p
JOIN pg_class c ON (p.relid = c.oid);
- relid | parentrelid | level | isleaf
--------------+-------------+-------+--------
- ptif_test0 | ptif_test | 0 | f
- ptif_test01 | ptif_test0 | 1 | t
+ relid | parentrelid | level | isleaf | pg_partition_root
+-------------+-------------+-------+--------+-------------------
+ ptif_test0 | ptif_test | 0 | f | ptif_test
+ ptif_test01 | ptif_test0 | 1 | t | ptif_test
(2 rows)
-- List from leaf table
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test01') p
JOIN pg_class c ON (p.relid = c.oid);
- relid | parentrelid | level | isleaf
--------------+-------------+-------+--------
- ptif_test01 | ptif_test0 | 0 | t
+ relid | parentrelid | level | isleaf | pg_partition_root
+-------------+-------------+-------+--------+-------------------
+ ptif_test01 | ptif_test0 | 0 | t | ptif_test
(1 row)
-- List all indexes members of the tree
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test_index');
- relid | parentrelid | level | isleaf
--------------------+------------------+-------+--------
- ptif_test_index | | 0 | f
- ptif_test0_index | ptif_test_index | 1 | f
- ptif_test1_index | ptif_test_index | 1 | f
- ptif_test2_index | ptif_test_index | 1 | t
- ptif_test01_index | ptif_test0_index | 2 | t
- ptif_test11_index | ptif_test1_index | 2 | t
+ relid | parentrelid | level | isleaf | pg_partition_root
+-------------------+------------------+-------+--------+-------------------
+ ptif_test_index | | 0 | f | ptif_test_index
+ ptif_test0_index | ptif_test_index | 1 | f | ptif_test_index
+ ptif_test1_index | ptif_test_index | 1 | f | ptif_test_index
+ ptif_test2_index | ptif_test_index | 1 | t | ptif_test_index
+ ptif_test01_index | ptif_test0_index | 2 | t | ptif_test_index
+ ptif_test11_index | ptif_test1_index | 2 | t | ptif_test_index
(6 rows)
-- List indexes from an intermediate level
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test0_index') p
JOIN pg_class c ON (p.relid = c.oid);
- relid | parentrelid | level | isleaf
--------------------+------------------+-------+--------
- ptif_test0_index | ptif_test_index | 0 | f
- ptif_test01_index | ptif_test0_index | 1 | t
+ relid | parentrelid | level | isleaf | pg_partition_root
+-------------------+------------------+-------+--------+-------------------
+ ptif_test0_index | ptif_test_index | 0 | f | ptif_test_index
+ ptif_test01_index | ptif_test0_index | 1 | t | ptif_test_index
(2 rows)
-- List from leaf index
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test01_index') p
JOIN pg_class c ON (p.relid = c.oid);
- relid | parentrelid | level | isleaf
--------------------+------------------+-------+--------
- ptif_test01_index | ptif_test0_index | 0 | t
+ relid | parentrelid | level | isleaf | pg_partition_root
+-------------------+------------------+-------+--------+-------------------
+ ptif_test01_index | ptif_test0_index | 0 | t | ptif_test_index
(1 row)
DROP TABLE ptif_test;
@@ -102,6 +120,12 @@ SELECT relid, parentrelid, level, isleaf
ptif_normal_table | | 0 | t
(1 row)
+SELECT pg_partition_root('ptif_normal_table');
+ pg_partition_root
+-------------------
+ ptif_normal_table
+(1 row)
+
DROP TABLE ptif_normal_table;
-- Views and materialized viewS cannot be part of a partition tree.
CREATE VIEW ptif_test_view AS SELECT 1;
@@ -110,5 +134,9 @@ SELECT * FROM pg_partition_tree('ptif_test_view');
ERROR: "ptif_test_view" is not a table, a foreign table, or an index
SELECT * FROM pg_partition_tree('ptif_test_matview');
ERROR: "ptif_test_matview" is not a table, a foreign table, or an index
+SELECT pg_partition_root('ptif_test_view');
+ERROR: "ptif_test_view" is not a table, a foreign table, or an index
+SELECT pg_partition_root('ptif_test_matview');
+ERROR: "ptif_test_matview" is not a table, a foreign table, or an index
DROP VIEW ptif_test_view;
DROP MATERIALIZED VIEW ptif_test_matview;
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql
index 5a76f22b05..f855d26174 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -2,6 +2,9 @@
-- Tests for pg_partition_tree
--
SELECT * FROM pg_partition_tree(NULL);
+SELECT * FROM pg_partition_tree(0);
+SELECT pg_partition_root(NULL);
+SELECT pg_partition_root(0);
-- Test table partition trees
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
@@ -28,26 +31,26 @@ CREATE INDEX ptif_test2_index ON ptif_test2 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
-- List all tables members of the tree
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test');
-- List tables from an intermediate level
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test0') p
JOIN pg_class c ON (p.relid = c.oid);
-- List from leaf table
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test01') p
JOIN pg_class c ON (p.relid = c.oid);
-- List all indexes members of the tree
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test_index');
-- List indexes from an intermediate level
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test0_index') p
JOIN pg_class c ON (p.relid = c.oid);
-- List from leaf index
-SELECT relid, parentrelid, level, isleaf
+SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid)
FROM pg_partition_tree('ptif_test01_index') p
JOIN pg_class c ON (p.relid = c.oid);
@@ -57,6 +60,7 @@ DROP TABLE ptif_test;
CREATE TABLE ptif_normal_table(a int);
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_normal_table');
+SELECT pg_partition_root('ptif_normal_table');
DROP TABLE ptif_normal_table;
-- Views and materialized viewS cannot be part of a partition tree.
@@ -64,5 +68,7 @@ CREATE VIEW ptif_test_view AS SELECT 1;
CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
SELECT * FROM pg_partition_tree('ptif_test_view');
SELECT * FROM pg_partition_tree('ptif_test_matview');
+SELECT pg_partition_root('ptif_test_view');
+SELECT pg_partition_root('ptif_test_matview');
DROP VIEW ptif_test_view;
DROP MATERIALIZED VIEW ptif_test_matview;
signature.asc
Description: PGP signature
