Hi Jesper,
Thanks for the quick feedback.
On 2018/07/27 1:30, Jesper Pedersen wrote:
> On 07/26/2018 04:47 AM, Amit Langote wrote:
>> Further, I've added a pg_partition_level that returns the level of a
>> partition in the partition tree wrt to the root of the *whole* partition
>> tree. But maybe we want this function to accept one more argument,
>> 'rootoid', the OID of the root table against which to measure the level?
>
> I don't think that is needed, or it should at least be an optional parameter.
Optional parameter sounds good, so made it get_partition_level(regclass [
, regclass ]) in the updated patch. Although, adding that argument is not
without possible surprises its result might evoke. Like, what happens if
you try to find the level of the root table by passing a leaf partition
oid for the root table argument, or pass a totally unrelated table for the
root table argument. For now, I've made the function return 0 for such cases.
>> OK, I fixed it to return just the count of leaf partitions and renamed it
>> as such (pg_partition_children_leaf_count), but wonder if it's been made
>> redundant by the addition of pg_partition_leaf_children.
>>
>
> I think with pg_partition_leaf_children that we don't need the _count
> method, called pg_partition_tree_leaf_count in the docs, as we can just do
> a COUNT().
Ah, okay. Removed pg_partition_tree_leaf_count.
Thanks,
Amit
>From b29895cc1fd876937da1d568d0bd009725bcfb60 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 16 Jan 2018 19:02:13 +0900
Subject: [PATCH v6] Add assorted partition reporting functions
---
doc/src/sgml/func.sgml | 86 ++++++++++
src/backend/catalog/partition.c | 241 ++++++++++++++++++++++++++-
src/backend/utils/cache/lsyscache.c | 22 +++
src/include/catalog/pg_proc.dat | 48 ++++++
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/partition_info.out | 204 +++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/partition_info.sql | 91 ++++++++++
9 files changed, 691 insertions(+), 5 deletions(-)
create mode 100644 src/test/regress/expected/partition_info.out
create mode 100644 src/test/regress/sql/partition_info.sql
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index edc9be92a6..e1b7ace898 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,92 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
The function returns the number of new collation objects it created.
</para>
+ <table id="functions-info-partition">
+ <title>Partitioning Information Functions</title>
+ <tgroup cols="3">
+ <thead>
+ <row><entry>Name</entry> <entry>Return Type</entry>
<entry>Description</entry></row>
+ </thead>
+
+ <tbody>
+ <row>
+
<entry><literal><function>pg_partition_parent(<type>regclass</type>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get parent if table is a partition, <literal>NULL</literal>
otherwise</entry>
+ </row>
+ <row>
+
<entry><literal><function>pg_partition_root_parent(<type>regclass</type>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get topmost parent of a partition within partition tree</entry>
+ </row>
+ <row>
+ <entry><literal><function>pg_partition_level(<type>regclass</type>,
<type>regclass</type>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get level of a partition within partition tree with respect to
given parent</entry>
+ </row>
+ <row>
+
<entry><literal><function>pg_partition_level(<type>regclass</type>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get level of a partition within partition tree with respect to
topmost root parent</entry>
+ </row>
+ <row>
+ <entry><literal><function>pg_partition_children(<type>regclass</type>,
<type>bool</type>)</function></literal></entry>
+ <entry><type>setof regclass</type></entry>
+ <entry>
+ get partitions of a table; only immediate partitions are returned,
+ unless all tables in the partition tree, including itself and
+ partitions of lower levels, are requested by passing
+ <literal>true</literal> for second argument
+ </entry>
+ </row>
+ <row>
+
<entry><literal><function>pg_partition_children(<type>regclass</type>)</function></literal></entry>
+ <entry><type>setof regclass</type></entry>
+ <entry>Shorthand for <literal>pg_partition_children(...,
false)</literal></entry>
+ </row>
+ <row>
+
<entry><literal><function>pg_partition_leaf_children(<type>regclass</type>)</function></literal></entry>
+ <entry><type>setof regclass</type></entry>
+ <entry>get all leaf partitions of a given table</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ If the table passed to <function>pg_partition_root_parent</function> is not
+ a partition, the same table is returned as the result.
+ </para>
+
+ <para>
+ For example, to check the total size of the data contained in
+ <structname>measurement</structname> table described in
+ <xref linkend="ddl-partitioning-declarative-example"/>, use the following
+ query:
+ </para>
+
+<programlisting>
+select pg_size_pretty(sum(pg_relation_size(p))) as total_size from
pg_partition_children('measurement', true) p;
+ total_size
+------------
+ 24 kB
+(1 row)
+</programlisting>
+
+ <para>
+ One could have used <function>pg_partition_leaf_children</function> in
+ this case and got the same result as shown below:
+ </para>
+
+<programlisting>
+select pg_size_pretty(sum(pg_relation_size(p))) as total_size from
pg_partition_leaf_children('measurement') p;
+ total_size
+------------
+ 24 kB
+(1 row)
+</programlisting>
+
+
</sect2>
<sect2 id="functions-admin-index">
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 558022647c..9d11d1ea16 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -23,13 +23,16 @@
#include "catalog/partition.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_partitioned_table.h"
+#include "funcapi.h"
#include "nodes/makefuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/prep.h"
#include "optimizer/var.h"
#include "partitioning/partbounds.h"
#include "rewrite/rewriteManip.h"
+#include "utils/builtins.h"
#include "utils/fmgroids.h"
+#include "utils/lsyscache.h"
#include "utils/partcache.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -38,16 +41,14 @@
static Oid get_partition_parent_worker(Relation inhRel, Oid relid);
static void get_partition_ancestors_worker(Relation inhRel, Oid relid,
List **ancestors);
+static Oid get_partition_root_parent(Oid relid);
+static List *get_partition_tree_leaf_tables(Oid relid);
/*
* get_partition_parent
* Obtain direct parent of given relation
*
* Returns inheritance parent of a partition by scanning pg_inherits
- *
- * Note: Because this function assumes that the relation whose OID is passed
- * as an argument will have precisely one parent, it should only be called
- * when it is known that the relation is a partition.
*/
Oid
get_partition_parent(Oid relid)
@@ -55,6 +56,9 @@ get_partition_parent(Oid relid)
Relation catalogRelation;
Oid result;
+ if (!get_rel_relispartition(relid))
+ return InvalidOid;
+
catalogRelation = heap_open(InheritsRelationId, AccessShareLock);
result = get_partition_parent_worker(catalogRelation, relid);
@@ -71,6 +75,10 @@ get_partition_parent(Oid relid)
* get_partition_parent_worker
* Scan the pg_inherits relation to return the OID of the parent
of the
* given relation
+ *
+ * Note: Because this function assumes that the relation whose OID is passed
+ * as an argument will have precisely one parent, it should only be called
+ * when it is known that the relation is a partition.
*/
static Oid
get_partition_parent_worker(Relation inhRel, Oid relid)
@@ -148,6 +156,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid,
List **ancestors)
}
/*
+ * get_partition_root_parent
+ *
+ * Returns root inheritance ancestor of a partition.
+ */
+Oid
+get_partition_root_parent(Oid relid)
+{
+ List *ancestors;
+ Oid result;
+
+ if (!get_rel_relispartition(relid))
+ return InvalidOid;
+
+ ancestors = get_partition_ancestors(relid);
+ result = llast_oid(ancestors);
+ Assert(!get_rel_relispartition(result));
+ list_free(ancestors);
+
+ return result;
+}
+
+/*
* map_partition_varattnos - maps varattno of any Vars in expr from the
* attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which
* may be either a leaf partition or a partitioned table, but both of which
@@ -357,3 +387,206 @@ get_proposed_default_constraint(List
*new_part_constraints)
return make_ands_implicit(defPartConstraint);
}
+
+/*
+ * SQL wrapper around get_partition_root_parent().
+ */
+Datum
+pg_partition_root_parent(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid rootoid;
+
+ rootoid = get_partition_root_parent(reloid);
+
+ /*
+ * If the relation is actually a partition, 'rootoid' has been set to
the
+ * OID of the root table in the partition tree.
+ */
+ if (OidIsValid(rootoid))
+ PG_RETURN_OID(rootoid);
+
+ /*
+ * Otherwise, the table's not a partition. That is, it's either the
root
+ * table in a partition tree or a standalone table that's not part of
any
+ * partition tree. In any case, return the table OID itself as the
+ * result.
+ */
+ PG_RETURN_OID(reloid);
+}
+
+/*
+ * SQL wrapper around get_partition_parent().
+ */
+Datum
+pg_partition_parent(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid parentoid;
+
+ parentoid = get_partition_parent(reloid);
+
+ /*
+ * If the relation is actually a partition, 'parentoid' has been set to
+ * the OID of the immediate parent.
+ */
+ if (OidIsValid(parentoid))
+ PG_RETURN_OID(parentoid);
+
+ /* Not a partition, return NULL. */
+ PG_RETURN_NULL();
+}
+
+/*
+ * Returns an integer representing the level a given partition is at in the
+ * partition tree that it's part of.
+ */
+Datum
+pg_partition_level(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid rootoid = PG_GETARG_OID(1);
+ List *ancestors = get_partition_ancestors(reloid);
+ int level;
+
+ /* If root is specified, find reloid's distance from it. */
+ if (OidIsValid(rootoid))
+ {
+ ListCell *lc;
+
+ if (!list_member_oid(ancestors, rootoid))
+ return 0;
+
+ level = 0;
+ foreach(lc, ancestors)
+ {
+ level++;
+ if (lfirst_oid(lc) == rootoid)
+ break;
+ }
+ }
+ else
+ /* Distance from the root of the whole tree. */
+ level = list_length(ancestors);
+
+ list_free(ancestors);
+ PG_RETURN_INT32(level);
+}
+
+/*
+ * Returns OIDs of tables in a partition tree.
+ */
+Datum
+pg_partition_children(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ Oid reloid = PG_GETARG_OID(0);
+ bool include_all = PG_GETARG_BOOL(1);
+ List *partoids;
+ ListCell **lc;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* switch to memory context appropriate for multiple function
calls */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (include_all)
+ partoids = find_all_inheritors(reloid, NoLock, NULL);
+ else
+ partoids = find_inheritance_children(reloid, NoLock);
+ lc = (ListCell **) palloc(sizeof(ListCell *));
+ *lc = list_head(partoids);
+
+ funcctx->user_fctx = (void *) lc;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ lc = (ListCell **) funcctx->user_fctx;
+
+ while (*lc != NULL)
+ {
+ Oid partoid = lfirst_oid(*lc);
+
+ *lc = lnext(*lc);
+ SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid));
+ }
+
+ SRF_RETURN_DONE(funcctx);
+}
+
+/*
+ * Returns OIDs of leaf tables in a partition tree.
+ */
+Datum
+pg_partition_leaf_children(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ Oid reloid = PG_GETARG_OID(0);
+ List *leafoids;
+ ListCell **lc;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* switch to memory context appropriate for multiple function
calls */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ leafoids = get_partition_tree_leaf_tables(reloid);
+ lc = (ListCell **) palloc(sizeof(ListCell *));
+ *lc = list_head(leafoids);
+
+ funcctx->user_fctx = (void *) lc;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ lc = (ListCell **) funcctx->user_fctx;
+
+ while (*lc != NULL)
+ {
+ Oid partoid = lfirst_oid(*lc);
+
+ *lc = lnext(*lc);
+ SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid));
+ }
+
+ SRF_RETURN_DONE(funcctx);
+}
+
+/*
+ * Returns number of leaf partitions tables in a partition tree
+ */
+static List *
+get_partition_tree_leaf_tables(Oid relid)
+{
+ List *partitions = find_all_inheritors(relid, NoLock, NULL);
+ ListCell *lc;
+ List *result = NIL;
+
+ foreach(lc, partitions)
+ {
+ Oid partoid = lfirst_oid(lc);
+
+ if (get_rel_relkind(partoid) != RELKIND_PARTITIONED_TABLE)
+ result = lappend_oid(result, partoid);
+ }
+
+ list_free(partitions);
+ return result;
+}
diff --git a/src/backend/utils/cache/lsyscache.c
b/src/backend/utils/cache/lsyscache.c
index bba595ad1d..19262c6c4d 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid)
}
/*
+ * get_rel_relispartition
+ *
+ * Returns the value of pg_class.relispartition for a given
relation.
+ */
+char
+get_rel_relispartition(Oid relid)
+{
+ HeapTuple tp;
+ Form_pg_class reltup;
+ bool result;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+ result = reltup->relispartition;
+ ReleaseSysCache(tp);
+
+ return result;
+}
+
+/*
* get_rel_tablespace
*
* Returns the pg_tablespace OID associated with a given relation.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a14651010f..e1d190f81a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,52 @@
proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any',
proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' },
+# function to get the root partition parent
+{ oid => '3423', descr => 'oid of the partition root parent',
+ proname => 'pg_partition_root_parent', prorettype => 'regclass',
+ proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' },
+
+# function to get the partition parent
+{ oid => '3424', descr => 'oid of the partition immediate parent',
+ proname => 'pg_partition_parent', prorettype => 'regclass',
+ proargtypes => 'regclass', prosrc => 'pg_partition_parent' },
+
+# function to get the level of a partition in the partition tree of given root
+{ oid => '3425', descr => 'level of a partition in the partition tree for
given root table',
+ proname => 'pg_partition_level', prorettype => 'int4',
+ proargtypes => 'regclass regclass', prosrc => 'pg_partition_level' },
+
+# function to get the level of a partition in the whole partition tree
+{ oid => '3426', descr => 'level of a partition in the partition tree',
+ proname => 'pg_partition_level', prolang => '14', prorettype => 'int4',
+ proargtypes => 'regclass',
+ prosrc => 'select pg_catalog.pg_partition_level($1, \'0\')' },
+
+# function to get OIDs of all tables in a given partition tree
+{ oid => '3427', descr => 'get OIDs of tables in a partition tree',
+ proname => 'pg_partition_children', prorettype => 'regclass',
+ prorows => '100', proretset => 't', proargtypes => 'regclass bool',
+ proallargtypes => '{regclass,bool,regclass}',
+ proargmodes => '{i,i,o}',
+ proargnames => '{relid,include_all,relid}',
+ prosrc => 'pg_partition_children' }
+
+# function to get OIDs of immediate
+{ oid => '3428', descr => 'get OIDs of tables in a partition tree',
+ proname => 'pg_partition_children', prolang => '14', prorettype =>
'regclass',
+ prorows => '100', proretset => 't', proargtypes => 'regclass',
+ proallargtypes => '{regclass,regclass}',
+ proargmodes => '{i,o}',
+ proargnames => '{relid,relid}',
+ prosrc => 'select pg_catalog.pg_partition_children($1, \'false\')' }
+
+# function to get OIDs of all tables in a given partition tree
+{ oid => '3429', descr => 'get OIDs of leaf partitions in a partition tree',
+ proname => 'pg_partition_leaf_children', prorettype => 'regclass',
+ prorows => '100', proretset => 't', proargtypes => 'regclass',
+ proallargtypes => '{regclass,regclass}',
+ proargmodes => '{i,o}',
+ proargnames => '{relid,relid}',
+ prosrc => 'pg_partition_leaf_children' }
+
]
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index e55ea4035b..d396d17ff1 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid);
extern Oid get_rel_namespace(Oid relid);
extern Oid get_rel_type_id(Oid relid);
extern char get_rel_relkind(Oid relid);
+extern char get_rel_relispartition(Oid relid);
extern Oid get_rel_tablespace(Oid relid);
extern char get_rel_persistence(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
diff --git a/src/test/regress/expected/partition_info.out
b/src/test/regress/expected/partition_info.out
new file mode 100644
index 0000000000..d34f3031c9
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,204 @@
+--
+-- Tests to show partition tree inspection functions
+--
+create table ptif_test (a int, b int) partition by range (a);
+create table ptif_test0 partition of ptif_test for values from (minvalue) to
(0) partition by list (b);
+create table ptif_test01 partition of ptif_test0 for values in (1);
+create table ptif_test1 partition of ptif_test for values from (0) to (100)
partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+create table ptif_test2 partition of ptif_test for values from (100) to
(maxvalue);
+insert into ptif_test select i, 1 from generate_series(-500, 500) i;
+select pg_partition_parent('ptif_test0') as parent;
+ parent
+-----------
+ ptif_test
+(1 row)
+
+select pg_partition_parent('ptif_test01') as parent;
+ parent
+------------
+ ptif_test0
+(1 row)
+
+select pg_partition_root_parent('ptif_test01') as root_parent;
+ root_parent
+-------------
+ ptif_test
+(1 row)
+
+-- pg_partition_level where partition level wrt whole-tree root is returned
+select pg_partition_level('ptif_test01') as level; -- 2
+ level
+-------
+ 2
+(1 row)
+
+select pg_partition_level('ptif_test0') as level; -- 1
+ level
+-------
+ 1
+(1 row)
+
+select pg_partition_level('ptif_test') as level; -- 0
+ level
+-------
+ 0
+(1 row)
+
+select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2
+ level
+-------
+ 2
+(1 row)
+
+select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1
+ level
+-------
+ 1
+(1 row)
+
+select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0
+ level
+-------
+ 0
+(1 row)
+
+select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1
+ level
+-------
+ 1
+(1 row)
+
+select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0
+ level
+-------
+ 0
+(1 row)
+
+select pg_partition_level('ptif_test0', 'ptif_test0') as level;
-- 0
+ level
+-------
+ 0
+(1 row)
+
+select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- 0
+ level
+-------
+ 0
+(1 row)
+
+-- all tables in the tree
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test', true) p;
+ relname | level | parent | root_parent | size
+-------------+-------+------------+-------------+-------
+ ptif_test | 0 | | ptif_test | 0
+ ptif_test0 | 1 | ptif_test | ptif_test | 0
+ ptif_test1 | 1 | ptif_test | ptif_test | 0
+ ptif_test2 | 1 | ptif_test | ptif_test | 16384
+ ptif_test01 | 2 | ptif_test0 | ptif_test | 24576
+ ptif_test11 | 2 | ptif_test1 | ptif_test | 8192
+(6 rows)
+
+-- only leaf partitions in the tree
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_leaf_children('ptif_test') p;
+ relname | level | parent | root_parent | size
+-------------+-------+------------+-------------+-------
+ ptif_test2 | 1 | ptif_test | ptif_test | 16384
+ ptif_test01 | 2 | ptif_test0 | ptif_test | 24576
+ ptif_test11 | 2 | ptif_test1 | ptif_test | 8192
+(3 rows)
+
+-- total relation size grouped by level
+select pg_partition_level(p) as level,
+ sum(pg_relation_size(p)) as level_size
+from pg_partition_children('ptif_test', true) p
+group by level order by level;
+ level | level_size
+-------+------------
+ 0 | 0
+ 1 | 16384
+ 2 | 32768
+(3 rows)
+
+-- total relation size of the whole tree
+select sum(pg_relation_size(p)) as total_size
+from pg_partition_children('ptif_test', true) p;
+ total_size
+------------
+ 49152
+(1 row)
+
+-- total relation size of the leaf partitions; should be same as above
+select sum(pg_relation_size(p)) as total_size
+from pg_partition_leaf_children('ptif_test') p;
+ total_size
+------------
+ 49152
+(1 row)
+
+-- immediate partitions only
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test', false) p;
+ relname | level | parent | root_parent | size
+------------+-------+-----------+-------------+-------
+ ptif_test0 | 1 | ptif_test | ptif_test | 0
+ ptif_test1 | 1 | ptif_test | ptif_test | 0
+ ptif_test2 | 1 | ptif_test | ptif_test | 16384
+(3 rows)
+
+-- could also be written as
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test') p;
+ relname | level | parent | root_parent | size
+------------+-------+-----------+-------------+-------
+ ptif_test0 | 1 | ptif_test | ptif_test | 0
+ ptif_test1 | 1 | ptif_test | ptif_test | 0
+ ptif_test2 | 1 | ptif_test | ptif_test | 16384
+(3 rows)
+
+-- immedidate partitions of ptif_test0, which is a non-root partitioned table
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test0') p;
+ relname | level | parent | root_parent | size
+-------------+-------+------------+-------------+-------
+ ptif_test01 | 2 | ptif_test0 | ptif_test | 24576
+(1 row)
+
+-- total size of first level partitions
+select sum(pg_relation_size(p)) as total_size
+from pg_partition_children('ptif_test') p;
+ total_size
+------------
+ 16384
+(1 row)
+
+-- number of leaf partitions in the tree
+select count(*) from pg_partition_leaf_children('ptif_test') as num_tables;
+ count
+-------
+ 3
+(1 row)
+
+drop table ptif_test;
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index 16f979c8d9..6cb820bbc4 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs
prepare without_oid c
# ----------
# Another group of parallel tests
# ----------
-test: identity partition_join partition_prune reloptions hash_part indexing
partition_aggregate
+test: identity partition_join partition_prune reloptions hash_part indexing
partition_aggregate partition_info
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 42632be675..7e374c2daa 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -188,6 +188,7 @@ test: reloptions
test: hash_part
test: indexing
test: partition_aggregate
+test: partition_info
test: event_trigger
test: fast_default
test: stats
diff --git a/src/test/regress/sql/partition_info.sql
b/src/test/regress/sql/partition_info.sql
new file mode 100644
index 0000000000..e3b2fa0be0
--- /dev/null
+++ b/src/test/regress/sql/partition_info.sql
@@ -0,0 +1,91 @@
+--
+-- Tests to show partition tree inspection functions
+--
+create table ptif_test (a int, b int) partition by range (a);
+create table ptif_test0 partition of ptif_test for values from (minvalue) to
(0) partition by list (b);
+create table ptif_test01 partition of ptif_test0 for values in (1);
+create table ptif_test1 partition of ptif_test for values from (0) to (100)
partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+create table ptif_test2 partition of ptif_test for values from (100) to
(maxvalue);
+insert into ptif_test select i, 1 from generate_series(-500, 500) i;
+
+select pg_partition_parent('ptif_test0') as parent;
+select pg_partition_parent('ptif_test01') as parent;
+select pg_partition_root_parent('ptif_test01') as root_parent;
+
+-- pg_partition_level where partition level wrt whole-tree root is returned
+select pg_partition_level('ptif_test01') as level; -- 2
+select pg_partition_level('ptif_test0') as level; -- 1
+select pg_partition_level('ptif_test') as level; -- 0
+
+select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2
+select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1
+select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0
+
+select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1
+select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0
+select pg_partition_level('ptif_test0', 'ptif_test0') as level;
-- 0
+select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- 0
+
+-- all tables in the tree
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test', true) p;
+
+-- only leaf partitions in the tree
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_leaf_children('ptif_test') p;
+
+-- total relation size grouped by level
+select pg_partition_level(p) as level,
+ sum(pg_relation_size(p)) as level_size
+from pg_partition_children('ptif_test', true) p
+group by level order by level;
+
+-- total relation size of the whole tree
+select sum(pg_relation_size(p)) as total_size
+from pg_partition_children('ptif_test', true) p;
+
+-- total relation size of the leaf partitions; should be same as above
+select sum(pg_relation_size(p)) as total_size
+from pg_partition_leaf_children('ptif_test') p;
+
+-- immediate partitions only
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test', false) p;
+
+-- could also be written as
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test') p;
+
+-- immedidate partitions of ptif_test0, which is a non-root partitioned table
+select p as relname,
+ pg_partition_level(p) as level,
+ pg_partition_parent(p) as parent,
+ pg_partition_root_parent(p) as root_parent,
+ pg_relation_size(p) as size
+from pg_partition_children('ptif_test0') p;
+
+-- total size of first level partitions
+select sum(pg_relation_size(p)) as total_size
+from pg_partition_children('ptif_test') p;
+
+-- number of leaf partitions in the tree
+select count(*) from pg_partition_leaf_children('ptif_test') as num_tables;
+
+drop table ptif_test;
--
2.11.0
