text: --
  -- CREATE_INDEX
  -- Create ancillary data structures (i.e. indices)
  --
  
  --
  -- BTREE
  --
  CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
  
  CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);
  
  CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);
  
  CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
  
  CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
  
  CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
  
  CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
  
  CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
  
  CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
  
  CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
  
  CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
  
  CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
  
  CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
  
  CREATE INDEX rix ON road USING btree (name text_ops);
  
  CREATE INDEX iix ON ihighway USING btree (name text_ops);
  
  CREATE INDEX six ON shighway USING btree (name text_ops);
  
  -- test comments
  COMMENT ON INDEX six_wrong IS 'bad index';
  COMMENT ON INDEX six IS 'good index';
  COMMENT ON INDEX six IS NULL;
  
  --
  -- BTREE ascending/descending cases
  --
  -- we load int4/text from pure descending data (each key is a new
  -- low key) and name/f8 from pure ascending data (each key is a new
  -- high key).  we had a bug where new low keys would sometimes be
  -- "lost".
  --
  CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
  
  CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
  
  CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
  
  CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
  
  --
  -- BTREE partial indices
  --
  CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
  	where unique1 < 20 or unique1 > 980;
  
  CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
  	where stringu1 < 'B';
  
  CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
  	where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
  
  --
  -- GiST (rtree-equivalent opclasses only)
  --
  CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base);
  
  CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1);
  
  CREATE INDEX gcircleind ON circle_tbl USING gist (f1);
  
  INSERT INTO POINT_TBL(f1) VALUES (NULL);
  
  CREATE INDEX gpointind ON point_tbl USING gist (f1);
  
  CREATE TEMP TABLE gpolygon_tbl AS
      SELECT polygon(home_base) AS f1 FROM slow_emp4000;
  INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' );
  INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' );
  
  CREATE TEMP TABLE gcircle_tbl AS
      SELECT circle(home_base) AS f1 FROM slow_emp4000;
  
  CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
  
  CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
  
  --
  -- Test GiST indexes
  --
  
  -- get non-indexed results for comparison purposes
  
  SET enable_seqscan = ON;
  SET enable_indexscan = OFF;
  SET enable_bitmapscan = OFF;
  
  SELECT * FROM fast_emp4000
      WHERE home_base @ '(200,200),(2000,1000)'::box
      ORDER BY (home_base[0])[0];
  
  SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  
  SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  
  SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
      ORDER BY (poly_center(f1))[0];
  
  SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
      ORDER BY area(f1);
  
  SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
  
  SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
  
  SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
  
  SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
  
  SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
  
  SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
  
  SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
  
  SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
  
  SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
  
  SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
  
  SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
  
  SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
  
  SELECT * FROM point_tbl WHERE f1 IS NULL;
  
  SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
  
  SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
  
  SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10;
  
  SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10;
  
  -- Now check the results from plain indexscan
  SET enable_seqscan = OFF;
  SET enable_indexscan = ON;
  SET enable_bitmapscan = OFF;
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM fast_emp4000
      WHERE home_base @ '(200,200),(2000,1000)'::box
      ORDER BY (home_base[0])[0];
  SELECT * FROM fast_emp4000
      WHERE home_base @ '(200,200),(2000,1000)'::box
      ORDER BY (home_base[0])[0];
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
      ORDER BY (poly_center(f1))[0];
  SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
      ORDER BY (poly_center(f1))[0];
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
      ORDER BY area(f1);
  SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
      ORDER BY area(f1);
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
  SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
  SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
  SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
  SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
  SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
  SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
  SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
  SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
  SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
  SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
  SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
  SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM point_tbl WHERE f1 IS NULL;
  SELECT * FROM point_tbl WHERE f1 IS NULL;
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
  SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1';
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
  SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10;
  SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10;
  
  EXPLAIN (COSTS OFF)
  SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10;
  SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10;
  
  -- Now check the results from bitmap indexscan
  SET enable_seqscan = OFF;
  SET enable_indexscan = OFF;
  SET enable_bitmapscan = ON;
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
  SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1';
  
  RESET enable_seqscan;
  RESET enable_indexscan;
  RESET enable_bitmapscan;
  
  --
  -- GIN over int[] and text[]
  --
  -- Note: GIN currently supports only bitmap scans, not plain indexscans
  --
  
  SET enable_seqscan = OFF;
  SET enable_indexscan = OFF;
  SET enable_bitmapscan = ON;
  
  CREATE INDEX intarrayidx ON array_index_op_test USING gin (i);
  
  explain (costs off)
  SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
  
  SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno;
  SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
  SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
  SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
  SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
  
  CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
  
  explain (costs off)
  SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
  
  SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t <@ '{}' ORDER BY seqno;
  
  -- And try it with a multicolumn GIN index
  
  DROP INDEX intarrayidx, textarrayidx;
  
  CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);
  
  SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
  SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;
  SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
  SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
  
  RESET enable_seqscan;
  RESET enable_indexscan;
  RESET enable_bitmapscan;
  
  --
  -- Try a GIN index with a lot of items with same key. (GIN creates a posting
  -- tree when there are enough duplicates)
  --
  CREATE TABLE array_gin_test (a int[]);
  
  INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g;
  
  CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a);
  
  SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}';
  
  DROP TABLE array_gin_test;
  
  --
  -- Test GIN index's reloptions
  --
  CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i)
    WITH (FASTUPDATE=on, GIN_PENDING_LIST_LIMIT=128);
  \d+ gin_relopts_test
  
  --
  -- HASH
  --
  CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
  
  CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
  
  CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
  
  CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
  
  CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
  CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
  DROP TABLE unlogged_hash_table;
  
  -- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
  
  -- Test hash index build tuplesorting.  Force hash tuplesort using low
  -- maintenance_work_mem setting and fillfactor:
  SET maintenance_work_mem = '1MB';
  CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10);
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
  SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
  DROP INDEX hash_tuplesort_idx;
  RESET maintenance_work_mem;
  
  
  --
  -- Test functional index
  --
  CREATE TABLE func_index_heap (f1 text, f2 text);
  CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
  
  INSERT INTO func_index_heap VALUES('ABC','DEF');
  INSERT INTO func_index_heap VALUES('AB','CDEFG');
  INSERT INTO func_index_heap VALUES('QWE','RTY');
  -- this should fail because of unique index:
  INSERT INTO func_index_heap VALUES('ABCD', 'EF');
  -- but this shouldn't:
  INSERT INTO func_index_heap VALUES('QWERTY');
  
  -- while we're here, see that the metadata looks sane
  \d func_index_heap
  \d func_index_index
  
  
  --
  -- Same test, expressional index
  --
  DROP TABLE func_index_heap;
  CREATE TABLE func_index_heap (f1 text, f2 text);
  CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
  
  INSERT INTO func_index_heap VALUES('ABC','DEF');
  INSERT INTO func_index_heap VALUES('AB','CDEFG');
  INSERT INTO func_index_heap VALUES('QWE','RTY');
  -- this should fail because of unique index:
  INSERT INTO func_index_heap VALUES('ABCD', 'EF');
  -- but this shouldn't:
  INSERT INTO func_index_heap VALUES('QWERTY');
  
  -- while we're here, see that the metadata looks sane
  \d func_index_heap
  \d func_index_index
  
  -- this should fail because of unsafe column type (anonymous record)
  create index on func_index_heap ((f1 || f2), (row(f1, f2)));
  
  
  --
  -- Test unique index with included columns
  --
  CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text);
  CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3);
  
  INSERT INTO covering_index_heap VALUES(1,1,'AAA');
  INSERT INTO covering_index_heap VALUES(1,2,'AAA');
  -- this should fail because of unique index on f1,f2:
  INSERT INTO covering_index_heap VALUES(1,2,'BBB');
  -- and this shouldn't:
  INSERT INTO covering_index_heap VALUES(1,4,'AAA');
  -- Try to build index on table that already contains data
  CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3);
  -- Try to use existing covering index as primary key
  ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX
  covering_pkey;
  DROP TABLE covering_index_heap;
  
  
  --
  -- Also try building functional, expressional, and partial indexes on
  -- tables that already contain data.
  --
  create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
  create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
  create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
  
  --
  -- Try some concurrent index builds
  --
  -- Unfortunately this only tests about half the code paths because there are
  -- no concurrent updates happening to the table at the same time.
  
  CREATE TABLE concur_heap (f1 text, f2 text);
  -- empty table
  CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
  CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1);
  INSERT INTO concur_heap VALUES  ('a','b');
  INSERT INTO concur_heap VALUES  ('b','b');
  -- unique index
  CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
  CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1);
  -- check if constraint is set up properly to be enforced
  INSERT INTO concur_heap VALUES ('b','x');
  -- check if constraint is enforced properly at build time
  CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
  -- test that expression indexes and partial indexes work concurrently
  CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
  CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
  -- here we also check that you can default the index name
  CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));
  -- You can't do a concurrent index build in a transaction
  BEGIN;
  CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
  COMMIT;
  -- test where predicate is able to do a transactional update during
  -- a concurrent build before switching pg_index state flags.
  CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE
  LANGUAGE plpgsql AS $$
  BEGIN
    EXECUTE 'SELECT txid_current()';
    RETURN true;
  END; $$;
  CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1)
    WHERE predicate_stable();
  DROP INDEX concur_index8;
  DROP FUNCTION predicate_stable();
  
  -- But you can do a regular index build in a transaction
  BEGIN;
  CREATE INDEX std_index on concur_heap(f2);
  COMMIT;
  
  -- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX
  VACUUM FULL concur_heap;
  REINDEX TABLE concur_heap;
  DELETE FROM concur_heap WHERE f1 = 'b';
  VACUUM FULL concur_heap;
  \d concur_heap
  REINDEX TABLE concur_heap;
  \d concur_heap
  
  -- Temporary tables with concurrent builds and on-commit actions
  -- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored.
  -- PRESERVE ROWS, the default.
  CREATE TEMP TABLE concur_temp (f1 int, f2 text)
    ON COMMIT PRESERVE ROWS;
  INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
  CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);
  DROP INDEX CONCURRENTLY concur_temp_ind;
  DROP TABLE concur_temp;
  -- ON COMMIT DROP
  BEGIN;
  CREATE TEMP TABLE concur_temp (f1 int, f2 text)
    ON COMMIT DROP;
  INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
  -- Fails when running in a transaction.
  CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);
  COMMIT;
  -- ON COMMIT DELETE ROWS
  CREATE TEMP TABLE concur_temp (f1 int, f2 text)
    ON COMMIT DELETE ROWS;
  INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
  CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);
  DROP INDEX CONCURRENTLY concur_temp_ind;
  DROP TABLE concur_temp;
  
  --
  -- Try some concurrent index drops
  --
  DROP INDEX CONCURRENTLY "concur_index2";				-- works
  DROP INDEX CONCURRENTLY IF EXISTS "concur_index2";		-- notice
  
  -- failures
  DROP INDEX CONCURRENTLY "concur_index2", "concur_index3";
  BEGIN;
  DROP INDEX CONCURRENTLY "concur_index5";
  ROLLBACK;
  
  -- successes
  DROP INDEX CONCURRENTLY IF EXISTS "concur_index3";
  DROP INDEX CONCURRENTLY "concur_index4";
  DROP INDEX CONCURRENTLY "concur_index5";
  DROP INDEX CONCURRENTLY "concur_index1";
  DROP INDEX CONCURRENTLY "concur_heap_expr_idx";
  
  \d concur_heap
  
  DROP TABLE concur_heap;
  
  --
  -- Test ADD CONSTRAINT USING INDEX
  --
  
  CREATE TABLE cwi_test( a int , b varchar(10), c char);
  
  -- add some data so that all tests have something to work with.
  
  INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6);
  
  CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b);
  ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx;
  
  \d cwi_test
  \d cwi_uniq_idx
  
  CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a);
  ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
  	ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY
  		USING INDEX cwi_uniq2_idx;
  
  \d cwi_test
  \d cwi_replaced_pkey
  
  DROP INDEX cwi_replaced_pkey;	-- Should fail; a constraint depends on it
  
  DROP TABLE cwi_test;
  
  -- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables
  CREATE TABLE cwi_test(a int) PARTITION BY hash (a);
  create unique index on cwi_test (a);
  alter table cwi_test add primary key using index cwi_test_a_idx ;
  DROP TABLE cwi_test;
  
  --
  -- Check handling of indexes on system columns
  --
  CREATE TABLE syscol_table (a INT);
  
  -- System columns cannot be indexed
  CREATE INDEX ON syscolcol_table (ctid);
  
  -- nor used in expressions
  CREATE INDEX ON syscol_table ((ctid >= '(1000,0)'));
  
  -- nor used in predicates
  CREATE INDEX ON syscol_table (a) WHERE ctid >= '(1000,0)';
  
  DROP TABLE syscol_table;
  
  --
  -- Tests for IS NULL/IS NOT NULL with b-tree indexes
  --
  
  SELECT unique1, unique2 INTO onek_with_null FROM onek;
  INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
  CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
  
  SET enable_seqscan = OFF;
  SET enable_indexscan = ON;
  SET enable_bitmapscan = ON;
  
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
  
  DROP INDEX onek_nulltest;
  
  CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
  
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
  
  DROP INDEX onek_nulltest;
  
  CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
  
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
  
  DROP INDEX onek_nulltest;
  
  CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2  nulls first,unique1);
  
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
  SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
  
  DROP INDEX onek_nulltest;
  
  -- Check initial-positioning logic too
  
  CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2);
  
  SET enable_seqscan = OFF;
  SET enable_indexscan = ON;
  SET enable_bitmapscan = OFF;
  
  SELECT unique1, unique2 FROM onek_with_null
    ORDER BY unique2 LIMIT 2;
  SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
    ORDER BY unique2 LIMIT 2;
  SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0
    ORDER BY unique2 LIMIT 2;
  
  SELECT unique1, unique2 FROM onek_with_null
    ORDER BY unique2 DESC LIMIT 2;
  SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
    ORDER BY unique2 DESC LIMIT 2;
  SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999
    ORDER BY unique2 DESC LIMIT 2;
  
  RESET enable_seqscan;
  RESET enable_indexscan;
  RESET enable_bitmapscan;
  
  DROP TABLE onek_with_null;
  
  --
  -- Check bitmap index path planning
  --
  
  EXPLAIN (COSTS OFF)
  SELECT * FROM tenk1
    WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
  SELECT * FROM tenk1
    WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
  
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM tenk1
    WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
  SELECT count(*) FROM tenk1
    WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
  
  --
  -- Check behavior with duplicate index column contents
  --
  
  CREATE TABLE dupindexcols AS
    SELECT unique1 as id, stringu2::text as f1 FROM tenk1;
  CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops);
  ANALYZE dupindexcols;
  
  EXPLAIN (COSTS OFF)
    SELECT count(*) FROM dupindexcols
      WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
  SELECT count(*) FROM dupindexcols
    WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
  
  --
  -- Check ordering of =ANY indexqual results (bug in 9.2.0)
  --
  
  vacuum tenk1;		-- ensure we get consistent plans here
  
  explain (costs off)
  SELECT unique1 FROM tenk1
  WHERE unique1 IN (1,42,7)
  ORDER BY unique1;
  
  SELECT unique1 FROM tenk1
  WHERE unique1 IN (1,42,7)
  ORDER BY unique1;
  
  explain (costs off)
  SELECT thousand, tenthous FROM tenk1
  WHERE thousand < 2 AND tenthous IN (1001,3000)
  ORDER BY thousand;
  
  SELECT thousand, tenthous FROM tenk1
  WHERE thousand < 2 AND tenthous IN (1001,3000)
  ORDER BY thousand;
  
  SET enable_indexonlyscan = OFF;
  
  explain (costs off)
  SELECT thousand, tenthous FROM tenk1
  WHERE thousand < 2 AND tenthous IN (1001,3000)
  ORDER BY thousand;
  
  SELECT thousand, tenthous FROM tenk1
  WHERE thousand < 2 AND tenthous IN (1001,3000)
  ORDER BY thousand;
  
  RESET enable_indexonlyscan;
  
  --
  -- Check elimination of constant-NULL subexpressions
  --
  
  explain (costs off)
    select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
  
  --
  -- Check matching of boolean index columns to WHERE conditions and sort keys
  --
  
  create temp table boolindex (b bool, i int, unique(b, i), junk float);
  
  explain (costs off)
    select * from boolindex order by b, i limit 10;
  explain (costs off)
    select * from boolindex where b order by i limit 10;
  explain (costs off)
    select * from boolindex where b = true order by i desc limit 10;
  explain (costs off)
    select * from boolindex where not b order by i limit 10;
  explain (costs off)
    select * from boolindex where b is true order by i desc limit 10;
  explain (costs off)
    select * from boolindex where b is false order by i desc limit 10;
  
  --
  -- REINDEX (VERBOSE)
  --
  CREATE TABLE reindex_verbose(id integer primary key);
  \set VERBOSITY terse \\ -- suppress machine-dependent details
  REINDEX (VERBOSE) TABLE reindex_verbose;
  \set VERBOSITY default
  DROP TABLE reindex_verbose;
  
  --
  -- REINDEX CONCURRENTLY
  --
  CREATE TABLE concur_reindex_tab (c1 int);
  -- REINDEX
  REINDEX TABLE concur_reindex_tab; -- notice
  REINDEX TABLE CONCURRENTLY concur_reindex_tab; -- notice
  ALTER TABLE concur_reindex_tab ADD COLUMN c2 text; -- add toast index
  -- Normal index with integer column
  CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab(c1);
  -- Normal index with text column
  CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab(c2);
  -- UNIQUE index with expression
  CREATE UNIQUE INDEX concur_reindex_ind3 ON concur_reindex_tab(abs(c1));
  -- Duplicate column names
  CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab(c1, c1, c2);
  -- Create table for check on foreign key dependence switch with indexes swapped
  ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
  CREATE TABLE concur_reindex_tab2 (c1 int REFERENCES concur_reindex_tab);
  INSERT INTO concur_reindex_tab VALUES  (1, 'a');
  INSERT INTO concur_reindex_tab VALUES  (2, 'a');
  -- Reindex concurrently of exclusion constraint currently not supported
  CREATE TABLE concur_reindex_tab3 (c1 int, c2 int4range, EXCLUDE USING gist (c2 WITH &&));
  INSERT INTO concur_reindex_tab3 VALUES  (3, '[1,2]');
  REINDEX INDEX CONCURRENTLY  concur_reindex_tab3_c2_excl;  -- error
  REINDEX TABLE CONCURRENTLY concur_reindex_tab3;  -- succeeds with warning
  INSERT INTO concur_reindex_tab3 VALUES  (4, '[2,4]');
  -- Check materialized views
  CREATE MATERIALIZED VIEW concur_reindex_matview AS SELECT * FROM concur_reindex_tab;
  -- Dependency lookup before and after the follow-up REINDEX commands.
  -- These should remain consistent.
  SELECT pg_describe_object(classid, objid, objsubid) as obj,
         pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
         deptype
  FROM pg_depend
  WHERE classid = 'pg_class'::regclass AND
    objid in ('concur_reindex_tab'::regclass,
              'concur_reindex_ind1'::regclass,
  	    'concur_reindex_ind2'::regclass,
  	    'concur_reindex_ind3'::regclass,
  	    'concur_reindex_ind4'::regclass,
  	    'concur_reindex_matview'::regclass)
    ORDER BY 1, 2;
  REINDEX INDEX CONCURRENTLY concur_reindex_ind1;
  REINDEX TABLE CONCURRENTLY concur_reindex_tab;
  REINDEX TABLE CONCURRENTLY concur_reindex_matview;
  SELECT pg_describe_object(classid, objid, objsubid) as obj,
         pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
         deptype
  FROM pg_depend
  WHERE classid = 'pg_class'::regclass AND
    objid in ('concur_reindex_tab'::regclass,
              'concur_reindex_ind1'::regclass,
  	    'concur_reindex_ind2'::regclass,
  	    'concur_reindex_ind3'::regclass,
  	    'concur_reindex_ind4'::regclass,
  	    'concur_reindex_matview'::regclass)
    ORDER BY 1, 2;
  -- Check that comments are preserved
  CREATE TABLE testcomment (i int);
  CREATE INDEX testcomment_idx1 ON testcomment (i);
  COMMENT ON INDEX testcomment_idx1 IS 'test comment';
  SELECT obj_description('testcomment_idx1'::regclass, 'pg_class');
  REINDEX TABLE testcomment;
  SELECT obj_description('testcomment_idx1'::regclass, 'pg_class');
  REINDEX TABLE CONCURRENTLY testcomment ;
  SELECT obj_description('testcomment_idx1'::regclass, 'pg_class');
  DROP TABLE testcomment;
  -- Check that indisclustered updates are preserved
  CREATE TABLE concur_clustered(i int);
  CREATE INDEX concur_clustered_i_idx ON concur_clustered(i);
  ALTER TABLE concur_clustered CLUSTER ON concur_clustered_i_idx;
  REINDEX TABLE CONCURRENTLY concur_clustered;
  SELECT indexrelid::regclass, indisclustered FROM pg_index
    WHERE indrelid = 'concur_clustered'::regclass;
  DROP TABLE concur_clustered;
  -- Check that indisreplident updates are preserved.
  CREATE TABLE concur_replident(i int NOT NULL);
  CREATE UNIQUE INDEX concur_replident_i_idx ON concur_replident(i);
  ALTER TABLE concur_replident REPLICA IDENTITY
    USING INDEX concur_replident_i_idx;
  SELECT indexrelid::regclass, indisreplident FROM pg_index
    WHERE indrelid = 'concur_replident'::regclass;
  REINDEX TABLE CONCURRENTLY concur_replident;
  SELECT indexrelid::regclass, indisreplident FROM pg_index
    WHERE indrelid = 'concur_replident'::regclass;
  DROP TABLE concur_replident;
  
  -- Partitions
  -- Create some partitioned tables
  CREATE TABLE concur_reindex_part (c1 int, c2 int) PARTITION BY RANGE (c1);
  CREATE TABLE concur_reindex_part_0 PARTITION OF concur_reindex_part
    FOR VALUES FROM (0) TO (10) PARTITION BY list (c2);
  CREATE TABLE concur_reindex_part_0_1 PARTITION OF concur_reindex_part_0
    FOR VALUES IN (1);
  CREATE TABLE concur_reindex_part_0_2 PARTITION OF concur_reindex_part_0
    FOR VALUES IN (2);
  -- This partitioned table will have no partitions.
  CREATE TABLE concur_reindex_part_10 PARTITION OF concur_reindex_part
    FOR VALUES FROM (10) TO (20) PARTITION BY list (c2);
  -- Create some partitioned indexes
  CREATE INDEX concur_reindex_part_index ON ONLY concur_reindex_part (c1);
  CREATE INDEX concur_reindex_part_index_0 ON ONLY concur_reindex_part_0 (c1);
  ALTER INDEX concur_reindex_part_index ATTACH PARTITION concur_reindex_part_index_0;
  -- This partitioned index will have no partitions.
  CREATE INDEX concur_reindex_part_index_10 ON ONLY concur_reindex_part_10 (c1);
  ALTER INDEX concur_reindex_part_index ATTACH PARTITION concur_reindex_part_index_10;
  CREATE INDEX concur_reindex_part_index_0_1 ON ONLY concur_reindex_part_0_1 (c1);
  ALTER INDEX concur_reindex_part_index_0 ATTACH PARTITION concur_reindex_part_index_0_1;
  CREATE INDEX concur_reindex_part_index_0_2 ON ONLY concur_reindex_part_0_2 (c1);
  ALTER INDEX concur_reindex_part_index_0 ATTACH PARTITION concur_reindex_part_index_0_2;
  SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
    ORDER BY relid, level;
  -- REINDEX fails for partitioned indexes
  REINDEX INDEX concur_reindex_part_index_10;
  REINDEX INDEX CONCURRENTLY concur_reindex_part_index_10;
  -- REINDEX is a no-op for partitioned tables
  REINDEX TABLE concur_reindex_part_10;
  REINDEX TABLE CONCURRENTLY concur_reindex_part_10;
  SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
    ORDER BY relid, level;
  -- REINDEX should preserve dependencies of partition tree.
  SELECT pg_describe_object(classid, objid, objsubid) as obj,
         pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
         deptype
  FROM pg_depend
  WHERE classid = 'pg_class'::regclass AND
    objid in ('concur_reindex_part'::regclass,
              'concur_reindex_part_0'::regclass,
              'concur_reindex_part_0_1'::regclass,
              'concur_reindex_part_0_2'::regclass,
              'concur_reindex_part_index'::regclass,
              'concur_reindex_part_index_0'::regclass,
              'concur_reindex_part_index_0_1'::regclass,
              'concur_reindex_part_index_0_2'::regclass)
    ORDER BY 1, 2;
  REINDEX INDEX CONCURRENTLY concur_reindex_part_index_0_1;
  REINDEX INDEX CONCURRENTLY concur_reindex_part_index_0_2;
  SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
    ORDER BY relid, level;
  REINDEX TABLE CONCURRENTLY concur_reindex_part_0_1;
  REINDEX TABLE CONCURRENTLY concur_reindex_part_0_2;
  SELECT pg_describe_object(classid, objid, objsubid) as obj,
         pg_describe_object(refclassid,refobjid,refobjsubid) as objref,
         deptype
  FROM pg_depend
  WHERE classid = 'pg_class'::regclass AND
    objid in ('concur_reindex_part'::regclass,
              'concur_reindex_part_0'::regclass,
              'concur_reindex_part_0_1'::regclass,
              'concur_reindex_part_0_2'::regclass,
              'concur_reindex_part_index'::regclass,
              'concur_reindex_part_index_0'::regclass,
              'concur_reindex_part_index_0_1'::regclass,
              'concur_reindex_part_index_0_2'::regclass)
    ORDER BY 1, 2;
  SELECT relid, parentrelid, level FROM pg_partition_tree('concur_reindex_part_index')
    ORDER BY relid, level;
  DROP TABLE concur_reindex_part;
  
  -- Check errors
  -- Cannot run inside a transaction block
  BEGIN;
  REINDEX TABLE CONCURRENTLY concur_reindex_tab;
  COMMIT;
  REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
  REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
  -- These are the toast table and index of pg_authid.
  REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
  REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
  REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
  -- Warns about catalog relations
  REINDEX SCHEMA CONCURRENTLY pg_catalog;
  
  -- Check the relation status, there should not be invalid indexes
  \d concur_reindex_tab
  DROP MATERIALIZED VIEW concur_reindex_matview;
  DROP TABLE concur_reindex_tab, concur_reindex_tab2, concur_reindex_tab3;
  
  -- Check handling of invalid indexes
  CREATE TABLE concur_reindex_tab4 (c1 int);
  INSERT INTO concur_reindex_tab4 VALUES (1), (1), (2);
  -- This trick creates an invalid index.
  CREATE UNIQUE INDEX CONCURRENTLY concur_reindex_ind5 ON concur_reindex_tab4 (c1);
  -- Reindexing concurrently this index fails with the same failure.
  -- The extra index created is itself invalid, and can be dropped.
  REINDEX INDEX CONCURRENTLY concur_reindex_ind5;
  \d concur_reindex_tab4
  DROP INDEX concur_reindex_ind5_ccnew;
  -- This makes the previous failure go away, so the index can become valid.
  DELETE FROM concur_reindex_tab4 WHERE c1 = 1;
  -- The invalid index is not processed when running REINDEX TABLE.
  REINDEX TABLE CONCURRENTLY concur_reindex_tab4;
  \d concur_reindex_tab4
  -- But it is fixed with REINDEX INDEX.
  REINDEX INDEX CONCURRENTLY concur_reindex_ind5;
  \d concur_reindex_tab4
  DROP TABLE concur_reindex_tab4;
  
  -- Check handling of indexes with expressions and predicates.  The
  -- definitions of the rebuilt indexes should match the original
  -- definitions.
  CREATE TABLE concur_exprs_tab (c1 int , c2 boolean);
  INSERT INTO concur_exprs_tab (c1, c2) VALUES (1369652450, FALSE),
    (414515746, TRUE),
    (897778963, FALSE);
  CREATE UNIQUE INDEX concur_exprs_index_expr
    ON concur_exprs_tab ((c1::text COLLATE "C"));
  CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
    WHERE (c1::text > 500000000::text COLLATE "C");
  CREATE UNIQUE INDEX concur_exprs_index_pred_2
    ON concur_exprs_tab ((1 / c1))
    WHERE ('-H') >= (c2::TEXT) COLLATE "C";
  ALTER INDEX concur_exprs_index_expr ALTER COLUMN 1 SET STATISTICS 100;
  ANALYZE concur_exprs_tab;
  SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
    'concur_exprs_index_expr'::regclass,
    'concur_exprs_index_pred'::regclass,
    'concur_exprs_index_pred_2'::regclass)
    GROUP BY starelid ORDER BY starelid::regclass::text;
  SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
  SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
  SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
  REINDEX TABLE CONCURRENTLY concur_exprs_tab;
  SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
  SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
  SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
  -- ALTER TABLE recreates the indexes, which should keep their collations.
  ALTER TABLE concur_exprs_tab ALTER c2 TYPE TEXT;
  SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
  SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
  SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
  -- Statistics should remain intact.
  SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
    'concur_exprs_index_expr'::regclass,
    'concur_exprs_index_pred'::regclass,
    'concur_exprs_index_pred_2'::regclass)
    GROUP BY starelid ORDER BY starelid::regclass::text;
  -- attstattarget should remain intact
  SELECT attrelid::regclass, attnum, attstattarget
    FROM pg_attribute WHERE attrelid IN (
      'concur_exprs_index_expr'::regclass,
      'concur_exprs_index_pred'::regclass,
      'concur_exprs_index_pred_2'::regclass)
    ORDER BY attrelid::regclass::text, attnum;
  DROP TABLE concur_exprs_tab;
  
  -- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.
  -- ON COMMIT PRESERVE ROWS, the default.
  CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text)
    ON COMMIT PRESERVE ROWS;
  INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar');
  CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2);
  REINDEX TABLE CONCURRENTLY concur_temp_tab_1;
  REINDEX INDEX CONCURRENTLY concur_temp_ind_1;
  -- Still fails in transaction blocks
  BEGIN;
  REINDEX INDEX CONCURRENTLY concur_temp_ind_1;
  COMMIT;
  -- ON COMMIT DELETE ROWS
  CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text)
    ON COMMIT DELETE ROWS;
  CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2);
  REINDEX TABLE CONCURRENTLY concur_temp_tab_2;
  REINDEX INDEX CONCURRENTLY concur_temp_ind_2;
  -- ON COMMIT DROP
  BEGIN;
  CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text)
    ON COMMIT PRESERVE ROWS;
  INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar');
  CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2);
  -- Fails when running in a transaction
  REINDEX INDEX CONCURRENTLY concur_temp_ind_3;
  COMMIT;
  -- REINDEX SCHEMA processes all temporary relations
  CREATE TABLE reindex_temp_before AS
  SELECT oid, relname, relfilenode, relkind, reltoastrelid
    FROM pg_class
    WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2');
  SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset
  REINDEX SCHEMA CONCURRENTLY :temp_schema_name;
  SELECT  b.relname,
          b.relkind,
          CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
          ELSE 'relfilenode has changed' END
    FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid
    ORDER BY 1;
  DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before;
  
  --
  -- REINDEX SCHEMA
  --
  REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist
  CREATE SCHEMA schema_to_reindex;
  SET search_path = 'schema_to_reindex';
  CREATE TABLE table1(col1 SERIAL PRIMARY KEY);
  INSERT INTO table1 SELECT generate_series(1,400);
  CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL);
  INSERT INTO table2 SELECT generate_series(1,400), 'abc';
  CREATE INDEX ON table2(col2);
  CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2;
  CREATE INDEX ON matview(col1);
  CREATE VIEW view AS SELECT col2 FROM table2;
  CREATE TABLE reindex_before AS
  SELECT oid, relname, relfilenode, relkind, reltoastrelid
  	FROM pg_class
  	where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
  INSERT INTO reindex_before
  SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid
  FROM pg_class WHERE oid IN
  	(SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0);
  INSERT INTO reindex_before
  SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
  FROM pg_class where oid in
  	(select indexrelid from pg_index where indrelid in
  		(select reltoastrelid from reindex_before where reltoastrelid > 0));
  REINDEX SCHEMA schema_to_reindex;
  CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
  	FROM pg_class
  	where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
  SELECT  b.relname,
          b.relkind,
          CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
          ELSE 'relfilenode has changed' END
    FROM reindex_before b JOIN pg_class a ON b.oid = a.oid
    ORDER BY 1;
  REINDEX SCHEMA schema_to_reindex;
  BEGIN;
  REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction
  END;
  
  -- concurrently
  REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
  
  -- Failure for unauthorized user
  CREATE ROLE regress_reindexuser NOLOGIN;
  SET SESSION ROLE regress_reindexuser;
  REINDEX SCHEMA schema_to_reindex;
  -- Permission failures with toast tables and indexes (pg_authid here)
  RESET ROLE;
  GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
  SET SESSION ROLE regress_reindexuser;
  REINDEX TABLE pg_toast.pg_toast_1260;
  REINDEX INDEX pg_toast.pg_toast_1260_index;
  
  -- Clean up
  RESET ROLE;
  REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser;
  DROP ROLE regress_reindexuser;
  DROP SCHEMA schema_to_reindex CASCADE;
  