partition tree inspection functions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: partition tree inspection functions
Date: 2018-06-26 05:08:04
Message-ID: 8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Add-assorted-partition-reporting-functions.patch text/plain 10.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-06-26 05:16:00 Re: automatic restore point
Previous Message Ashutosh Bapat 2018-06-26 04:49:45 Re: Problem while updating a foreign table pointing to a partitioned table on foreign server