Hi.
As discussed a little while back [1] and also recently mentioned [2], here
is a patch that adds a set of functions to inspect the details of a
partition tree. There are three functions:
pg_partition_parent(regclass) returns regclass
pg_partition_root_parent(regclass) returns regclass
pg_partition_tree_tables(regclass) returns setof regclass
Here is an example showing how one may want to use them.
create table p (a int, b int) partition by range (a);
create table p0 partition of p for values from (minvalue) to (0) partition
by hash (b);
create table p00 partition of p0 for values with (modulus 2, remainder 0);
create table p01 partition of p0 for values with (modulus 2, remainder 1);
create table p1 partition of p for values from (0) to (maxvalue) partition
by hash (b);
create table p10 partition of p1 for values with (modulus 2, remainder 0);
create table p11 partition of p1 for values with (modulus 2, remainder 1);
insert into p select i, i from generate_series(-5, 5) i;
select pg_partition_parent('p0') as parent;
parent
--------
p
(1 row)
Time: 1.469 ms
select pg_partition_parent('p01') as parent;
parent
--------
p0
(1 row)
Time: 1.330 ms
select pg_partition_root_parent('p01') as root_parent;
root_parent
-------------
p
(1 row)
select p as relname,
pg_partition_parent(p) as parent,
pg_partition_root_parent(p) as root_parent
from pg_partition_tree_tables('p') p;
relname | parent | root_parent
---------+--------+-------------
p | | p
p0 | p | p
p1 | p | p
p00 | p0 | p
p01 | p0 | p
p10 | p1 | p
p11 | p1 | p
(7 rows)
select p as relname,
pg_partition_parent(p) as parent,
pg_partition_root_parent(p) as root_parent,
pg_relation_size(p) as size
from pg_partition_tree_tables('p') p;
relname | parent | root_parent | size
---------+--------+-------------+------
p | | p | 0
p0 | p | p | 0
p1 | p | p | 0
p00 | p0 | p | 8192
p01 | p0 | p | 8192
p10 | p1 | p | 8192
p11 | p1 | p | 8192
(7 rows)
select sum(pg_relation_size(p)) as total_size
from pg_partition_tree_tables('p') p;
total_size
-------------
32768
(1 row)
Feedback is welcome!
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/flat/495cec7e-f8d9-7e13-4807-90dbf4eec4ea%40lab.ntt.co.jp
[2]
https://www.postgresql.org/message-id/18e000e8-9bcc-1bb5-2f50-56d434c8be1f%40lab.ntt.co.jp
From 190158bd4b937b1978bfa29e8e9801fa04e0df0d Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 16 Jan 2018 19:02:13 +0900
Subject: [PATCH v1] Add assorted partition reporting functions
---
doc/src/sgml/func.sgml | 34 ++++++++++
src/backend/catalog/partition.c | 129 ++++++++++++++++++++++++++++++++++--
src/backend/utils/cache/lsyscache.c | 22 ++++++
src/include/catalog/partition.h | 1 +
src/include/catalog/pg_proc.dat | 18 +++++
src/include/utils/lsyscache.h | 1 +
6 files changed, 201 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5dce8ef178..df621d1e17 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19995,6 +19995,40 @@ 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_root_parent(<parameter>regclass</parameter>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get root table of partition tree of which the table is
part</entry>
+ </row>
+ <row>
+
<entry><literal><function>pg_partition_parent(<parameter>regclass</parameter>)</function></literal></entry>
+ <entry><type>regclass</type></entry>
+ <entry>get parent table if the table is a partition,
<literal>NULL</literal> otherwise</entry>
+ </row>
+ <row>
+
<entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>)</function></literal></entry>
+ <entry><type>setof regclass</type></entry>
+ <entry>get all tables in partition tree under given root table</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ If the table passed to <function>pg_partition_root_parent</function> is not
+ a partition, the same table is returned as the result. Result of
+ <function>pg_partition_tree_tables</function> also contains the table
+ that's passed to it as the first row.
+ </para>
+
</sect2>
<sect2 id="functions-admin-index">
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 558022647c..5b3e8d52c5 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -23,13 +23,16 @@
#include "catalog/partition.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_partitioned_table.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"
@@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel,
Oid relid,
* Obtain direct parent of given relation
*
* Returns inheritance parent of a partition by scanning pg_inherits
- *
- * Note: Because this function assumes that the relation whose OID is passed
- * as an argument will have precisely one parent, it should only be called
- * when it is known that the relation is a partition.
*/
Oid
get_partition_parent(Oid relid)
@@ -55,6 +54,9 @@ get_partition_parent(Oid relid)
Relation catalogRelation;
Oid result;
+ if (!get_rel_relispartition(relid))
+ return InvalidOid;
+
catalogRelation = heap_open(InheritsRelationId, AccessShareLock);
result = get_partition_parent_worker(catalogRelation, relid);
@@ -71,6 +73,10 @@ get_partition_parent(Oid relid)
* get_partition_parent_worker
* Scan the pg_inherits relation to return the OID of the parent
of the
* given relation
+ *
+ * Note: Because this function assumes that the relation whose OID is passed
+ * as an argument will have precisely one parent, it should only be called
+ * when it is known that the relation is a partition.
*/
static Oid
get_partition_parent_worker(Relation inhRel, Oid relid)
@@ -148,6 +154,27 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid,
List **ancestors)
}
/*
+ * get_partition_root_parent
+ *
+ * Returns root inheritance ancestor of a partition.
+ */
+Oid
+get_partition_root_parent(Oid relid)
+{
+ List *ancestors;
+ Oid result;
+
+ if (!get_rel_relispartition(relid))
+ return InvalidOid;
+
+ ancestors = get_partition_ancestors(relid);
+ result = llast_oid(ancestors);
+ list_free(ancestors);
+
+ return result;
+}
+
+/*
* map_partition_varattnos - maps varattno of any Vars in expr from the
* attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which
* may be either a leaf partition or a partitioned table, but both of which
@@ -357,3 +384,97 @@ get_proposed_default_constraint(List *new_part_constraints)
return make_ands_implicit(defPartConstraint);
}
+
+/*
+ * SQL wrapper around get_partition_root_parent().
+ */
+Datum
+pg_partition_root_parent(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid rootoid;
+
+ rootoid = get_partition_root_parent(reloid);
+
+ /*
+ * If the relation is actually a partition, 'rootoid' has been set to
the
+ * OID of the root table in the partition tree.
+ */
+ if (OidIsValid(rootoid))
+ PG_RETURN_OID(rootoid);
+ /*
+ * Otherwise, the table's not a partition. That is, it's either the
root
+ * table in a partition tree or a standalone table that's not part of
any
+ * partition tree. In any case, return the table OID itself as the
+ * result.
+ */
+ else
+ PG_RETURN_OID(reloid);
+}
+
+/*
+ * SQL wrapper around get_partition_parent().
+ */
+Datum
+pg_partition_parent(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid parentoid;
+
+ parentoid = get_partition_parent(reloid);
+
+ /*
+ * If the relation is actually a partition, 'parentoid' has been set to
+ * the OID of the immediate parent.
+ */
+ if (OidIsValid(parentoid))
+ PG_RETURN_OID(parentoid);
+ else
+ /* Not a partition, return NULL. */
+ PG_RETURN_NULL();
+}
+
+/*
+ * Returns OIDs of tables in a partition tree.
+ */
+Datum
+pg_partition_tree_tables(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ Oid reloid = PG_GETARG_OID(0);
+ List *partoids;
+ ListCell **lc;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* switch to memory context appropriate for multiple function
calls */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ partoids = find_all_inheritors(reloid, NoLock, NULL);
+ lc = (ListCell **) palloc(sizeof(ListCell *));
+ *lc = list_head(partoids);
+ funcctx->user_fctx = (void *) lc;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ lc = (ListCell **) funcctx->user_fctx;
+
+ while (*lc != NULL)
+ {
+ Oid partoid = lfirst_oid(*lc);
+
+ *lc = lnext(*lc);
+ SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid));
+ }
+
+ SRF_RETURN_DONE(funcctx);
+}
diff --git a/src/backend/utils/cache/lsyscache.c
b/src/backend/utils/cache/lsyscache.c
index bba595ad1d..19262c6c4d 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid)
}
/*
+ * get_rel_relispartition
+ *
+ * Returns the value of pg_class.relispartition for a given
relation.
+ */
+char
+get_rel_relispartition(Oid relid)
+{
+ HeapTuple tp;
+ Form_pg_class reltup;
+ bool result;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+ result = reltup->relispartition;
+ ReleaseSysCache(tp);
+
+ return result;
+}
+
+/*
* get_rel_tablespace
*
* Returns the pg_tablespace OID associated with a given relation.
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 1f49e5d3a9..a2b11f40bc 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -32,6 +32,7 @@ typedef struct PartitionDescData
extern Oid get_partition_parent(Oid relid);
extern List *get_partition_ancestors(Oid relid);
+extern Oid get_partition_root_parent(Oid relid);
extern List *map_partition_varattnos(List *expr, int fromrel_varno,
Relation to_rel, Relation
from_rel,
bool *found_whole_row);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 40d54ed030..b4725b8634 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10206,4 +10206,22 @@
proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any',
proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' },
+# function to get the root partition parent
+{ oid => '3423', descr => 'oid of the partition root parent',
+ proname => 'pg_partition_root_parent', prorettype => 'regclass',
+ proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' },
+
+# function to get the partition parent
+{ oid => '3424', descr => 'oid of the partition immediate parent',
+ proname => 'pg_partition_parent', prorettype => 'regclass',
+ proargtypes => 'regclass', prosrc => 'pg_partition_parent' },
+
+# function to get OIDs of all tables in a given partition tree
+{ oid => '3425', descr => 'get OIDs of tables in a partition tree',
+ proname => 'pg_partition_tree_tables', prorettype => '2205',
+ prorows => '100', proretset => 't', proargtypes => 'regclass',
+ proallargtypes => '{regclass,regclass}',
+ proargmodes => '{i,o}',
+ proargnames => '{relid,relid}', prosrc => 'pg_partition_tree_tables' }
+
]
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index e55ea4035b..d396d17ff1 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid);
extern Oid get_rel_namespace(Oid relid);
extern Oid get_rel_type_id(Oid relid);
extern char get_rel_relkind(Oid relid);
+extern char get_rel_relispartition(Oid relid);
extern Oid get_rel_tablespace(Oid relid);
extern char get_rel_persistence(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
--
2.11.0
