On 2018/10/29 12:59, Michael Paquier wrote: > On Fri, Oct 19, 2018 at 06:55:09PM +0900, Amit Langote wrote: >> Yeah, we could make it the responsibility of the callers of >> find_all_inheritors and find_inheritance_children to check relhassubclass >> as an optimization and remove any reference to relhassubclass from >> pg_inherits.c. Although we can write such a patch, it seems like it'd be >> bigger than the patch to ensure the correct value of relhassubclass for >> indexes, which I just posted on the other thread [1]. > > And what is present as patch 0001 on this thread has been committed as > 55853d6, so we are good for this part.
Thank you for that. :) >>> Anyway, it seems that you are right here. Just setting relhassubclass >>> for partitioned indexes feels more natural with what's on HEAD now. >>> Even if I'd like to see all those hypothetical columns in pg_class go >>> away, that cannot happen without a close lookup at the performance >>> impact. >> >> Okay, I updated the patch on this thread. > > Thanks for the new version. > >> Since the updated patch depends on the correct value of relhassubclass >> being set for indexes, this patch should be applied on top of the other >> patch. I've attached here both. > > - if (!has_subclass(parentrelId)) > + if (get_rel_relkind(parentrelId) != RELKIND_PARTITIONED_INDEX && > + !has_subclass(parentrelId)) > return NIL; > > You don't need this bit anymore, relhassubclass is now set for > partitioned indexes. Oh, how did I forget to do that! Removed. > + ereport(ERROR, > + (errcode(ERRCODE_WRONG_OBJECT_TYPE), > + errmsg("\"%s\" is not a table, a foreign table, or an index", > + get_rel_name(rootrelid)))); > Should this also list "partitioned tables and partitioned indexes"? The > style is heavy, but that maps with what pgstattuple does.. Hmm, I think we mention the word "partitioned" in the error message only if partitioning is required to perform an operation but it's absent (for example, trying to attach partition to a non-partitioned table) or if its presence prevents certain operation from being performed (for example, calling pgrowlocks() on a partitioned table). Neither seems true in this case. One can pass a relation of any of the types mentioned in the above error message to pg_partition_tree and get some output from it. > The tests should include also something for a leaf index when fed to > pg_partition_tree() (in order to control the index names you could just > attach an index to a partition after creating it, but I leave that up to > you). > > + > <entry><literal><function>pg_partition_tree(<type>oid</type>)</function></literal></entry> > + <entry><type>setof record</type></entry> > > The change to regclass has not been reflected yet in the documentation > and the implementation, because... > >> Another change I made is something Robert and Alvaro seem to agree about >> -- to use regclass instead of oid type as input/output columns. > > ... I am in minority here, it feels lonely ;) I've fixed the documentation to mention regclass as the input type. Also, I've also modified tests to not use ::regclass. Thanks, Amit
>From 1eb7589b4ded57752c4c285db03e2b0cc37b63dc Mon Sep 17 00:00:00 2001 From: Michael Paquier <mich...@paquier.xyz> Date: Fri, 5 Oct 2018 14:41:17 +0900 Subject: [PATCH v17] 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's name, its immediate parent's name, a boolean value telling if the relation is a leaf in the tree and an integer telling 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 | 43 ++++++++ src/backend/utils/adt/Makefile | 4 +- src/backend/utils/adt/partitionfuncs.c | 150 +++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 9 ++ src/test/regress/expected/partition_info.out | 116 +++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 74 +++++++++++++ 8 files changed, 396 insertions(+), 3 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 96d45419e5..58d7ea9da2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20216,6 +20216,49 @@ 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>regclass</type>)</function></literal></entry> + <entry><type>setof record</type></entry> + <entry> + List information about tables or indexes in a partition tree for a + given partitioned table or partitioned index, with one row for each + partition and table or index itself. Information provided includes + the name of the partition, the name of its immediate parent, a boolean + value telling if the partition is a leaf, and an integer telling its + level in the hierarchy. The value of level begins at <literal>0</literal> + for the input table or index 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'); + 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 746c7ae844..20eead1798 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 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..db546d713d --- /dev/null +++ b/src/backend/utils/adt/partitionfuncs.c @@ -0,0 +1,150 @@ +/*------------------------------------------------------------------------- + * + * 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); + char relkind = get_rel_relkind(rootrelid); + FuncCallContext *funcctx; + ListCell **next; + + /* Only allow relation types that can appear in partition trees. */ + if (relkind != RELKIND_RELATION && + relkind != RELKIND_FOREIGN_TABLE && + relkind != RELKIND_INDEX && + relkind != RELKIND_PARTITIONED_TABLE && + relkind != RELKIND_PARTITIONED_INDEX) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table, a foreign table, or an index", + get_rel_name(rootrelid)))); + + /* 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, AccessShareLock, NULL); + + tupdesc = CreateTemplateTupleDesc(PG_PARTITION_TREE_COLS, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid", + REGCLASSOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid", + REGCLASSOID, -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 && + relkind != RELKIND_PARTITIONED_INDEX); + + /* 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/pg_proc.dat b/src/include/catalog/pg_proc.dat index 4d7fe1b383..4026018ba9 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10029,4 +10029,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 => 'regclass', + proallargtypes => '{regclass,regclass,regclass,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..e0de00722b --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,116 @@ +-- +-- 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; +-- test index partition tree as well +CREATE INDEX ptif_test_index ON ONLY ptif_test (a); +CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a); +ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index; +CREATE INDEX ptif_test01_index ON ptif_test01 (a); +ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index; +CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a); +ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index; +CREATE INDEX ptif_test11_index ON ptif_test11 (a); +ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index; +CREATE INDEX ptif_test2_index ON ptif_test2 (a); +ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index; +-- all tables in the tree with some size information +SELECT relid, parentrelid, level, isleaf, + pg_relation_size(relid) = 0 AS is_empty + FROM pg_partition_tree('ptif_test'); + 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 +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test0') 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, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test01') p + JOIN pg_class c ON (p.relid = c.oid); + relid | parentrelid | level | isleaf +-------------+-------------+-------+-------- + ptif_test01 | ptif_test0 | 0 | t +(1 row) + +-- same for the index tree +SELECT relid, parentrelid, level, isleaf, + pg_relation_size(relid) = 0 AS is_empty + FROM pg_partition_tree('ptif_test_index'); + relid | parentrelid | level | isleaf | is_empty +-------------------+------------------+-------+--------+---------- + ptif_test_index | | 0 | f | t + ptif_test0_index | ptif_test_index | 1 | f | t + ptif_test1_index | ptif_test_index | 1 | f | t + ptif_test2_index | ptif_test_index | 1 | t | f + ptif_test01_index | ptif_test0_index | 2 | t | f + ptif_test11_index | ptif_test1_index | 2 | t | f +(6 rows) + +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test0_index') p + JOIN pg_class c ON (p.relid = c.oid); + relid | parentrelid | level | isleaf +-------------------+------------------+-------+-------- + ptif_test0_index | ptif_test_index | 0 | f + ptif_test01_index | ptif_test0_index | 1 | t +(2 rows) + +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test01_index') p + JOIN pg_class c ON (p.relid = c.oid); + relid | parentrelid | level | isleaf +-------------------+------------------+-------+-------- + ptif_test01_index | ptif_test0_index | 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') 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, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_normal_table') 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; +-- check that passing relation types that cannot be in partition trees +-- gives an error +CREATE VIEW ptif_test_view AS SELECT 1; +SELECT * FROM pg_partition_tree('ptif_test_view'); +ERROR: "ptif_test_view" is not a table, a foreign table, or an index +DROP VIEW ptif_test_view; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 8f07343c1a..b5e15501dd 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 265e2cda50..49329ffbb6 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -185,6 +185,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..6913669edf --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,74 @@ +-- +-- 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; + +-- test index partition tree as well +CREATE INDEX ptif_test_index ON ONLY ptif_test (a); +CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a); +ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index; +CREATE INDEX ptif_test01_index ON ptif_test01 (a); +ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index; +CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a); +ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index; +CREATE INDEX ptif_test11_index ON ptif_test11 (a); +ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index; +CREATE INDEX ptif_test2_index ON ptif_test2 (a); +ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index; + +-- all tables in the tree with some size information +SELECT relid, parentrelid, level, isleaf, + pg_relation_size(relid) = 0 AS is_empty + FROM pg_partition_tree('ptif_test'); + +-- passing an intermediate level partitioned +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test0') p + JOIN pg_class c ON (p.relid = c.oid); + +-- passing a leaf partition +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test01') p + JOIN pg_class c ON (p.relid = c.oid); + +-- same for the index tree +SELECT relid, parentrelid, level, isleaf, + pg_relation_size(relid) = 0 AS is_empty + FROM pg_partition_tree('ptif_test_index'); + +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test0_index') p + JOIN pg_class c ON (p.relid = c.oid); + +SELECT relid, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_test01_index') 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') 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, parentrelid, level, isleaf + FROM pg_partition_tree('ptif_normal_table') p + JOIN pg_class c ON (p.relid = c.oid); +DROP TABLE ptif_normal_table; + +-- check that passing relation types that cannot be in partition trees +-- gives an error +CREATE VIEW ptif_test_view AS SELECT 1; +SELECT * FROM pg_partition_tree('ptif_test_view'); +DROP VIEW ptif_test_view; -- 2.11.0