1-- 2-- Tests for functions providing information about partitions 3-- 4SELECT * FROM pg_partition_tree(NULL); 5SELECT * FROM pg_partition_tree(0); 6SELECT * FROM pg_partition_ancestors(NULL); 7SELECT * FROM pg_partition_ancestors(0); 8SELECT pg_partition_root(NULL); 9SELECT pg_partition_root(0); 10 11-- Test table partition trees 12CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a); 13CREATE TABLE ptif_test0 PARTITION OF ptif_test 14 FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b); 15CREATE TABLE ptif_test01 PARTITION OF ptif_test0 FOR VALUES IN (1); 16CREATE TABLE ptif_test1 PARTITION OF ptif_test 17 FOR VALUES FROM (0) TO (100) PARTITION BY list (b); 18CREATE TABLE ptif_test11 PARTITION OF ptif_test1 FOR VALUES IN (1); 19CREATE TABLE ptif_test2 PARTITION OF ptif_test 20 FOR VALUES FROM (100) TO (200); 21-- This partitioned table should remain with no partitions. 22CREATE TABLE ptif_test3 PARTITION OF ptif_test 23 FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b); 24 25-- Test pg_partition_root for tables 26SELECT pg_partition_root('ptif_test'); 27SELECT pg_partition_root('ptif_test0'); 28SELECT pg_partition_root('ptif_test01'); 29SELECT pg_partition_root('ptif_test3'); 30 31-- Test index partition tree 32CREATE INDEX ptif_test_index ON ONLY ptif_test (a); 33CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a); 34ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index; 35CREATE INDEX ptif_test01_index ON ptif_test01 (a); 36ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index; 37CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a); 38ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index; 39CREATE INDEX ptif_test11_index ON ptif_test11 (a); 40ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index; 41CREATE INDEX ptif_test2_index ON ptif_test2 (a); 42ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index; 43CREATE INDEX ptif_test3_index ON ptif_test3 (a); 44ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index; 45 46-- Test pg_partition_root for indexes 47SELECT pg_partition_root('ptif_test_index'); 48SELECT pg_partition_root('ptif_test0_index'); 49SELECT pg_partition_root('ptif_test01_index'); 50SELECT pg_partition_root('ptif_test3_index'); 51 52-- List all tables members of the tree 53SELECT relid, parentrelid, level, isleaf 54 FROM pg_partition_tree('ptif_test'); 55-- List tables from an intermediate level 56SELECT relid, parentrelid, level, isleaf 57 FROM pg_partition_tree('ptif_test0') p 58 JOIN pg_class c ON (p.relid = c.oid); 59-- List from leaf table 60SELECT relid, parentrelid, level, isleaf 61 FROM pg_partition_tree('ptif_test01') p 62 JOIN pg_class c ON (p.relid = c.oid); 63-- List from partitioned table with no partitions 64SELECT relid, parentrelid, level, isleaf 65 FROM pg_partition_tree('ptif_test3') p 66 JOIN pg_class c ON (p.relid = c.oid); 67-- List all ancestors of root and leaf tables 68SELECT * FROM pg_partition_ancestors('ptif_test01'); 69SELECT * FROM pg_partition_ancestors('ptif_test'); 70-- List all members using pg_partition_root with leaf table reference 71SELECT relid, parentrelid, level, isleaf 72 FROM pg_partition_tree(pg_partition_root('ptif_test01')) p 73 JOIN pg_class c ON (p.relid = c.oid); 74 75-- List all indexes members of the tree 76SELECT relid, parentrelid, level, isleaf 77 FROM pg_partition_tree('ptif_test_index'); 78-- List indexes from an intermediate level 79SELECT relid, parentrelid, level, isleaf 80 FROM pg_partition_tree('ptif_test0_index') p 81 JOIN pg_class c ON (p.relid = c.oid); 82-- List from leaf index 83SELECT relid, parentrelid, level, isleaf 84 FROM pg_partition_tree('ptif_test01_index') p 85 JOIN pg_class c ON (p.relid = c.oid); 86-- List from partitioned index with no partitions 87SELECT relid, parentrelid, level, isleaf 88 FROM pg_partition_tree('ptif_test3_index') p 89 JOIN pg_class c ON (p.relid = c.oid); 90-- List all members using pg_partition_root with leaf index reference 91SELECT relid, parentrelid, level, isleaf 92 FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p 93 JOIN pg_class c ON (p.relid = c.oid); 94-- List all ancestors of root and leaf indexes 95SELECT * FROM pg_partition_ancestors('ptif_test01_index'); 96SELECT * FROM pg_partition_ancestors('ptif_test_index'); 97 98DROP TABLE ptif_test; 99 100-- Table that is not part of any partition tree is not listed. 101CREATE TABLE ptif_normal_table(a int); 102SELECT relid, parentrelid, level, isleaf 103 FROM pg_partition_tree('ptif_normal_table'); 104SELECT * FROM pg_partition_ancestors('ptif_normal_table'); 105SELECT pg_partition_root('ptif_normal_table'); 106DROP TABLE ptif_normal_table; 107 108-- Various partitioning-related functions return empty/NULL if passed relations 109-- of types that cannot be part of a partition tree; for example, views, 110-- materialized views, legacy inheritance children or parents, etc. 111CREATE VIEW ptif_test_view AS SELECT 1; 112CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1; 113CREATE TABLE ptif_li_parent (); 114CREATE TABLE ptif_li_child () INHERITS (ptif_li_parent); 115SELECT * FROM pg_partition_tree('ptif_test_view'); 116SELECT * FROM pg_partition_tree('ptif_test_matview'); 117SELECT * FROM pg_partition_tree('ptif_li_parent'); 118SELECT * FROM pg_partition_tree('ptif_li_child'); 119SELECT * FROM pg_partition_ancestors('ptif_test_view'); 120SELECT * FROM pg_partition_ancestors('ptif_test_matview'); 121SELECT * FROM pg_partition_ancestors('ptif_li_parent'); 122SELECT * FROM pg_partition_ancestors('ptif_li_child'); 123SELECT pg_partition_root('ptif_test_view'); 124SELECT pg_partition_root('ptif_test_matview'); 125SELECT pg_partition_root('ptif_li_parent'); 126SELECT pg_partition_root('ptif_li_child'); 127DROP VIEW ptif_test_view; 128DROP MATERIALIZED VIEW ptif_test_matview; 129DROP TABLE ptif_li_parent, ptif_li_child; 130