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 | Resend email |
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 |
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 |