On Fri, Dec 07, 2018 at 11:46:05AM +0900, Michael Paquier wrote: > On Thu, Dec 06, 2018 at 10:48:59PM -0300, Alvaro Herrera wrote: >> I think adding a pg_partition_root() call to as many pg_partition_tree >> tests as you modified is overkill ... OTOH I'd have one test that >> invokes pg_partition_tree(pg_partition_root(some-partition)) to verify >> that starting from any point in the tree you get the whole tree. > > Good idea, thanks for the input.
The recent commit cc53123 has fixed a couple of issues with pg_partition_tree, so attached is a rebased patch which similarly makes pg_partition_root return NULL for unsupported relkinds and undefined relations. I have also simplified the tests based on Alvaro's suggestion to use pg_partition_tree(pg_partition_root(partfoo)). 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 6fb4f6bc50..a354a7cc7b 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -25,6 +25,34 @@
#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, by either raising an error or doing something
+ * else.
+ */
+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)
+ return false;
+
+ return true;
+}
/*
* pg_partition_tree
@@ -39,19 +67,10 @@ 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;
- if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(rootrelid)))
- PG_RETURN_NULL();
-
- /* Return NULL for relation types that cannot appear in partition trees */
- if (relkind != RELKIND_RELATION &&
- relkind != RELKIND_FOREIGN_TABLE &&
- relkind != RELKIND_INDEX &&
- relkind != RELKIND_PARTITIONED_TABLE &&
- relkind != RELKIND_PARTITIONED_INDEX)
+ if (!check_rel_for_partition_info(rootrelid))
PG_RETURN_NULL();
/* stuff done only on the first call of the function */
@@ -153,3 +172,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 f79fcfe029..80ace84df6 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 202d820827..30d5a50f76 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -1,5 +1,5 @@
--
--- Tests for pg_partition_tree
+-- Tests for functions providing information about partitions
--
SELECT * FROM pg_partition_tree(NULL);
relid | parentrelid | isleaf | level
@@ -12,6 +12,18 @@ SELECT * FROM pg_partition_tree(0);
| | |
(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
@@ -66,6 +78,20 @@ SELECT relid, parentrelid, level, isleaf
ptif_test01 | ptif_test0 | 0 | t
(1 row)
+-- List all members using pg_partition_root with leaf table reference
+SELECT relid, parentrelid, level, isleaf
+ FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
+ JOIN pg_class c ON (p.relid = c.oid);
+ 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
+(6 rows)
+
-- List all indexes members of the tree
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test_index');
@@ -98,8 +124,22 @@ SELECT relid, parentrelid, level, isleaf
ptif_test01_index | ptif_test0_index | 0 | t
(1 row)
+-- List all members using pg_partition_root with leaf index reference
+SELECT relid, parentrelid, level, isleaf
+ FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
+ JOIN pg_class c ON (p.relid = c.oid);
+ 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
+(6 rows)
+
DROP TABLE ptif_test;
--- A table not part of a partition tree works is the only member listed.
+-- A table not part of a partition tree is the only member listed.
CREATE TABLE ptif_normal_table(a int);
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_normal_table');
@@ -108,6 +148,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;
@@ -124,5 +170,17 @@ SELECT * FROM pg_partition_tree('ptif_test_matview');
| | |
(1 row)
+SELECT pg_partition_root('ptif_test_view');
+ pg_partition_root
+-------------------
+
+(1 row)
+
+SELECT pg_partition_root('ptif_test_matview');
+ pg_partition_root
+-------------------
+
+(1 row)
+
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 9b55a7fe5c..bbe9a6911e 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -1,8 +1,10 @@
--
--- Tests for pg_partition_tree
+-- Tests for functions providing information about partitions
--
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);
@@ -39,6 +41,10 @@ SELECT relid, parentrelid, level, isleaf
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test01') p
JOIN pg_class c ON (p.relid = c.oid);
+-- List all members using pg_partition_root with leaf table reference
+SELECT relid, parentrelid, level, isleaf
+ FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
+ JOIN pg_class c ON (p.relid = c.oid);
-- List all indexes members of the tree
SELECT relid, parentrelid, level, isleaf
@@ -51,13 +57,18 @@ SELECT relid, parentrelid, level, isleaf
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test01_index') p
JOIN pg_class c ON (p.relid = c.oid);
+-- List all members using pg_partition_root with leaf index reference
+SELECT relid, parentrelid, level, isleaf
+ FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
+ JOIN pg_class c ON (p.relid = c.oid);
DROP TABLE ptif_test;
--- A table not part of a partition tree works is the only member listed.
+-- A table not part of a partition tree is the only member listed.
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.
@@ -65,5 +76,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
