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 <amitlangot...@gmail.com> 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