From: Michael Paquier <michael@paquier.xyz>
To: Postgres hackers <pgsql-hackers@postgresql.org>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Subject: Add pg_partition_root to get top-most parent of a partition tree
Date: Fri, 7 Dec 2018 10:40:15 +0900
Message-ID: <20181207014015.GP2407@paquier.xyz> (raw)
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.GO2407@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
Attachments:
[text/x-diff] partition-root-v1.patch (13.7K, ../20181207014015.GP2407@paquier.xyz/2-partition-root-v1.patch)
download | inline diff:diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex 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.cindex 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.datindex 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.outindex 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.sqlindex 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;
[application/pgp-signature] signature.asc (832B, ../20181207014015.GP2407@paquier.xyz/3-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: michael@paquier.xyz, Langote_Amit_f8@lab.ntt.co.jp, alvherre@2ndquadrant.com
Subject: Re: Add pg_partition_root to get top-most parent of a partition tree
In-Reply-To: <20181207014015.GP2407@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox