From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Jeevan Ladhe <jeevan.ladhe@enterprisedb.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: partition tree inspection functions
Date: Thu, 9 Aug 2018 13:05:56 +0900
Message-ID: <8cd38eb0-91b8-3c3d-e0b2-e387b51c96b5@lab.ntt.co.jp> (raw)
In-Reply-To: <CAEepm=2MFQWr8uGjDQT-Ho7Xao8gPe__EvB4d1yPdXMUqO-bHQ@mail.gmail.com>
References: <8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp>
<d5f5b9a8-0c88-8043-2007-be37096bed39@lab.ntt.co.jp>
<20180627121615.GF2667@paquier.xyz>
<45e28062-976d-45bf-56f8-9746f551da02@lab.ntt.co.jp>
<20180628034341.GH11054@paquier.xyz>
<6baeb45a-6222-6b88-342d-37fc7d3cf89a@lab.ntt.co.jp>
<4f4a2518-6c62-5463-6ac2-1241ae9d6041@redhat.com>
<e12ff710-dcf5-18ff-b4a4-cc42432d84c4@lab.ntt.co.jp>
<38183355-ea7c-9443-0c38-6124fcf5f38b@redhat.com>
<5686970f-b4e8-bbe5-9109-b97763714c19@lab.ntt.co.jp>
<da9f8b6d-13bb-18fe-7777-b3be7bdd327b@redhat.com>
<3273a7af-3aca-eb86-cedb-f7b28be00831@lab.ntt.co.jp>
<CA+TgmobhiREgUvKuMOoCCsPC6orjWKt_XoSd5shhCL7QVo8=Ag@mail.gmail.com>
<28d6f8eb-afda-1bfd-141d-ca489cffb0ee@lab.ntt.co.jp>
<7f2e6674-9196-9fe7-1405-368bafd64198@redhat.com>
<5e91cf39-4727-903a-779c-7b7b081ac7e9@lab.ntt.co.jp>
<CAEepm=0DT_i+kP7iVJyzAOqHaJBEuEK3PkYtZ+RFNBvHLEQBSw@mail.gmail.com>
<CAEepm=2MFQWr8uGjDQT-Ho7Xao8gPe__EvB4d1yPdXMUqO-bHQ@mail.gmail.com>
Thanks Thomas for notifying.
On 2018/08/08 20:47, Thomas Munro wrote:
> On Wed, Aug 8, 2018 at 11:21 PM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
>> On Tue, Aug 7, 2018 at 7:32 PM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> Attached updated patch adds isleaf to pg_partition_children's output.
>>
>> Hmm, I wonder where this garbage is coming from:
>
> partition.c:437:3: error: array index 3 is past the end of the array
> (which contains 3 elements) [-Werror,-Warray-bounds]
Oops, fixed.
> That'll do it. Also:
>
> partition.c:409:19: error: implicit declaration of function
> 'get_rel_relkind' is invalid in C99
> [-Werror,-Wimplicit-function-declaration]
Fixed too.
Attached updated patch.
Thanks,
Amit
Attachments:v11-0001-Add-pg_partition_children-to-report-partitions.patch (text/plain, 12.7K) download
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.sgmlindex 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.cindex 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.datindex 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.outnew file mode 100644index 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_scheduleindex 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_scheduleindex 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.sqlnew file mode 100644index 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, thomas.munro@enterprisedb.com, jesper.pedersen@redhat.com, robertmhaas@gmail.com, michael@paquier.xyz, jeevan.ladhe@enterprisedb.com
Subject: Re: partition tree inspection functions
In-Reply-To: <8cd38eb0-91b8-3c3d-e0b2-e387b51c96b5@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox