Re: [HACKERS] (9.1) btree_gist support for searching on not equals
On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2010-08-02 at 12:27 -0400, Robert Haas wrote: I was also wondering if it would be worth adding some additional regression testing to contrib/btree_gist exercising this new functionality. Thoughts? Sure. I attached two tests. Committed. I renamed the test to not_equals rather than mixed and added an EXPLAIN (COSTS OFF) in there to verify that the index is actually being used. (I might have to remove that if it turns out not to be stable between an index scan and a bitmap index scan, but let's see what the buildfarm says.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] (9.1) btree_gist support for searching on not equals
Robert Haas robertmh...@gmail.com writes: On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis pg...@j-davis.com wrote: Sure. I attached two tests. Committed. I see no sign of a commit from here ... regards, tom lane -- 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] (9.1) btree_gist support for searching on not equals
On Tue, Aug 3, 2010 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis pg...@j-davis.com wrote: Sure. I attached two tests. Committed. I see no sign of a commit from here ... Sigh. Forgot to exit my editor. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] (9.1) btree_gist support for searching on not equals
On Sun, 2010-08-01 at 21:57 -0400, Robert Haas wrote: On Fri, Jul 16, 2010 at 1:19 AM, Jeff Davis pg...@j-davis.com wrote: Thank you for the review. On Mon, 2010-07-12 at 17:17 +0900, Itagaki Takahiro wrote: (1) Exclusion constraints support for operators where x operator x is false (tiny patch) https://commitfest.postgresql.org/action/patch_view?id=307 (2) btree_gist support for searching on (not equals) https://commitfest.postgresql.org/action/patch_view?id=308 Those patches should be committed at once because (2) requires (1) to work with EXCLUDE constraints. Also, (1) has no benefits without (2) because we have no use cases for as an index-able operator. Both patches are very simple and small, and worked as expected both WHERE and EXCLUDE constraints cases. It appears that Tom already committed (1). I'd like to ask you to write additional documentation about btree_gist [1] that the module will be more useful when it is used with exclusion constraints together. Without documentation, no users find the usages. Good idea, new patch attached. It seems pretty odd to define a constant called BTNotEqualStrategyNumber in contrib/btree_gist. Shouldn't we either call this something else, or define it in access/skey.h? Considering that there seem to be some interesting gymnastics being done with BTMaxStrategyNumber, I'd vote for the former. Maybe just BtreeGistNotEqualStrategyNumber? Sounds good to me. At some point we may be interested to add this to BTree, as well. But we can cross that bridge when we come to it. Regards, Jeff Davis -- 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] (9.1) btree_gist support for searching on not equals
On Mon, Aug 2, 2010 at 2:39 AM, Jeff Davis pg...@j-davis.com wrote: On Sun, 2010-08-01 at 21:57 -0400, Robert Haas wrote: On Fri, Jul 16, 2010 at 1:19 AM, Jeff Davis pg...@j-davis.com wrote: Thank you for the review. On Mon, 2010-07-12 at 17:17 +0900, Itagaki Takahiro wrote: (1) Exclusion constraints support for operators where x operator x is false (tiny patch) https://commitfest.postgresql.org/action/patch_view?id=307 (2) btree_gist support for searching on (not equals) https://commitfest.postgresql.org/action/patch_view?id=308 Those patches should be committed at once because (2) requires (1) to work with EXCLUDE constraints. Also, (1) has no benefits without (2) because we have no use cases for as an index-able operator. Both patches are very simple and small, and worked as expected both WHERE and EXCLUDE constraints cases. It appears that Tom already committed (1). I'd like to ask you to write additional documentation about btree_gist [1] that the module will be more useful when it is used with exclusion constraints together. Without documentation, no users find the usages. Good idea, new patch attached. It seems pretty odd to define a constant called BTNotEqualStrategyNumber in contrib/btree_gist. Shouldn't we either call this something else, or define it in access/skey.h? Considering that there seem to be some interesting gymnastics being done with BTMaxStrategyNumber, I'd vote for the former. Maybe just BtreeGistNotEqualStrategyNumber? Sounds good to me. OK, committed that way. At some point we may be interested to add this to BTree, as well. But we can cross that bridge when we come to it. Yeah. I was also wondering if it would be worth adding some additional regression testing to contrib/btree_gist exercising this new functionality. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] (9.1) btree_gist support for searching on not equals
On Mon, 2010-08-02 at 12:27 -0400, Robert Haas wrote:
I was also wondering if it would be worth adding some additional
regression testing to contrib/btree_gist exercising this new
functionality. Thoughts?
Sure. I attached two tests.
Regards,
Jeff Davis
*** a/contrib/btree_gist/Makefile
--- b/contrib/btree_gist/Makefile
***
*** 11,17 DATA_built = btree_gist.sql
DATA= uninstall_btree_gist.sql
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz time timetz \
! date interval macaddr inet cidr text varchar char bytea bit varbit numeric
ifdef USE_PGXS
PG_CONFIG = pg_config
--- 11,17
DATA= uninstall_btree_gist.sql
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz time timetz \
! date interval macaddr inet cidr text varchar char bytea bit varbit numeric mixed
ifdef USE_PGXS
PG_CONFIG = pg_config
*** /dev/null
--- b/contrib/btree_gist/expected/mixed.out
***
*** 0
--- 1,31
+ SET enable_seqscan = 'false';
+ -- test search for not equals
+ CREATE TABLE test_ne (
+a TIMESTAMP,
+b NUMERIC
+ );
+ CREATE INDEX test_ne_idx ON test_ne USING gist (a, b);
+ INSERT INTO test_ne SELECT '2009-01-01', 10.7 FROM generate_series(1,1000);
+ INSERT INTO test_ne VALUES('2007-02-03', -91.3);
+ INSERT INTO test_ne VALUES('2011-09-01', 43.7);
+ INSERT INTO test_ne SELECT '2009-01-01', 10.7 FROM generate_series(1,1000);
+ SELECT * FROM test_ne WHERE a '2009-01-01' AND b 10.7;
+ a | b
+ --+---
+ Sat Feb 03 00:00:00 2007 | -91.3
+ Thu Sep 01 00:00:00 2011 | 43.7
+ (2 rows)
+
+ -- test search for not equals using an exclusion constraint
+ CREATE TABLE zoo (
+cage INTEGER,
+animal TEXT,
+EXCLUDE USING gist (cage WITH =, animal WITH )
+ );
+ NOTICE: CREATE TABLE / EXCLUDE will create implicit index zoo_cage_animal_excl for table zoo
+ INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT INTO zoo VALUES(123, 'zebra');
+ 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');
*** /dev/null
--- b/contrib/btree_gist/sql/mixed.sql
***
*** 0
--- 1,30
+
+ SET enable_seqscan = 'false';
+
+ -- test search for not equals
+
+ CREATE TABLE test_ne (
+a TIMESTAMP,
+b NUMERIC
+ );
+ CREATE INDEX test_ne_idx ON test_ne USING gist (a, b);
+
+ INSERT INTO test_ne SELECT '2009-01-01', 10.7 FROM generate_series(1,1000);
+ INSERT INTO test_ne VALUES('2007-02-03', -91.3);
+ INSERT INTO test_ne VALUES('2011-09-01', 43.7);
+ INSERT INTO test_ne SELECT '2009-01-01', 10.7 FROM generate_series(1,1000);
+
+ SELECT * FROM test_ne WHERE a '2009-01-01' AND b 10.7;
+
+ -- test search for not equals using an exclusion constraint
+
+ CREATE TABLE zoo (
+cage INTEGER,
+animal TEXT,
+EXCLUDE USING gist (cage WITH =, animal WITH )
+ );
+
+ INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT INTO zoo VALUES(123, 'lion');
+ INSERT INTO zoo VALUES(124, 'lion');
--
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] (9.1) btree_gist support for searching on not equals
On Fri, Jul 16, 2010 at 1:19 AM, Jeff Davis pg...@j-davis.com wrote: Thank you for the review. On Mon, 2010-07-12 at 17:17 +0900, Itagaki Takahiro wrote: (1) Exclusion constraints support for operators where x operator x is false (tiny patch) https://commitfest.postgresql.org/action/patch_view?id=307 (2) btree_gist support for searching on (not equals) https://commitfest.postgresql.org/action/patch_view?id=308 Those patches should be committed at once because (2) requires (1) to work with EXCLUDE constraints. Also, (1) has no benefits without (2) because we have no use cases for as an index-able operator. Both patches are very simple and small, and worked as expected both WHERE and EXCLUDE constraints cases. It appears that Tom already committed (1). I'd like to ask you to write additional documentation about btree_gist [1] that the module will be more useful when it is used with exclusion constraints together. Without documentation, no users find the usages. Good idea, new patch attached. It seems pretty odd to define a constant called BTNotEqualStrategyNumber in contrib/btree_gist. Shouldn't we either call this something else, or define it in access/skey.h? Considering that there seem to be some interesting gymnastics being done with BTMaxStrategyNumber, I'd vote for the former. Maybe just BtreeGistNotEqualStrategyNumber? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] (9.1) btree_gist support for searching on not equals
2010/7/16 Jeff Davis pg...@j-davis.com: I'd like to ask you to write additional documentation about btree_gist [1] that the module will be more useful when it is used with exclusion constraints together. Without documentation, no users find the usages. | Example using an Exclusion Constraint to enforce the constraint | that a cage at a zoo can contain only one kind of animal: Very interesting example :-) The patch will be applied immediately. -- Itagaki Takahiro -- 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] (9.1) btree_gist support for searching on not equals
Hi, Thank you for the review. On Mon, 2010-07-12 at 17:17 +0900, Itagaki Takahiro wrote: (1) Exclusion constraints support for operators where x operator x is false (tiny patch) https://commitfest.postgresql.org/action/patch_view?id=307 (2) btree_gist support for searching on (not equals) https://commitfest.postgresql.org/action/patch_view?id=308 Those patches should be committed at once because (2) requires (1) to work with EXCLUDE constraints. Also, (1) has no benefits without (2) because we have no use cases for as an index-able operator. Both patches are very simple and small, and worked as expected both WHERE and EXCLUDE constraints cases. It appears that Tom already committed (1). I'd like to ask you to write additional documentation about btree_gist [1] that the module will be more useful when it is used with exclusion constraints together. Without documentation, no users find the usages. Good idea, new patch attached. Regards, Jeff Davis *** 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
Re: [HACKERS] (9.1) btree_gist support for searching on not equals
(1) Exclusion constraints support for operators where x operator x is false (tiny patch) https://commitfest.postgresql.org/action/patch_view?id=307 (2) btree_gist support for searching on (not equals) https://commitfest.postgresql.org/action/patch_view?id=308 Those patches should be committed at once because (2) requires (1) to work with EXCLUDE constraints. Also, (1) has no benefits without (2) because we have no use cases for as an index-able operator. Both patches are very simple and small, and worked as expected both WHERE and EXCLUDE constraints cases. I'd like to ask you to write additional documentation about btree_gist [1] that the module will be more useful when it is used with exclusion constraints together. Without documentation, no users find the usages. Of course the docs can be postponed if you have a plan to write docs when PERIOD types are introduced, [1] http://developer.postgresql.org/pgdocs/postgres/btree-gist.html The patch was not applied to 9.0, but the reason was just no time to test [2]. We have enough time to test for 9.1, so we can apply it now! [2] http://archives.postgresql.org/pgsql-hackers/2010-05/msg01874.php -- Itagaki Takahiro -- 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] (9.1) btree_gist support for searching on not equals
Marko Tiikkaja marko.tiikk...@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 -- 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] (9.1) btree_gist support for searching on not equals
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 -- 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] (9.1) btree_gist support for searching on not equals
On Mon, May 24, 2010 at 11:25 PM, Jeff Davis pg...@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 -- 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] (9.1) btree_gist support for searching on not equals
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
