Hi Jesper,
On 2018/07/20 21:26, Jesper Pedersen wrote:
> On 07/19/2018 10:27 PM, Amit Langote wrote:
>> On 2018/07/19 23:18, Jesper Pedersen wrote:
>>> What about renaming pg_partition_tree_tables() to pg_partition_children(),
>>> and have it work like
>>>
>>> select * from pg_partition_children('p', true);
>>> ---------
>>> p
>>> p0
>>> p1
>>> p00
>>> p01
>>> p10
>>> p11
>>> (7 rows)
>>>
>>> select * from pg_partition_children('p', false);
>>> ---------
>>> p0
>>> p1
>>> (2 rows)
>>>
>>> e.g. if 'bool include_all' is true all nodes under the node, including
>>> itself, are fetched. With false only nodes directly under the node,
>>> excluding itself, are returned. If there are no children NULL is returned.
>>
>> That's a big change to make to what this function does, but if that's
>> what's useful we could make it. As an alternative, wouldn't it help to
>> implement the idea that Dilip mentioned upthread of providing a function
>> to report the level of a given table in the partition hierarchy -- 0 for
>> root, 1 for its partitions and so on?
>
> Yes, Dilip's idea could work. I just don't think that
> pg_partition_tree_tables() as is would have a benefit over time.
Alright, I have replaced pg_partition_tree_tables with
pg_partition_children with an 'include_all' argument, as you suggested,
but I implemented it as an optional argument. So, one would use that
argument only if need to get *all* partitions. I have also added a
pg_partition_leaf_children() that returns just the leaf partitions, which
wasn't there in the previous versions.
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?
>> Basically, as also discussed before, users can already use SQL to get the
>> information they want out of the relevant catalogs (pg_inherits, etc.).
>> But, such user queries might not be very future-proof as we might want to
>> change the catalog organization in the future, so we'd like to provide
>> users a proper interface to begin with. Keeping that in mind, it'd be
>> better to think carefully about what we ought to be doing here. Input
>> like yours is greatly helpful for that.
>>
>
> We could have the patch include pg_partition_root_parent and
> pg_partition_parent, and leave the rest for a future CommitFest such that
> more people could provide feedback on what they would like to see in this
> space.
Yeah, that would be appreciated.
>>> Yeah, but I was thinking that the function would only return the number of
>>> actual tables that contains data, e.g. not include 'p', 'p0' and 'p1' in
>>> the count; otherwise you could use 'select count(*) from
>>> pg_partition_children('p', true)' like you said.
>>
>> Maybe call it pg_partition_tree_leaf_count() or some such then?
>
> That could work.
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.
Thanks for the feedback.
Regards,
Amit
>From 425241ff8a35eea0d23add53c8feed601f5e076c Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Tue, 16 Jan 2018 19:02:13 +0900
Subject: [PATCH v5] 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 | 47 ++++++
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/partition_info.out | 161 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/partition_info.sql | 80 +++++++++
9 files changed, 636 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..829fcae9dd 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>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get level of a partition within partition tree with respect to
the topmost 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>
+ <row>
+
<entry><literal><function>pg_partition_tree_leaf_count(<type>regclass</type>)</function></literal></entry>
+ <entry><type>integer</type></entry>
+ <entry>get number of leaf tables in partition tree with given table as
top-most parent</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..299d8e79e7 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);
+ List *ancestors = get_partition_ancestors(reloid);
+ int 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;
+}
+
+/*
+ * Returns number of leaf partitions tables in a partition tree
+ */
+Datum
+pg_partition_children_leaf_count(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ List *partitions = find_all_inheritors(reloid, NoLock, NULL);
+ ListCell *lc;
+ int result = 0;
+
+ foreach(lc, partitions)
+ {
+ if (get_rel_relkind(lfirst_oid(lc)) !=
RELKIND_PARTITIONED_TABLE)
+ result++;
+ }
+
+ list_free(partitions);
+ PG_RETURN_INT32(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..8b97bdbbde 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,51 @@
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 where a partition is at in the partition tree
+{ oid => '3425', descr => 'level of a partition in the partition tree',
+ proname => 'pg_partition_level', prorettype => 'int4',
+ proargtypes => 'regclass', prosrc => 'pg_partition_level' },
+
+# function to get OIDs of all tables in a given partition tree
+{ oid => '3426', 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 => '3427', 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 => '3428', 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' }
+
+# function to get the number of leaf partitions in a given partition tree
+{ oid => '3429', descr => 'number of leaf partitions in the partition tree',
+ proname => 'pg_partition_children_leaf_count', prorettype => 'int4',
+ proargtypes => 'regclass', prosrc => 'pg_partition_children_leaf_count' },
+
]
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..86046b7f7b
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,161 @@
+--
+-- 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)
+
+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)
+
+-- 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 pg_partition_children_leaf_count('ptif_test') as num_tables;
+ num_tables
+------------
+ 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..6c031e8198
--- /dev/null
+++ b/src/test/regress/sql/partition_info.sql
@@ -0,0 +1,80 @@
+--
+-- 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;
+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
+
+-- 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 pg_partition_children_leaf_count('ptif_test') as num_tables;
+
+drop table ptif_test;
--
2.11.0
