(9.1) btree_gist support for searching on "not equals"
This patch adds support to btree_gist for searching on <> ("not
equals").
This allows an interesting use of exclusion constraints:
Say you have a table:
create table zoo
(
cage int,
animal text,
exclude using gist (cage with =, animal with <>)
);
That will permit you to add as many zebras as you want to a given cage,
and as many lions as you want to another cage, but will not allow you to
mix zebras and lions in the same cage.
It also allows you to enforce the constraint that only one tuple exists
in a table by doing something like:
create table a
(
i int,
exclude using gist (i with <>),
unique (i)
);
Regards,
Jeff Davis
Attachments:
btree-gist-ne.patchtext/x-patch; charset=UTF-8; name=btree-gist-ne.patchDownload
*** a/contrib/btree_gist/btree_gist.h
--- b/contrib/btree_gist/btree_gist.h
***************
*** 9,14 ****
--- 9,16 ----
#include "access/itup.h"
#include "access/nbtree.h"
+ #define BTNotEqualStrategyNumber 6
+
/* indexed types */
enum gbtree_type
*** a/contrib/btree_gist/btree_gist.sql.in
--- b/contrib/btree_gist/btree_gist.sql.in
***************
*** 143,148 **** AS
--- 143,149 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_oid_consistent (internal, oid, int2, oid, internal),
FUNCTION 2 gbt_oid_union (bytea, internal),
FUNCTION 3 gbt_oid_compress (internal),
***************
*** 200,205 **** AS
--- 201,207 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_int2_consistent (internal, int2, int2, oid, internal),
FUNCTION 2 gbt_int2_union (bytea, internal),
FUNCTION 3 gbt_int2_compress (internal),
***************
*** 256,261 **** AS
--- 258,264 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_int4_consistent (internal, int4, int2, oid, internal),
FUNCTION 2 gbt_int4_union (bytea, internal),
FUNCTION 3 gbt_int4_compress (internal),
***************
*** 312,317 **** AS
--- 315,321 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_int8_consistent (internal, int8, int2, oid, internal),
FUNCTION 2 gbt_int8_union (bytea, internal),
FUNCTION 3 gbt_int8_compress (internal),
***************
*** 369,374 **** AS
--- 373,379 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_float4_consistent (internal, float4, int2, oid, internal),
FUNCTION 2 gbt_float4_union (bytea, internal),
FUNCTION 3 gbt_float4_compress (internal),
***************
*** 428,433 **** AS
--- 433,439 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_float8_consistent (internal, float8, int2, oid, internal),
FUNCTION 2 gbt_float8_union (bytea, internal),
FUNCTION 3 gbt_float8_compress (internal),
***************
*** 495,500 **** AS
--- 501,507 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_ts_consistent (internal, timestamp, int2, oid, internal),
FUNCTION 2 gbt_ts_union (bytea, internal),
FUNCTION 3 gbt_ts_compress (internal),
***************
*** 514,519 **** AS
--- 521,527 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_tstz_consistent (internal, timestamptz, int2, oid, internal),
FUNCTION 2 gbt_ts_union (bytea, internal),
FUNCTION 3 gbt_tstz_compress (internal),
***************
*** 581,586 **** AS
--- 589,595 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_time_consistent (internal, time, int2, oid, internal),
FUNCTION 2 gbt_time_union (bytea, internal),
FUNCTION 3 gbt_time_compress (internal),
***************
*** 598,603 **** AS
--- 607,613 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_timetz_consistent (internal, timetz, int2, oid, internal),
FUNCTION 2 gbt_time_union (bytea, internal),
FUNCTION 3 gbt_timetz_compress (internal),
***************
*** 655,660 **** AS
--- 665,671 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_date_consistent (internal, date, int2, oid, internal),
FUNCTION 2 gbt_date_union (bytea, internal),
FUNCTION 3 gbt_date_compress (internal),
***************
*** 717,722 **** AS
--- 728,734 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_intv_consistent (internal, interval, int2, oid, internal),
FUNCTION 2 gbt_intv_union (bytea, internal),
FUNCTION 3 gbt_intv_compress (internal),
***************
*** 773,778 **** AS
--- 785,791 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_cash_consistent (internal, money, int2, oid, internal),
FUNCTION 2 gbt_cash_union (bytea, internal),
FUNCTION 3 gbt_cash_compress (internal),
***************
*** 829,834 **** AS
--- 842,848 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_macad_consistent (internal, macaddr, int2, oid, internal),
FUNCTION 2 gbt_macad_union (bytea, internal),
FUNCTION 3 gbt_macad_compress (internal),
***************
*** 897,902 **** AS
--- 911,917 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_text_consistent (internal, text, int2, oid, internal),
FUNCTION 2 gbt_text_union (bytea, internal),
FUNCTION 3 gbt_text_compress (internal),
***************
*** 916,921 **** AS
--- 931,937 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bpchar_consistent (internal, bpchar , int2, oid, internal),
FUNCTION 2 gbt_text_union (bytea, internal),
FUNCTION 3 gbt_bpchar_compress (internal),
***************
*** 973,978 **** AS
--- 989,995 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bytea_consistent (internal, bytea, int2, oid, internal),
FUNCTION 2 gbt_bytea_union (bytea, internal),
FUNCTION 3 gbt_bytea_compress (internal),
***************
*** 1030,1035 **** AS
--- 1047,1053 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_numeric_consistent (internal, numeric, int2, oid, internal),
FUNCTION 2 gbt_numeric_union (bytea, internal),
FUNCTION 3 gbt_numeric_compress (internal),
***************
*** 1085,1090 **** AS
--- 1103,1109 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bit_consistent (internal, bit, int2, oid, internal),
FUNCTION 2 gbt_bit_union (bytea, internal),
FUNCTION 3 gbt_bit_compress (internal),
***************
*** 1104,1109 **** AS
--- 1123,1129 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bit_consistent (internal, bit, int2, oid, internal),
FUNCTION 2 gbt_bit_union (bytea, internal),
FUNCTION 3 gbt_bit_compress (internal),
***************
*** 1162,1167 **** AS
--- 1182,1188 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_inet_consistent (internal, inet, int2, oid, internal),
FUNCTION 2 gbt_inet_union (bytea, internal),
FUNCTION 3 gbt_inet_compress (internal),
***************
*** 1180,1185 **** AS
--- 1201,1207 ----
OPERATOR 3 = (inet, inet) ,
OPERATOR 4 >= (inet, inet) ,
OPERATOR 5 > (inet, inet) ,
+ OPERATOR 6 <> (inet, inet) ,
FUNCTION 1 gbt_inet_consistent (internal, inet, int2, oid, internal),
FUNCTION 2 gbt_inet_union (bytea, internal),
FUNCTION 3 gbt_inet_compress (internal),
*** a/contrib/btree_gist/btree_utils_num.c
--- b/contrib/btree_gist/btree_utils_num.c
***************
*** 225,230 **** gbt_num_consistent(
--- 225,233 ----
case BTGreaterEqualStrategyNumber:
retval = (*tinfo->f_le) (query, key->upper);
break;
+ case BTNotEqualStrategyNumber:
+ retval = ! ((*tinfo->f_eq) (query, key->lower) && (*tinfo->f_eq) (query, key->upper));
+ break;
default:
retval = FALSE;
}
*** a/contrib/btree_gist/btree_utils_var.c
--- b/contrib/btree_gist/btree_utils_var.c
***************
*** 596,601 **** gbt_var_consistent(
--- 596,604 ----
retval = (*tinfo->f_cmp) ((bytea *) query, key->upper) <= 0
|| gbt_var_node_pf_match(key, query, tinfo);
break;
+ case BTNotEqualStrategyNumber:
+ retval = ! ((*tinfo->f_eq) (query, key->lower) && (*tinfo->f_eq) (query, key->upper));
+ break;
default:
retval = FALSE;
}
On 5/21/10 11:47 PM +0300, Jeff Davis wrote:
It also allows you to enforce the constraint that only one tuple exists
in a table by doing something like:create table a
(
i int,
exclude using gist (i with<>),
unique (i)
);
FWIW, this is achievable a lot more easily:
CREATE UNIQUE INDEX "a_single_row" ON a ((1));
Regards,
Marko Tiikkaja
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:
On 5/21/10 11:47 PM +0300, Jeff Davis wrote:
It also allows you to enforce the constraint that only one tuple exists
in a table by doing something like:create table a
(
i int,
exclude using gist (i with<>),
unique (i)
);
+1. I've not read the code, but it might be considerable that we can
abort index scans if we find a first index entry for "i". While we must
scan all candidates for "WHERE i <> ?", but we can abort for the constraint
case because we know existing values are all the same.
FWIW, this is achievable a lot more easily:
CREATE UNIQUE INDEX "a_single_row" ON a ((1));
The former exclusion constraint means "one same value for all rows",
but your alternative means "a_single_row", right?
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
On Sat, 2010-05-22 at 01:02 +0300, Marko Tiikkaja wrote:
On 5/21/10 11:47 PM +0300, Jeff Davis wrote:
It also allows you to enforce the constraint that only one tuple exists
in a table by doing something like:create table a
(
i int,
exclude using gist (i with<>),
unique (i)
);FWIW, this is achievable a lot more easily:
CREATE UNIQUE INDEX "a_single_row" ON a ((1));
Yes, you're right. Also, neither of us accounted for NULLs, so I suppose
a NOT NULL is necessary as well.
I think the original case (same values only) is potentially useful
enough that we should support it.
Regards,
Jeff Davis
On Mon, May 24, 2010 at 11:25 PM, Jeff Davis <pgsql@j-davis.com> wrote:
I think the original case (same values only) is potentially useful
enough that we should support it.
+1.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company