Adding upstream version 15.4.upstream/15.4upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 351 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 0000000..42b6bc7
--- /dev/null
+++ b/src/test/regress/expected/partition_info.out
@@ -0,0 +1,351 @@
+--
+-- Tests for functions providing information about partitions
+--
+SELECT * FROM pg_partition_tree(NULL);
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+SELECT * FROM pg_partition_tree(0);
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors(NULL);
+ relid 
+-------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors(0);
+ relid 
+-------
+(0 rows)
+
+SELECT pg_partition_root(NULL);
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+SELECT pg_partition_root(0);
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+-- 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 (200);
+-- 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);
+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;
+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');
+    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_test3  | ptif_test   |     1 | f
+ ptif_test01 | ptif_test0  |     2 | t
+ ptif_test11 | ptif_test1  |     2 | t
+(7 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 from partitioned table with no partitions
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test3') p
+  JOIN pg_class c ON (p.relid = c.oid);
+   relid    | parentrelid | level | isleaf 
+------------+-------------+-------+--------
+ ptif_test3 | ptif_test   |     0 | f
+(1 row)
+
+-- List all ancestors of root and leaf tables
+SELECT * FROM pg_partition_ancestors('ptif_test01');
+    relid    
+-------------
+ ptif_test01
+ ptif_test0
+ ptif_test
+(3 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_test');
+   relid   
+-----------
+ ptif_test
+(1 row)
+
+-- List all members using pg_partition_root with leaf table reference
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
+  JOIN pg_class c ON (p.relid = c.oid);
+    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_test3  | ptif_test   |     1 | f
+ ptif_test01 | ptif_test0  |     2 | t
+ ptif_test11 | ptif_test1  |     2 | t
+(7 rows)
+
+-- 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_test3_index  | ptif_test_index  |     1 | f
+ ptif_test01_index | ptif_test0_index |     2 | t
+ ptif_test11_index | ptif_test1_index |     2 | t
+(7 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)
+
+-- List from partitioned index with no partitions
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_test3_index') p
+  JOIN pg_class c ON (p.relid = c.oid);
+      relid       |   parentrelid   | level | isleaf 
+------------------+-----------------+-------+--------
+ ptif_test3_index | ptif_test_index |     0 | f
+(1 row)
+
+-- List all members using pg_partition_root with leaf index reference
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
+  JOIN pg_class c ON (p.relid = c.oid);
+       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_test3_index  | ptif_test_index  |     1 | f
+ ptif_test01_index | ptif_test0_index |     2 | t
+ ptif_test11_index | ptif_test1_index |     2 | t
+(7 rows)
+
+-- List all ancestors of root and leaf indexes
+SELECT * FROM pg_partition_ancestors('ptif_test01_index');
+       relid       
+-------------------
+ ptif_test01_index
+ ptif_test0_index
+ ptif_test_index
+(3 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_test_index');
+      relid      
+-----------------
+ ptif_test_index
+(1 row)
+
+DROP TABLE ptif_test;
+-- Table that is not part of any partition tree is not listed.
+CREATE TABLE ptif_normal_table(a int);
+SELECT relid, parentrelid, level, isleaf
+  FROM pg_partition_tree('ptif_normal_table');
+ relid | parentrelid | level | isleaf 
+-------+-------------+-------+--------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_normal_table');
+ relid 
+-------
+(0 rows)
+
+SELECT pg_partition_root('ptif_normal_table');
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+DROP TABLE ptif_normal_table;
+-- Various partitioning-related functions return empty/NULL if passed relations
+-- of types that cannot be part of a partition tree; for example, views,
+-- materialized views, legacy inheritance children or parents, etc.
+CREATE VIEW ptif_test_view AS SELECT 1;
+CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
+CREATE TABLE ptif_li_parent ();
+CREATE TABLE ptif_li_child () INHERITS (ptif_li_parent);
+SELECT * FROM pg_partition_tree('ptif_test_view');
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+SELECT * FROM pg_partition_tree('ptif_test_matview');
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+SELECT * FROM pg_partition_tree('ptif_li_parent');
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+SELECT * FROM pg_partition_tree('ptif_li_child');
+ relid | parentrelid | isleaf | level 
+-------+-------------+--------+-------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_test_view');
+ relid 
+-------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_test_matview');
+ relid 
+-------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_li_parent');
+ relid 
+-------
+(0 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_li_child');
+ relid 
+-------
+(0 rows)
+
+SELECT pg_partition_root('ptif_test_view');
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+SELECT pg_partition_root('ptif_test_matview');
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+SELECT pg_partition_root('ptif_li_parent');
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+SELECT pg_partition_root('ptif_li_child');
+ pg_partition_root 
+-------------------
+ 
+(1 row)
+
+DROP VIEW ptif_test_view;
+DROP MATERIALIZED VIEW ptif_test_matview;
+DROP TABLE ptif_li_parent, ptif_li_child;