EXCLUDE USING hash(i WITH =)

From: Erwin Brandstetter <brsaweda(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: EXCLUDE USING hash(i WITH =)
Date: 2019-03-06 18:00:26
Message-ID: CAGHENJ6NT9OaAyOKykuZrss8k0rqM8+DsL18WBJuynAgXyk3Cw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

The manual currently advises:
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

EXCLUDE [ USING *index_method* ] ( *exclude_element* WITH *operator* [, ...
> ] ) *index_parameters* [ WHERE ( *predicate* ) ][...]Although it's
> allowed, there is little point in using B-tree or hash indexes with an
> exclusion constraint, *because this does nothing that an ordinary unique
> constraint doesn't do better*. So in practice the access method will
> always be GiST or SP-GiST.

However, hash indexes do not support UNIQUE:
https://www.postgresql.org/docs/current/indexes-unique.html

Currently, only B-tree indexes can be declared unique.
>

But an exclusion constraint with "USING hash" seems to do exactly that
(more expensively, granted), handling hash collisions gracefully. Demo
(original idea by user FunctorSalad on stackoverflow:
https://stackoverflow.com/questions/47976185/postgresql-ok-to-use-hash-exclude-constraint-for-uniqueness/47976504?noredirect=1#comment96799970_47976504
):

CREATE TABLE exclude_hast_test(
i int,
EXCLUDE USING hash(i WITH =)
);

INSERT INTO exclude_hast_test VALUES (213182),(1034649); -- hashint4()
collision!

More detailed fiddle:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8a9fc48f74f93f8aed0964f3796a0b04

Would seem particularly attractive for values too large for btree indexes.
An index on a hash value is the recommended workaround, but an exclusion
constraint also handles hash collisions automatically. (Or even for any wide
column to keep index size low.)

Hence my questions:

- Why does an exclusion constraint with "USING hash(i WITH =)" enforce
uniqueness, while we still can't create a "UNIQUE index ... USING hash .."?
- Why would the manual discourage its use? Should I file a documentation
bug?

Regards
Erwin

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-03-06 18:29:18 Re: query has no destination for result data
Previous Message Rob Sargent 2019-03-06 16:19:59 Re: query has no destination for result data