partition tree inspection functions

On 2018/06/26 14:08, Amit Langote wrote:

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!

Added this to July CF.

Thanks,
Amit

Hi Amit,

On Tue, Jun 26, 2018 at 1:07 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp

wrote:

On 2018/06/26 14:08, Amit Langote wrote:

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

I quickly tried applying your patch. Created couple of tables,
subpartitions with
mix of range and list partitions, and I see these 3 functions are working as
documented.

Also, the patch does not have any 'make check' failures.

I will do the further code review and post if any comments.

Regards,
Jeevan Ladhe

On 2018/06/26 16:54, Jeevan Ladhe wrote:

Hi Amit,

On Tue, Jun 26, 2018 at 1:07 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp

wrote:

On 2018/06/26 14:08, Amit Langote wrote:

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

I quickly tried applying your patch. Created couple of tables,
subpartitions with
mix of range and list partitions, and I see these 3 functions are working as
documented.

Also, the patch does not have any 'make check' failures.

I will do the further code review and post if any comments.

Thanks Jeevan for reviewing.

Thanks,
Amit

On Tue, Jun 26, 2018 at 04:57:53PM +0900, Amit Langote wrote:

Thanks Jeevan for reviewing.

I would like to make things more user-friendly in this area, but could
you add a couple of tests to show up how things work? I just had a very
quick glance at what's proposed at the top of the thread.
--
Michael

Thanks for taking a look.

On 2018/06/27 21:16, Michael Paquier wrote:

I would like to make things more user-friendly in this area, but could
you add a couple of tests to show up how things work? I just had a very
quick glance at what's proposed at the top of the thread.

I thought about adding tests, but wasn't sure where such tests would go.

For now, I've added them to create_table.sql, but maybe that's not where
they really belong. Attached updated patch with tests.

Thanks,
Amit

On Thu, Jun 28, 2018 at 11:50:13AM +0900, Amit Langote wrote:

For now, I've added them to create_table.sql, but maybe that's not where
they really belong. Attached updated patch with tests.

It would have imagined that just creating a new file, say
partition_desc.sql or similar is nicer.

+   ancestors = get_partition_ancestors(relid);
+   result = llast_oid(ancestors);
+   list_free(ancestors);
Relying on the fact that the top-most parent should be the last one in
the list is brittle in my opinion.

What this patch proposes is:
- pg_partition_root_parent to get the top-most parent within a partition
tree for a partition.
- pg_partition_parent to get the direct parent for a partition.
- pg_partition_tree_tables to get a full list of all the children
underneath.

As the goal is to facilitate the life of users so as they don't have to
craft any WITH RECURSIVE, I think that we could live with that.

+   <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>
Okay for that part as well.

I haven't yet looked at the code in details, but what you are proposing
here looks sound. Could you think about adding an example in the docs
about how to use them? Say for a measurement table here is a query to
get the full size a partition tree takes.. That's one idea.
--
Michael

Thanks again for quick review.

On 2018/06/28 12:43, Michael Paquier wrote:

On Thu, Jun 28, 2018 at 11:50:13AM +0900, Amit Langote wrote:

For now, I've added them to create_table.sql, but maybe that's not where
they really belong. Attached updated patch with tests.

It would have imagined that just creating a new file, say
partition_desc.sql or similar is nicer.

How about partition_info.sql because they're testing partitioning
information functions? partition_desc reminded me of PartitionDesc, an
internal structure used in the partitioning codem which made me a bit
uncomfortable.

+   ancestors = get_partition_ancestors(relid);
+   result = llast_oid(ancestors);
+   list_free(ancestors);

Relying on the fact that the top-most parent should be the last one in
the list is brittle in my opinion.

get_partition_ancestor stops adding OIDs to the list once it reaches a
table in the ancestor chain that doesn't itself have parent (the root), so
the last OID in the returned list *must* be the root parent.

Do you think adding a check that the OID in result is indeed NOT a
partition would make it look less brittle? I added an Assert below that
llast_oid statement.

What this patch proposes is:
- pg_partition_root_parent to get the top-most parent within a partition
tree for a partition.
- pg_partition_parent to get the direct parent for a partition.
- pg_partition_tree_tables to get a full list of all the children
underneath.

As the goal is to facilitate the life of users so as they don't have to
craft any WITH RECURSIVE, I think that we could live with that.

+   <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>
Okay for that part as well.

I haven't yet looked at the code in details, but what you are proposing
here looks sound. Could you think about adding an example in the docs
about how to use them? Say for a measurement table here is a query to
get the full size a partition tree takes.. That's one idea.

OK, I've added an example below the table of functions added by the patch.

Attached updated patch.

Thanks,
Amit

On 6/26/18 07:08, Amit Langote wrote:

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

Does this add anything over writing a recursive query on pg_inherits?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018/06/28 17:40, Peter Eisentraut wrote:

On 6/26/18 07:08, Amit Langote wrote:

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

Does this add anything over writing a recursive query on pg_inherits?

As far as the information output is concerned, it doesn't.

Thanks,
Amit

On 6/28/18 10:59, Amit Langote wrote:

On 2018/06/28 17:40, Peter Eisentraut wrote:

On 6/26/18 07:08, Amit Langote wrote:

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

Does this add anything over writing a recursive query on pg_inherits?

As far as the information output is concerned, it doesn't.

I'm thinking, an SQL query might be more efficient if you want to
qualify the query further. For example, give me all tables in this tree
that match '2018'. If you wrote your functions as SQL-language
functions, the optimizer could perhaps inline them and optimize them
further.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Jun 28, 2018 at 12:37:14PM +0200, Peter Eisentraut wrote:

I'm thinking, an SQL query might be more efficient if you want to
qualify the query further. For example, give me all tables in this tree
that match '2018'. If you wrote your functions as SQL-language
functions, the optimizer could perhaps inline them and optimize them
further.

Are you thinking about SQL functions here? Here is an example of query
able to fetch an entire partition tree.
WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'your_parent_table_name_here' AND
relkind = 'p'
UNION ALL
SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition
)
SELECT * FROM partition_info;

Getting the direct parent is immediate, and getting the top-most parent
would be rather similar to that. Not much elegant in my opinion, but
that's mainly a matter of taste?
--
Michael

On Thu, Jun 28, 2018 at 11:19 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

It would have imagined that just creating a new file, say
partition_desc.sql or similar is nicer.

How about partition_info.sql because they're testing partitioning
information functions? partition_desc reminded me of PartitionDesc, an
internal structure used in the partitioning codem which made me a bit
uncomfortable.

I think we should just add calls to these functions/views wherever we
are creating/altering or deleting objects to test a partition tree. I
serves two purposes, testing the objects created/modified and testing
the functions. Adding a new test file means we have to craft new
objects, which are sometimes readily available in some other test
files. Of course, we might find that certain cases are not covered by
existing tests, but then that also means that those cases are not
covered by object modification/creation tests as well.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

On 6/28/18 13:30, Michael Paquier wrote:

On Thu, Jun 28, 2018 at 12:37:14PM +0200, Peter Eisentraut wrote:

I'm thinking, an SQL query might be more efficient if you want to
qualify the query further. For example, give me all tables in this tree
that match '2018'. If you wrote your functions as SQL-language
functions, the optimizer could perhaps inline them and optimize them
further.

Are you thinking about SQL functions here? Here is an example of query
able to fetch an entire partition tree.
WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'your_parent_table_name_here' AND
relkind = 'p'

[...]

Yes, this kind of thing should be more efficient than building the
entire tree in a C function and then filtering it afterwards.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018/06/28 22:01, Ashutosh Bapat wrote:

On Thu, Jun 28, 2018 at 11:19 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

It would have imagined that just creating a new file, say
partition_desc.sql or similar is nicer.

How about partition_info.sql because they're testing partitioning
information functions? partition_desc reminded me of PartitionDesc, an
internal structure used in the partitioning codem which made me a bit
uncomfortable.

I think we should just add calls to these functions/views wherever we
are creating/altering or deleting objects to test a partition tree. I
serves two purposes, testing the objects created/modified and testing
the functions. Adding a new test file means we have to craft new
objects, which are sometimes readily available in some other test
files. Of course, we might find that certain cases are not covered by
existing tests, but then that also means that those cases are not
covered by object modification/creation tests as well.

I think that makes sense. I couldn't assess by looking at tests for
various functions listed on 9.25. System Information Functions whether
there is some established practice about adding tests for them and/or
about where to put them.

For this particular set of functions, insert.sql may be a good place as it
has many tests involving multi-level partitioned tables.

Thanks,
Amit

On 2018/06/29 6:19, Peter Eisentraut wrote:

On 6/28/18 13:30, Michael Paquier wrote:

On Thu, Jun 28, 2018 at 12:37:14PM +0200, Peter Eisentraut wrote:

I'm thinking, an SQL query might be more efficient if you want to
qualify the query further. For example, give me all tables in this tree
that match '2018'. If you wrote your functions as SQL-language
functions, the optimizer could perhaps inline them and optimize them
further.

Are you thinking about SQL functions here? Here is an example of query
able to fetch an entire partition tree.
WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'your_parent_table_name_here' AND
relkind = 'p'

[...]

Yes, this kind of thing should be more efficient than building the
entire tree in a C function and then filtering it afterwards.

Hmm, it would be nice if the user-specified filters could get pushed down
under the CTE scan that will get generated for recursive union, but it
doesn't afaics. If there's no way to write the query such that they do
get pushed down, then using a C function to build the tree sounds better
than using a query.

For example, I compared using the quoted query (thanks Michael) and the
proposed pg_partition_tree_tables function on a partition tree with 1000
partitions and don't see much difference.

WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'ht' AND
relkind = 'p'
UNION ALL
SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition
)
SELECT * FROM partition_info WHERE relname LIKE '%01%';
relid │ relname │ relsize │ relispartition │ relkind
───────┼─────────┼─────────┼────────────────┼─────────
18616 │ ht_101 │ 0 │ t │ r
18916 │ ht_201 │ 0 │ t │ r
19216 │ ht_301 │ 0 │ t │ r
19516 │ ht_401 │ 0 │ t │ r
19816 │ ht_501 │ 0 │ t │ r
20116 │ ht_601 │ 0 │ t │ r
20416 │ ht_701 │ 0 │ t │ r
20716 │ ht_801 │ 0 │ t │ r
21016 │ ht_901 │ 0 │ t │ r
(9 rows)

Time: 47.562 ms

select p::oid as relid, p as relname, pg_relation_size(p) as relsize,
c.relispartition, c.relkind
from pg_partition_tree_tables('ht') p, pg_class c
where p::oid = c.oid and p::text like '%01%';
relid │ relname │ relsize │ relispartition │ relkind
───────┼─────────┼─────────┼────────────────┼─────────
18616 │ ht_101 │ 0 │ t │ r
18916 │ ht_201 │ 0 │ t │ r
19216 │ ht_301 │ 0 │ t │ r
19516 │ ht_401 │ 0 │ t │ r
19816 │ ht_501 │ 0 │ t │ r
20116 │ ht_601 │ 0 │ t │ r
20416 │ ht_701 │ 0 │ t │ r
20716 │ ht_801 │ 0 │ t │ r
21016 │ ht_901 │ 0 │ t │ r
(9 rows)

Time: 34.357 ms

Am I missing something?

Now, if the users write the query themselves and add whatever filters they
want to use, then that might be the fastest.

WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'ht' AND
relkind = 'p'
UNION ALL
SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition AND c.relname LIKE '%01%'
)
SELECT * FROM partition_info p WHERE p.relname LIKE '%01%';
relid │ relname │ relsize │ relispartition │ relkind
───────┼─────────┼─────────┼────────────────┼─────────
18616 │ ht_101 │ 0 │ t │ r
18916 │ ht_201 │ 0 │ t │ r
19216 │ ht_301 │ 0 │ t │ r
19516 │ ht_401 │ 0 │ t │ r
19816 │ ht_501 │ 0 │ t │ r
20116 │ ht_601 │ 0 │ t │ r
20416 │ ht_701 │ 0 │ t │ r
20716 │ ht_801 │ 0 │ t │ r
21016 │ ht_901 │ 0 │ t │ r
(9 rows)

Time: 27.276 ms

Thanks,
Amit

On Tue, Jun 26, 2018 at 10:38 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

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

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)

Is it a good idea to provide a function or an option which can provide
partitions detail in hierarchical order?

i.e
relname level
p 0
p0 1
p00 2
p01 2
p1 1
....

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Hi Amit,

On 06/28/2018 01:49 AM, Amit Langote wrote:

OK, I've added an example below the table of functions added by the patch.

Attached updated patch.

You forgot to remove the test output in create_table.out, so check-world
is failing.

In pg_partition_parent

+	else
+	/* Not a partition, return NULL. */
+		PG_RETURN_NULL();

I would just remove the "else" such that PG_RETURN_NULL() is fall-through.

I think pg_partition_tree_tables should have an option to exclude the
table that is being queried from the result (bool include_self).

Maybe a function like pg_partition_number_of_partitions() could be of
benefit to count the number of actual partitions in a tree. Especially
useful in complex scenarios,

select pg_partition_number_of_partitions('p') as number;

number
---------
4
(1 row)

New status: WfA

Best regards,
Jesper

Hi Dilip,

Sorry it took me a while to reply.

On 2018/06/29 14:30, Dilip Kumar wrote:

On Tue, Jun 26, 2018 at 10:38 AM, Amit Langote wrote:

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

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)

Is it a good idea to provide a function or an option which can provide
partitions detail in hierarchical order?

i.e
relname level
p 0
p0 1
p00 2
p01 2
p1 1

Yeah, might be a good idea. We could have a function
pg_partition_tree_level(OID) which will return the level of the table
that's passed to it the way you wrote above, meaning 0 for the root
parent, 1 for the root's immediate partitions, 2 for their partitions, and
so on.

Thanks,
Amit

Thanks for the review, Jesper.

On 2018/07/18 23:35, Jesper Pedersen wrote:

On 06/28/2018 01:49 AM, Amit Langote wrote:

OK, I've added an example below the table of functions added by the patch.

Attached updated patch.

You forgot to remove the test output in create_table.out, so check-world
is failing.

Oops, I'd noticed that but forgotten to post an updated patch.

Fixed.

In pg_partition_parent

+��� else
+��� /* Not a partition, return NULL. */
+������� PG_RETURN_NULL();

I would just remove the "else" such that PG_RETURN_NULL() is fall-through.

OK, done.

I think pg_partition_tree_tables should have an option to exclude the
table that is being queried from the result (bool include_self).

Doesn't sound too bad, so added include_self.

Maybe a function like pg_partition_number_of_partitions() could be of
benefit to count the number of actual partitions in a tree. Especially
useful in complex scenarios,

�select pg_partition_number_of_partitions('p') as number;

�� number
�---------
� 4
�(1 row)

Okay, adding one more function at this point may not be asking for too
much. Although, select count(*) from pg_partition_tree_tables('p') would
give you the count, a special function seems nice.

New status: WfA

Attached updated patch.

Thanks,
Amit