On 2018/01/02 22:45, Peter Eisentraut wrote:
> On 12/28/17 16:24, David Rowley wrote:
>>> select pg_partition_root(c.oid), c.relname, pg_table_size(c.oid)
>>> from pg_class c
>>> order by 1
>>>
>>> select pg_partition_root(c.oid), sum(pg_table_size(c.oid))
>>> from pg_class c
>>> group by 1
>>
>> That seems much nicer. I assume "root" would mean the top level
>> partitioned table. If so, would we also want
>> pg_partition_parent(regclass)? Or maybe something to control the
>> number of "levels-up" the function would run for. If we had that then
>> maybe -1 could mean "go until you find a table with no parent".
>
> Hmm, we need to think through some scenarios for what one would really
> want to do with this functionality.
>
> Clearly, the existing behavior is useful for management tasks like bloat
> and vacuum monitoring.
>
> And on the other hand you might want to have a logical view of, how big
> is this partitioned table altogether.
>
> But what are the uses for dealing with partial partition hierarchies?
> How easy do we need to make that?
I think having pg_partition_root() and pg_partition_parent() will give
users enough to get useful views as follows:
drop table p;
create table p (a int) partition by list (a);
create table p123 partition of p for values in (1, 2, 3) partition by list
(a);
create table p12 partition of p1 for values in (1, 2) partition by list (a);
create table p12 partition of p123 for values in (1, 2) partition by list (a);
create table p1 partition of p12 for values in (1);
create table p2 partition of p12 for values in (2);
create table p3 partition of p123 for values in (3);
insert into p select 1 from generate_series(1, 100);
insert into p select 2 from generate_series(1, 100);
insert into p select 3 from generate_series(1, 100);
select pg_partition_root(oid) as root_parent,
pg_partition_parent(oid) as parent,
relname as relname,
pg_total_relation_size(oid) as size
from pg_class
where relnamespace = 'public'::regnamespace
order by 4;
root_parent | parent | relname | size
-------------+--------+---------+------
p | | p | 0
p | p | p123 | 0
p | p123 | p12 | 0
p | p12 | p1 | 8192
p | p12 | p2 | 8192
p | p123 | p3 | 8192
(6 rows)
select pg_partition_root(oid) as root_parent,
sum(pg_total_relation_size(oid)) as size
from pg_class
where relnamespace = 'public'::regnamespace
group by 1
order by 1;
root_parent | size
-------------+-------
p | 24576
(1 row)
Attached a WIP patch.
Thanks,
Amit
From b1c0973c2b363d03b4d074d324560048f48ad5a7 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 a pg_partition_root() and pg_partition_parent()
---
src/backend/catalog/partition.c | 37 ++++++++++++++++++++++++++++++++++++-
src/backend/utils/adt/misc.c | 34 ++++++++++++++++++++++++++++++++++
src/backend/utils/cache/lsyscache.c | 22 ++++++++++++++++++++++
src/include/catalog/partition.h | 1 +
src/include/catalog/pg_proc.h | 8 ++++++++
src/include/utils/lsyscache.h | 1 +
6 files changed, 102 insertions(+), 1 deletion(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8adc4ee977..cf5f971b91 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -181,6 +181,7 @@ static int partition_bound_bsearch(PartitionKey key,
static int get_partition_bound_num_indexes(PartitionBoundInfo b);
static int get_greatest_modulus(PartitionBoundInfo b);
static uint64 compute_hash_value(PartitionKey key, Datum *values, bool
*isnull);
+static Oid get_partition_parent_internal(Oid relid, bool recurse_to_root);
/* SQL-callable function for use in hash partition CHECK constraints */
PG_FUNCTION_INFO_V1(satisfies_hash_partition);
@@ -1362,7 +1363,7 @@ check_default_allows_bound(Relation parent, Relation
default_rel,
/*
* get_partition_parent
*
- * Returns inheritance parent of a partition by scanning pg_inherits
+ * Returns inheritance parent of a partition.
*
* 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
@@ -1371,6 +1372,37 @@ check_default_allows_bound(Relation parent, Relation
default_rel,
Oid
get_partition_parent(Oid relid)
{
+ if (!get_rel_relispartition(relid))
+ return InvalidOid;
+
+ return get_partition_parent_internal(relid, false);
+}
+
+/*
+ * get_partition_root_parent
+ *
+ * Returns root inheritance ancestor of a partition.
+ */
+Oid
+get_partition_root_parent(Oid relid)
+{
+ if (!get_rel_relispartition(relid))
+ return InvalidOid;
+
+ return get_partition_parent_internal(relid, true);
+}
+
+/*
+ * get_partition_parent_internal
+ *
+ * Returns inheritance parent of a partition by scanning pg_inherits.
+ * If recurse_to_root, it will check if the parent itself is a partition and
+ * if so, it will recurse to find its parent and so on until root parent is
+ * found.
+ */
+static Oid
+get_partition_parent_internal(Oid relid, bool recurse_to_root)
+{
Form_pg_inherits form;
Relation catalogRelation;
SysScanDesc scan;
@@ -1402,6 +1434,9 @@ get_partition_parent(Oid relid)
systable_endscan(scan);
heap_close(catalogRelation, AccessShareLock);
+ if (recurse_to_root && get_rel_relispartition(result))
+ result = get_partition_parent_internal(result, recurse_to_root);
+
return result;
}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 2e1e020c4b..2bced6a637 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -997,3 +997,37 @@ pg_get_replica_identity_index(PG_FUNCTION_ARGS)
else
PG_RETURN_NULL();
}
+
+/*
+ * SQL wrapper around get_partition_root_parent() in
+ * src/backend/catalog/partition.c.
+ */
+Datum
+pg_partition_root(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid rootoid;
+
+ rootoid = get_partition_root_parent(reloid);
+ if (OidIsValid(rootoid))
+ PG_RETURN_OID(rootoid);
+ else
+ PG_RETURN_OID(reloid);
+}
+
+/*
+ * SQL wrapper around get_partition_parent() in
+ * src/backend/catalog/partition.c.
+ */
+Datum
+pg_partition_parent(PG_FUNCTION_ARGS)
+{
+ Oid reloid = PG_GETARG_OID(0);
+ Oid parentoid;
+
+ parentoid = get_partition_parent(reloid);
+ if (OidIsValid(parentoid))
+ PG_RETURN_OID(parentoid);
+ else
+ PG_RETURN_NULL();
+}
diff --git a/src/backend/utils/cache/lsyscache.c
b/src/backend/utils/cache/lsyscache.c
index e8aa179347..92353a6004 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1836,6 +1836,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 2faf0ca26e..287642b01b 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -52,6 +52,7 @@ extern PartitionBoundInfo
partition_bounds_copy(PartitionBoundInfo src,
extern void check_new_partition_bound(char *relname, Relation parent,
PartitionBoundSpec *spec);
extern Oid get_partition_parent(Oid relid);
+extern Oid get_partition_root_parent(Oid relid);
extern List *get_qual_from_partbound(Relation rel, Relation parent,
PartitionBoundSpec *spec);
extern List *map_partition_varattnos(List *expr, int fromrel_varno,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f01648c961..0f5022dad7 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5533,6 +5533,14 @@ DESCR("list of files in the WAL directory");
DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f
f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_
satisfies_hash_partition _null_ _null_ _null_ ));
DESCR("hash partition CHECK constraint");
+/* function to get the root partition parent */
+DATA(insert OID = 3281 ( pg_partition_root PGNSP PGUID 12 10 0 0 0 f f f f t
f s s 1 0 2205 "2205" _null_ _null_ _null_ _null_ _null_ pg_partition_root
_null_ _null_ _null_ ));
+DESCR("oid of the partition root parent");
+
+/* function to get the partition parent */
+DATA(insert OID = 3556 ( pg_partition_parent PGNSP PGUID 12 10 0 0 0 f f f f
t f s s 1 0 2205 "2205" _null_ _null_ _null_ _null_ _null_ pg_partition_parent
_null_ _null_ _null_ ));
+DESCR("oid of the partition immediate parent");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 9731e6f7ae..1000d9fd13 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -127,6 +127,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
