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,10) 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); 79DROP TABLE tbl; 80 81CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 82 UNIQUE(c1,c2) INCLUDE(c3,c4)); 83SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 84SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 85-- ensure that constraint works 86INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 87DROP TABLE tbl; 88 89CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 90 PRIMARY KEY(c1,c2) INCLUDE(c3,c4)); 91SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 92SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 93-- ensure that constraint works 94INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 95INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 96INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x; 97DROP TABLE tbl; 98 99CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 100 EXCLUDE USING btree (c1 WITH =) INCLUDE(c3,c4)); 101SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 102SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 103-- ensure that constraint works 104INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 105INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x; 106DROP TABLE tbl; 107 108/* 109 * 3.0 Test ALTER TABLE DROP COLUMN. 110 * Any column deletion leads to index deletion. 111 */ 112CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 int); 113CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2, c3, c4); 114SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 115ALTER TABLE tbl DROP COLUMN c3; 116SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 117DROP TABLE tbl; 118 119/* 120 * 3.1 Test ALTER TABLE DROP COLUMN. 121 * Included column deletion leads to the index deletion, 122 * AS well AS key columns deletion. It's explained in documentation. 123 */ 124CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box); 125CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDE(c3,c4); 126SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 127ALTER TABLE tbl DROP COLUMN c3; 128SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 129DROP TABLE tbl; 130 131/* 132 * 3.2 Test ALTER TABLE DROP COLUMN. 133 * Included column deletion leads to the index deletion. 134 * AS well AS key columns deletion. It's explained in documentation. 135 */ 136CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 137SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 138ALTER TABLE tbl DROP COLUMN c3; 139SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 140ALTER TABLE tbl DROP COLUMN c1; 141SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 142DROP TABLE tbl; 143 144/* 145 * 3.3 Test ALTER TABLE SET STATISTICS 146 */ 147CREATE TABLE tbl (c1 int, c2 int); 148CREATE INDEX tbl_idx ON tbl (c1, (c1+0)) INCLUDE (c2); 149ALTER INDEX tbl_idx ALTER COLUMN 1 SET STATISTICS 1000; 150ALTER INDEX tbl_idx ALTER COLUMN 2 SET STATISTICS 1000; 151ALTER INDEX tbl_idx ALTER COLUMN 3 SET STATISTICS 1000; 152ALTER INDEX tbl_idx ALTER COLUMN 4 SET STATISTICS 1000; 153DROP TABLE tbl; 154 155/* 156 * 4. CREATE INDEX CONCURRENTLY 157 */ 158CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 159INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,1000) AS x; 160CREATE UNIQUE INDEX CONCURRENTLY on tbl (c1, c2) INCLUDE (c3, c4); 161SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 162DROP TABLE tbl; 163 164 165/* 166 * 5. REINDEX 167 */ 168CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 169SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 170ALTER TABLE tbl DROP COLUMN c3; 171SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 172REINDEX INDEX tbl_c1_c2_c3_c4_key; 173SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 174ALTER TABLE tbl DROP COLUMN c1; 175SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 176DROP TABLE tbl; 177 178/* 179 * 7. Check various AMs. All but btree must fail. 180 */ 181CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box); 182CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4); 183CREATE INDEX on tbl USING gist(c3) INCLUDE (c4); 184CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4); 185CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4); 186CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4); 187CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4); 188CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4); 189DROP TABLE tbl; 190 191/* 192 * 8. Update, delete values in indexed table. 193 */ 194CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box); 195INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 196CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree(c1, c2) INCLUDE (c3,c4); 197UPDATE tbl SET c1 = 100 WHERE c1 = 2; 198UPDATE tbl SET c1 = 1 WHERE c1 = 3; 199-- should fail 200UPDATE tbl SET c2 = 2 WHERE c1 = 1; 201UPDATE tbl SET c3 = 1; 202DELETE FROM tbl WHERE c1 = 5 OR c3 = 12; 203DROP TABLE tbl; 204 205/* 206 * 9. Alter column type. 207 */ 208CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 209INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 210ALTER TABLE tbl ALTER c1 TYPE bigint; 211ALTER TABLE tbl ALTER c3 TYPE bigint; 212\d tbl 213DROP TABLE tbl; 214