1-- Creating an index on a partitioned table makes the partitions 2-- automatically get the index 3create table idxpart (a int, b int, c text) partition by range (a); 4 5-- relhassubclass of a partitioned index is false before creating any partition. 6-- It will be set after the first partition is created. 7create index idxpart_idx on idxpart (a); 8select relhassubclass from pg_class where relname = 'idxpart_idx'; 9 10-- Check that partitioned indexes are present in pg_indexes. 11select indexdef from pg_indexes where indexname like 'idxpart_idx%'; 12drop index idxpart_idx; 13 14create table idxpart1 partition of idxpart for values from (0) to (10); 15create table idxpart2 partition of idxpart for values from (10) to (100) 16 partition by range (b); 17create table idxpart21 partition of idxpart2 for values from (0) to (100); 18 19-- Even with partitions, relhassubclass should not be set if a partitioned 20-- index is created only on the parent. 21create index idxpart_idx on only idxpart(a); 22select relhassubclass from pg_class where relname = 'idxpart_idx'; 23drop index idxpart_idx; 24 25create index on idxpart (a); 26select relname, relkind, relhassubclass, inhparent::regclass 27 from pg_class left join pg_index ix on (indexrelid = oid) 28 left join pg_inherits on (ix.indexrelid = inhrelid) 29 where relname like 'idxpart%' order by relname; 30drop table idxpart; 31 32-- Some unsupported features 33create table idxpart (a int, b int, c text) partition by range (a); 34create table idxpart1 partition of idxpart for values from (0) to (10); 35create index concurrently on idxpart (a); 36drop table idxpart; 37 38-- Verify bugfix with query on indexed partitioned table with no partitions 39-- https://postgr.es/m/20180124162006.pmapfiznhgngwtjf@alvherre.pgsql 40CREATE TABLE idxpart (col1 INT) PARTITION BY RANGE (col1); 41CREATE INDEX ON idxpart (col1); 42CREATE TABLE idxpart_two (col2 INT); 43SELECT col2 FROM idxpart_two fk LEFT OUTER JOIN idxpart pk ON (col1 = col2); 44DROP table idxpart, idxpart_two; 45 46-- Verify bugfix with index rewrite on ALTER TABLE / SET DATA TYPE 47-- https://postgr.es/m/CAKcux6mxNCGsgATwf5CGMF8g4WSupCXicCVMeKUTuWbyxHOMsQ@mail.gmail.com 48CREATE TABLE idxpart (a INT, b TEXT, c INT) PARTITION BY RANGE(a); 49CREATE TABLE idxpart1 PARTITION OF idxpart FOR VALUES FROM (MINVALUE) TO (MAXVALUE); 50CREATE INDEX partidx_abc_idx ON idxpart (a, b, c); 51INSERT INTO idxpart (a, b, c) SELECT i, i, i FROM generate_series(1, 50) i; 52ALTER TABLE idxpart ALTER COLUMN c TYPE numeric; 53DROP TABLE idxpart; 54 55-- If a table without index is attached as partition to a table with 56-- an index, the index is automatically created 57create table idxpart (a int, b int, c text) partition by range (a); 58create index idxparti on idxpart (a); 59create index idxparti2 on idxpart (b, c); 60create table idxpart1 (like idxpart); 61\d idxpart1 62alter table idxpart attach partition idxpart1 for values from (0) to (10); 63\d idxpart1 64\d+ idxpart1_a_idx 65\d+ idxpart1_b_c_idx 66 67-- Forbid ALTER TABLE when attaching or detaching an index to a partition. 68create index idxpart_c on only idxpart (c); 69create index idxpart1_c on idxpart1 (c); 70alter table idxpart_c attach partition idxpart1_c for values from (10) to (20); 71alter index idxpart_c attach partition idxpart1_c; 72select relname, relpartbound from pg_class 73 where relname in ('idxpart_c', 'idxpart1_c') 74 order by relname; 75alter table idxpart_c detach partition idxpart1_c; 76drop table idxpart; 77 78-- If a partition already has an index, don't create a duplicative one 79create table idxpart (a int, b int) partition by range (a, b); 80create table idxpart1 partition of idxpart for values from (0, 0) to (10, 10); 81create index on idxpart1 (a, b); 82create index on idxpart (a, b); 83\d idxpart1 84select relname, relkind, relhassubclass, inhparent::regclass 85 from pg_class left join pg_index ix on (indexrelid = oid) 86 left join pg_inherits on (ix.indexrelid = inhrelid) 87 where relname like 'idxpart%' order by relname; 88drop table idxpart; 89 90-- DROP behavior for partitioned indexes 91create table idxpart (a int) partition by range (a); 92create index on idxpart (a); 93create table idxpart1 partition of idxpart for values from (0) to (10); 94drop index idxpart1_a_idx; -- no way 95drop index concurrently idxpart_a_idx; -- unsupported 96drop index idxpart_a_idx; -- both indexes go away 97select relname, relkind from pg_class 98 where relname like 'idxpart%' order by relname; 99create index on idxpart (a); 100drop table idxpart1; -- the index on partition goes away too 101select relname, relkind from pg_class 102 where relname like 'idxpart%' order by relname; 103drop table idxpart; 104 105-- DROP behavior with temporary partitioned indexes 106create temp table idxpart_temp (a int) partition by range (a); 107create index on idxpart_temp(a); 108create temp table idxpart1_temp partition of idxpart_temp 109 for values from (0) to (10); 110drop index idxpart1_temp_a_idx; -- error 111-- non-concurrent drop is enforced here, so it is a valid case. 112drop index concurrently idxpart_temp_a_idx; 113select relname, relkind from pg_class 114 where relname like 'idxpart_temp%' order by relname; 115drop table idxpart_temp; 116 117-- ALTER INDEX .. ATTACH, error cases 118create table idxpart (a int, b int) partition by range (a, b); 119create table idxpart1 partition of idxpart for values from (0, 0) to (10, 10); 120create index idxpart_a_b_idx on only idxpart (a, b); 121create index idxpart1_a_b_idx on idxpart1 (a, b); 122create index idxpart1_tst1 on idxpart1 (b, a); 123create index idxpart1_tst2 on idxpart1 using hash (a); 124create index idxpart1_tst3 on idxpart1 (a, b) where a > 10; 125 126alter index idxpart attach partition idxpart1; 127alter index idxpart_a_b_idx attach partition idxpart1; 128alter index idxpart_a_b_idx attach partition idxpart_a_b_idx; 129alter index idxpart_a_b_idx attach partition idxpart1_b_idx; 130alter index idxpart_a_b_idx attach partition idxpart1_tst1; 131alter index idxpart_a_b_idx attach partition idxpart1_tst2; 132alter index idxpart_a_b_idx attach partition idxpart1_tst3; 133-- OK 134alter index idxpart_a_b_idx attach partition idxpart1_a_b_idx; 135alter index idxpart_a_b_idx attach partition idxpart1_a_b_idx; -- quiet 136 137-- reject dupe 138create index idxpart1_2_a_b on idxpart1 (a, b); 139alter index idxpart_a_b_idx attach partition idxpart1_2_a_b; 140drop table idxpart; 141-- make sure everything's gone 142select indexrelid::regclass, indrelid::regclass 143 from pg_index where indexrelid::regclass::text like 'idxpart%'; 144 145-- Don't auto-attach incompatible indexes 146create table idxpart (a int, b int) partition by range (a); 147create table idxpart1 (a int, b int); 148create index on idxpart1 using hash (a); 149create index on idxpart1 (a) where b > 1; 150create index on idxpart1 ((a + 0)); 151create index on idxpart1 (a, a); 152create index on idxpart (a); 153alter table idxpart attach partition idxpart1 for values from (0) to (1000); 154\d idxpart1 155drop table idxpart; 156 157-- If CREATE INDEX ONLY, don't create indexes on partitions; and existing 158-- indexes on partitions don't change parent. ALTER INDEX ATTACH can change 159-- the parent after the fact. 160create table idxpart (a int) partition by range (a); 161create table idxpart1 partition of idxpart for values from (0) to (100); 162create table idxpart2 partition of idxpart for values from (100) to (1000) 163 partition by range (a); 164create table idxpart21 partition of idxpart2 for values from (100) to (200); 165create table idxpart22 partition of idxpart2 for values from (200) to (300); 166create index on idxpart22 (a); 167create index on only idxpart2 (a); 168create index on idxpart (a); 169-- Here we expect that idxpart1 and idxpart2 have a new index, but idxpart21 170-- does not; also, idxpart22 is not attached. 171\d idxpart1 172\d idxpart2 173\d idxpart21 174select indexrelid::regclass, indrelid::regclass, inhparent::regclass 175 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 176where indexrelid::regclass::text like 'idxpart%' 177 order by indexrelid::regclass::text collate "C"; 178alter index idxpart2_a_idx attach partition idxpart22_a_idx; 179select indexrelid::regclass, indrelid::regclass, inhparent::regclass 180 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 181where indexrelid::regclass::text like 'idxpart%' 182 order by indexrelid::regclass::text collate "C"; 183-- attaching idxpart22 is not enough to set idxpart22_a_idx valid ... 184alter index idxpart2_a_idx attach partition idxpart22_a_idx; 185\d idxpart2 186-- ... but this one is. 187create index on idxpart21 (a); 188alter index idxpart2_a_idx attach partition idxpart21_a_idx; 189\d idxpart2 190drop table idxpart; 191 192-- When a table is attached a partition and it already has an index, a 193-- duplicate index should not get created, but rather the index becomes 194-- attached to the parent's index. 195create table idxpart (a int, b int, c text) partition by range (a); 196create index idxparti on idxpart (a); 197create index idxparti2 on idxpart (b, c); 198create table idxpart1 (like idxpart including indexes); 199\d idxpart1 200select relname, relkind, inhparent::regclass 201 from pg_class left join pg_index ix on (indexrelid = oid) 202 left join pg_inherits on (ix.indexrelid = inhrelid) 203 where relname like 'idxpart%' order by relname; 204alter table idxpart attach partition idxpart1 for values from (0) to (10); 205\d idxpart1 206select relname, relkind, inhparent::regclass 207 from pg_class left join pg_index ix on (indexrelid = oid) 208 left join pg_inherits on (ix.indexrelid = inhrelid) 209 where relname like 'idxpart%' order by relname; 210drop table idxpart; 211 212-- Verify that attaching an invalid index does not mark the parent index valid. 213-- On the other hand, attaching a valid index marks not only its direct 214-- ancestor valid, but also any indirect ancestor that was only missing the one 215-- that was just made valid 216create table idxpart (a int, b int) partition by range (a); 217create table idxpart1 partition of idxpart for values from (1) to (1000) partition by range (a); 218create table idxpart11 partition of idxpart1 for values from (1) to (100); 219create index on only idxpart1 (a); 220create index on only idxpart (a); 221-- this results in two invalid indexes: 222select relname, indisvalid from pg_class join pg_index on indexrelid = oid 223 where relname like 'idxpart%' order by relname; 224-- idxpart1_a_idx is not valid, so idxpart_a_idx should not become valid: 225alter index idxpart_a_idx attach partition idxpart1_a_idx; 226select relname, indisvalid from pg_class join pg_index on indexrelid = oid 227 where relname like 'idxpart%' order by relname; 228-- after creating and attaching this, both idxpart1_a_idx and idxpart_a_idx 229-- should become valid 230create index on idxpart11 (a); 231alter index idxpart1_a_idx attach partition idxpart11_a_idx; 232select relname, indisvalid from pg_class join pg_index on indexrelid = oid 233 where relname like 'idxpart%' order by relname; 234drop table idxpart; 235 236-- verify dependency handling during ALTER TABLE DETACH PARTITION 237create table idxpart (a int) partition by range (a); 238create table idxpart1 (like idxpart); 239create index on idxpart1 (a); 240create index on idxpart (a); 241create table idxpart2 (like idxpart); 242alter table idxpart attach partition idxpart1 for values from (0000) to (1000); 243alter table idxpart attach partition idxpart2 for values from (1000) to (2000); 244create table idxpart3 partition of idxpart for values from (2000) to (3000); 245select relname, relkind from pg_class where relname like 'idxpart%' order by relname; 246-- a) after detaching partitions, the indexes can be dropped independently 247alter table idxpart detach partition idxpart1; 248alter table idxpart detach partition idxpart2; 249alter table idxpart detach partition idxpart3; 250drop index idxpart1_a_idx; 251drop index idxpart2_a_idx; 252drop index idxpart3_a_idx; 253select relname, relkind from pg_class where relname like 'idxpart%' order by relname; 254drop table idxpart, idxpart1, idxpart2, idxpart3; 255select relname, relkind from pg_class where relname like 'idxpart%' order by relname; 256 257create table idxpart (a int) partition by range (a); 258create table idxpart1 (like idxpart); 259create index on idxpart1 (a); 260create index on idxpart (a); 261create table idxpart2 (like idxpart); 262alter table idxpart attach partition idxpart1 for values from (0000) to (1000); 263alter table idxpart attach partition idxpart2 for values from (1000) to (2000); 264create table idxpart3 partition of idxpart for values from (2000) to (3000); 265-- b) after detaching, dropping the index on parent does not remove the others 266select relname, relkind from pg_class where relname like 'idxpart%' order by relname; 267alter table idxpart detach partition idxpart1; 268alter table idxpart detach partition idxpart2; 269alter table idxpart detach partition idxpart3; 270drop index idxpart_a_idx; 271select relname, relkind from pg_class where relname like 'idxpart%' order by relname; 272drop table idxpart, idxpart1, idxpart2, idxpart3; 273select relname, relkind from pg_class where relname like 'idxpart%' order by relname; 274 275create table idxpart (a int, b int, c int) partition by range(a); 276create index on idxpart(c); 277create table idxpart1 partition of idxpart for values from (0) to (250); 278create table idxpart2 partition of idxpart for values from (250) to (500); 279alter table idxpart detach partition idxpart2; 280\d idxpart2 281alter table idxpart2 drop column c; 282\d idxpart2 283drop table idxpart, idxpart2; 284 285-- Verify that expression indexes inherit correctly 286create table idxpart (a int, b int) partition by range (a); 287create table idxpart1 (like idxpart); 288create index on idxpart1 ((a + b)); 289create index on idxpart ((a + b)); 290create table idxpart2 (like idxpart); 291alter table idxpart attach partition idxpart1 for values from (0000) to (1000); 292alter table idxpart attach partition idxpart2 for values from (1000) to (2000); 293create table idxpart3 partition of idxpart for values from (2000) to (3000); 294select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef 295 from pg_class join pg_inherits on inhrelid = oid, 296 lateral pg_get_indexdef(pg_class.oid) 297 where relkind in ('i', 'I') and relname like 'idxpart%' order by relname; 298drop table idxpart; 299 300-- Verify behavior for collation (mis)matches 301create table idxpart (a text) partition by range (a); 302create table idxpart1 (like idxpart); 303create table idxpart2 (like idxpart); 304create index on idxpart2 (a collate "POSIX"); 305create index on idxpart2 (a); 306create index on idxpart2 (a collate "C"); 307alter table idxpart attach partition idxpart1 for values from ('aaa') to ('bbb'); 308alter table idxpart attach partition idxpart2 for values from ('bbb') to ('ccc'); 309create table idxpart3 partition of idxpart for values from ('ccc') to ('ddd'); 310create index on idxpart (a collate "C"); 311create table idxpart4 partition of idxpart for values from ('ddd') to ('eee'); 312select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef 313 from pg_class left join pg_inherits on inhrelid = oid, 314 lateral pg_get_indexdef(pg_class.oid) 315 where relkind in ('i', 'I') and relname like 'idxpart%' order by relname; 316drop table idxpart; 317 318-- Verify behavior for opclass (mis)matches 319create table idxpart (a text) partition by range (a); 320create table idxpart1 (like idxpart); 321create table idxpart2 (like idxpart); 322create index on idxpart2 (a); 323alter table idxpart attach partition idxpart1 for values from ('aaa') to ('bbb'); 324alter table idxpart attach partition idxpart2 for values from ('bbb') to ('ccc'); 325create table idxpart3 partition of idxpart for values from ('ccc') to ('ddd'); 326create index on idxpart (a text_pattern_ops); 327create table idxpart4 partition of idxpart for values from ('ddd') to ('eee'); 328-- must *not* have attached the index we created on idxpart2 329select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef 330 from pg_class left join pg_inherits on inhrelid = oid, 331 lateral pg_get_indexdef(pg_class.oid) 332 where relkind in ('i', 'I') and relname like 'idxpart%' order by relname; 333drop index idxpart_a_idx; 334create index on only idxpart (a text_pattern_ops); 335-- must reject 336alter index idxpart_a_idx attach partition idxpart2_a_idx; 337drop table idxpart; 338 339-- Verify that attaching indexes maps attribute numbers correctly 340create table idxpart (col1 int, a int, col2 int, b int) partition by range (a); 341create table idxpart1 (b int, col1 int, col2 int, col3 int, a int); 342alter table idxpart drop column col1, drop column col2; 343alter table idxpart1 drop column col1, drop column col2, drop column col3; 344alter table idxpart attach partition idxpart1 for values from (0) to (1000); 345create index idxpart_1_idx on only idxpart (b, a); 346create index idxpart1_1_idx on idxpart1 (b, a); 347create index idxpart1_1b_idx on idxpart1 (b); 348-- test expressions and partial-index predicate, too 349create index idxpart_2_idx on only idxpart ((b + a)) where a > 1; 350create index idxpart1_2_idx on idxpart1 ((b + a)) where a > 1; 351create index idxpart1_2b_idx on idxpart1 ((a + b)) where a > 1; 352create index idxpart1_2c_idx on idxpart1 ((b + a)) where b > 1; 353alter index idxpart_1_idx attach partition idxpart1_1b_idx; -- fail 354alter index idxpart_1_idx attach partition idxpart1_1_idx; 355alter index idxpart_2_idx attach partition idxpart1_2b_idx; -- fail 356alter index idxpart_2_idx attach partition idxpart1_2c_idx; -- fail 357alter index idxpart_2_idx attach partition idxpart1_2_idx; -- ok 358select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef 359 from pg_class left join pg_inherits on inhrelid = oid, 360 lateral pg_get_indexdef(pg_class.oid) 361 where relkind in ('i', 'I') and relname like 'idxpart%' order by relname; 362drop table idxpart; 363 364-- Make sure the partition columns are mapped correctly 365create table idxpart (a int, b int, c text) partition by range (a); 366create index idxparti on idxpart (a); 367create index idxparti2 on idxpart (c, b); 368create table idxpart1 (c text, a int, b int); 369alter table idxpart attach partition idxpart1 for values from (0) to (10); 370create table idxpart2 (c text, a int, b int); 371create index on idxpart2 (a); 372create index on idxpart2 (c, b); 373alter table idxpart attach partition idxpart2 for values from (10) to (20); 374select c.relname, pg_get_indexdef(indexrelid) 375 from pg_class c join pg_index i on c.oid = i.indexrelid 376 where indrelid::regclass::text like 'idxpart%' 377 order by indexrelid::regclass::text collate "C"; 378drop table idxpart; 379 380-- Verify that columns are mapped correctly in expression indexes 381create table idxpart (col1 int, col2 int, a int, b int) partition by range (a); 382create table idxpart1 (col2 int, b int, col1 int, a int); 383create table idxpart2 (col1 int, col2 int, b int, a int); 384alter table idxpart drop column col1, drop column col2; 385alter table idxpart1 drop column col1, drop column col2; 386alter table idxpart2 drop column col1, drop column col2; 387create index on idxpart2 (abs(b)); 388alter table idxpart attach partition idxpart2 for values from (0) to (1); 389create index on idxpart (abs(b)); 390create index on idxpart ((b + 1)); 391alter table idxpart attach partition idxpart1 for values from (1) to (2); 392select c.relname, pg_get_indexdef(indexrelid) 393 from pg_class c join pg_index i on c.oid = i.indexrelid 394 where indrelid::regclass::text like 'idxpart%' 395 order by indexrelid::regclass::text collate "C"; 396drop table idxpart; 397 398-- Verify that columns are mapped correctly for WHERE in a partial index 399create table idxpart (col1 int, a int, col3 int, b int) partition by range (a); 400alter table idxpart drop column col1, drop column col3; 401create table idxpart1 (col1 int, col2 int, col3 int, col4 int, b int, a int); 402alter table idxpart1 drop column col1, drop column col2, drop column col3, drop column col4; 403alter table idxpart attach partition idxpart1 for values from (0) to (1000); 404create table idxpart2 (col1 int, col2 int, b int, a int); 405create index on idxpart2 (a) where b > 1000; 406alter table idxpart2 drop column col1, drop column col2; 407alter table idxpart attach partition idxpart2 for values from (1000) to (2000); 408create index on idxpart (a) where b > 1000; 409select c.relname, pg_get_indexdef(indexrelid) 410 from pg_class c join pg_index i on c.oid = i.indexrelid 411 where indrelid::regclass::text like 'idxpart%' 412 order by indexrelid::regclass::text collate "C"; 413drop table idxpart; 414 415-- Column number mapping: dropped columns in the partition 416create table idxpart1 (drop_1 int, drop_2 int, col_keep int, drop_3 int); 417alter table idxpart1 drop column drop_1; 418alter table idxpart1 drop column drop_2; 419alter table idxpart1 drop column drop_3; 420create index on idxpart1 (col_keep); 421create table idxpart (col_keep int) partition by range (col_keep); 422create index on idxpart (col_keep); 423alter table idxpart attach partition idxpart1 for values from (0) to (1000); 424\d idxpart 425\d idxpart1 426select attrelid::regclass, attname, attnum from pg_attribute 427 where attrelid::regclass::text like 'idxpart%' and attnum > 0 428 order by attrelid::regclass, attnum; 429drop table idxpart; 430 431-- Column number mapping: dropped columns in the parent table 432create table idxpart(drop_1 int, drop_2 int, col_keep int, drop_3 int) partition by range (col_keep); 433alter table idxpart drop column drop_1; 434alter table idxpart drop column drop_2; 435alter table idxpart drop column drop_3; 436create table idxpart1 (col_keep int); 437create index on idxpart1 (col_keep); 438create index on idxpart (col_keep); 439alter table idxpart attach partition idxpart1 for values from (0) to (1000); 440\d idxpart 441\d idxpart1 442select attrelid::regclass, attname, attnum from pg_attribute 443 where attrelid::regclass::text like 'idxpart%' and attnum > 0 444 order by attrelid::regclass, attnum; 445drop table idxpart; 446 447-- 448-- Constraint-related indexes 449-- 450 451-- Verify that it works to add primary key / unique to partitioned tables 452create table idxpart (a int primary key, b int) partition by range (a); 453\d idxpart 454-- multiple primary key on child should fail 455create table failpart partition of idxpart (b primary key) for values from (0) to (100); 456drop table idxpart; 457-- primary key on child is okay if there's no PK in the parent, though 458create table idxpart (a int) partition by range (a); 459create table idxpart1pk partition of idxpart (a primary key) for values from (0) to (100); 460\d idxpart1pk 461drop table idxpart; 462 463-- Failing to use the full partition key is not allowed 464create table idxpart (a int unique, b int) partition by range (a, b); 465create table idxpart (a int, b int unique) partition by range (a, b); 466create table idxpart (a int primary key, b int) partition by range (b, a); 467create table idxpart (a int, b int primary key) partition by range (b, a); 468 469-- OK if you use them in some other order 470create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a); 471drop table idxpart; 472 473-- not other types of index-based constraints 474create table idxpart (a int, exclude (a with = )) partition by range (a); 475 476-- no expressions in partition key for PK/UNIQUE 477create table idxpart (a int primary key, b int) partition by range ((b + a)); 478create table idxpart (a int unique, b int) partition by range ((b + a)); 479 480-- use ALTER TABLE to add a primary key 481create table idxpart (a int, b int, c text) partition by range (a, b); 482alter table idxpart add primary key (a); -- not an incomplete one though 483alter table idxpart add primary key (a, b); -- this works 484\d idxpart 485create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000); 486\d idxpart1 487drop table idxpart; 488 489-- use ALTER TABLE to add a unique constraint 490create table idxpart (a int, b int) partition by range (a, b); 491alter table idxpart add unique (a); -- not an incomplete one though 492alter table idxpart add unique (b, a); -- this works 493\d idxpart 494drop table idxpart; 495 496-- Exclusion constraints cannot be added 497create table idxpart (a int, b int) partition by range (a); 498alter table idxpart add exclude (a with =); 499drop table idxpart; 500 501-- When (sub)partitions are created, they also contain the constraint 502create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b); 503create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10); 504create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20) 505 partition by range (b); 506create table idxpart21 partition of idxpart2 for values from (10) to (15); 507create table idxpart22 partition of idxpart2 for values from (15) to (20); 508create table idxpart3 (b int not null, a int not null); 509alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30); 510select conname, contype, conrelid::regclass, conindid::regclass, conkey 511 from pg_constraint where conrelid::regclass::text like 'idxpart%' 512 order by conname; 513drop table idxpart; 514 515-- Verify that multi-layer partitioning honors the requirement that all 516-- columns in the partition key must appear in primary/unique key 517create table idxpart (a int, b int, primary key (a)) partition by range (a); 518create table idxpart2 partition of idxpart 519for values from (0) to (1000) partition by range (b); -- fail 520drop table idxpart; 521 522-- Ditto for the ATTACH PARTITION case 523create table idxpart (a int unique, b int) partition by range (a); 524create table idxpart1 (a int not null, b int, unique (a, b)) 525 partition by range (a, b); 526alter table idxpart attach partition idxpart1 for values from (1) to (1000); 527DROP TABLE idxpart, idxpart1; 528 529-- Multi-layer partitioning works correctly in this case: 530create table idxpart (a int, b int, primary key (a, b)) partition by range (a); 531create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b); 532create table idxpart21 partition of idxpart2 for values from (0) to (1000); 533select conname, contype, conrelid::regclass, conindid::regclass, conkey 534 from pg_constraint where conrelid::regclass::text like 'idxpart%' 535 order by conname; 536drop table idxpart; 537 538-- If a partitioned table has a unique/PK constraint, then it's not possible 539-- to drop the corresponding constraint in the children; nor it's possible 540-- to drop the indexes individually. Dropping the constraint in the parent 541-- gets rid of the lot. 542create table idxpart (i int) partition by hash (i); 543create table idxpart0 partition of idxpart (i) for values with (modulus 2, remainder 0); 544create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1); 545alter table idxpart0 add primary key(i); 546alter table idxpart add primary key(i); 547select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid, 548 conname, conislocal, coninhcount, connoinherit, convalidated 549 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 550 left join pg_constraint con on (idx.indexrelid = con.conindid) 551 where indrelid::regclass::text like 'idxpart%' 552 order by indexrelid::regclass::text collate "C"; 553drop index idxpart0_pkey; -- fail 554drop index idxpart1_pkey; -- fail 555alter table idxpart0 drop constraint idxpart0_pkey; -- fail 556alter table idxpart1 drop constraint idxpart1_pkey; -- fail 557alter table idxpart drop constraint idxpart_pkey; -- ok 558select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid, 559 conname, conislocal, coninhcount, connoinherit, convalidated 560 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 561 left join pg_constraint con on (idx.indexrelid = con.conindid) 562 where indrelid::regclass::text like 'idxpart%' 563 order by indexrelid::regclass::text collate "C"; 564drop table idxpart; 565 566-- If the partition to be attached already has a primary key, fail if 567-- it doesn't match the parent's PK. 568CREATE TABLE idxpart (c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(10)) PARTITION BY RANGE(c1); 569CREATE TABLE idxpart1 (LIKE idxpart); 570ALTER TABLE idxpart1 ADD PRIMARY KEY (c1, c2); 571ALTER TABLE idxpart ATTACH PARTITION idxpart1 FOR VALUES FROM (100) TO (200); 572DROP TABLE idxpart, idxpart1; 573 574-- Ditto if there is some distance between the PKs (subpartitioning) 575create table idxpart (a int, b int, primary key (a)) partition by range (a); 576create table idxpart1 (a int not null, b int) partition by range (a); 577create table idxpart11 (a int not null, b int primary key); 578alter table idxpart1 attach partition idxpart11 for values from (0) to (1000); 579alter table idxpart attach partition idxpart1 for values from (0) to (10000); 580drop table idxpart, idxpart1, idxpart11; 581 582-- If a partitioned table has a constraint whose index is not valid, 583-- attaching a missing partition makes it valid. 584create table idxpart (a int) partition by range (a); 585create table idxpart0 (like idxpart); 586alter table idxpart0 add primary key (a); 587alter table idxpart attach partition idxpart0 for values from (0) to (1000); 588alter table only idxpart add primary key (a); 589select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid, 590 conname, conislocal, coninhcount, connoinherit, convalidated 591 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 592 left join pg_constraint con on (idx.indexrelid = con.conindid) 593 where indrelid::regclass::text like 'idxpart%' 594 order by indexrelid::regclass::text collate "C"; 595alter index idxpart_pkey attach partition idxpart0_pkey; 596select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid, 597 conname, conislocal, coninhcount, connoinherit, convalidated 598 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 599 left join pg_constraint con on (idx.indexrelid = con.conindid) 600 where indrelid::regclass::text like 'idxpart%' 601 order by indexrelid::regclass::text collate "C"; 602drop table idxpart; 603 604-- Related to the above scenario: ADD PRIMARY KEY on the parent mustn't 605-- automatically propagate NOT NULL to child columns. 606create table idxpart (a int) partition by range (a); 607create table idxpart0 (like idxpart); 608alter table idxpart0 add unique (a); 609alter table idxpart attach partition idxpart0 default; 610alter table only idxpart add primary key (a); -- fail, no NOT NULL constraint 611alter table idxpart0 alter column a set not null; 612alter table only idxpart add primary key (a); -- now it works 613alter table idxpart0 alter column a drop not null; -- fail, pkey needs it 614drop table idxpart; 615 616-- if a partition has a unique index without a constraint, does not attach 617-- automatically; creates a new index instead. 618create table idxpart (a int, b int) partition by range (a); 619create table idxpart1 (a int not null, b int); 620create unique index on idxpart1 (a); 621alter table idxpart add primary key (a); 622alter table idxpart attach partition idxpart1 for values from (1) to (1000); 623select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid, 624 conname, conislocal, coninhcount, connoinherit, convalidated 625 from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) 626 left join pg_constraint con on (idx.indexrelid = con.conindid) 627 where indrelid::regclass::text like 'idxpart%' 628 order by indexrelid::regclass::text collate "C"; 629drop table idxpart; 630 631-- Can't attach an index without a corresponding constraint 632create table idxpart (a int, b int) partition by range (a); 633create table idxpart1 (a int not null, b int); 634create unique index on idxpart1 (a); 635alter table idxpart attach partition idxpart1 for values from (1) to (1000); 636alter table only idxpart add primary key (a); 637alter index idxpart_pkey attach partition idxpart1_a_idx; -- fail 638drop table idxpart; 639 640-- Test that unique constraints are working 641create table idxpart (a int, b text, primary key (a, b)) partition by range (a); 642create table idxpart1 partition of idxpart for values from (0) to (100000); 643create table idxpart2 (c int, like idxpart); 644insert into idxpart2 (c, a, b) values (42, 572814, 'inserted first'); 645alter table idxpart2 drop column c; 646create unique index on idxpart (a); 647alter table idxpart attach partition idxpart2 for values from (100000) to (1000000); 648insert into idxpart values (0, 'zero'), (42, 'life'), (2^16, 'sixteen'); 649insert into idxpart select 2^g, format('two to power of %s', g) from generate_series(15, 17) g; 650insert into idxpart values (16, 'sixteen'); 651insert into idxpart (b, a) values ('one', 142857), ('two', 285714); 652insert into idxpart select a * 2, b || b from idxpart where a between 2^16 and 2^19; 653insert into idxpart values (572814, 'five'); 654insert into idxpart values (857142, 'six'); 655select tableoid::regclass, * from idxpart order by a; 656drop table idxpart; 657 658-- intentionally leave some objects around 659create table idxpart (a int) partition by range (a); 660create table idxpart1 partition of idxpart for values from (0) to (100); 661create table idxpart2 partition of idxpart for values from (100) to (1000) 662 partition by range (a); 663create table idxpart21 partition of idxpart2 for values from (100) to (200); 664create table idxpart22 partition of idxpart2 for values from (200) to (300); 665create index on idxpart22 (a); 666create index on only idxpart2 (a); 667alter index idxpart2_a_idx attach partition idxpart22_a_idx; 668create index on idxpart (a); 669create table idxpart_another (a int, b int, primary key (a, b)) partition by range (a); 670create table idxpart_another_1 partition of idxpart_another for values from (0) to (100); 671create table idxpart3 (c int, b int, a int) partition by range (a); 672alter table idxpart3 drop column b, drop column c; 673create table idxpart31 partition of idxpart3 for values from (1000) to (1200); 674create table idxpart32 partition of idxpart3 for values from (1200) to (1400); 675alter table idxpart attach partition idxpart3 for values from (1000) to (2000); 676 677-- More objects intentionally left behind, to verify some pg_dump/pg_upgrade 678-- behavior; see https://postgr.es/m/20190321204928.GA17535@alvherre.pgsql 679create schema regress_indexing; 680set search_path to regress_indexing; 681create table pk (a int primary key) partition by range (a); 682create table pk1 partition of pk for values from (0) to (1000); 683create table pk2 (b int, a int); 684alter table pk2 drop column b; 685alter table pk2 alter a set not null; 686alter table pk attach partition pk2 for values from (1000) to (2000); 687create table pk3 partition of pk for values from (2000) to (3000); 688create table pk4 (like pk); 689alter table pk attach partition pk4 for values from (3000) to (4000); 690create table pk5 (like pk) partition by range (a); 691create table pk51 partition of pk5 for values from (4000) to (4500); 692create table pk52 partition of pk5 for values from (4500) to (5000); 693alter table pk attach partition pk5 for values from (4000) to (5000); 694reset search_path; 695 696-- Test that covering partitioned indexes work in various cases 697create table covidxpart (a int, b int) partition by list (a); 698create unique index on covidxpart (a) include (b); 699create table covidxpart1 partition of covidxpart for values in (1); 700create table covidxpart2 partition of covidxpart for values in (2); 701insert into covidxpart values (1, 1); 702insert into covidxpart values (1, 1); 703create table covidxpart3 (b int, c int, a int); 704alter table covidxpart3 drop c; 705alter table covidxpart attach partition covidxpart3 for values in (3); 706insert into covidxpart values (3, 1); 707insert into covidxpart values (3, 1); 708create table covidxpart4 (b int, a int); 709create unique index on covidxpart4 (a) include (b); 710create unique index on covidxpart4 (a); 711alter table covidxpart attach partition covidxpart4 for values in (4); 712insert into covidxpart values (4, 1); 713insert into covidxpart values (4, 1); 714create unique index on covidxpart (b) include (a); -- should fail 715 716-- check that detaching a partition also detaches the primary key constraint 717create table parted_pk_detach_test (a int primary key) partition by list (a); 718create table parted_pk_detach_test1 partition of parted_pk_detach_test for values in (1); 719alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey; -- should fail 720alter table parted_pk_detach_test detach partition parted_pk_detach_test1; 721alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey; 722drop table parted_pk_detach_test, parted_pk_detach_test1; 723create table parted_uniq_detach_test (a int unique) partition by list (a); 724create table parted_uniq_detach_test1 partition of parted_uniq_detach_test for values in (1); 725alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key; -- should fail 726alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1; 727alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key; 728drop table parted_uniq_detach_test, parted_uniq_detach_test1; 729 730-- check that dropping a column takes with it any partitioned indexes 731-- depending on it. 732create table parted_index_col_drop(a int, b int, c int) 733 partition by list (a); 734create table parted_index_col_drop1 partition of parted_index_col_drop 735 for values in (1) partition by list (a); 736-- leave this partition without children. 737create table parted_index_col_drop2 partition of parted_index_col_drop 738 for values in (2) partition by list (a); 739create table parted_index_col_drop11 partition of parted_index_col_drop1 740 for values in (1); 741create index on parted_index_col_drop (b); 742create index on parted_index_col_drop (c); 743create index on parted_index_col_drop (b, c); 744alter table parted_index_col_drop drop column c; 745\d parted_index_col_drop 746\d parted_index_col_drop1 747\d parted_index_col_drop2 748\d parted_index_col_drop11 749drop table parted_index_col_drop; 750