On 2018/10/03 12:37, Michael Paquier wrote:
> On Mon, Oct 01, 2018 at 04:27:57PM +0900, Amit Langote wrote:
>> Yeah, maybe there is no reason to delay proceeding with
>> pg_partition_children which provides a useful functionality.
>
> So, I have been looking at your patch, and there are a couple of things
> which could be improved.
Thanks for reviewing and updating the patch.
> Putting the new function pg_partition_children() in partition.c is a
> bad idea I think. So instead I think that we should put that in a
> different location, say utils/adt/partitionfuncs.c.
Okay, sounds like a good idea.
> + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid",
> + REGCLASSOID, -1, 0);
> + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid",
> + REGCLASSOID, -1, 0);
> REGCLASSOID is used mainly for casting, so instead let's use OIDOID like
> any other system function.
Check.
> + values[2] = psprintf("%d", level);
> + values[3] = psprintf("%c", relkind == RELKIND_PARTITIONED_TABLE ?
> + 'f' :
> + 't');
> Using Datum objects is more elegant in this context.
Agreed. I think I'd just copied the psprintf code from some other function.
> isleaf is not particularly useful as it can just be fetched with a join
> on pg_class/relkind. So I think that we had better remove it.
That's a bit imposing on the users to know about relkind, but maybe that's
okay.
> I have cleaned up a bit tests, removing duplicates and most of the
> things which touched the size of relations to have something more
> portable.
Thanks for that.
> We could have a flavor using a relation name in input with qualified
> names handled properly (see pg_get_viewdef_name for example), not sure
> if that's really mandatory so I left that out.
Having to always use the typecast (::regclass) may be a bit annoying, but
we can always add that version later.
> I have also added some
> comments here and there. The docs could be worded a bit better still.
>
> My result is the patch attached. What do you think?
I looked at the updated patch and couldn't resist making some changes,
which see in the attached diff file. Also attached is the updated patch.
Thanks,
Amit
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d41c09b68b..6dfa3dc977 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20209,12 +20209,13 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
<entry><literal><function>pg_partition_tree(<type>oid</type>)</function></literal></entry>
<entry><type>setof record</type></entry>
<entry>
- List information about a partition tree for the given partitioned
- table, consisting of one row for each partition in a tree. The
- information available is the OID of the partition, the OID of its
- immediate partitioned table, and its level in the hierarchy,
- beginning at <literal>0</literal> for the top-most parent, and
- incremented by <literal>1</literal> for each level up.
+ 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, 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>
diff --git a/src/backend/utils/adt/partitionfuncs.c
b/src/backend/utils/adt/partitionfuncs.c
index fc0a904967..41c57cac2d 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -1,7 +1,7 @@
/*-------------------------------------------------------------------------
*
* partitionfuncs.c
- * Functions for accessing partitioning data
+ * Functions for accessing partitioning related metadata
*
* Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
diff --git a/src/test/regress/sql/partition_info.sql
b/src/test/regress/sql/partition_info.sql
index 2ad75882b0..d9d96a284c 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -17,11 +17,24 @@ SELECT relid::regclass, parentrelid::regclass, level,
pg_relation_size(relid) = 0 AS is_empty
FROM pg_partition_tree('ptif_test'::regclass);
--- children of the main tree
-SELECT relid::regclass, parentrelid::regclass, level
- FROM pg_partition_tree('ptif_test0'::regclass);
-SELECT relid::regclass, parentrelid::regclass, level
- FROM pg_partition_tree('ptif_test01'::regclass);
+-- check that it works correctly even if it's passed other tables in the tree
+
+-- passing an intermediate level partitioned table
+SELECT relid::regclass, parentrelid::regclass, level, relkind <> 'p' AS 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, relkind <> 'p' AS isleaf
+ FROM pg_partition_tree('ptif_test01'::regclass) p
+ JOIN pg_class c ON (p.relid = c.oid);
+
+-- 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, relkind <> 'p' AS isleaf
+ FROM pg_partition_tree('ptif_normal_table'::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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9a7f683658..6dfa3dc977 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20197,6 +20197,47 @@ 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, 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');
+ 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..41c57cac2d
--- /dev/null
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -0,0 +1,128 @@
+/*-------------------------------------------------------------------------
+ *
+ * partitionfuncs.c
+ * Functions for accessing partitioning 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_inherits.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
+#include "utils/fmgrprotos.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 and its level in
+ * the hierarchy.
+ */
+Datum
+pg_partition_tree(PG_FUNCTION_ARGS)
+{
+#define PG_PARTITION_TREE_COLS 3
+ 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, "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);
+ 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;
+
+ /* level */
+ if (relid != rootrelid)
+ {
+ foreach(lc, ancestors)
+ {
+ level++;
+ if (lfirst_oid(lc) == rootrelid)
+ break;
+ }
+ }
+ values[2] = 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 8e4145f42b..5525ad427b 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' },
+# 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,int4}',
+ proargmodes => '{i,o,o,o}',
+ proargnames => '{rootrelid,relid,parentrelid,level}',
+ prosrc => 'pg_partition_tree' }
+
]
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..d9d96a284c
--- /dev/null
+++ b/src/test/regress/sql/partition_info.sql
@@ -0,0 +1,43 @@
+--
+-- 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(-500, 500) i;
+
+-- all tables in the tree with some size information
+SELECT relid::regclass, parentrelid::regclass, level,
+ pg_relation_size(relid) = 0 AS is_empty
+ FROM pg_partition_tree('ptif_test'::regclass);
+
+-- check that it works correctly even if it's passed other tables in the tree
+
+-- passing an intermediate level partitioned table
+SELECT relid::regclass, parentrelid::regclass, level, relkind <> 'p' AS 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, relkind <> 'p' AS isleaf
+ FROM pg_partition_tree('ptif_test01'::regclass) p
+ JOIN pg_class c ON (p.relid = c.oid);
+
+-- 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, relkind <> 'p' AS isleaf
+ FROM pg_partition_tree('ptif_normal_table'::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;
