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