On Fri, Oct 17, 2014 at 12:56:52PM -0400, Tom Lane wrote:
> David G Johnston <david.g.johns...@gmail.com> writes:
> > The question is whether we explain the implications of not being WAL-logged
> > in an error message or simply state the fact and let the documentation
> > explain the hazards - basically just output:
> > "hash indexes are not WAL-logged and their use is discouraged"
>
> +1. The warning message is not the place to be trying to explain all the
> details.
OK, updated patch attached.
--
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..3c1e90e
*** 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 their use is discouraged")));
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..8326e94
*** 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 their use is discouraged
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
! WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
! WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
! WARNING: hash indexes are not WAL-logged and their use is discouraged
-- 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..1a61a5b
*** 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 their use is discouraged
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..8e41a4b
*** 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 their use is discouraged
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..e29e2fb
*** 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 their use is discouraged
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..59cb1e0
*** 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 their use is discouraged
-- 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
