From dc496cba91feb8cb3aea4438337c98efdfac9b8c Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Thu, 18 Jan 2018 20:57:13 +0900 Subject: [PATCH 1/2] Regression test for the failure of check_index_only. check_index_only forgets the case where two or more index columns on the same table column but with different operator class. --- src/test/regress/expected/create_index.out | 41 ++++++++++++++++++++++++++++++ src/test/regress/sql/create_index.sql | 26 +++++++++++++++++++ 2 files changed, 67 insertions(+) diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 031a0bc..1d107f6 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2990,6 +2990,47 @@ explain (costs off) (4 rows) -- +-- Check for duplicate indexkey with different opclasses +-- +-- opclass that doesn't have function 9 (GIST_FETCH_PROC) for GiST +CREATE OPERATOR CLASS test_inet_ops FOR TYPE inet USING gist AS + OPERATOR 3 &&, + FUNCTION 1 inet_gist_consistent (internal, inet, smallint, oid, internal), + FUNCTION 2 inet_gist_union (internal, internal), + FUNCTION 3 inet_gist_compress (internal), + FUNCTION 5 inet_gist_penalty (internal, internal, internal), + FUNCTION 6 inet_gist_picksplit (internal, internal), + FUNCTION 7 inet_gist_same (inet, inet, internal); +CREATE TABLE t (a inet); +CREATE INDEX ON t USING gist (a test_inet_ops, a inet_ops); +INSERT INTO t VALUES ('192.168.0.1'); +VACUUM t; +SELECT * FROM t WHERE a && '192.168.0.1'; -- should retuan a value + a +------------- + 192.168.0.1 +(1 row) + +-- enfoce GiST +SET enable_seqscan TO false; +SET enable_bitmapscan TO false; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM t WHERE a && '192.168.0.1'; -- shoudn't be index only scan + QUERY PLAN +---------------------------------------------------------- + Index Scan using t_a_a1_idx on t (actual rows=1 loops=1) + Index Cond: (a && '192.168.0.1'::inet) +(2 rows) + +SELECT * FROM t WHERE a && '192.168.0.1'; -- also should return a value + a +------------- + 192.168.0.1 +(1 row) + +-- cleanup +DROP TABLE t; +DROP OPERATOR CLASS test_inet_ops USING gist; +-- -- REINDEX (VERBOSE) -- CREATE TABLE reindex_verbose(id integer primary key); diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index a45e8eb..e8016f4 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -1026,6 +1026,32 @@ explain (costs off) select * from boolindex where not b order by i limit 10; -- +-- Check for duplicate indexkey with different opclasses +-- +-- opclass that doesn't have function 9 (GIST_FETCH_PROC) for GiST +CREATE OPERATOR CLASS test_inet_ops FOR TYPE inet USING gist AS + OPERATOR 3 &&, + FUNCTION 1 inet_gist_consistent (internal, inet, smallint, oid, internal), + FUNCTION 2 inet_gist_union (internal, internal), + FUNCTION 3 inet_gist_compress (internal), + FUNCTION 5 inet_gist_penalty (internal, internal, internal), + FUNCTION 6 inet_gist_picksplit (internal, internal), + FUNCTION 7 inet_gist_same (inet, inet, internal); +CREATE TABLE t (a inet); +CREATE INDEX ON t USING gist (a test_inet_ops, a inet_ops); +INSERT INTO t VALUES ('192.168.0.1'); +VACUUM t; +SELECT * FROM t WHERE a && '192.168.0.1'; -- should retuan a value +-- enfoce GiST +SET enable_seqscan TO false; +SET enable_bitmapscan TO false; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM t WHERE a && '192.168.0.1'; -- shoudn't be index only scan +SELECT * FROM t WHERE a && '192.168.0.1'; -- also should return a value +-- cleanup +DROP TABLE t; +DROP OPERATOR CLASS test_inet_ops USING gist; + +-- -- REINDEX (VERBOSE) -- CREATE TABLE reindex_verbose(id integer primary key); -- 2.9.2