On Fri, Dec 14, 2018 at 02:20:27PM +0900, Amit Langote wrote: > Given that pg_partition_root will return a valid result for any relation > that can be part of a partition tree, it seems strange that the above > sentence says "for the given partitioned table or partitioned index". It > should perhaps say: > > Return the top-most parent of the partition tree to which the given > relation belongs
Check.
> +static bool
> +check_rel_for_partition_info(Oid relid)
> +{
> + char relkind;
> +
> + /* Check if relation exists */
> + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(relid)))
> + return false;
>
> This should be checked in the caller imho.
On this one I disagree, both pg_partition_root and pg_partition_tree
share the same semantics on the matter. If the set of functions gets
expanded again later on, I got the feeling that we could forget about it
again, and at least placing the check here has the merit to make out
future selves not forget about that pattern..
> I can't imagine this function growing more code to perform additional
> checks beside just checking the relkind, so the name of this function may
> be a bit too ambitious. How about calling it
> check_rel_can_be_partition()? The comment above the function could be a
> much simpler sentence too. I know I may be just bikeshedding here
> though.
The review is also here for that. The routine name you are suggesting
looks good to me.
> + /*
> + * If the relation is not a partition, return itself as a result.
> + */
> + if (!get_rel_relispartition(relid))
> + PG_RETURN_OID(relid);
>
> Maybe the comment here could say "The relation itself may be the root
> parent".
Check. I tweaked the comment in this sense.
> "valid" is duplicated in the last sentence in the comment. Anyway, what's
> being Asserted can be described simply as:
>
> /*
> * 'rootrelid' must contain a valid OID, given that the input relation is
> * a valid partition tree member as checked above.
> */
Changed in this sense. Please find attached an updated patch.
--
Michael
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b3336ea9be..b328c31637 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 to which the given
+ relation belongs.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt/partitionfuncs.c
index 6fb4f6bc50..55588a8fd3 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_can_be_partition(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_can_be_partition(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_can_be_partition(relid))
+ PG_RETURN_NULL();
+
+ /*
+ * If the relation is not a partition (it may be the partition parent),
+ * 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);
+
+ /*
+ * "rootrelid" must contain a valid OID, given that the input relation
+ * is a valid partition tree member as checked 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
