agora inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH v4] Add assorted partition reporting functions
9+ messages / 2 participants
[nested] [flat]

* [PATCH v4] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 doc/src/sgml/func.sgml                       |  56 ++++++++++
 src/backend/catalog/partition.c              | 155 ++++++++++++++++++++++++++-
 src/backend/utils/cache/lsyscache.c          |  22 ++++
 src/include/catalog/partition.h              |   1 +
 src/include/catalog/pg_proc.dat              |  24 +++++
 src/include/utils/lsyscache.h                |   1 +
 src/test/regress/expected/partition_info.out |  88 +++++++++++++++
 src/test/regress/parallel_schedule           |   2 +-
 src/test/regress/serial_schedule             |   1 +
 src/test/regress/sql/partition_info.sql      |  40 +++++++
 10 files changed, 385 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..33119f2265 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,62 @@ 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(<parameter>regclass</parameter>)</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(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>regclass</type></entry>
+       <entry>get top-most parent of a partition within partition tree</entry>
+      </row>
+      <row>
+       <entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>, <parameter>bool</parameter>)</function></literal></entry>
+       <entry><type>setof regclass</type></entry>
+       <entry>get all tables in partition tree with given table as top-most parent</entry>
+      </row>
+      <row>
+       <entry><literal><function>pg_partition_tree_number_of_tables(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>integer</type></entry>
+       <entry>get number of 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.  Result of
+    <function>pg_partition_tree_tables</function> also contains the table
+    that's passed to it as the first row, unless explicitly requested not
+    to be included by passing <literal>false</literal> for the second
+    argument. 
+   </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_tree_tables('measurement', true) 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..20635ce513 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"
@@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid,
  *		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 +54,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 +73,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 +154,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 +385,122 @@ 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 OIDs of tables in a partition tree.
+ */
+Datum
+pg_partition_tree_tables(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	Oid		reloid = PG_GETARG_OID(0);
+	bool	include_self = 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);
+
+		partoids = find_all_inheritors(reloid, NoLock, NULL);
+		lc = (ListCell **) palloc(sizeof(ListCell *));
+		*lc = list_head(partoids);
+
+		/*
+		 * Passed in OID is put at the head of the list returned by
+		 * find_all_inheritors().  If user asked to not include the passed in
+		 * relid in the result, skip it by making *lc point to the next
+		 * list member.
+		 */
+		if (!include_self)
+			*lc = lnext(*lc);
+
+		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 tables in a partition tree
+ */
+Datum
+pg_partition_tree_number_of_tables(PG_FUNCTION_ARGS)
+{
+	Oid		reloid = PG_GETARG_OID(0);
+	List   *partitions = find_all_inheritors(reloid, NoLock, NULL);
+	int		result = list_length(partitions);
+
+	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/partition.h b/src/include/catalog/partition.h
index 1f49e5d3a9..a2b11f40bc 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -32,6 +32,7 @@ typedef struct PartitionDescData
 
 extern Oid	get_partition_parent(Oid relid);
 extern List *get_partition_ancestors(Oid relid);
+extern Oid	get_partition_root_parent(Oid relid);
 extern List *map_partition_varattnos(List *expr, int fromrel_varno,
 						Relation to_rel, Relation from_rel,
 						bool *found_whole_row);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a14651010f..5835a9aaf2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,28 @@
   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 OIDs of all tables in a given partition tree
+{ oid => '3425', descr => 'get OIDs of tables in a partition tree',
+  proname => 'pg_partition_tree_tables', prorettype => '2205',
+  prorows => '100', proretset => 't', proargtypes => 'regclass bool',
+  proallargtypes => '{regclass,bool,regclass}',
+  proargmodes => '{i,i,o}',
+  proargnames => '{relid,include_self,relid}',
+  prosrc => 'pg_partition_tree_tables' }
+
+# function to get the number of tables in a given partition tree
+{ oid => '3426', descr => 'number of tables in the partition tree',
+  proname => 'pg_partition_tree_number_of_tables', prorettype => 'int4',
+  proargtypes => 'regclass', prosrc => 'pg_partition_tree_number_of_tables' },
+
 ]
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..745b9fcbb5
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,88 @@
+--
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test', true) p;
+   relname   |   parent   | root_parent 
+-------------+------------+-------------
+ ptif_test   |            | ptif_test
+ ptif_test0  | ptif_test  | ptif_test
+ ptif_test1  | ptif_test  | ptif_test
+ ptif_test01 | ptif_test0 | ptif_test
+ ptif_test11 | ptif_test1 | ptif_test
+(5 rows)
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test', true) p;
+   relname   |   parent   | root_parent | size 
+-------------+------------+-------------+------
+ ptif_test   |            | ptif_test   |    0
+ ptif_test0  | ptif_test  | ptif_test   |    0
+ ptif_test1  | ptif_test  | ptif_test   |    0
+ ptif_test01 | ptif_test0 | ptif_test   | 8192
+ ptif_test11 | ptif_test1 | ptif_test   | 8192
+(5 rows)
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test', true) p;
+ total_size 
+------------
+      16384
+(1 row)
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test', false) p;
+   relname   |   parent   | root_parent | size 
+-------------+------------+-------------+------
+ ptif_test0  | ptif_test  | ptif_test   |    0
+ ptif_test1  | ptif_test  | ptif_test   |    0
+ ptif_test01 | ptif_test0 | ptif_test   | 8192
+ ptif_test11 | ptif_test1 | ptif_test   | 8192
+(4 rows)
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test', false) p;
+ total_size 
+------------
+      16384
+(1 row)
+
+select pg_partition_tree_number_of_tables('ptif_test') as num_tables;
+ num_tables 
+------------
+          5
+(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..6772740fca
--- /dev/null
+++ b/src/test/regress/sql/partition_info.sql
@@ -0,0 +1,40 @@
+--
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test', true) p;
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test', true) p;
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test', true) p;
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test', false) p;
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test', false) p;
+
+select pg_partition_tree_number_of_tables('ptif_test') as num_tables;
+
+drop table ptif_test;
-- 
2.11.0


--------------1A2E6EFB868FDFF2FD7331C8--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v6] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 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


--------------47B6CCCFBDE8802B935B0C7A--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v7] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 doc/src/sgml/func.sgml                       |  86 ++++++++++
 src/backend/catalog/partition.c              | 244 ++++++++++++++++++++++++++-
 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, 694 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..d90aaf8bb8 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,209 @@ 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 (reloid == rootoid)
+			return 0;
+
+		if (!list_member_oid(ancestors, rootoid))
+			return -1;
+
+		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..e93069913c
--- /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;	-- -1
+ level 
+-------
+    -1
+(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..5675e8c526
--- /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;	-- -1
+
+-- 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


--------------24D47EF568EBA525B01BEEC7--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v8] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 doc/src/sgml/func.sgml                       |  89 ++++++++++
 src/backend/catalog/partition.c              | 244 ++++++++++++++++++++++++++-
 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, 697 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..326f4dbe58 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,95 @@ 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, it is returned as the its own root parent.  If the table
+    passed for the second argument of <function>pg_partition_level</function>
+    is not same as the first argument or not an ancestor of the table in the
+    first argument, -1 is returned as the result.
+   </para>
+
+   <para>
+    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..d90aaf8bb8 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,209 @@ 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 (reloid == rootoid)
+			return 0;
+
+		if (!list_member_oid(ancestors, rootoid))
+			return -1;
+
+		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..e93069913c
--- /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;	-- -1
+ level 
+-------
+    -1
+(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..5675e8c526
--- /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;	-- -1
+
+-- 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


--------------87361936771E96D5B9B3CE60--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v3] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 doc/src/sgml/func.sgml                       |  49 ++++++++++
 src/backend/catalog/partition.c              | 130 ++++++++++++++++++++++++++-
 src/backend/utils/cache/lsyscache.c          |  22 +++++
 src/include/catalog/partition.h              |   1 +
 src/include/catalog/pg_proc.dat              |  18 ++++
 src/include/utils/lsyscache.h                |   1 +
 src/test/regress/expected/create_table.out   |  60 +++++++++++++
 src/test/regress/expected/partition_info.out |  62 +++++++++++++
 src/test/regress/parallel_schedule           |   2 +-
 src/test/regress/serial_schedule             |   1 +
 src/test/regress/sql/partition_info.sql      |  25 ++++++
 11 files changed, 366 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 5dce8ef178..884b9c36a5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,55 @@ 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(<parameter>regclass</parameter>)</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(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>regclass</type></entry>
+       <entry>get top-most parent of a partition within partition tree</entry>
+      </row>
+      <row>
+       <entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>setof regclass</type></entry>
+       <entry>get all 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.  Result of
+    <function>pg_partition_tree_tables</function> also contains the table
+    that's passed to it as the first row.
+   </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_tree_tables('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..9af1c25bba 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"
@@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid,
  *		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 +54,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 +73,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 +154,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 +385,97 @@ 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.
+	 */
+	else
+		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);
+	else
+	/* Not a partition, return NULL. */
+		PG_RETURN_NULL();
+}
+
+/*
+ * Returns OIDs of tables in a partition tree.
+ */
+Datum
+pg_partition_tree_tables(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	Oid		reloid = PG_GETARG_OID(0);
+	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);
+
+		partoids = find_all_inheritors(reloid, NoLock, NULL);
+		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);
+}
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/partition.h b/src/include/catalog/partition.h
index 1f49e5d3a9..a2b11f40bc 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -32,6 +32,7 @@ typedef struct PartitionDescData
 
 extern Oid	get_partition_parent(Oid relid);
 extern List *get_partition_ancestors(Oid relid);
+extern Oid	get_partition_root_parent(Oid relid);
 extern List *map_partition_varattnos(List *expr, int fromrel_varno,
 						Relation to_rel, Relation from_rel,
 						bool *found_whole_row);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 40d54ed030..b4725b8634 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,22 @@
   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 OIDs of all tables in a given partition tree
+{ oid => '3425', descr => 'get OIDs of tables in a partition tree',
+  proname => 'pg_partition_tree_tables', prorettype => '2205',
+  prorows => '100', proretset => 't', proargtypes => 'regclass',
+  proallargtypes => '{regclass,regclass}',
+  proargmodes => '{i,o}',
+  proargnames => '{relid,relid}', prosrc => 'pg_partition_tree_tables' }
+
 ]
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/create_table.out b/src/test/regress/expected/create_table.out
index 672719e5d5..f702518d4e 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -909,3 +909,63 @@ ERROR:  cannot create a temporary relation as partition of permanent relation "p
 create temp table temp_part partition of temp_parted default; -- ok
 drop table perm_parted cascade;
 drop table temp_parted cascade;
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test') p;
+   relname   |   parent   | root_parent 
+-------------+------------+-------------
+ ptif_test   |            | ptif_test
+ ptif_test0  | ptif_test  | ptif_test
+ ptif_test1  | ptif_test  | ptif_test
+ ptif_test01 | ptif_test0 | ptif_test
+ ptif_test11 | ptif_test1 | ptif_test
+(5 rows)
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test') p;
+   relname   |   parent   | root_parent | size 
+-------------+------------+-------------+------
+ ptif_test   |            | ptif_test   |    0
+ ptif_test0  | ptif_test  | ptif_test   |    0
+ ptif_test1  | ptif_test  | ptif_test   |    0
+ ptif_test01 | ptif_test0 | ptif_test   | 8192
+ ptif_test11 | ptif_test1 | ptif_test   | 8192
+(5 rows)
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test') p;
+ total_size 
+------------
+      16384
+(1 row)
+
+drop table ptif_test;
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
new file mode 100644
index 0000000000..dfb42c2f1b
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,62 @@
+--
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test') p;
+   relname   |   parent   | root_parent 
+-------------+------------+-------------
+ ptif_test   |            | ptif_test
+ ptif_test0  | ptif_test  | ptif_test
+ ptif_test1  | ptif_test  | ptif_test
+ ptif_test01 | ptif_test0 | ptif_test
+ ptif_test11 | ptif_test1 | ptif_test
+(5 rows)
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test') p;
+   relname   |   parent   | root_parent | size 
+-------------+------------+-------------+------
+ ptif_test   |            | ptif_test   |    0
+ ptif_test0  | ptif_test  | ptif_test   |    0
+ ptif_test1  | ptif_test  | ptif_test   |    0
+ ptif_test01 | ptif_test0 | ptif_test   | 8192
+ ptif_test11 | ptif_test1 | ptif_test   | 8192
+(5 rows)
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test') p;
+ total_size 
+------------
+      16384
+(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..35f5986ee0
--- /dev/null
+++ b/src/test/regress/sql/partition_info.sql
@@ -0,0 +1,25 @@
+--
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test') p;
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test') p;
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test') p;
+drop table ptif_test;
-- 
2.11.0


--------------811B5F71712F672BE8507357--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v2] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 doc/src/sgml/func.sgml                     |  34 ++++++++
 src/backend/catalog/partition.c            | 129 ++++++++++++++++++++++++++++-
 src/backend/utils/cache/lsyscache.c        |  22 +++++
 src/include/catalog/partition.h            |   1 +
 src/include/catalog/pg_proc.dat            |  18 ++++
 src/include/utils/lsyscache.h              |   1 +
 src/test/regress/expected/create_table.out |  60 ++++++++++++++
 src/test/regress/sql/create_table.sql      |  24 ++++++
 8 files changed, 285 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5dce8ef178..df621d1e17 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,40 @@ 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_root_parent(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>regclass</type></entry>
+       <entry>get root table of partition tree of which the table is part</entry>
+      </row>
+      <row>
+       <entry><literal><function>pg_partition_parent(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>regclass</type></entry>
+       <entry>get parent table if the table is a partition, <literal>NULL</literal> otherwise</entry>
+      </row>
+      <row>
+       <entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>)</function></literal></entry>
+       <entry><type>setof regclass</type></entry>
+       <entry>get all tables in partition tree under given root 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.  Result of
+    <function>pg_partition_tree_tables</function> also contains the table
+    that's passed to it as the first row.
+   </para>
+
   </sect2>
 
   <sect2 id="functions-admin-index">
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 558022647c..5b3e8d52c5 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"
@@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid,
  *		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 +54,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 +73,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 +154,27 @@ 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);
+	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 +384,97 @@ 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.
+	 */
+	else
+		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);
+	else
+	/* Not a partition, return NULL. */
+		PG_RETURN_NULL();
+}
+
+/*
+ * Returns OIDs of tables in a partition tree.
+ */
+Datum
+pg_partition_tree_tables(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	Oid		reloid = PG_GETARG_OID(0);
+	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);
+
+		partoids = find_all_inheritors(reloid, NoLock, NULL);
+		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);
+}
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/partition.h b/src/include/catalog/partition.h
index 1f49e5d3a9..a2b11f40bc 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -32,6 +32,7 @@ typedef struct PartitionDescData
 
 extern Oid	get_partition_parent(Oid relid);
 extern List *get_partition_ancestors(Oid relid);
+extern Oid	get_partition_root_parent(Oid relid);
 extern List *map_partition_varattnos(List *expr, int fromrel_varno,
 						Relation to_rel, Relation from_rel,
 						bool *found_whole_row);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 40d54ed030..b4725b8634 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,22 @@
   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 OIDs of all tables in a given partition tree
+{ oid => '3425', descr => 'get OIDs of tables in a partition tree',
+  proname => 'pg_partition_tree_tables', prorettype => '2205',
+  prorows => '100', proretset => 't', proargtypes => 'regclass',
+  proallargtypes => '{regclass,regclass}',
+  proargmodes => '{i,o}',
+  proargnames => '{relid,relid}', prosrc => 'pg_partition_tree_tables' }
+
 ]
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/create_table.out b/src/test/regress/expected/create_table.out
index 672719e5d5..f702518d4e 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -909,3 +909,63 @@ ERROR:  cannot create a temporary relation as partition of permanent relation "p
 create temp table temp_part partition of temp_parted default; -- ok
 drop table perm_parted cascade;
 drop table temp_parted cascade;
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test') p;
+   relname   |   parent   | root_parent 
+-------------+------------+-------------
+ ptif_test   |            | ptif_test
+ ptif_test0  | ptif_test  | ptif_test
+ ptif_test1  | ptif_test  | ptif_test
+ ptif_test01 | ptif_test0 | ptif_test
+ ptif_test11 | ptif_test1 | ptif_test
+(5 rows)
+
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test') p;
+   relname   |   parent   | root_parent | size 
+-------------+------------+-------------+------
+ ptif_test   |            | ptif_test   |    0
+ ptif_test0  | ptif_test  | ptif_test   |    0
+ ptif_test1  | ptif_test  | ptif_test   |    0
+ ptif_test01 | ptif_test0 | ptif_test   | 8192
+ ptif_test11 | ptif_test1 | ptif_test   | 8192
+(5 rows)
+
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test') p;
+ total_size 
+------------
+      16384
+(1 row)
+
+drop table ptif_test;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 78944950fe..d0a083dd69 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -735,3 +735,27 @@ create temp table temp_part partition of perm_parted default; -- error
 create temp table temp_part partition of temp_parted default; -- ok
 drop table perm_parted cascade;
 drop table temp_parted cascade;
+
+-- 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 (maxvalue) partition by list (b);
+create table ptif_test11 partition of ptif_test1 for values in (1);
+insert into ptif_test select i, 1 from generate_series(-5, 5) 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	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent
+from	pg_partition_tree_tables('ptif_test') p;
+select	p as relname,
+		pg_partition_parent(p) as parent,
+		pg_partition_root_parent(p) as root_parent,
+		pg_relation_size(p) as size
+from	pg_partition_tree_tables('ptif_test') p;
+select	sum(pg_relation_size(p)) as total_size
+from	pg_partition_tree_tables('ptif_test') p;
+drop table ptif_test;
-- 
2.11.0


--------------DE56535FB8085EFCFB300C28--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 src/backend/catalog/partition.c     | 117 +++++++++++++++++++++++++++++++++++-
 src/backend/utils/cache/lsyscache.c |  22 +++++++
 src/include/catalog/partition.h     |   1 +
 src/include/catalog/pg_proc.h       |  12 ++++
 src/include/utils/lsyscache.h       |   1 +
 5 files changed, 152 insertions(+), 1 deletion(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8adc4ee977..ac92bbfa71 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -32,6 +32,7 @@
 #include "catalog/pg_type.h"
 #include "commands/tablecmds.h"
 #include "executor/executor.h"
+#include "funcapi.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
@@ -181,6 +182,7 @@ static int partition_bound_bsearch(PartitionKey key,
 static int	get_partition_bound_num_indexes(PartitionBoundInfo b);
 static int	get_greatest_modulus(PartitionBoundInfo b);
 static uint64 compute_hash_value(PartitionKey key, Datum *values, bool *isnull);
+static Oid get_partition_parent_internal(Oid relid, bool recurse_to_root);
 
 /* SQL-callable function for use in hash partition CHECK constraints */
 PG_FUNCTION_INFO_V1(satisfies_hash_partition);
@@ -1362,7 +1364,7 @@ check_default_allows_bound(Relation parent, Relation default_rel,
 /*
  * get_partition_parent
  *
- * Returns inheritance parent of a partition by scanning pg_inherits
+ * Returns inheritance parent of a partition.
  *
  * 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
@@ -1371,6 +1373,37 @@ check_default_allows_bound(Relation parent, Relation default_rel,
 Oid
 get_partition_parent(Oid relid)
 {
+	if (!get_rel_relispartition(relid))
+		return InvalidOid;
+
+	return get_partition_parent_internal(relid, false);
+}
+
+/*
+ * get_partition_root_parent
+ *
+ * Returns root inheritance ancestor of a partition.
+ */
+Oid
+get_partition_root_parent(Oid relid)
+{
+	if (!get_rel_relispartition(relid))
+		return InvalidOid;
+
+	return get_partition_parent_internal(relid, true);
+}
+
+/*
+ * get_partition_parent_internal
+ *
+ * Returns inheritance parent of a partition by scanning pg_inherits.
+ * If recurse_to_root, it will check if the parent itself is a partition and
+ * if so, it will recurse to find its parent and so on until root parent is
+ * found.
+ */
+static Oid
+get_partition_parent_internal(Oid relid, bool recurse_to_root)
+{
 	Form_pg_inherits form;
 	Relation	catalogRelation;
 	SysScanDesc scan;
@@ -1402,6 +1435,9 @@ get_partition_parent(Oid relid)
 	systable_endscan(scan);
 	heap_close(catalogRelation, AccessShareLock);
 
+	if (recurse_to_root && get_rel_relispartition(result))
+		result = get_partition_parent_internal(result, recurse_to_root);
+
 	return result;
 }
 
@@ -3396,3 +3432,82 @@ satisfies_hash_partition(PG_FUNCTION_ARGS)
 
 	PG_RETURN_BOOL(rowHash % modulus == remainder);
 }
+
+/*
+ * SQL wrapper around get_partition_root_parent() in
+ * src/backend/catalog/partition.c.
+ */
+Datum
+pg_partition_root(PG_FUNCTION_ARGS)
+{
+	Oid		reloid = PG_GETARG_OID(0);
+	Oid		rootoid;
+
+	rootoid = get_partition_root_parent(reloid);
+	if (OidIsValid(rootoid))
+		PG_RETURN_OID(rootoid);
+	else
+		PG_RETURN_OID(reloid);
+}
+
+/*
+ * SQL wrapper around get_partition_parent() in
+ * src/backend/catalog/partition.c.
+ */
+Datum
+pg_partition_parent(PG_FUNCTION_ARGS)
+{
+	Oid		reloid = PG_GETARG_OID(0);
+	Oid		parentoid;
+
+	parentoid = get_partition_parent(reloid);
+	if (OidIsValid(parentoid))
+		PG_RETURN_OID(parentoid);
+	else
+		PG_RETURN_NULL();
+}
+
+/*
+ * Returns Oids of tables in a publication.
+ */
+Datum
+pg_partition_tree_tables(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	Oid		reloid = PG_GETARG_OID(0);
+	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);
+
+		partoids = find_all_inheritors(reloid, NoLock, NULL);
+		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);
+}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index e8aa179347..92353a6004 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1836,6 +1836,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/partition.h b/src/include/catalog/partition.h
index 2faf0ca26e..287642b01b 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -52,6 +52,7 @@ extern PartitionBoundInfo partition_bounds_copy(PartitionBoundInfo src,
 extern void check_new_partition_bound(char *relname, Relation parent,
 						  PartitionBoundSpec *spec);
 extern Oid	get_partition_parent(Oid relid);
+extern Oid	get_partition_root_parent(Oid relid);
 extern List *get_qual_from_partbound(Relation rel, Relation parent,
 						PartitionBoundSpec *spec);
 extern List *map_partition_varattnos(List *expr, int fromrel_varno,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f01648c961..64942b310c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5533,6 +5533,18 @@ DESCR("list of files in the WAL directory");
 DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ ));
 DESCR("hash partition CHECK constraint");
 
+/* function to get the root partition parent */
+DATA(insert OID = 3281 (  pg_partition_root PGNSP PGUID 12 10 0 0 0 f f f f t f s s 1 0 2205 "2205" _null_ _null_ _null_ _null_ _null_ pg_partition_root _null_ _null_ _null_ ));
+DESCR("oid of the partition root parent");
+
+/* function to get the partition parent */
+DATA(insert OID = 3556 (  pg_partition_parent PGNSP PGUID 12 10 0 0 0 f f f f t f s s 1 0 2205 "2205" _null_ _null_ _null_ _null_ _null_ pg_partition_parent _null_ _null_ _null_ ));
+DESCR("oid of the partition immediate parent");
+
+/* function to get OIDs of all tables in a given partition tree */
+DATA(insert OID = 3696 ( pg_partition_tree_tables	PGNSP PGUID 12 1 1000 0 0 f f f f t t s s 1 0 2205 "2205" "{2205,2205}" "{i,o}" "{relid,relid}" _null_ _null_ pg_partition_tree_tables _null_ _null_ _null_ ));
+DESCR("get OIDs of tables in a partition tree");
+
 /*
  * Symbolic values for provolatile column: these indicate whether the result
  * of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 9731e6f7ae..1000d9fd13 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -127,6 +127,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);
-- 
2.11.0


--------------FB214BDFA9CD15B454D4B087--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v5] Add assorted partition reporting functions
@ 2018-01-16 10:02  amit <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: amit @ 2018-01-16 10:02 UTC (permalink / raw)

---
 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


--------------FB017433C2081A1BE90BB8A4--





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* [PATCH v2 3/4] Add alias to be used as "unset" state.
@ 2025-09-04 16:22  Nikolay Shaplov <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: Nikolay Shaplov @ 2025-09-04 16:22 UTC (permalink / raw)

Add `unset_alias` string parameter to ternary reloption definition. This will allow
user explicitly switch ternary option to "unset" state. Use this feature to
implement `vacuum_index_cleanup` and gist's `buffering` reloptions as ternary
reloptions
---
 src/backend/access/common/reloptions.c | 91 +++++++++++---------------
 src/backend/access/gist/gistbuild.c    |  4 +-
 src/backend/commands/vacuum.c          | 11 ++--
 src/include/access/gist_private.h      | 10 +--
 src/include/access/reloptions.h        |  7 +-
 src/include/utils/rel.h                | 10 +--
 src/test/regress/expected/gist.out     |  3 +-
 7 files changed, 54 insertions(+), 82 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index f543f11001..3637646641 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -170,6 +170,27 @@ static relopt_ternary ternaryRelOpts[] =
 			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
 			ShareUpdateExclusiveLock
 		},
+		NULL,
+		TERNARY_UNSET
+	},
+	{
+		{
+			"vacuum_index_cleanup",
+			"Controls index vacuuming and index cleanup",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		"auto",
+		TERNARY_UNSET
+	},
+	{
+		{
+			"buffering",
+			"Enables buffering build for this GiST index",
+			RELOPT_KIND_GIST,
+			AccessExclusiveLock
+		},
+		"auto",
 		TERNARY_UNSET
 	},
 	/* list terminator */
@@ -489,30 +510,6 @@ static relopt_real realRelOpts[] =
 	{{NULL}}
 };
 
-/* values from StdRdOptIndexCleanup */
-static relopt_enum_elt_def StdRdOptIndexCleanupValues[] =
-{
-	{"auto", STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO},
-	{"on", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
-	{"off", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
-	{"true", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
-	{"false", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
-	{"yes", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
-	{"no", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
-	{"1", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
-	{"0", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
-	{(const char *) NULL}		/* list terminator */
-};
-
-/* values from GistOptBufferingMode */
-static relopt_enum_elt_def gistBufferingOptValues[] =
-{
-	{"auto", GIST_OPTION_BUFFERING_AUTO},
-	{"on", GIST_OPTION_BUFFERING_ON},
-	{"off", GIST_OPTION_BUFFERING_OFF},
-	{(const char *) NULL}		/* list terminator */
-};
-
 /* values from ViewOptCheckOption */
 static relopt_enum_elt_def viewCheckOptValues[] =
 {
@@ -524,28 +521,6 @@ static relopt_enum_elt_def viewCheckOptValues[] =
 
 static relopt_enum enumRelOpts[] =
 {
-	{
-		{
-			"vacuum_index_cleanup",
-			"Controls index vacuuming and index cleanup",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
-			ShareUpdateExclusiveLock
-		},
-		StdRdOptIndexCleanupValues,
-		STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO,
-		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
-	},
-	{
-		{
-			"buffering",
-			"Enables buffering build for this GiST index",
-			RELOPT_KIND_GIST,
-			AccessExclusiveLock
-		},
-		gistBufferingOptValues,
-		GIST_OPTION_BUFFERING_AUTO,
-		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
-	},
 	{
 		{
 			"check_option",
@@ -913,13 +888,14 @@ add_local_bool_reloption(local_relopts *relopts, const char *name,
  */
 static relopt_ternary *
 init_ternary_reloption(bits32 kinds, const char *name, const char *desc,
-					ternary default_val, LOCKMODE lockmode)
+			ternary default_val, const char* unset_alias, LOCKMODE lockmode)
 {
 	relopt_ternary *newoption;
 
 	newoption = (relopt_ternary *) allocate_reloption(kinds,
 									RELOPT_TYPE_TERNARY, name, desc, lockmode);
 	newoption->default_val = default_val;
+	newoption->unset_alias = unset_alias;
 
 	return newoption;
 }
@@ -930,10 +906,10 @@ init_ternary_reloption(bits32 kinds, const char *name, const char *desc,
  */
 void
 add_ternary_reloption(bits32 kinds, const char *name, const char *desc,
-				   ternary default_val, LOCKMODE lockmode)
+			ternary default_val, const char* unset_alias, LOCKMODE lockmode)
 {
 	relopt_ternary *newoption = init_ternary_reloption(kinds, name, desc,
-												 default_val, lockmode);
+											default_val, unset_alias, lockmode);
 
 	add_reloption((relopt_gen *) newoption);
 }
@@ -947,11 +923,11 @@ add_ternary_reloption(bits32 kinds, const char *name, const char *desc,
 void
 add_local_ternary_reloption(local_relopts *relopts, const char *name,
 							const char *desc, ternary default_val,
-							int offset)
+							const char* unset_alias, int offset)
 {
 	relopt_ternary *newoption = init_ternary_reloption(RELOPT_KIND_LOCAL,
 												name, desc,
-												default_val, 0);
+												default_val, unset_alias, 0);
 
 	add_local_reloption(relopts, (relopt_gen *) newoption, offset);
 }
@@ -1692,8 +1668,19 @@ parse_one_reloption(relopt_value *option, char *text_str, int text_len,
 		case RELOPT_TYPE_TERNARY:
 			{
 				bool b;
+				relopt_ternary *opt = (relopt_ternary *) option->gen;
+
 				parsed = parse_bool(value, &b);
 				option->values.ternary_val = b ? TERNARY_TRUE : TERNARY_FALSE;
+
+				if (!parsed && opt->unset_alias)
+				{
+					if (pg_strcasecmp(value, opt->unset_alias) == 0)
+					{
+						option->values.ternary_val = TERNARY_UNSET;
+						parsed = true;
+					}
+				}
 				if (validate && !parsed)
 					ereport(ERROR,
 							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1988,7 +1975,7 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, user_catalog_table)},
 		{"parallel_workers", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, parallel_workers)},
-		{"vacuum_index_cleanup", RELOPT_TYPE_ENUM,
+		{"vacuum_index_cleanup", RELOPT_TYPE_TERNARY,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_TERNARY,
 		offsetof(StdRdOptions, vacuum_truncate)},
diff --git a/src/backend/access/gist/gistbuild.c b/src/backend/access/gist/gistbuild.c
index 9b2ec9815f..7f641f7825 100644
--- a/src/backend/access/gist/gistbuild.c
+++ b/src/backend/access/gist/gistbuild.c
@@ -213,9 +213,9 @@ gistbuild(Relation heap, Relation index, IndexInfo *indexInfo)
 	 */
 	if (options)
 	{
-		if (options->buffering_mode == GIST_OPTION_BUFFERING_ON)
+		if (options->buffering_mode == TERNARY_TRUE)
 			buildstate.buildMode = GIST_BUFFERING_STATS;
-		else if (options->buffering_mode == GIST_OPTION_BUFFERING_OFF)
+		else if (options->buffering_mode == TERNARY_FALSE)
 			buildstate.buildMode = GIST_BUFFERING_DISABLED;
 		else					/* must be "auto" */
 			buildstate.buildMode = GIST_BUFFERING_AUTO;
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 977babff54..61b4bdb682 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -2175,22 +2175,21 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params,
 	 */
 	if (params.index_cleanup == VACOPTVALUE_UNSPECIFIED)
 	{
-		StdRdOptIndexCleanup vacuum_index_cleanup;
+		ternary vacuum_index_cleanup;
 
 		if (rel->rd_options == NULL)
-			vacuum_index_cleanup = STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO;
+			vacuum_index_cleanup = TERNARY_UNSET;
 		else
 			vacuum_index_cleanup =
 				((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup;
 
-		if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO)
+		if (vacuum_index_cleanup == TERNARY_UNSET)
 			params.index_cleanup = VACOPTVALUE_AUTO;
-		else if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON)
+		else if (vacuum_index_cleanup == TERNARY_TRUE)
 			params.index_cleanup = VACOPTVALUE_ENABLED;
 		else
 		{
-			Assert(vacuum_index_cleanup ==
-				   STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF);
+			Assert(vacuum_index_cleanup == TERNARY_FALSE);
 			params.index_cleanup = VACOPTVALUE_DISABLED;
 		}
 	}
diff --git a/src/include/access/gist_private.h b/src/include/access/gist_private.h
index 39404ec7cd..a931c988fb 100644
--- a/src/include/access/gist_private.h
+++ b/src/include/access/gist_private.h
@@ -380,14 +380,6 @@ typedef struct GISTBuildBuffers
 	int			rootlevel;
 } GISTBuildBuffers;
 
-/* GiSTOptions->buffering_mode values */
-typedef enum GistOptBufferingMode
-{
-	GIST_OPTION_BUFFERING_AUTO,
-	GIST_OPTION_BUFFERING_ON,
-	GIST_OPTION_BUFFERING_OFF,
-} GistOptBufferingMode;
-
 /*
  * Storage type for GiST's reloptions
  */
@@ -395,7 +387,7 @@ typedef struct GiSTOptions
 {
 	int32		vl_len_;		/* varlena header (do not touch directly!) */
 	int			fillfactor;		/* page fill factor in percent (0..100) */
-	GistOptBufferingMode buffering_mode;	/* buffering build mode */
+	ternary		buffering_mode;	/* buffering build mode */
 } GiSTOptions;
 
 /* gist.c */
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index a436697658..d2a1c7afb7 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -99,6 +99,7 @@ typedef struct relopt_bool
 typedef struct relopt_rernary
 {
 	relopt_gen	gen;
+	const char	*unset_alias; /* word that will be treaed as unset value */
 	int 		default_val;
 } relopt_ternary;
 
@@ -191,7 +192,8 @@ extern relopt_kind add_reloption_kind(void);
 extern void add_bool_reloption(bits32 kinds, const char *name, const char *desc,
 							   bool default_val, LOCKMODE lockmode);
 extern void add_ternary_reloption(bits32 kinds, const char *name,
-					const char *desc, int default_val, LOCKMODE lockmode);
+					const char *desc, ternary default_val,
+					const char* unset_alias, LOCKMODE lockmode);
 extern void add_int_reloption(bits32 kinds, const char *name, const char *desc,
 							  int default_val, int min_val, int max_val,
 							  LOCKMODE lockmode);
@@ -213,7 +215,8 @@ extern void add_local_bool_reloption(local_relopts *relopts, const char *name,
 									 int offset);
 extern void add_local_ternary_reloption(local_relopts *relopts,
 								const char *name, const char *desc,
-								ternary default_val, int offset);
+								ternary default_val, const char* unset_alias,
+								int offset);
 extern void add_local_int_reloption(local_relopts *relopts, const char *name,
 									const char *desc, int default_val,
 									int min_val, int max_val, int offset);
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 7346d618f9..95a18c6d16 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -329,14 +329,6 @@ typedef struct AutoVacOpts
 	float8		analyze_scale_factor;
 } AutoVacOpts;
 
-/* StdRdOptions->vacuum_index_cleanup values */
-typedef enum StdRdOptIndexCleanup
-{
-	STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO = 0,
-	STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF,
-	STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON,
-} StdRdOptIndexCleanup;
-
 typedef struct StdRdOptions
 {
 	int32		vl_len_;		/* varlena header (do not touch directly!) */
@@ -345,7 +337,7 @@ typedef struct StdRdOptions
 	AutoVacOpts autovacuum;		/* autovacuum-related options */
 	bool		user_catalog_table; /* use as an additional catalog relation */
 	int			parallel_workers;	/* max number of parallel workers */
-	StdRdOptIndexCleanup vacuum_index_cleanup;	/* controls index vacuuming */
+	ternary		vacuum_index_cleanup; /* controls index vacuuming */
 	ternary		vacuum_truncate;	/* enables vacuum to truncate a relation */
 
 	/*
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index c75bbb23b6..76751d1859 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -12,8 +12,7 @@ create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = au
 drop index gist_pointidx2, gist_pointidx3, gist_pointidx4;
 -- Make sure bad values are refused
 create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
-ERROR:  invalid value for enum option "buffering": invalid_value
-DETAIL:  Valid values are "on", "off", and "auto".
+ERROR:  invalid value for ternary option "buffering": invalid_value
 create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
 ERROR:  value 9 out of bounds for option "fillfactor"
 DETAIL:  Valid values are between "10" and "100".
-- 
2.39.2


--nextPart5129932.5fSG56mABF
Content-Disposition: attachment; filename="v2-0004-Extra-tests.patch"
Content-Transfer-Encoding: 7Bit
Content-Type: text/x-patch; charset="unicode-2-0-utf-8";
 name="v2-0004-Extra-tests.patch"



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

end of thread, other threads:[~2025-09-04 16:22 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-16 10:02 [PATCH v7] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH v8] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH v2] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH v6] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH v3] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH v5] Add assorted partition reporting functions amit <[email protected]>
2018-01-16 10:02 [PATCH v4] Add assorted partition reporting functions amit <[email protected]>
2025-09-04 16:22 [PATCH v2 3/4] Add alias to be used as "unset" state. Nikolay Shaplov <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox