1/* 2 * 1.test CREATE INDEX 3 * 4 * Deliberately avoid dropping objects in this section, to get some pg_dump 5 * coverage. 6 */ 7 8-- Regular index with included columns 9CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box); 10INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 11CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4); 12-- duplicate column is pretty pointless, but we allow it anyway 13CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3); 14SELECT pg_get_indexdef(i.indexrelid) 15FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 16WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname; 17\d tbl_include_reg_idx 18 19-- Unique index and unique constraint 20CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box); 21INSERT INTO tbl_include_unique1 SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 22CREATE UNIQUE INDEX tbl_include_unique1_idx_unique ON tbl_include_unique1 using btree (c1, c2) INCLUDE (c3, c4); 23ALTER TABLE tbl_include_unique1 add UNIQUE USING INDEX tbl_include_unique1_idx_unique; 24ALTER TABLE tbl_include_unique1 add UNIQUE (c1, c2) INCLUDE (c3, c4); 25SELECT pg_get_indexdef(i.indexrelid) 26FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 27WHERE i.indrelid = 'tbl_include_unique1'::regclass ORDER BY c.relname; 28 29-- Unique index and unique constraint. Both must fail. 30CREATE TABLE tbl_include_unique2 (c1 int, c2 int, c3 int, c4 box); 31INSERT INTO tbl_include_unique2 SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 32CREATE UNIQUE INDEX tbl_include_unique2_idx_unique ON tbl_include_unique2 using btree (c1, c2) INCLUDE (c3, c4); 33ALTER TABLE tbl_include_unique2 add UNIQUE (c1, c2) INCLUDE (c3, c4); 34 35-- PK constraint 36CREATE TABLE tbl_include_pk (c1 int, c2 int, c3 int, c4 box); 37INSERT INTO tbl_include_pk SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 38ALTER TABLE tbl_include_pk add PRIMARY KEY (c1, c2) INCLUDE (c3, c4); 39SELECT pg_get_indexdef(i.indexrelid) 40FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 41WHERE i.indrelid = 'tbl_include_pk'::regclass ORDER BY c.relname; 42 43CREATE TABLE tbl_include_box (c1 int, c2 int, c3 int, c4 box); 44INSERT INTO tbl_include_box SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 45CREATE UNIQUE INDEX tbl_include_box_idx_unique ON tbl_include_box using btree (c1, c2) INCLUDE (c3, c4); 46ALTER TABLE tbl_include_box add PRIMARY KEY USING INDEX tbl_include_box_idx_unique; 47SELECT pg_get_indexdef(i.indexrelid) 48FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 49WHERE i.indrelid = 'tbl_include_box'::regclass ORDER BY c.relname; 50 51-- PK constraint. Must fail. 52CREATE TABLE tbl_include_box_pk (c1 int, c2 int, c3 int, c4 box); 53INSERT INTO tbl_include_box_pk SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 54ALTER TABLE tbl_include_box_pk add PRIMARY KEY (c1, c2) INCLUDE (c3, c4); 55 56 57/* 58 * 2. Test CREATE TABLE with constraint 59 */ 60CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 61 CONSTRAINT covering UNIQUE(c1,c2) INCLUDE(c3,c4)); 62SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 63SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 64-- ensure that constraint works 65INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 66DROP TABLE tbl; 67 68CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 69 CONSTRAINT covering PRIMARY KEY(c1,c2) INCLUDE(c3,c4)); 70SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 71SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 72-- ensure that constraint works 73INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 74INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 75INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,300) AS x; 76explain (costs off) 77select * from tbl where (c1,c2,c3) < (2,5,1); 78select * from tbl where (c1,c2,c3) < (2,5,1); 79-- row comparison that compares high key at page boundary 80SET enable_seqscan = off; 81explain (costs off) 82select * from tbl where (c1,c2,c3) < (262,1,1) limit 1; 83select * from tbl where (c1,c2,c3) < (262,1,1) limit 1; 84DROP TABLE tbl; 85RESET enable_seqscan; 86 87CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 88 UNIQUE(c1,c2) INCLUDE(c3,c4)); 89SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 90SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 91-- ensure that constraint works 92INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 93DROP TABLE tbl; 94 95CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 96 PRIMARY KEY(c1,c2) INCLUDE(c3,c4)); 97SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 98SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 99-- ensure that constraint works 100INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 101INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 102INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x; 103DROP TABLE tbl; 104 105CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 106 EXCLUDE USING btree (c1 WITH =) INCLUDE(c3,c4)); 107SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 108SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 109-- ensure that constraint works 110INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 111INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x; 112DROP TABLE tbl; 113 114/* 115 * 3.0 Test ALTER TABLE DROP COLUMN. 116 * Any column deletion leads to index deletion. 117 */ 118CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 int); 119CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2, c3, c4); 120SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 121ALTER TABLE tbl DROP COLUMN c3; 122SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 123DROP TABLE tbl; 124 125/* 126 * 3.1 Test ALTER TABLE DROP COLUMN. 127 * Included column deletion leads to the index deletion, 128 * AS well AS key columns deletion. It's explained in documentation. 129 */ 130CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box); 131CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDE(c3,c4); 132SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 133ALTER TABLE tbl DROP COLUMN c3; 134SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 135DROP TABLE tbl; 136 137/* 138 * 3.2 Test ALTER TABLE DROP COLUMN. 139 * Included column deletion leads to the index deletion. 140 * AS well AS key columns deletion. It's explained in documentation. 141 */ 142CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 143SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 144ALTER TABLE tbl DROP COLUMN c3; 145SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 146ALTER TABLE tbl DROP COLUMN c1; 147SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 148DROP TABLE tbl; 149 150/* 151 * 3.3 Test ALTER TABLE SET STATISTICS 152 */ 153CREATE TABLE tbl (c1 int, c2 int); 154CREATE INDEX tbl_idx ON tbl (c1, (c1+0)) INCLUDE (c2); 155ALTER INDEX tbl_idx ALTER COLUMN 1 SET STATISTICS 1000; 156ALTER INDEX tbl_idx ALTER COLUMN 2 SET STATISTICS 1000; 157ALTER INDEX tbl_idx ALTER COLUMN 3 SET STATISTICS 1000; 158ALTER INDEX tbl_idx ALTER COLUMN 4 SET STATISTICS 1000; 159DROP TABLE tbl; 160 161/* 162 * 4. CREATE INDEX CONCURRENTLY 163 */ 164CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 165INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,1000) AS x; 166CREATE UNIQUE INDEX CONCURRENTLY on tbl (c1, c2) INCLUDE (c3, c4); 167SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 168DROP TABLE tbl; 169 170 171/* 172 * 5. REINDEX 173 */ 174CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 175SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 176ALTER TABLE tbl DROP COLUMN c3; 177SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 178REINDEX INDEX tbl_c1_c2_c3_c4_key; 179SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 180ALTER TABLE tbl DROP COLUMN c1; 181SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 182DROP TABLE tbl; 183 184/* 185 * 7. Check various AMs. All but btree, gist and spgist must fail. 186 */ 187CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box); 188CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4); 189CREATE INDEX on tbl USING gist(c3) INCLUDE (c1, c4); 190CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4); 191CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4); 192CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4); 193CREATE INDEX on tbl USING rtree(c3) INCLUDE (c1, c4); 194CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4); 195DROP TABLE tbl; 196 197/* 198 * 8. Update, delete values in indexed table. 199 */ 200CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box); 201INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 202CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree(c1, c2) INCLUDE (c3,c4); 203UPDATE tbl SET c1 = 100 WHERE c1 = 2; 204UPDATE tbl SET c1 = 1 WHERE c1 = 3; 205-- should fail 206UPDATE tbl SET c2 = 2 WHERE c1 = 1; 207UPDATE tbl SET c3 = 1; 208DELETE FROM tbl WHERE c1 = 5 OR c3 = 12; 209DROP TABLE tbl; 210 211/* 212 * 9. Alter column type. 213 */ 214CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 215INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 216ALTER TABLE tbl ALTER c1 TYPE bigint; 217ALTER TABLE tbl ALTER c3 TYPE bigint; 218\d tbl 219DROP TABLE tbl; 220