pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Add pg_partition_root to display top-most parent of a partition
10+ messages / 3 participants
[nested] [flat]
* pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-02-08 00:07 Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-02-08 00:07 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Add pg_partition_root to display top-most parent of a partition tree
This is useful when looking at partition trees with multiple layers, and
combined with pg_partition_tree, it provides the possibility to show up
an entire tree by just knowing one member at any level.
Author: Michael Paquier
Reviewed-by: Álvaro Herrera, Amit Langote
Discussion: https://postgr.es/m/20181207014015.GP2407@paquier.xyz
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/3677a0b26bb2f3f72d16dc7fa6f34c305badacce
Modified Files
--------------
doc/src/sgml/func.sgml | 11 ++++
src/backend/utils/adt/partitionfuncs.c | 75 ++++++++++++++++++++++++----
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 5 ++
src/test/regress/expected/partition_info.out | 58 +++++++++++++++++++++
src/test/regress/sql/partition_info.sql | 13 +++++
6 files changed, 153 insertions(+), 11 deletions(-)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 03:26 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2019-03-22 03:26 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-committers@lists.postgresql.org
On 2019-Feb-08, Michael Paquier wrote:
> Add pg_partition_root to display top-most parent of a partition tree
>
> This is useful when looking at partition trees with multiple layers, and
> combined with pg_partition_tree, it provides the possibility to show up
> an entire tree by just knowing one member at any level.
I noticed days ago that if you call pg_partition_root on the topmost
partitioned table, the server crashes :-)
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 03:55 Michael Paquier <michael@paquier.xyz>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-03-22 03:55 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-committers@lists.postgresql.org
On Fri, Mar 22, 2019 at 12:26:12AM -0300, Alvaro Herrera wrote:
> I noticed days ago that if you call pg_partition_root on the topmost
> partitioned table, the server crashes :-)
It's when you think that the thing is actually done that another issue
pops up. The attached fixes the issue, I suggest to return the
top-most parent as result if the input is the top-most parent itself.
Returning NULL does not make sense as in this case the relation can be
part of a partition tree.
--
Michael
Attachments:
[text/x-diff] partition-root-fix.patch (3.7K, ../../20190322035538.GO20192@paquier.xyz/2-partition-root-fix.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt/partitionfuncs.c
index 98c8ef77f1..ff21cd3783 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -191,6 +191,14 @@ pg_partition_root(PG_FUNCTION_ARGS)
/* Fetch the top-most parent */
ancestors = get_partition_ancestors(relid);
+
+ /*
+ * If the listed partition is already the top-most parent, just
+ * return itself.
+ */
+ if (ancestors == NIL)
+ PG_RETURN_OID(relid);
+
rootrelid = llast_oid(ancestors);
list_free(ancestors);
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
index 92aa6abe3f..42b6bc77ca 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -46,6 +46,31 @@ CREATE TABLE ptif_test2 PARTITION OF ptif_test
-- This partitioned table should remain with no partitions.
CREATE TABLE ptif_test3 PARTITION OF ptif_test
FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+-- Test pg_partition_root for tables
+SELECT pg_partition_root('ptif_test');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test0');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test01');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test3');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
-- Test index partition tree
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
@@ -60,6 +85,31 @@ CREATE INDEX ptif_test2_index ON ptif_test2 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
CREATE INDEX ptif_test3_index ON ptif_test3 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
+-- Test pg_partition_root for indexes
+SELECT pg_partition_root('ptif_test_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test0_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test01_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test3_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
-- List all tables members of the tree
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test');
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql
index adaca23232..b5060bec7f 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -22,6 +22,12 @@ CREATE TABLE ptif_test2 PARTITION OF ptif_test
CREATE TABLE ptif_test3 PARTITION OF ptif_test
FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+-- Test pg_partition_root for tables
+SELECT pg_partition_root('ptif_test');
+SELECT pg_partition_root('ptif_test0');
+SELECT pg_partition_root('ptif_test01');
+SELECT pg_partition_root('ptif_test3');
+
-- Test index partition tree
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
@@ -37,6 +43,12 @@ ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
CREATE INDEX ptif_test3_index ON ptif_test3 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
+-- Test pg_partition_root for indexes
+SELECT pg_partition_root('ptif_test_index');
+SELECT pg_partition_root('ptif_test0_index');
+SELECT pg_partition_root('ptif_test01_index');
+SELECT pg_partition_root('ptif_test3_index');
+
-- List all tables members of the tree
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test');
[application/pgp-signature] signature.asc (832B, ../../20190322035538.GO20192@paquier.xyz/3-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Suspect SPAM] Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 04:09 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 10+ messages in thread
From: Amit Langote @ 2019-03-22 04:09 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-committers@lists.postgresql.org
Hi,
On 2019/03/22 12:55, Michael Paquier wrote:
> On Fri, Mar 22, 2019 at 12:26:12AM -0300, Alvaro Herrera wrote:
>> I noticed days ago that if you call pg_partition_root on the topmost
>> partitioned table, the server crashes :-)
I thought we already fixed that last month, but that was pg_partition_tree().
> It's when you think that the thing is actually done that another issue
> pops up. The attached fixes the issue, I suggest to return the
> top-most parent as result if the input is the top-most parent itself.
> Returning NULL does not make sense as in this case the relation can be
> part of a partition tree.
Thanks for writing the patch.
/* Fetch the top-most parent */
ancestors = get_partition_ancestors(relid);
Maybe, the patch should update this comment to say "Fetch the list of
ancestors".
+
+ /*
+ * If the listed partition is already the top-most parent, just
+ * return itself.
+ */
What does "listed partition" mean? Maybe, you mean "If the input
relation..."? How about write this as:
If the input relation is itself the top-most parent, just return itself.
Regards,
Amit
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Suspect SPAM] Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 04:12 Michael Paquier <michael@paquier.xyz>
parent: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
1 sibling, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-03-22 04:12 UTC (permalink / raw)
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-committers@lists.postgresql.org
On Fri, Mar 22, 2019 at 01:09:23PM +0900, Amit Langote wrote:
> /* Fetch the top-most parent */
> ancestors = get_partition_ancestors(relid);
>
> Maybe, the patch should update this comment to say "Fetch the list of
> ancestors".
Perhaps so. Note that actually the list is fetched to grab the
top-most parent from it, so it sort of makes sense?
> What does "listed partition" mean? Maybe, you mean "If the input
> relation..."? How about write this as:
Input relation sounds better.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20190322041225.GS20192@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 04:16 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
parent: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
1 sibling, 0 replies; 10+ messages in thread
From: Amit Langote @ 2019-03-22 04:16 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-committers@lists.postgresql.org
Sorry about the messed up subject string of my previous reply. I failed
to notice that our internal mailing software occasionally adds that to
email headers before the emails get to my machine. I've been asked to be
careful before, but I didn't notice it again today. :-(
Thanks,
Amit
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 04:28 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Amit Langote @ 2019-03-22 04:28 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-committers@lists.postgresql.org
On 2019/03/22 13:12, Michael Paquier wrote:
> On Fri, Mar 22, 2019 at 01:09:23PM +0900, Amit Langote wrote:
>> /* Fetch the top-most parent */
>> ancestors = get_partition_ancestors(relid);
>>
>> Maybe, the patch should update this comment to say "Fetch the list of
>> ancestors".
>
> Perhaps so. Note that actually the list is fetched to grab the
> top-most parent from it, so it sort of makes sense?
It looked fine before, but the new lines added by patch makes it look
wrong/misplaced somehow.
Thanks,
Amit
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 05:13 Michael Paquier <michael@paquier.xyz>
parent: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-03-22 05:13 UTC (permalink / raw)
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-committers@lists.postgresql.org
On Fri, Mar 22, 2019 at 01:28:19PM +0900, Amit Langote wrote:
> It looked fine before, but the new lines added by patch makes it look
> wrong/misplaced somehow.
Okay, what do you think about the attached then?
--
Michael
Attachments:
[text/x-diff] partition-root-fix-v2.patch (3.8K, ../../20190322051349.GU20192@paquier.xyz/2-partition-root-fix-v2.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt/partitionfuncs.c
index 98c8ef77f1..17211888dd 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -189,8 +189,16 @@ pg_partition_root(PG_FUNCTION_ARGS)
if (!check_rel_can_be_partition(relid))
PG_RETURN_NULL();
- /* Fetch the top-most parent */
+ /* fetch the list of ancestors */
ancestors = get_partition_ancestors(relid);
+
+ /*
+ * If the input relation is already the top-most parent, just
+ * return itself.
+ */
+ if (ancestors == NIL)
+ PG_RETURN_OID(relid);
+
rootrelid = llast_oid(ancestors);
list_free(ancestors);
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
index 92aa6abe3f..42b6bc77ca 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -46,6 +46,31 @@ CREATE TABLE ptif_test2 PARTITION OF ptif_test
-- This partitioned table should remain with no partitions.
CREATE TABLE ptif_test3 PARTITION OF ptif_test
FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+-- Test pg_partition_root for tables
+SELECT pg_partition_root('ptif_test');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test0');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test01');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test3');
+ pg_partition_root
+-------------------
+ ptif_test
+(1 row)
+
-- Test index partition tree
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
@@ -60,6 +85,31 @@ CREATE INDEX ptif_test2_index ON ptif_test2 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
CREATE INDEX ptif_test3_index ON ptif_test3 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
+-- Test pg_partition_root for indexes
+SELECT pg_partition_root('ptif_test_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test0_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test01_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test3_index');
+ pg_partition_root
+-------------------
+ ptif_test_index
+(1 row)
+
-- List all tables members of the tree
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test');
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql
index adaca23232..b5060bec7f 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -22,6 +22,12 @@ CREATE TABLE ptif_test2 PARTITION OF ptif_test
CREATE TABLE ptif_test3 PARTITION OF ptif_test
FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+-- Test pg_partition_root for tables
+SELECT pg_partition_root('ptif_test');
+SELECT pg_partition_root('ptif_test0');
+SELECT pg_partition_root('ptif_test01');
+SELECT pg_partition_root('ptif_test3');
+
-- Test index partition tree
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
@@ -37,6 +43,12 @@ ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
CREATE INDEX ptif_test3_index ON ptif_test3 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
+-- Test pg_partition_root for indexes
+SELECT pg_partition_root('ptif_test_index');
+SELECT pg_partition_root('ptif_test0_index');
+SELECT pg_partition_root('ptif_test01_index');
+SELECT pg_partition_root('ptif_test3_index');
+
-- List all tables members of the tree
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test');
[application/pgp-signature] signature.asc (832B, ../../20190322051349.GU20192@paquier.xyz/3-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 07:05 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Amit Langote @ 2019-03-22 07:05 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-committers@lists.postgresql.org
On 2019/03/22 14:13, Michael Paquier wrote:
> On Fri, Mar 22, 2019 at 01:28:19PM +0900, Amit Langote wrote:
>> It looked fine before, but the new lines added by patch makes it look
>> wrong/misplaced somehow.
>
> Okay, what do you think about the attached then?
Thanks, looks fine.
Regards,
Amit
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgsql: Add pg_partition_root to display top-most parent of a partition
@ 2019-03-22 08:35 Michael Paquier <michael@paquier.xyz>
parent: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
0 siblings, 0 replies; 10+ messages in thread
From: Michael Paquier @ 2019-03-22 08:35 UTC (permalink / raw)
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-committers@lists.postgresql.org
On Fri, Mar 22, 2019 at 04:05:20PM +0900, Amit Langote wrote:
> Thanks, looks fine.
OK, committed.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20190322083528.GW20192@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-03-22 08:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-08 00:07 pgsql: Add pg_partition_root to display top-most parent of a partition Michael Paquier <michael@paquier.xyz>
2019-03-22 03:26 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-03-22 03:55 ` Michael Paquier <michael@paquier.xyz>
2019-03-22 04:09 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2019-03-22 04:12 ` Michael Paquier <michael@paquier.xyz>
2019-03-22 04:28 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2019-03-22 05:13 ` Michael Paquier <michael@paquier.xyz>
2019-03-22 07:05 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2019-03-22 08:35 ` Michael Paquier <michael@paquier.xyz>
2019-03-22 04:16 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox