1-- 2-- CREATE_INDEX 3-- Create ancillary data structures (i.e. indices) 4-- 5 6-- 7-- BTREE 8-- 9CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops); 10 11CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops); 12 13CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops); 14 15CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops); 16 17CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops); 18 19CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops); 20 21CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops); 22 23CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops); 24 25CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops); 26 27CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous); 28 29CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops); 30 31CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops); 32 33CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops); 34 35CREATE INDEX rix ON road USING btree (name text_ops); 36 37CREATE INDEX iix ON ihighway USING btree (name text_ops); 38 39CREATE INDEX six ON shighway USING btree (name text_ops); 40 41-- test comments 42COMMENT ON INDEX six_wrong IS 'bad index'; 43COMMENT ON INDEX six IS 'good index'; 44COMMENT ON INDEX six IS NULL; 45 46-- 47-- BTREE ascending/descending cases 48-- 49-- we load int4/text from pure descending data (each key is a new 50-- low key) and name/f8 from pure ascending data (each key is a new 51-- high key). we had a bug where new low keys would sometimes be 52-- "lost". 53-- 54CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops); 55 56CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops); 57 58CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops); 59 60CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops); 61 62-- 63-- BTREE partial indices 64-- 65CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops) 66 where unique1 < 20 or unique1 > 980; 67 68CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops) 69 where stringu1 < 'B'; 70 71CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops) 72 where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K'; 73 74-- 75-- GiST (rtree-equivalent opclasses only) 76-- 77CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base); 78 79CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); 80 81CREATE INDEX gcircleind ON circle_tbl USING gist (f1); 82 83INSERT INTO POINT_TBL(f1) VALUES (NULL); 84 85CREATE INDEX gpointind ON point_tbl USING gist (f1); 86 87CREATE TEMP TABLE gpolygon_tbl AS 88 SELECT polygon(home_base) AS f1 FROM slow_emp4000; 89INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); 90INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' ); 91 92CREATE TEMP TABLE gcircle_tbl AS 93 SELECT circle(home_base) AS f1 FROM slow_emp4000; 94 95CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1); 96 97CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1); 98 99-- 100-- Test GiST indexes 101-- 102 103-- get non-indexed results for comparison purposes 104 105SET enable_seqscan = ON; 106SET enable_indexscan = OFF; 107SET enable_bitmapscan = OFF; 108 109SELECT * FROM fast_emp4000 110 WHERE home_base <@ '(200,200),(2000,1000)'::box 111 ORDER BY (home_base[0])[0]; 112 113SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 114 115SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 116 117SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon 118 ORDER BY (poly_center(f1))[0]; 119 120SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 121 ORDER BY area(f1); 122 123SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 124 125SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 126 127SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 128 129SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 130 131SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 132 133SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 134 135SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 136 137SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 138 139SELECT count(*) FROM point_tbl p WHERE p.f1 <<| '(0.0, 0.0)'; 140 141SELECT count(*) FROM point_tbl p WHERE p.f1 |>> '(0.0, 0.0)'; 142 143SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 144 145SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 146 147SELECT * FROM point_tbl WHERE f1 IS NULL; 148 149SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 150 151SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 152 153SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 154 155SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 156 157-- Now check the results from plain indexscan 158SET enable_seqscan = OFF; 159SET enable_indexscan = ON; 160SET enable_bitmapscan = OFF; 161 162EXPLAIN (COSTS OFF) 163SELECT * FROM fast_emp4000 164 WHERE home_base <@ '(200,200),(2000,1000)'::box 165 ORDER BY (home_base[0])[0]; 166SELECT * FROM fast_emp4000 167 WHERE home_base <@ '(200,200),(2000,1000)'::box 168 ORDER BY (home_base[0])[0]; 169 170EXPLAIN (COSTS OFF) 171SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 172SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 173 174EXPLAIN (COSTS OFF) 175SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 176SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 177 178EXPLAIN (COSTS OFF) 179SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon 180 ORDER BY (poly_center(f1))[0]; 181SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon 182 ORDER BY (poly_center(f1))[0]; 183 184EXPLAIN (COSTS OFF) 185SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 186 ORDER BY area(f1); 187SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 188 ORDER BY area(f1); 189 190EXPLAIN (COSTS OFF) 191SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 192SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 193 194EXPLAIN (COSTS OFF) 195SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 196SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 197 198EXPLAIN (COSTS OFF) 199SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 200SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 201 202EXPLAIN (COSTS OFF) 203SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 204SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 205 206EXPLAIN (COSTS OFF) 207SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 208SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 209 210EXPLAIN (COSTS OFF) 211SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 212SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 213 214EXPLAIN (COSTS OFF) 215SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 216SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 217 218EXPLAIN (COSTS OFF) 219SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 220SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 221 222EXPLAIN (COSTS OFF) 223SELECT count(*) FROM point_tbl p WHERE p.f1 <<| '(0.0, 0.0)'; 224SELECT count(*) FROM point_tbl p WHERE p.f1 <<| '(0.0, 0.0)'; 225 226EXPLAIN (COSTS OFF) 227SELECT count(*) FROM point_tbl p WHERE p.f1 |>> '(0.0, 0.0)'; 228SELECT count(*) FROM point_tbl p WHERE p.f1 |>> '(0.0, 0.0)'; 229 230EXPLAIN (COSTS OFF) 231SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 232SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 233 234EXPLAIN (COSTS OFF) 235SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 236SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 237 238EXPLAIN (COSTS OFF) 239SELECT * FROM point_tbl WHERE f1 IS NULL; 240SELECT * FROM point_tbl WHERE f1 IS NULL; 241 242EXPLAIN (COSTS OFF) 243SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 244SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 245 246EXPLAIN (COSTS OFF) 247SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 248SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 249 250EXPLAIN (COSTS OFF) 251SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 252SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 253 254EXPLAIN (COSTS OFF) 255SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 256SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 257 258-- Now check the results from bitmap indexscan 259SET enable_seqscan = OFF; 260SET enable_indexscan = OFF; 261SET enable_bitmapscan = ON; 262 263EXPLAIN (COSTS OFF) 264SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 265SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 266 267RESET enable_seqscan; 268RESET enable_indexscan; 269RESET enable_bitmapscan; 270 271-- 272-- GIN over int[] and text[] 273-- 274-- Note: GIN currently supports only bitmap scans, not plain indexscans 275-- 276 277SET enable_seqscan = OFF; 278SET enable_indexscan = OFF; 279SET enable_bitmapscan = ON; 280 281CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); 282 283explain (costs off) 284SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 285 286SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 287SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 288SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno; 289SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno; 290SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno; 291SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno; 292SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; 293SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno; 294SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno; 295SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno; 296SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno; 297SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno; 298SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 299SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; 300SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; 301SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 302 303CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); 304 305explain (costs off) 306SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 307 308SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 309SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; 310SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; 311SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; 312SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 313SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 314SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; 315SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno; 316SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 317SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno; 318SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno; 319SELECT * FROM array_index_op_test WHERE t <@ '{}' ORDER BY seqno; 320 321-- And try it with a multicolumn GIN index 322 323DROP INDEX intarrayidx, textarrayidx; 324 325CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t); 326 327SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 328SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 329SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno; 330SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; 331SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; 332SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; 333SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 334SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 335SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 336 337RESET enable_seqscan; 338RESET enable_indexscan; 339RESET enable_bitmapscan; 340 341-- 342-- Try a GIN index with a lot of items with same key. (GIN creates a posting 343-- tree when there are enough duplicates) 344-- 345CREATE TABLE array_gin_test (a int[]); 346 347INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g; 348 349CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a); 350 351SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}'; 352 353DROP TABLE array_gin_test; 354 355-- 356-- Test GIN index's reloptions 357-- 358CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) 359 WITH (FASTUPDATE=on, GIN_PENDING_LIST_LIMIT=128); 360\d+ gin_relopts_test 361 362-- 363-- HASH 364-- 365CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); 366 367CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); 368 369CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); 370 371CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60); 372 373CREATE UNLOGGED TABLE unlogged_hash_table (id int4); 374CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); 375DROP TABLE unlogged_hash_table; 376 377-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); 378 379-- Test hash index build tuplesorting. Force hash tuplesort using low 380-- maintenance_work_mem setting and fillfactor: 381SET maintenance_work_mem = '1MB'; 382CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10); 383EXPLAIN (COSTS OFF) 384SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 385SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 386DROP INDEX hash_tuplesort_idx; 387RESET maintenance_work_mem; 388 389 390-- 391-- Test functional index 392-- 393CREATE TABLE func_index_heap (f1 text, f2 text); 394CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); 395 396INSERT INTO func_index_heap VALUES('ABC','DEF'); 397INSERT INTO func_index_heap VALUES('AB','CDEFG'); 398INSERT INTO func_index_heap VALUES('QWE','RTY'); 399-- this should fail because of unique index: 400INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 401-- but this shouldn't: 402INSERT INTO func_index_heap VALUES('QWERTY'); 403 404-- while we're here, see that the metadata looks sane 405\d func_index_heap 406\d func_index_index 407 408 409-- 410-- Same test, expressional index 411-- 412DROP TABLE func_index_heap; 413CREATE TABLE func_index_heap (f1 text, f2 text); 414CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); 415 416INSERT INTO func_index_heap VALUES('ABC','DEF'); 417INSERT INTO func_index_heap VALUES('AB','CDEFG'); 418INSERT INTO func_index_heap VALUES('QWE','RTY'); 419-- this should fail because of unique index: 420INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 421-- but this shouldn't: 422INSERT INTO func_index_heap VALUES('QWERTY'); 423 424-- while we're here, see that the metadata looks sane 425\d func_index_heap 426\d func_index_index 427 428-- this should fail because of unsafe column type (anonymous record) 429create index on func_index_heap ((f1 || f2), (row(f1, f2))); 430 431 432-- 433-- Test unique index with included columns 434-- 435CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text); 436CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3); 437 438INSERT INTO covering_index_heap VALUES(1,1,'AAA'); 439INSERT INTO covering_index_heap VALUES(1,2,'AAA'); 440-- this should fail because of unique index on f1,f2: 441INSERT INTO covering_index_heap VALUES(1,2,'BBB'); 442-- and this shouldn't: 443INSERT INTO covering_index_heap VALUES(1,4,'AAA'); 444-- Try to build index on table that already contains data 445CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3); 446-- Try to use existing covering index as primary key 447ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX 448covering_pkey; 449DROP TABLE covering_index_heap; 450 451 452-- 453-- Also try building functional, expressional, and partial indexes on 454-- tables that already contain data. 455-- 456create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); 457create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); 458create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; 459 460-- 461-- Try some concurrent index builds 462-- 463-- Unfortunately this only tests about half the code paths because there are 464-- no concurrent updates happening to the table at the same time. 465 466CREATE TABLE concur_heap (f1 text, f2 text); 467-- empty table 468CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); 469CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1); 470INSERT INTO concur_heap VALUES ('a','b'); 471INSERT INTO concur_heap VALUES ('b','b'); 472-- unique index 473CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); 474CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1); 475-- check if constraint is set up properly to be enforced 476INSERT INTO concur_heap VALUES ('b','x'); 477-- check if constraint is enforced properly at build time 478CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); 479-- test that expression indexes and partial indexes work concurrently 480CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; 481CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; 482-- here we also check that you can default the index name 483CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); 484-- You can't do a concurrent index build in a transaction 485BEGIN; 486CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); 487COMMIT; 488-- test where predicate is able to do a transactional update during 489-- a concurrent build before switching pg_index state flags. 490CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE 491LANGUAGE plpgsql AS $$ 492BEGIN 493 EXECUTE 'SELECT txid_current()'; 494 RETURN true; 495END; $$; 496CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) 497 WHERE predicate_stable(); 498DROP INDEX concur_index8; 499DROP FUNCTION predicate_stable(); 500 501-- But you can do a regular index build in a transaction 502BEGIN; 503CREATE INDEX std_index on concur_heap(f2); 504COMMIT; 505 506-- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX 507VACUUM FULL concur_heap; 508REINDEX TABLE concur_heap; 509DELETE FROM concur_heap WHERE f1 = 'b'; 510VACUUM FULL concur_heap; 511\d concur_heap 512REINDEX TABLE concur_heap; 513\d concur_heap 514 515-- Temporary tables with concurrent builds and on-commit actions 516-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. 517-- PRESERVE ROWS, the default. 518CREATE TEMP TABLE concur_temp (f1 int, f2 text) 519 ON COMMIT PRESERVE ROWS; 520INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 521CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 522DROP INDEX CONCURRENTLY concur_temp_ind; 523DROP TABLE concur_temp; 524-- ON COMMIT DROP 525BEGIN; 526CREATE TEMP TABLE concur_temp (f1 int, f2 text) 527 ON COMMIT DROP; 528INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 529-- Fails when running in a transaction. 530CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 531COMMIT; 532-- ON COMMIT DELETE ROWS 533CREATE TEMP TABLE concur_temp (f1 int, f2 text) 534 ON COMMIT DELETE ROWS; 535INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 536CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 537DROP INDEX CONCURRENTLY concur_temp_ind; 538DROP TABLE concur_temp; 539 540-- 541-- Try some concurrent index drops 542-- 543DROP INDEX CONCURRENTLY "concur_index2"; -- works 544DROP INDEX CONCURRENTLY IF EXISTS "concur_index2"; -- notice 545 546-- failures 547DROP INDEX CONCURRENTLY "concur_index2", "concur_index3"; 548BEGIN; 549DROP INDEX CONCURRENTLY "concur_index5"; 550ROLLBACK; 551 552-- successes 553DROP INDEX CONCURRENTLY IF EXISTS "concur_index3"; 554DROP INDEX CONCURRENTLY "concur_index4"; 555DROP INDEX CONCURRENTLY "concur_index5"; 556DROP INDEX CONCURRENTLY "concur_index1"; 557DROP INDEX CONCURRENTLY "concur_heap_expr_idx"; 558 559\d concur_heap 560 561DROP TABLE concur_heap; 562 563-- 564-- Test ADD CONSTRAINT USING INDEX 565-- 566 567CREATE TABLE cwi_test( a int , b varchar(10), c char); 568 569-- add some data so that all tests have something to work with. 570 571INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6); 572 573CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b); 574ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx; 575 576\d cwi_test 577\d cwi_uniq_idx 578 579CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a); 580ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx, 581 ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY 582 USING INDEX cwi_uniq2_idx; 583 584\d cwi_test 585\d cwi_replaced_pkey 586 587DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it 588 589-- Check that non-default index options are rejected 590CREATE UNIQUE INDEX cwi_uniq3_idx ON cwi_test(a desc); 591ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx; -- fail 592CREATE UNIQUE INDEX cwi_uniq4_idx ON cwi_test(b collate "POSIX"); 593ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx; -- fail 594 595DROP TABLE cwi_test; 596 597-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables 598CREATE TABLE cwi_test(a int) PARTITION BY hash (a); 599create unique index on cwi_test (a); 600alter table cwi_test add primary key using index cwi_test_a_idx ; 601DROP TABLE cwi_test; 602 603-- 604-- Check handling of indexes on system columns 605-- 606CREATE TABLE syscol_table (a INT); 607 608-- System columns cannot be indexed 609CREATE INDEX ON syscolcol_table (ctid); 610 611-- nor used in expressions 612CREATE INDEX ON syscol_table ((ctid >= '(1000,0)')); 613 614-- nor used in predicates 615CREATE INDEX ON syscol_table (a) WHERE ctid >= '(1000,0)'; 616 617DROP TABLE syscol_table; 618 619-- 620-- Tests for IS NULL/IS NOT NULL with b-tree indexes 621-- 622 623CREATE TABLE onek_with_null AS SELECT unique1, unique2 FROM onek; 624INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL); 625CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1); 626 627SET enable_seqscan = OFF; 628SET enable_indexscan = ON; 629SET enable_bitmapscan = ON; 630 631SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 632SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 633SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 634SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 635SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 636SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 637 638DROP INDEX onek_nulltest; 639 640CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1); 641 642SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 643SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 644SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 645SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 646SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 647SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 648 649DROP INDEX onek_nulltest; 650 651CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1); 652 653SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 654SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 655SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 656SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 657SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 658SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 659 660DROP INDEX onek_nulltest; 661 662CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1); 663 664SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 665SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 666SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 667SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 668SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 669SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 670 671DROP INDEX onek_nulltest; 672 673-- Check initial-positioning logic too 674 675CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2); 676 677SET enable_seqscan = OFF; 678SET enable_indexscan = ON; 679SET enable_bitmapscan = OFF; 680 681SELECT unique1, unique2 FROM onek_with_null 682 ORDER BY unique2 LIMIT 2; 683SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 684 ORDER BY unique2 LIMIT 2; 685SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0 686 ORDER BY unique2 LIMIT 2; 687 688SELECT unique1, unique2 FROM onek_with_null 689 ORDER BY unique2 DESC LIMIT 2; 690SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 691 ORDER BY unique2 DESC LIMIT 2; 692SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999 693 ORDER BY unique2 DESC LIMIT 2; 694 695RESET enable_seqscan; 696RESET enable_indexscan; 697RESET enable_bitmapscan; 698 699DROP TABLE onek_with_null; 700 701-- 702-- Check bitmap index path planning 703-- 704 705EXPLAIN (COSTS OFF) 706SELECT * FROM tenk1 707 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 708SELECT * FROM tenk1 709 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 710 711EXPLAIN (COSTS OFF) 712SELECT count(*) FROM tenk1 713 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 714SELECT count(*) FROM tenk1 715 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 716 717-- 718-- Check behavior with duplicate index column contents 719-- 720 721CREATE TABLE dupindexcols AS 722 SELECT unique1 as id, stringu2::text as f1 FROM tenk1; 723CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); 724ANALYZE dupindexcols; 725 726EXPLAIN (COSTS OFF) 727 SELECT count(*) FROM dupindexcols 728 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 729SELECT count(*) FROM dupindexcols 730 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 731 732-- 733-- Check ordering of =ANY indexqual results (bug in 9.2.0) 734-- 735 736vacuum tenk1; -- ensure we get consistent plans here 737 738explain (costs off) 739SELECT unique1 FROM tenk1 740WHERE unique1 IN (1,42,7) 741ORDER BY unique1; 742 743SELECT unique1 FROM tenk1 744WHERE unique1 IN (1,42,7) 745ORDER BY unique1; 746 747explain (costs off) 748SELECT thousand, tenthous FROM tenk1 749WHERE thousand < 2 AND tenthous IN (1001,3000) 750ORDER BY thousand; 751 752SELECT thousand, tenthous FROM tenk1 753WHERE thousand < 2 AND tenthous IN (1001,3000) 754ORDER BY thousand; 755 756SET enable_indexonlyscan = OFF; 757 758explain (costs off) 759SELECT thousand, tenthous FROM tenk1 760WHERE thousand < 2 AND tenthous IN (1001,3000) 761ORDER BY thousand; 762 763SELECT thousand, tenthous FROM tenk1 764WHERE thousand < 2 AND tenthous IN (1001,3000) 765ORDER BY thousand; 766 767RESET enable_indexonlyscan; 768 769-- 770-- Check elimination of constant-NULL subexpressions 771-- 772 773explain (costs off) 774 select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); 775 776-- 777-- Check matching of boolean index columns to WHERE conditions and sort keys 778-- 779 780create temp table boolindex (b bool, i int, unique(b, i), junk float); 781 782explain (costs off) 783 select * from boolindex order by b, i limit 10; 784explain (costs off) 785 select * from boolindex where b order by i limit 10; 786explain (costs off) 787 select * from boolindex where b = true order by i desc limit 10; 788explain (costs off) 789 select * from boolindex where not b order by i limit 10; 790explain (costs off) 791 select * from boolindex where b is true order by i desc limit 10; 792explain (costs off) 793 select * from boolindex where b is false order by i desc limit 10; 794 795-- 796-- REINDEX (VERBOSE) 797-- 798CREATE TABLE reindex_verbose(id integer primary key); 799\set VERBOSITY terse \\ -- suppress machine-dependent details 800REINDEX (VERBOSE) TABLE reindex_verbose; 801\set VERBOSITY default 802DROP TABLE reindex_verbose; 803 804-- 805-- REINDEX CONCURRENTLY 806-- 807CREATE TABLE concur_reindex_tab (c1 int); 808-- REINDEX 809REINDEX TABLE concur_reindex_tab; -- notice 810REINDEX (CONCURRENTLY) TABLE concur_reindex_tab; -- notice 811ALTER TABLE concur_reindex_tab ADD COLUMN c2 text; -- add toast index 812-- Normal index with integer column 813CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab(c1); 814-- Normal index with text column 815CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab(c2); 816-- UNIQUE index with expression 817CREATE UNIQUE INDEX concur_reindex_ind3 ON concur_reindex_tab(abs(c1)); 818-- Duplicate column names 819CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab(c1, c1, c2); 820-- Create table for check on foreign key dependence switch with indexes swapped 821ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1; 822CREATE TABLE concur_reindex_tab2 (c1 int REFERENCES concur_reindex_tab); 823INSERT INTO concur_reindex_tab VALUES (1, 'a'); 824INSERT INTO concur_reindex_tab VALUES (2, 'a'); 825-- Reindex concurrently of exclusion constraint currently not supported 826CREATE TABLE concur_reindex_tab3 (c1 int, c2 int4range, EXCLUDE USING gist (c2 WITH &&)); 827INSERT INTO concur_reindex_tab3 VALUES (3, '[1,2]'); 828REINDEX INDEX CONCURRENTLY concur_reindex_tab3_c2_excl; -- error 829REINDEX TABLE CONCURRENTLY concur_reindex_tab3; -- succeeds with warning 830INSERT INTO concur_reindex_tab3 VALUES (4, '[2,4]'); 831-- Check materialized views 832CREATE MATERIALIZED VIEW concur_reindex_matview AS SELECT * FROM concur_reindex_tab; 833-- Dependency lookup before and after the follow-up REINDEX commands. 834-- These should remain consistent. 835SELECT pg_describe_object(classid, objid, objsubid) as obj, 836 pg_describe_object(refclassid,refobjid,refobjsubid) as objref, 837 deptype 838FROM pg_depend 839WHERE classid = 'pg_class'::regclass AND 840 objid in ('concur_reindex_tab'::regclass, 841 'concur_reindex_ind1'::regclass, 842 'concur_reindex_ind2'::regclass, 843 'concur_reindex_ind3'::regclass, 844 'concur_reindex_ind4'::regclass, 845 'concur_reindex_matview'::regclass) 846 ORDER BY 1, 2; 847REINDEX INDEX CONCURRENTLY concur_reindex_ind1; 848REINDEX TABLE CONCURRENTLY concur_reindex_tab; 849REINDEX TABLE CONCURRENTLY concur_reindex_matview; 850SELECT pg_describe_object(classid, objid, objsubid) as obj, 851 pg_describe_object(refclassid,refobjid,refobjsubid) as objref, 852 deptype 853FROM pg_depend 854WHERE classid = 'pg_class'::regclass AND 855 objid in ('concur_reindex_tab'::regclass, 856 'concur_reindex_ind1'::regclass, 857 'concur_reindex_ind2'::regclass, 858 'concur_reindex_ind3'::regclass, 859 'concur_reindex_ind4'::regclass, 860 'concur_reindex_matview'::regclass) 861 ORDER BY 1, 2; 862-- Check that comments are preserved 863CREATE TABLE testcomment (i int); 864CREATE INDEX testcomment_idx1 ON testcomment (i); 865COMMENT ON INDEX testcomment_idx1 IS 'test comment'; 866SELECT obj_description('testcomment_idx1'::regclass, 'pg_class'); 867REINDEX TABLE testcomment; 868SELECT obj_description('testcomment_idx1'::regclass, 'pg_class'); 869REINDEX TABLE CONCURRENTLY testcomment ; 870SELECT obj_description('testcomment_idx1'::regclass, 'pg_class'); 871DROP TABLE testcomment; 872-- Check that indisclustered updates are preserved 873CREATE TABLE concur_clustered(i int); 874CREATE INDEX concur_clustered_i_idx ON concur_clustered(i); 875ALTER TABLE concur_clustered CLUSTER ON concur_clustered_i_idx; 876REINDEX TABLE CONCURRENTLY concur_clustered; 877SELECT indexrelid::regclass, indisclustered FROM pg_index 878 WHERE indrelid = 'concur_clustered'::regclass; 879DROP TABLE concur_clustered; 880-- Check that indisreplident updates are preserved. 881CREATE TABLE concur_replident(i int NOT NULL); 882CREATE UNIQUE INDEX concur_replident_i_idx ON concur_replident(i); 883ALTER TABLE concur_replident REPLICA IDENTITY 884 USING INDEX concur_replident_i_idx; 885SELECT indexrelid::regclass, indisreplident FROM pg_index 886 WHERE indrelid = 'concur_replident'::regclass; 887REINDEX TABLE CONCURRENTLY concur_replident; 888SELECT indexrelid::regclass, indisreplident FROM pg_index 889 WHERE indrelid = 'concur_replident'::regclass; 890DROP TABLE concur_replident; 891-- Check that opclass parameters are preserved 892CREATE TABLE concur_appclass_tab(i tsvector, j tsvector, k tsvector); 893CREATE INDEX concur_appclass_ind on concur_appclass_tab 894 USING gist (i tsvector_ops (siglen='1000'), j tsvector_ops (siglen='500')); 895CREATE INDEX concur_appclass_ind_2 on concur_appclass_tab 896 USING gist (k tsvector_ops (siglen='300'), j tsvector_ops); 897REINDEX TABLE CONCURRENTLY concur_appclass_tab; 898\d concur_appclass_tab 899DROP TABLE concur_appclass_tab; 900 901-- Partitions 902-- Create some partitioned tables 903CREATE TABLE concur_reindex_part (c1 int, c2 int) PARTITION BY RANGE (c1); 904CREATE TABLE concur_reindex_part_0 PARTITION OF concur_reindex_part 905 FOR VALUES FROM (0) TO (10) PARTITION BY list (c2); 906CREATE TABLE concur_reindex_part_0_1 PARTITION OF concur_reindex_part_0 907 FOR VALUES IN (1); 908CREATE TABLE concur_reindex_part_0_2 PARTITION OF concur_reindex_part_0 909 FOR VALUES IN (2); 910-- This partitioned table will have no partitions. 911CREATE TABLE concur_reindex_part_10 PARTITION OF concur_reindex_part 912 FOR VALUES FROM (10) TO (20) PARTITION BY list (c2); 913-- Create some partitioned indexes 914CREATE INDEX concur_reindex_part_index ON ONLY concur_reindex_part (c1); 915CREATE INDEX concur_reindex_part_index_0 ON ONLY concur_reindex_part_0 (c1); 916ALTER INDEX concur_reindex_part_index ATTACH PARTITION concur_reindex_part_index_0; 917-- This partitioned index will have no partitions. 918CREATE INDEX concur_reindex_part_index_10 ON ONLY concur_reindex_part_10 (c1); 919ALTER INDEX concur_reindex_part_index ATTACH PARTITION concur_reindex_part_index_10; 920CREATE INDEX concur_reindex_part_index_0_1 ON ONLY concur_reindex_part_0_1 (c1); 921ALTER INDEX concur_reindex_part_index_0 ATTACH PARTITION concur_reindex_part_index_0_1; 922CREATE INDEX concur_reindex_part_index_0_2 ON ONLY concur_reindex_part_0_2 (c1); 923ALTER INDEX concur_reindex_part_index_0 ATTACH PARTITION concur_reindex_part_index_0_2; 924SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index') 925 ORDER BY relid, level; 926SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index') 927 ORDER BY relid, level; 928-- REINDEX should preserve dependencies of partition tree. 929SELECT pg_describe_object(classid, objid, objsubid) as obj, 930 pg_describe_object(refclassid,refobjid,refobjsubid) as objref, 931 deptype 932FROM pg_depend 933WHERE classid = 'pg_class'::regclass AND 934 objid in ('concur_reindex_part'::regclass, 935 'concur_reindex_part_0'::regclass, 936 'concur_reindex_part_0_1'::regclass, 937 'concur_reindex_part_0_2'::regclass, 938 'concur_reindex_part_index'::regclass, 939 'concur_reindex_part_index_0'::regclass, 940 'concur_reindex_part_index_0_1'::regclass, 941 'concur_reindex_part_index_0_2'::regclass) 942 ORDER BY 1, 2; 943REINDEX INDEX CONCURRENTLY concur_reindex_part_index_0_1; 944REINDEX INDEX CONCURRENTLY concur_reindex_part_index_0_2; 945SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index') 946 ORDER BY relid, level; 947REINDEX TABLE CONCURRENTLY concur_reindex_part_0_1; 948REINDEX TABLE CONCURRENTLY concur_reindex_part_0_2; 949SELECT pg_describe_object(classid, objid, objsubid) as obj, 950 pg_describe_object(refclassid,refobjid,refobjsubid) as objref, 951 deptype 952FROM pg_depend 953WHERE classid = 'pg_class'::regclass AND 954 objid in ('concur_reindex_part'::regclass, 955 'concur_reindex_part_0'::regclass, 956 'concur_reindex_part_0_1'::regclass, 957 'concur_reindex_part_0_2'::regclass, 958 'concur_reindex_part_index'::regclass, 959 'concur_reindex_part_index_0'::regclass, 960 'concur_reindex_part_index_0_1'::regclass, 961 'concur_reindex_part_index_0_2'::regclass) 962 ORDER BY 1, 2; 963SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index') 964 ORDER BY relid, level; 965 966-- REINDEX for partitioned indexes 967-- REINDEX TABLE fails for partitioned indexes 968-- Top-most parent index 969REINDEX TABLE concur_reindex_part_index; -- error 970REINDEX TABLE CONCURRENTLY concur_reindex_part_index; -- error 971-- Partitioned index with no leaves 972REINDEX TABLE concur_reindex_part_index_10; -- error 973REINDEX TABLE CONCURRENTLY concur_reindex_part_index_10; -- error 974-- Cannot run in a transaction block 975BEGIN; 976REINDEX INDEX concur_reindex_part_index; 977ROLLBACK; 978-- Helper functions to track changes of relfilenodes in a partition tree. 979-- Create a table tracking the relfilenode state. 980CREATE OR REPLACE FUNCTION create_relfilenode_part(relname text, indname text) 981 RETURNS VOID AS 982 $func$ 983 BEGIN 984 EXECUTE format(' 985 CREATE TABLE %I AS 986 SELECT oid, relname, relfilenode, relkind, reltoastrelid 987 FROM pg_class 988 WHERE oid IN 989 (SELECT relid FROM pg_partition_tree(''%I''));', 990 relname, indname); 991 END 992 $func$ LANGUAGE plpgsql; 993CREATE OR REPLACE FUNCTION compare_relfilenode_part(tabname text) 994 RETURNS TABLE (relname name, relkind "char", state text) AS 995 $func$ 996 BEGIN 997 RETURN QUERY EXECUTE 998 format( 999 'SELECT b.relname, 1000 b.relkind, 1001 CASE WHEN a.relfilenode = b.relfilenode THEN ''relfilenode is unchanged'' 1002 ELSE ''relfilenode has changed'' END 1003 -- Do not join with OID here as CONCURRENTLY changes it. 1004 FROM %I b JOIN pg_class a ON b.relname = a.relname 1005 ORDER BY 1;', tabname); 1006 END 1007 $func$ LANGUAGE plpgsql; 1008-- Check that expected relfilenodes are changed, non-concurrent case. 1009SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index'); 1010REINDEX INDEX concur_reindex_part_index; 1011SELECT * FROM compare_relfilenode_part('reindex_index_status'); 1012DROP TABLE reindex_index_status; 1013-- concurrent case. 1014SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index'); 1015REINDEX INDEX CONCURRENTLY concur_reindex_part_index; 1016SELECT * FROM compare_relfilenode_part('reindex_index_status'); 1017DROP TABLE reindex_index_status; 1018 1019-- REINDEX for partitioned tables 1020-- REINDEX INDEX fails for partitioned tables 1021-- Top-most parent 1022REINDEX INDEX concur_reindex_part; -- error 1023REINDEX INDEX CONCURRENTLY concur_reindex_part; -- error 1024-- Partitioned with no leaves 1025REINDEX INDEX concur_reindex_part_10; -- error 1026REINDEX INDEX CONCURRENTLY concur_reindex_part_10; -- error 1027-- Cannot run in a transaction block 1028BEGIN; 1029REINDEX TABLE concur_reindex_part; 1030ROLLBACK; 1031-- Check that expected relfilenodes are changed, non-concurrent case. 1032-- Note that the partition tree changes of the *indexes* need to be checked. 1033SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index'); 1034REINDEX TABLE concur_reindex_part; 1035SELECT * FROM compare_relfilenode_part('reindex_index_status'); 1036DROP TABLE reindex_index_status; 1037-- concurrent case. 1038SELECT create_relfilenode_part('reindex_index_status', 'concur_reindex_part_index'); 1039REINDEX TABLE CONCURRENTLY concur_reindex_part; 1040SELECT * FROM compare_relfilenode_part('reindex_index_status'); 1041DROP TABLE reindex_index_status; 1042 1043DROP FUNCTION create_relfilenode_part; 1044DROP FUNCTION compare_relfilenode_part; 1045 1046-- Cleanup of partition tree used for REINDEX test. 1047DROP TABLE concur_reindex_part; 1048 1049-- Check errors 1050-- Cannot run inside a transaction block 1051BEGIN; 1052REINDEX TABLE CONCURRENTLY concur_reindex_tab; 1053COMMIT; 1054REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation 1055REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index 1056-- These are the toast table and index of pg_authid. 1057REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table 1058REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index 1059REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM 1060-- Warns about catalog relations 1061REINDEX SCHEMA CONCURRENTLY pg_catalog; 1062 1063-- Check the relation status, there should not be invalid indexes 1064\d concur_reindex_tab 1065DROP MATERIALIZED VIEW concur_reindex_matview; 1066DROP TABLE concur_reindex_tab, concur_reindex_tab2, concur_reindex_tab3; 1067 1068-- Check handling of invalid indexes 1069CREATE TABLE concur_reindex_tab4 (c1 int); 1070INSERT INTO concur_reindex_tab4 VALUES (1), (1), (2); 1071-- This trick creates an invalid index. 1072CREATE UNIQUE INDEX CONCURRENTLY concur_reindex_ind5 ON concur_reindex_tab4 (c1); 1073-- Reindexing concurrently this index fails with the same failure. 1074-- The extra index created is itself invalid, and can be dropped. 1075REINDEX INDEX CONCURRENTLY concur_reindex_ind5; 1076\d concur_reindex_tab4 1077DROP INDEX concur_reindex_ind5_ccnew; 1078-- This makes the previous failure go away, so the index can become valid. 1079DELETE FROM concur_reindex_tab4 WHERE c1 = 1; 1080-- The invalid index is not processed when running REINDEX TABLE. 1081REINDEX TABLE CONCURRENTLY concur_reindex_tab4; 1082\d concur_reindex_tab4 1083-- But it is fixed with REINDEX INDEX. 1084REINDEX INDEX CONCURRENTLY concur_reindex_ind5; 1085\d concur_reindex_tab4 1086DROP TABLE concur_reindex_tab4; 1087 1088-- Check handling of indexes with expressions and predicates. The 1089-- definitions of the rebuilt indexes should match the original 1090-- definitions. 1091CREATE TABLE concur_exprs_tab (c1 int , c2 boolean); 1092INSERT INTO concur_exprs_tab (c1, c2) VALUES (1369652450, FALSE), 1093 (414515746, TRUE), 1094 (897778963, FALSE); 1095CREATE UNIQUE INDEX concur_exprs_index_expr 1096 ON concur_exprs_tab ((c1::text COLLATE "C")); 1097CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1) 1098 WHERE (c1::text > 500000000::text COLLATE "C"); 1099CREATE UNIQUE INDEX concur_exprs_index_pred_2 1100 ON concur_exprs_tab ((1 / c1)) 1101 WHERE ('-H') >= (c2::TEXT) COLLATE "C"; 1102ALTER INDEX concur_exprs_index_expr ALTER COLUMN 1 SET STATISTICS 100; 1103ANALYZE concur_exprs_tab; 1104SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN ( 1105 'concur_exprs_index_expr'::regclass, 1106 'concur_exprs_index_pred'::regclass, 1107 'concur_exprs_index_pred_2'::regclass) 1108 GROUP BY starelid ORDER BY starelid::regclass::text; 1109SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass); 1110SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass); 1111SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); 1112REINDEX TABLE CONCURRENTLY concur_exprs_tab; 1113SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass); 1114SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass); 1115SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); 1116-- ALTER TABLE recreates the indexes, which should keep their collations. 1117ALTER TABLE concur_exprs_tab ALTER c2 TYPE TEXT; 1118SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass); 1119SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass); 1120SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); 1121-- Statistics should remain intact. 1122SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN ( 1123 'concur_exprs_index_expr'::regclass, 1124 'concur_exprs_index_pred'::regclass, 1125 'concur_exprs_index_pred_2'::regclass) 1126 GROUP BY starelid ORDER BY starelid::regclass::text; 1127-- attstattarget should remain intact 1128SELECT attrelid::regclass, attnum, attstattarget 1129 FROM pg_attribute WHERE attrelid IN ( 1130 'concur_exprs_index_expr'::regclass, 1131 'concur_exprs_index_pred'::regclass, 1132 'concur_exprs_index_pred_2'::regclass) 1133 ORDER BY attrelid::regclass::text, attnum; 1134DROP TABLE concur_exprs_tab; 1135 1136-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored. 1137-- ON COMMIT PRESERVE ROWS, the default. 1138CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text) 1139 ON COMMIT PRESERVE ROWS; 1140INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar'); 1141CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2); 1142REINDEX TABLE CONCURRENTLY concur_temp_tab_1; 1143REINDEX INDEX CONCURRENTLY concur_temp_ind_1; 1144-- Still fails in transaction blocks 1145BEGIN; 1146REINDEX INDEX CONCURRENTLY concur_temp_ind_1; 1147COMMIT; 1148-- ON COMMIT DELETE ROWS 1149CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text) 1150 ON COMMIT DELETE ROWS; 1151CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2); 1152REINDEX TABLE CONCURRENTLY concur_temp_tab_2; 1153REINDEX INDEX CONCURRENTLY concur_temp_ind_2; 1154-- ON COMMIT DROP 1155BEGIN; 1156CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text) 1157 ON COMMIT PRESERVE ROWS; 1158INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar'); 1159CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2); 1160-- Fails when running in a transaction 1161REINDEX INDEX CONCURRENTLY concur_temp_ind_3; 1162COMMIT; 1163-- REINDEX SCHEMA processes all temporary relations 1164CREATE TABLE reindex_temp_before AS 1165SELECT oid, relname, relfilenode, relkind, reltoastrelid 1166 FROM pg_class 1167 WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2'); 1168SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset 1169REINDEX SCHEMA CONCURRENTLY :temp_schema_name; 1170SELECT b.relname, 1171 b.relkind, 1172 CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' 1173 ELSE 'relfilenode has changed' END 1174 FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid 1175 ORDER BY 1; 1176DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; 1177 1178-- 1179-- REINDEX SCHEMA 1180-- 1181REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist 1182CREATE SCHEMA schema_to_reindex; 1183SET search_path = 'schema_to_reindex'; 1184CREATE TABLE table1(col1 SERIAL PRIMARY KEY); 1185INSERT INTO table1 SELECT generate_series(1,400); 1186CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL); 1187INSERT INTO table2 SELECT generate_series(1,400), 'abc'; 1188CREATE INDEX ON table2(col2); 1189CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2; 1190CREATE INDEX ON matview(col1); 1191CREATE VIEW view AS SELECT col2 FROM table2; 1192CREATE TABLE reindex_before AS 1193SELECT oid, relname, relfilenode, relkind, reltoastrelid 1194 FROM pg_class 1195 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 1196INSERT INTO reindex_before 1197SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid 1198FROM pg_class WHERE oid IN 1199 (SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0); 1200INSERT INTO reindex_before 1201SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid 1202FROM pg_class where oid in 1203 (select indexrelid from pg_index where indrelid in 1204 (select reltoastrelid from reindex_before where reltoastrelid > 0)); 1205REINDEX SCHEMA schema_to_reindex; 1206CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind 1207 FROM pg_class 1208 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 1209SELECT b.relname, 1210 b.relkind, 1211 CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' 1212 ELSE 'relfilenode has changed' END 1213 FROM reindex_before b JOIN pg_class a ON b.oid = a.oid 1214 ORDER BY 1; 1215REINDEX SCHEMA schema_to_reindex; 1216BEGIN; 1217REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction 1218END; 1219 1220-- concurrently 1221REINDEX SCHEMA CONCURRENTLY schema_to_reindex; 1222 1223-- Failure for unauthorized user 1224CREATE ROLE regress_reindexuser NOLOGIN; 1225SET SESSION ROLE regress_reindexuser; 1226REINDEX SCHEMA schema_to_reindex; 1227-- Permission failures with toast tables and indexes (pg_authid here) 1228RESET ROLE; 1229GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser; 1230SET SESSION ROLE regress_reindexuser; 1231REINDEX TABLE pg_toast.pg_toast_1260; 1232REINDEX INDEX pg_toast.pg_toast_1260_index; 1233 1234-- Clean up 1235RESET ROLE; 1236REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser; 1237DROP ROLE regress_reindexuser; 1238DROP SCHEMA schema_to_reindex CASCADE; 1239