Now that we have the create hash index warning in 9.5, I realized that
we don't warn about hash indexes with PITR, only crash recovery and
streaming. This patch fixes that.
Is the wording "cannot be used" too vague. The CREATE INDEX manual
page has the words "give wrong answers to queries", which might be
better, but is kind of long for an error message. Suggestions?
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
new file mode 100644
index e469b17..43df32f
*** a/doc/src/sgml/ref/create_index.sgml
--- b/doc/src/sgml/ref/create_index.sgml
*************** Indexes:
*** 474,480 ****
Also, changes to hash indexes are not replicated over streaming or
file-based replication after the initial base backup, so they
give wrong answers to queries that subsequently use them.
! For these reasons, hash index use is presently discouraged.
</para>
</caution>
--- 474,481 ----
Also, changes to hash indexes are not replicated over streaming or
file-based replication after the initial base backup, so they
give wrong answers to queries that subsequently use them.
! Hash indexes are also not properly restored during point-in-time
! recovery. For these reasons, hash index use is presently discouraged.
</para>
</caution>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
new file mode 100644
index 8a1cb4b..03833d7
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
*************** DefineIndex(Oid relationId,
*** 491,497 ****
if (strcmp(accessMethodName, "hash") == 0)
ereport(WARNING,
! (errmsg("hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers")));
if (stmt->unique && !accessMethodForm->amcanunique)
ereport(ERROR,
--- 491,497 ----
if (strcmp(accessMethodName, "hash") == 0)
ereport(WARNING,
! (errmsg("hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers")));
if (stmt->unique && !accessMethodForm->amcanunique)
ereport(ERROR,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
new file mode 100644
index a2bef7a..11325e4
*** a/src/test/regress/expected/create_index.out
--- b/src/test/regress/expected/create_index.out
*************** DROP TABLE array_gin_test;
*** 2238,2250 ****
-- HASH
--
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
--
-- Test functional index
--- 2238,2250 ----
-- HASH
--
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
--
-- Test functional index
diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out
new file mode 100644
index fa23b52..47ac5a6
*** a/src/test/regress/expected/enum.out
--- b/src/test/regress/expected/enum.out
*************** DROP INDEX enumtest_btree;
*** 383,389 ****
-- Hash index / opclass with the = operator
--
CREATE INDEX enumtest_hash ON enumtest USING hash (col);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
SELECT * FROM enumtest WHERE col = 'orange';
col
--------
--- 383,389 ----
-- Hash index / opclass with the = operator
--
CREATE INDEX enumtest_hash ON enumtest USING hash (col);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
SELECT * FROM enumtest WHERE col = 'orange';
col
--------
diff --git a/src/test/regress/expected/macaddr.out b/src/test/regress/expected/macaddr.out
new file mode 100644
index 91edc5a..45eac53
*** a/src/test/regress/expected/macaddr.out
--- b/src/test/regress/expected/macaddr.out
*************** SELECT * FROM macaddr_data;
*** 39,45 ****
CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b);
CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1;
a | b | trunc
----+-------------------+-------------------
--- 39,45 ----
CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b);
CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1;
a | b | trunc
----+-------------------+-------------------
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
new file mode 100644
index 45df81a..7b1f8b7
*** a/src/test/regress/expected/replica_identity.out
--- b/src/test/regress/expected/replica_identity.out
*************** CREATE INDEX test_replica_identity_keyab
*** 11,17 ****
CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb);
CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey);
CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3));
CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3';
-- default is 'd'/DEFAULT for user created tables
--- 11,17 ----
CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb);
CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey);
CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3));
CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3';
-- default is 'd'/DEFAULT for user created tables
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
new file mode 100644
index 97c0ce3..de9d018
*** a/src/test/regress/expected/uuid.out
--- b/src/test/regress/expected/uuid.out
*************** SELECT COUNT(*) FROM guid1 WHERE guid_fi
*** 114,120 ****
-- btree and hash index creation test
CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used on standby servers
-- unique index test
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
-- should fail
--- 114,120 ----
-- btree and hash index creation test
CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
! WARNING: hash indexes are not WAL-logged and thus are not crash-safe and cannot be used for point-in-time recovery or on standby servers
-- unique index test
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
-- should fail
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
