Re: [HACKERS] EXLCUDE constraints and Hash indexes

[Jim Nasby]

On 8/17/16 8:12 AM, Andrew Gierth wrote:

I also recently found a case where using btree exclusion constraints was
useful: a unique index on an expression can't be marked deferrable, but
the equivalent exclusion constraint can be.


That seems well worth documenting...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] EXLCUDE constraints and Hash indexes

[Andrew Gierth]

> "Jeff" == Jeff Janes  writes:

 Jeff> From: https://www.postgresql.org/docs/9.4/static/sql-createtable.html

 Jeff> "The access method must support amgettuple (see Chapter 55); at
 Jeff> present this means GIN cannot be used. Although it's allowed, there is
 Jeff> little point in using B-tree or hash indexes with an exclusion
 Jeff> constraint, because this does nothing that an ordinary unique
 Jeff> constraint doesn't do better. So in practice the access method will
 Jeff> always be GiST or SP-GiST."

I also recently found a case where using btree exclusion constraints was
useful: a unique index on an expression can't be marked deferrable, but
the equivalent exclusion constraint can be.

-- 
Andrew (irc:RhodiumToad)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] EXLCUDE constraints and Hash indexes

[Jeff Janes]

From: https://www.postgresql.org/docs/9.4/static/sql-createtable.html

"The access method must support amgettuple (see Chapter 55); at
present this means GIN cannot be used. 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."

This is misleading.  Hash indexes do not support unique constraints
directly, but do support them via the EXCLUDE syntax using "WITH =".
This is nice if you want a unique index on something that might
occasionally exceed 1/3 of 8kB (titin, I'm looking at you)

Trivial doc patch attached.

Cheers,

Jeff
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
new file mode 100644
index bf2ad64..77d46de
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
*** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 574,583 
The access method must support amgettuple (see ); at present this means GIN
cannot be used.  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.
   
  
   
--- 574,583 
The access method must support amgettuple (see ); at present this means GIN
cannot be used.  Although it's allowed, there is little point in using
!   B-tree 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,
!   SP-GiST, or hash.
   
  
   

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers