On Thu, Oct 04, 2018 at 05:18:07PM +0900, Michael Paquier wrote: > So it seems that I am clearly outvoted here ;) > > Okay, let's do as you folks propose.
And attached is a newer version with this isleaf stuff and the previous feedback from Amit integrated, as long as I recall about it. The version is indented, and tutti-quanti. Does that look fine to everybody here? The CF bot should normally pick up this patch, the previous garbage seen in one of the tests seems to come from the previous implementation which used strings... -- Michael
From 11bf47ee3137b6a15699bfda50b0904f2e10a415 Mon Sep 17 00:00:00 2001 From: Michael Paquier <mich...@paquier.xyz> Date: Fri, 5 Oct 2018 14:41:17 +0900 Subject: [PATCH] Add pg_partition_tree to display information about partitions This new function is useful to display a full tree of partitions with a partitioned table given in output, and avoids the need of any complex WITH RECURSIVE when looking at partition trees which are multi-level deep. It returns a set of records, one for each partition, containing the partition OID, its immediate parent OID, if the relation is a leaf in the tree and its level in the partition tree with given table considered as root, beginning at zero for the root, and incrementing by one each time the scan goes one level down. Author: Amit Langote Reviewed-by: Jesper Pedersen, Michael Paquier Discussion: https://postgr.es/m/8d00e51a-9a51-ad02-d53e-ba6bf50b2...@lab.ntt.co.jp --- doc/src/sgml/func.sgml | 42 ++++++ src/backend/utils/adt/Makefile | 4 +- src/backend/utils/adt/partitionfuncs.c | 137 +++++++++++++++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 9 ++ src/test/regress/expected/partition_info.out | 67 +++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 42 ++++++ 9 files changed, 302 insertions(+), 4 deletions(-) create mode 100644 src/backend/utils/adt/partitionfuncs.c 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 f984d069e1..be315aaabb 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20197,6 +20197,48 @@ 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_tree(<type>oid</type>)</function></literal></entry> + <entry><type>setof record</type></entry> + <entry> + List information about table in a partition tree for a given + partitioned table, which consists of one row for each partition and + table itself. Information provided includes the OID of the partition, + the OID of its immediate parent, if the partition is a leaf and its + level in the hierarchy. The value of level begins at + <literal>0</literal> for the input table in its role as the root of + the partition tree, <literal>1</literal> for its partitions, + <literal>2</literal> for their partitions, and so on. + </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_tree('measurement'::regclass); + total_size +------------ + 24 kB +(1 row) +</programlisting> + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 4b35dbb8bb..132ec7620c 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -20,8 +20,8 @@ OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \ jsonfuncs.o like.o lockfuncs.o mac.o mac8.o misc.o nabstime.o name.o \ network.o network_gist.o network_selfuncs.o network_spgist.o \ numeric.o numutils.o oid.o oracle_compat.o \ - orderedsetaggs.o pg_locale.o pg_lsn.o pg_upgrade_support.o \ - pgstatfuncs.o \ + orderedsetaggs.o partitionfuncs.o pg_locale.o pg_lsn.o \ + pg_upgrade_support.o pgstatfuncs.o \ pseudotypes.o quote.o rangetypes.o rangetypes_gist.o \ rangetypes_selfuncs.o rangetypes_spgist.o rangetypes_typanalyze.o \ regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \ diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt/partitionfuncs.c new file mode 100644 index 0000000000..c1743524c8 --- /dev/null +++ b/src/backend/utils/adt/partitionfuncs.c @@ -0,0 +1,137 @@ +/*------------------------------------------------------------------------- + * + * partitionfuncs.c + * Functions for accessing partition-related metadata + * + * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/utils/adt/partitionfuncs.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/htup_details.h" +#include "catalog/partition.h" +#include "catalog/pg_class.h" +#include "catalog/pg_inherits.h" +#include "catalog/pg_type.h" +#include "funcapi.h" +#include "utils/fmgrprotos.h" +#include "utils/lsyscache.h" + + +/* + * pg_partition_tree + * + * Produce a view with one row per member of a partition tree, beginning + * from the top-most parent given by the caller. This gives information + * about each partition, its immediate partitioned parent, if it is + * a leaf partition and its level in the hierarchy. + */ +Datum +pg_partition_tree(PG_FUNCTION_ARGS) +{ +#define PG_PARTITION_TREE_COLS 4 + Oid rootrelid = PG_GETARG_OID(0); + FuncCallContext *funcctx; + ListCell **next; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcxt; + TupleDesc tupdesc; + List *partitions; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcxt = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + partitions = find_all_inheritors(rootrelid, NoLock, NULL); + + tupdesc = CreateTemplateTupleDesc(PG_PARTITION_TREE_COLS, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid", + OIDOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid", + OIDOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "isleaf", + BOOLOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "level", + INT4OID, -1, 0); + + funcctx->tuple_desc = BlessTupleDesc(tupdesc); + + /* allocate memory for user context */ + next = (ListCell **) palloc(sizeof(ListCell *)); + *next = list_head(partitions); + funcctx->user_fctx = (void *) next; + + MemoryContextSwitchTo(oldcxt); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + next = (ListCell **) funcctx->user_fctx; + + if (*next != NULL) + { + Datum values[PG_PARTITION_TREE_COLS]; + bool nulls[PG_PARTITION_TREE_COLS]; + HeapTuple tuple; + Oid relid = lfirst_oid(*next); + char relkind = get_rel_relkind(relid); + List *ancestors = get_partition_ancestors(lfirst_oid(*next)); + Oid parentid = InvalidOid; + int level = 0; + Datum result; + ListCell *lc; + + /* + * Form tuple with appropriate data. + */ + MemSet(nulls, 0, sizeof(nulls)); + MemSet(values, 0, sizeof(values)); + + /* relid */ + values[0] = ObjectIdGetDatum(relid); + + /* parentid */ + if (ancestors != NIL) + parentid = linitial_oid(ancestors); + if (OidIsValid(parentid)) + values[1] = ObjectIdGetDatum(parentid); + else + nulls[1] = true; + + /* isleaf */ + values[2] = BoolGetDatum(relkind != RELKIND_PARTITIONED_TABLE); + + /* level */ + if (relid != rootrelid) + { + foreach(lc, ancestors) + { + level++; + if (lfirst_oid(lc) == rootrelid) + break; + } + } + values[3] = Int32GetDatum(level); + + *next = lnext(*next); + + tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls); + result = HeapTupleGetDatum(tuple); + SRF_RETURN_NEXT(funcctx, result); + } + + /* done when there are no more elements left */ + SRF_RETURN_DONE(funcctx); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 89e2b60916..0d44a7c680 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201810051 +#define CATALOG_VERSION_NO 201810052 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 8579822bcd..a234a45b60 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10216,4 +10216,13 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# information about a partition tree +{ oid => '3423', descr => 'view partition tree tables', + proname => 'pg_partition_tree', prorows => '1000', proretset => 't', + provolatile => 'v', prorettype => 'record', proargtypes => 'oid', + proallargtypes => '{oid,oid,oid,bool,int4}', + proargmodes => '{i,o,o,o,o}', + proargnames => '{rootrelid,relid,parentrelid,isleaf,level}', + prosrc => 'pg_partition_tree' } + ] diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..6771a1ddb1 --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,67 @@ +-- +-- Tests for pg_partition_tree +-- +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(-100, 200) AS i; +-- all tables in the tree with some size information +SELECT relid::regclass, parentrelid::regclass, level, isleaf, + pg_relation_size(relid) = 0 AS is_empty + FROM pg_partition_tree('ptif_test'::regclass); + relid | parentrelid | level | isleaf | is_empty +-------------+-------------+-------+--------+---------- + ptif_test | | 0 | f | t + ptif_test0 | ptif_test | 1 | f | t + ptif_test1 | ptif_test | 1 | f | t + ptif_test2 | ptif_test | 1 | t | f + ptif_test01 | ptif_test0 | 2 | t | f + ptif_test11 | ptif_test1 | 2 | t | f +(6 rows) + +-- passing an intermediate level partitioned table +SELECT relid::regclass, parentrelid::regclass, level, isleaf + FROM pg_partition_tree('ptif_test0'::regclass) p + JOIN pg_class c ON (p.relid = c.oid); + relid | parentrelid | level | isleaf +-------------+-------------+-------+-------- + ptif_test0 | ptif_test | 0 | f + ptif_test01 | ptif_test0 | 1 | t +(2 rows) + +-- passing a leaf partition +SELECT relid::regclass, parentrelid::regclass, level, isleaf + FROM pg_partition_tree('ptif_test01'::regclass) p + JOIN pg_class c ON (p.relid = c.oid); + relid | parentrelid | level | isleaf +-------------+-------------+-------+-------- + ptif_test01 | ptif_test0 | 0 | t +(1 row) + +-- this results 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_tree('ptif_test01'::regclass) WHERE level = 1; + total_size +------------ + +(1 row) + +DROP TABLE ptif_test; +-- check that passing a table that's not part of any partition tree works +-- the same as passing a leaf partition +CREATE TABLE ptif_normal_table(a int); +SELECT relid::regclass, parentrelid::regclass, level, isleaf + FROM pg_partition_tree('ptif_normal_table'::regclass) p + JOIN pg_class c ON (p.relid = c.oid); + relid | parentrelid | level | isleaf +-------------------+-------------+-------+-------- + ptif_normal_table | | 0 | t +(1 row) + +DROP TABLE ptif_normal_table; 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..28f1a5e4a6 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,42 @@ +-- +-- Tests for pg_partition_tree +-- +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(-100, 200) AS i; + +-- all tables in the tree with some size information +SELECT relid::regclass, parentrelid::regclass, level, isleaf, + pg_relation_size(relid) = 0 AS is_empty + FROM pg_partition_tree('ptif_test'::regclass); + +-- passing an intermediate level partitioned table +SELECT relid::regclass, parentrelid::regclass, level, isleaf + FROM pg_partition_tree('ptif_test0'::regclass) p + JOIN pg_class c ON (p.relid = c.oid); + +-- passing a leaf partition +SELECT relid::regclass, parentrelid::regclass, level, isleaf + FROM pg_partition_tree('ptif_test01'::regclass) p + JOIN pg_class c ON (p.relid = c.oid); + +-- this results 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_tree('ptif_test01'::regclass) WHERE level = 1; + +DROP TABLE ptif_test; + +-- check that passing a table that's not part of any partition tree works +-- the same as passing a leaf partition +CREATE TABLE ptif_normal_table(a int); +SELECT relid::regclass, parentrelid::regclass, level, isleaf + FROM pg_partition_tree('ptif_normal_table'::regclass) p + JOIN pg_class c ON (p.relid = c.oid); +DROP TABLE ptif_normal_table; -- 2.19.0
signature.asc
Description: PGP signature
