Hi,
On 2018/08/03 21:35, Jesper Pedersen wrote:
> Hi Amit,
>
> On 08/03/2018 04:28 AM, Amit Langote wrote:
>> That's a good idea, thanks.
>>
>> Actually, by the time I sent the last version of the patch or maybe few
>> versions before that, I too had started thinking if we shouldn't just have
>> a SETOF RECORD function like you've outlined here, but wasn't sure of the
>> fields it should have. (relid, parentid, level) seems like a good start,
>> or maybe that's just what we need.
>>
>
> I think there should be a column that identifies leaf partitions (bool
> isleaf), otherwise it isn't obvious in complex scenarios.
Ah, getting isleaf directly from pg_partition_children would be better
than an application figuring that out by itself.
>> Note that the level that's returned for each table is computed wrt the
>> root table passed to the function and not the actual root partition.
>
> If you are given a leaf partition as input, then you will have to keep
> executing the query until you find the root, and count those. So, I think
> it should be either be the level to the root, or there should be another
> column that lists that (rootlevel).
The function pg_partition_children is to get partitions found under a
given root table. If you pass a leaf partition to it, then there is
nothing under, just the leaf partition itself, and its level wrt itself is
0. That's what Robert said too, to which you replied:
On 2018/08/03 22:11, Jesper Pedersen wrote:
> We had the 2 pg_partition_level() functions and
> pg_partition_leaf_children() in v8, so it would be good to get those back.
Do we need a pg_partition_level that expects the individual partition OID
to be passed to it or can we do with the information we get from the
revised pg_partition_children? In earlier revisions,
pg_partition_children returned only the partition OIDs, so we needed to
provide pg_partition_* functions for getting the parent, root parent,
level, etc. separately. I mean to ask if is there a need for having these
functions separately if the revised pg_partition_children already outputs
that information?
pg_partition_leaf_children()'s output can be obtained as follows, after
adding isleaf column to pg_partition_children's output:
select * from pg_partition_children('<root>') where isleaf;
Attached updated patch adds isleaf to pg_partition_children's output.
Thanks,
Amit
From a5c9388b984ed7e70d3ed074c9ef3eb6de975d4a Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Fri, 3 Aug 2018 17:06:05 +0900
Subject: [PATCH v10] 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 | 89 ++++++++++++++++++++++++++++
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, 256 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..5c919a5bee 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -23,12 +23,15 @@
#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/partcache.h"
#include "utils/rel.h"
@@ -357,3 +360,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[3];
+ 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
