Add pg_partition_tree to display information about partitions

This new function is useful to display a full tree of partitions with a
partitioned table given in output, and avoids the need of any complex
WITH RECURSIVE query when looking at partition trees which are
deep multiple levels.

It returns a set of records, one for each partition, containing the
partition's name, its immediate parent's name, a boolean value telling
if the relation is a leaf in the tree and an integer telling its level
in the partition tree with given table considered as root, beginning at
zero for the root, and incrementing by one each time the scan goes one
level down.

Author: Amit Langote
Reviewed-by: Jesper Pedersen, Michael Paquier, Robert Haas
Discussion: https://postgr.es/m/[email protected]

1 files changed, 114 insertions, 0 deletions

diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
new file mode 100644
index 00000000000..6b116125e6d
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,114 @@
+--
+-- Tests for pg_partition_tree
+--
+SELECT * FROM pg_partition_tree(NULL);
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+-- Test table partition trees
+CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE ptif_test0 PARTITION OF ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE ptif_test01 PARTITION OF ptif_test0 FOR VALUES IN (1);
+CREATE TABLE ptif_test1 PARTITION OF ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE ptif_test11 PARTITION OF ptif_test1 FOR VALUES IN (1);
+CREATE TABLE ptif_test2 PARTITION OF ptif_test
+  FOR VALUES FROM (100) TO (maxvalue);
+-- Test index partition tree
+CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
+ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index;
+CREATE INDEX ptif_test01_index ON ptif_test01 (a);
+ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a);
+ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index;
+CREATE INDEX ptif_test11_index ON ptif_test11 (a);
+ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index;
+CREATE INDEX ptif_test2_index ON ptif_test2 (a);
+ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
+-- List all tables members of the tree
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test');
+    relid    | parentrelid | level | isleaf 
+-------------+-------------+-------+--------
+ ptif_test   |             |     0 | f
+ ptif_test0  | ptif_test   |     1 | f
+ ptif_test1  | ptif_test   |     1 | f
+ ptif_test2  | ptif_test   |     1 | t
+ ptif_test01 | ptif_test0  |     2 | t
+ ptif_test11 | ptif_test1  |     2 | t
+(6 rows)
+
+-- List tables from an intermediate level
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test0') p
+  JOIN pg_class c ON (p.relid = c.oid);
+    relid    | parentrelid | level | isleaf 
+-------------+-------------+-------+--------
+ ptif_test0  | ptif_test   |     0 | f
+ ptif_test01 | ptif_test0  |     1 | t
+(2 rows)
+
+-- List from leaf table
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test01') p
+  JOIN pg_class c ON (p.relid = c.oid);
+    relid    | parentrelid | level | isleaf 
+-------------+-------------+-------+--------
+ ptif_test01 | ptif_test0  |     0 | t
+(1 row)
+
+-- List all indexes members of the tree
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test_index');
+       relid       |   parentrelid    | level | isleaf 
+-------------------+------------------+-------+--------
+ ptif_test_index   |                  |     0 | f
+ ptif_test0_index  | ptif_test_index  |     1 | f
+ ptif_test1_index  | ptif_test_index  |     1 | f
+ ptif_test2_index  | ptif_test_index  |     1 | t
+ ptif_test01_index | ptif_test0_index |     2 | t
+ ptif_test11_index | ptif_test1_index |     2 | t
+(6 rows)
+
+-- List indexes from an intermediate level
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test0_index') p
+  JOIN pg_class c ON (p.relid = c.oid);
+       relid       |   parentrelid    | level | isleaf 
+-------------------+------------------+-------+--------
+ ptif_test0_index  | ptif_test_index  |     0 | f
+ ptif_test01_index | ptif_test0_index |     1 | t
+(2 rows)
+
+-- List from leaf index
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test01_index') p
+  JOIN pg_class c ON (p.relid = c.oid);
+       relid       |   parentrelid    | level | isleaf 
+-------------------+------------------+-------+--------
+ ptif_test01_index | ptif_test0_index |     0 | t
+(1 row)
+
+DROP TABLE ptif_test;
+-- A table not part of a partition tree works is the only member listed.
+CREATE TABLE ptif_normal_table(a int);
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_normal_table');
+       relid       | parentrelid | level | isleaf 
+-------------------+-------------+-------+--------
+ ptif_normal_table |             |     0 | t
+(1 row)
+
+DROP TABLE ptif_normal_table;
+-- Views and materialized viewS cannot be part of a partition tree.
+CREATE VIEW ptif_test_view AS SELECT 1;
+CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
+SELECT * FROM pg_partition_tree('ptif_test_view');
+ERROR:  "ptif_test_view" is not a table, a foreign table, or an index
+SELECT * FROM pg_partition_tree('ptif_test_matview');
+ERROR:  "ptif_test_matview" is not a table, a foreign table, or an index
+DROP VIEW ptif_test_view;
+DROP MATERIALIZED VIEW ptif_test_matview;