v11-0001-Add-pg_partition_children-to-report-partitions.patch

From de9a597f8cfc1e8707c047ec7ec760ef01244a03 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Fri, 3 Aug 2018 17:06:05 +0900
Subject: [PATCH v11] Add pg_partition_children to report partitions

It returns set of records one for each partition containing the
partition name, parent name, and level in the partition tree with
given table as root
---
 doc/src/sgml/func.sgml                       | 37 ++++++++++++
 src/backend/catalog/partition.c              | 90 ++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat              |  9 +++
 src/test/regress/expected/partition_info.out | 84 ++++++++++++++++++++++++++
 src/test/regress/parallel_schedule           |  2 +-
 src/test/regress/serial_schedule             |  1 +
 src/test/regress/sql/partition_info.sql      | 35 +++++++++++
 7 files changed, 257 insertions(+), 1 deletion(-)
 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..6b10aa3b3d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,43 @@ 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_children(<type>regclass</type>)</function></literal></entry>
+       <entry><type>setof record</type></entry>
+       <entry>
+        List name, parent name, level, and whether it's a leaf partition for
+        each partition contained in the partition tree with given root table,
+        including the root table itself
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <para>
+    To check the total size of the data contained in
+    <structname>measurement</structname> table described in
+    <xref linkend="ddl-partitioning-declarative-example"/>, one could use the
+    following query:
+   </para>
+
+<programlisting>
+select pg_size_pretty(sum(pg_relation_size(relid))) as total_size from pg_partition_children('measurement');
+ 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..6061bfc369 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -23,13 +23,17 @@
 #include "catalog/partition.h"
 #include "catalog/pg_inherits.h"
 #include "catalog/pg_partitioned_table.h"
+#include "catalog/pg_type.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"
@@ -357,3 +361,89 @@ get_proposed_default_constraint(List *new_part_constraints)
 
 	return make_ands_implicit(defPartConstraint);
 }
+
+Datum
+pg_partition_children(PG_FUNCTION_ARGS)
+{
+	Oid		rootrelid = PG_GETARG_OID(0);
+	FuncCallContext *funccxt;
+	ListCell **next;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcxt;
+		TupleDesc	tupdesc;
+		List   *partitions;
+
+		funccxt = SRF_FIRSTCALL_INIT();
+		oldcxt = MemoryContextSwitchTo(funccxt->multi_call_memory_ctx);
+
+		partitions = find_all_inheritors(rootrelid, NoLock, NULL);
+
+		tupdesc = CreateTemplateTupleDesc(4, false);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid",
+						   REGCLASSOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid",
+						   REGCLASSOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "level",
+						   INT4OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "isleaf",
+						   BOOLOID, -1, 0);
+
+		next = (ListCell **) palloc(sizeof(ListCell *));
+		*next = list_head(partitions);
+
+		funccxt->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+		funccxt->user_fctx = (void *) next;
+
+		MemoryContextSwitchTo(oldcxt);
+	}
+
+	funccxt = SRF_PERCALL_SETUP();
+	next = (ListCell **) funccxt->user_fctx;
+
+	if (*next != NULL)
+	{
+		HeapTuple	tuple;
+		char	   *values[4];
+		Oid			relid = lfirst_oid(*next);
+		char		relkind = get_rel_relkind(relid);
+		List	   *ancestors = get_partition_ancestors(lfirst_oid(*next));
+		Oid			parent = InvalidOid;
+		int			level = 0;
+		ListCell   *lc;
+
+		/* relid */
+		values[0] = psprintf("%u", relid);
+
+		/* parentid */
+		if (ancestors != NIL)
+			parent = linitial_oid(ancestors);
+		if (OidIsValid(parent))
+			values[1] = psprintf("%u", parent);
+		else
+			values[1] = NULL;
+
+		/* level */
+		if (relid != rootrelid)
+		{
+			foreach(lc, ancestors)
+			{
+				level++;
+				if (lfirst_oid(lc) == rootrelid)
+					break;
+			}
+		}
+		values[2] = psprintf("%d", level);
+		values[3] = psprintf("%c", relkind == RELKIND_PARTITIONED_TABLE ?
+									'f' :
+									't');
+
+		tuple = BuildTupleFromCStrings(funccxt->attinmeta, values);
+
+		*next = lnext(*next);
+		SRF_RETURN_NEXT(funccxt, HeapTupleGetDatum(tuple));
+	}
+
+	SRF_RETURN_DONE(funccxt);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a14651010f..5768041dd6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,13 @@
   proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any',
   proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' },
 
+# function to get tables in partition tree of a given root table
+{ oid => '3423', descr => 'view partition tree tables',
+  proname => 'pg_partition_children', prorows => '1000', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => 'regclass',
+  proallargtypes => '{regclass,regclass,regclass,int4,bool}',
+  proargmodes => '{i,o,o,o,o}',
+  proargnames => '{rootrelid,relid,parentid,level,isleaf}',
+  prosrc => 'pg_partition_children' }
+
 ]
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
new file mode 100644
index 0000000000..387b52d009
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,84 @@
+--
+-- Tests for pg_partition_children
+--
+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;
+-- all tables in the tree
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test');
+    relid    |  parentid  | level | isleaf | size  
+-------------+------------+-------+--------+-------
+ ptif_test   |            |     0 | f      |     0
+ ptif_test0  | ptif_test  |     1 | f      |     0
+ ptif_test1  | ptif_test  |     1 | f      |     0
+ ptif_test2  | ptif_test  |     1 | t      | 16384
+ ptif_test01 | ptif_test0 |     2 | t      | 24576
+ ptif_test11 | ptif_test1 |     2 | t      |  8192
+(6 rows)
+
+-- all table excluding the root
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test') where level > 0;
+    relid    |  parentid  | level | isleaf | size  
+-------------+------------+-------+--------+-------
+ ptif_test0  | ptif_test  |     1 | f      |     0
+ ptif_test1  | ptif_test  |     1 | f      |     0
+ ptif_test2  | ptif_test  |     1 | t      | 16384
+ ptif_test01 | ptif_test0 |     2 | t      | 24576
+ ptif_test11 | ptif_test1 |     2 | t      |  8192
+(5 rows)
+
+-- all leaf partitions
+select * from pg_partition_children('ptif_test') where isleaf;
+    relid    |  parentid  | level | isleaf 
+-------------+------------+-------+--------
+ ptif_test2  | ptif_test  |     1 | t
+ ptif_test01 | ptif_test0 |     2 | t
+ ptif_test11 | ptif_test1 |     2 | t
+(3 rows)
+
+-- total size of all partitions
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test');
+ total_size 
+------------
+      49152
+(1 row)
+
+-- total size of first level partitions
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test') where level = 1;
+ total_size 
+------------
+      16384
+(1 row)
+
+-- check that passing a lower-level table to pg_partition_children works
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test0');
+    relid    |  parentid  | level | isleaf | size  
+-------------+------------+-------+--------+-------
+ ptif_test0  | ptif_test  |     0 | f      |     0
+ ptif_test01 | ptif_test0 |     1 | t      | 24576
+(2 rows)
+
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test01');
+    relid    |  parentid  | level | isleaf | size  
+-------------+------------+-------+--------+-------
+ ptif_test01 | ptif_test0 |     0 | t      | 24576
+(1 row)
+
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test01');
+ total_size 
+------------
+      24576
+(1 row)
+
+-- this one should result in null, as there are no level 1 partitions of a leaf partition
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test01') where level = 1;
+ total_size 
+------------
+           
+(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..24ceabed1b
--- /dev/null
+++ b/src/test/regress/sql/partition_info.sql
@@ -0,0 +1,35 @@
+--
+-- Tests for pg_partition_children
+--
+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;
+
+-- all tables in the tree
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test');
+
+-- all table excluding the root
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test') where level > 0;
+
+-- all leaf partitions
+select * from pg_partition_children('ptif_test') where isleaf;
+
+-- total size of all partitions
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test');
+
+-- total size of first level partitions
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test') where level = 1;
+
+-- check that passing a lower-level table to pg_partition_children works
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test0');
+select *, pg_relation_size(relid) as size from pg_partition_children('ptif_test01');
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test01');
+
+-- this one should result in null, as there are no level 1 partitions of a leaf partition
+select sum(pg_relation_size(relid)) as total_size from pg_partition_children('ptif_test01') where level = 1;
+
+drop table ptif_test;
-- 
2.11.0