Horton
Menu

btree-gist-ne-20100715.patch

text/x-patch

Download
Filename: btree-gist-ne-20100715.patch
Type: text/x-patch
Part: 0
Message: Re: (9.1) btree_gist support for searching on "not equals"

Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: context
File+−
contrib/btree_gist/btree_gist.h 2 0
contrib/btree_gist/btree_gist.sql.in 22 0
contrib/btree_gist/btree_utils_num.c 3 0
contrib/btree_gist/btree_utils_var.c 3 0
doc/src/sgml/btree-gist.sgml 36 0 
*** 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;
  	}
*** a/doc/src/sgml/btree-gist.sgml
--- b/doc/src/sgml/btree-gist.sgml
***************
*** 27,35 ****
--- 27,47 ----
    GiST operator classes.
   </para>
  
+  <para>
+   In addition to the typical btree search operators, btree_gist also
+   provides search operators for <literal>&lt;&gt;</literal> ("not
+   equals"). This may be useful in combination with an
+   <link linkend="SQL-CREATETABLE-EXCLUDE">Exclusion Constraint</link>,
+   as descibed below.
+  </para>
+ 
   <sect2>
    <title>Example usage</title>
  
+   <para>
+    Simple example using btree_gist instead of btree:
+   </para>
+ 
  <programlisting>
  CREATE TABLE test (a int4);
  -- create index
***************
*** 38,43 **** CREATE INDEX testidx ON test USING gist (a);
--- 50,79 ----
  SELECT * FROM test WHERE a &lt; 10;
  </programlisting>
  
+   <para>
+    Example using an <link linkend="SQL-CREATETABLE-EXCLUDE">Exclusion
+    Constraint</link> to enforce the constraint that a cage at a zoo
+    can contain only one kind of animal:
+   </para>
+ 
+ <programlisting>
+ => CREATE TABLE zoo (
+   cage   INTEGER,
+   animal TEXT,
+   EXCLUDE USING gist (cage WITH =, animal WITH <>)
+ );
+ 
+ => INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT 0 1
+ => INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT 0 1
+ => INSERT INTO zoo VALUES(123, 'lion');
+ ERROR:  conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
+ DETAIL:  Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
+ => INSERT INTO zoo VALUES(124, 'lion');
+ INSERT 0 1
+ </programlisting>
+ 
   </sect2>
  
   <sect2>