From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [Sender Address Forgery]Re: pg_(total_)relation_size and partitioned tables |
Date: | 2018-01-19 09:28:41 |
Message-ID: | 7a9c5328-5328-52a3-2a3d-bf1434b4dd1d@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thanks for taking a look.
On 2018/01/19 14:39, Michael Paquier wrote:
> On Thu, Jan 18, 2018 at 06:54:18PM +0900, Amit Langote wrote:
>> I think having pg_partition_root() and pg_partition_parent() will give
>> users enough to get useful views as follows:
>
> So... pg_partition_root() gives you access to the highest relation in
> the hierarchy, and pg_partition_parent() gives you access to the direct
> parent.
Right.
>> 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);
>
> You need to reorder those queries, the creation of the first p12 would
> fail as p1 does not exist at this point.
Oops. I had copy-pasted above commands from the psql's \s output and
ended up copying the command I didn't intend to. Here it is again, but
without the mistake I made in my last email:
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 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);
> Wouldn't also a
> pg_partition_tree() be useful? You could shape it as a function which
> returns all regclass partitions in the tree as unique entries. Combined
> with pg_partition_parent() it can be powerful as it returns NULL for the
> partition at the top of the tree. So I think that we could live without
> pg_partition_root(). At the end, let's design something which makes
> unnecessary the use of WITH RECURSIVE when looking at a full partition
> tree to ease the user's life.
Do you mean pg_partition_tree(regclass), that returns all partitions in
the partition tree whose root is passed as the parameter?
Perhaps, like the following (roughly implemented in the attached)?
select pg_partition_root(p) as root_parent,
pg_partition_parent(p) as parent,
p as relname,
pg_total_relation_size(p) as size
from pg_partition_tree_tables('p') p
order by 4;
root_parent | parent | relname | size
-------------+--------+---------+---------
p | | p | 0
p | p | p123 | 0
p | p123 | p12 | 0
p | p123 | p3 | 3653632
p | p12 | p1 | 3653632
p | p12 | p2 | 3653632
(6 rows)
> Documentation, as well as regression tests, would be welcome :)
OK, I will add those things in the next version.
Thanks,
Amit
Attachment | Content-Type | Size |
---|---|---|
0001-Add-assorted-partition-reporting-functions.patch | text/plain | 8.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Khandekar | 2018-01-19 09:37:14 | Re: [HACKERS] UPDATE of partition key |
Previous Message | Magnus Hagander | 2018-01-19 09:26:19 | Re: [HACKERS] Function to move the position of a replication slot |