diff options
| -rw-r--r-- | doc/src/sgml/filelist.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 73 | ||||
| -rw-r--r-- | src/backend/utils/adt/rangetypes.c | 31 | ||||
| -rw-r--r-- | src/test/regress/expected/rangetypes.out | 42 | ||||
| -rw-r--r-- | src/test/regress/expected/sanity_check.out | 4 | ||||
| -rw-r--r-- | src/test/regress/output/misc.source | 4 | ||||
| -rw-r--r-- | src/test/regress/sql/rangetypes.sql | 48 |
7 files changed, 101 insertions, 103 deletions
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index fb69415f800..7a698e59b3e 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -25,7 +25,7 @@ <!ENTITY mvcc SYSTEM "mvcc.sgml"> <!ENTITY perform SYSTEM "perform.sgml"> <!ENTITY queries SYSTEM "queries.sgml"> -<!entity rangetypes SYSTEM "rangetypes.sgml"> +<!ENTITY rangetypes SYSTEM "rangetypes.sgml"> <!ENTITY rowtypes SYSTEM "rowtypes.sgml"> <!ENTITY syntax SYSTEM "syntax.sgml"> <!ENTITY textsearch SYSTEM "textsearch.sgml"> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7ecf7510435..b9ef827984d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10461,6 +10461,10 @@ SELECT NULLIF(value, '(none)') ... <title>Range Functions and Operators</title> <para> + See <xref linkend="rangetypes"> for an overview of range types. + </para> + + <para> <xref linkend="range-operators-table"> shows the operators available for range types. </para> @@ -10556,65 +10560,74 @@ SELECT NULLIF(value, '(none)') ... <row> <entry> <literal>&<</literal> </entry> - <entry>Does not extend to the right of?</entry> + <entry>does not extend to the right of</entry> <entry><literal>int8range(1,20) &< int8range(18,20)</literal></entry> <entry><literal>t</literal></entry> </row> <row> <entry> <literal>&></literal> </entry> - <entry>Does not extend to the left of?</entry> + <entry>does not extend to the left of</entry> <entry><literal>int8range(7,20) &> int8range(5,10)</literal></entry> <entry><literal>t</literal></entry> </row> <row> <entry> <literal>-|-</literal> </entry> - <entry>adjacent?</entry> + <entry>is adjacent to</entry> <entry><literal>numrange(1.1,2.2) -|- numrange(2.2,3.3)</literal></entry> <entry><literal>t</literal></entry> </row> <row> <entry> <literal>+</literal> </entry> - <entry>Union</entry> + <entry>union</entry> <entry><literal>numrange(5,15) + numrange(10,20)</literal></entry> <entry><literal>[5,20)</literal></entry> </row> <row> - <entry> <literal>-</literal> </entry> - <entry>Difference</entry> - <entry><literal>int8range(5,15) - int8range(10,20)</literal></entry> - <entry><literal>[5,10)</literal></entry> - </row> - - <row> <entry> <literal>*</literal> </entry> - <entry>Intersection</entry> + <entry>intersection</entry> <entry><literal>int8range(5,15) * int8range(10,20)</literal></entry> <entry><literal>[10,15)</literal></entry> </row> + <row> + <entry> <literal>-</literal> </entry> + <entry>difference</entry> + <entry><literal>int8range(5,15) - int8range(10,20)</literal></entry> + <entry><literal>[5,10)</literal></entry> + </row> + </tbody> </tgroup> </table> <para> - Range comparisons compare the lower bounds first, and only if - equal, compare the upper bounds. This is generally most useful for - B-tree indexes, rather than being useful comparisons by themselves. + The simple comparison operators <literal><</literal>, + <literal>></literal>, <literal><=</literal>, and + <literal>>=</literal> compare the lower bounds first, and only if those + are equal, compare the upper bounds. These comparisons are not usually + very useful for ranges, but are provided to allow B-tree indexes to be + constructed on ranges. </para> <para> - See <xref linkend="rangetypes"> for more details about range operator - behavior. + The left-of/right-of/adjacent operators always return false when an empty + range is involved; that is, an empty range is not considered to be either + before or after any other range. + </para> + + <para> + The union and difference operators will fail if the resulting range would + need to contain two disjoint sub-ranges, as such a range cannot be + represented. </para> <para> <xref linkend="range-functions-table"> shows the functions - available for use with range types. See <xref linkend="rangetypes"> - for more information and examples of the use of these functions. + available for use with range types. </para> <indexterm> @@ -10658,7 +10671,7 @@ SELECT NULLIF(value, '(none)') ... <function>lower</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> + <entry>range's element type</entry> <entry>lower bound of range</entry> <entry><literal>lower(numrange(1.1,2.2))</literal></entry> <entry><literal>1.1</literal></entry> @@ -10669,7 +10682,7 @@ SELECT NULLIF(value, '(none)') ... <function>upper</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> + <entry>range's element type</entry> <entry>upper bound of range</entry> <entry><literal>upper(numrange(1.1,2.2))</literal></entry> <entry><literal>2.2</literal></entry> @@ -10680,7 +10693,7 @@ SELECT NULLIF(value, '(none)') ... <function>isempty</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> + <entry><type>boolean</type></entry> <entry>is the range empty?</entry> <entry><literal>isempty(numrange(1.1,2.2))</literal></entry> <entry><literal>false</literal></entry> @@ -10691,8 +10704,8 @@ SELECT NULLIF(value, '(none)') ... <function>lower_inc</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> - <entry>is the lower bound of the range inclusive?</entry> + <entry><type>boolean</type></entry> + <entry>is the lower bound inclusive?</entry> <entry><literal>lower_inc(numrange(1.1,2.2))</literal></entry> <entry><literal>true</literal></entry> </row> @@ -10702,8 +10715,8 @@ SELECT NULLIF(value, '(none)') ... <function>upper_inc</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> - <entry>is the upper bound of the range inclusive?</entry> + <entry><type>boolean</type></entry> + <entry>is the upper bound inclusive?</entry> <entry><literal>upper_inc(numrange(1.1,2.2))</literal></entry> <entry><literal>false</literal></entry> </row> @@ -10713,8 +10726,8 @@ SELECT NULLIF(value, '(none)') ... <function>lower_inf</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> - <entry>is the lower bound of the range infinite?</entry> + <entry><type>boolean</type></entry> + <entry>is the lower bound infinite?</entry> <entry><literal>lower_inf('(,)'::daterange)</literal></entry> <entry><literal>true</literal></entry> </row> @@ -10724,8 +10737,8 @@ SELECT NULLIF(value, '(none)') ... <function>upper_inf</function>(<type>anyrange</type>) </literal> </entry> - <entry><type>anyrange</type></entry> - <entry>is the upper bound of the range infinite?</entry> + <entry><type>boolean</type></entry> + <entry>is the upper bound infinite?</entry> <entry><literal>upper_inf('(,)'::daterange)</literal></entry> <entry><literal>true</literal></entry> </row> diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c index f773c31813a..5c16ca1b19e 100644 --- a/src/backend/utils/adt/rangetypes.c +++ b/src/backend/utils/adt/rangetypes.c @@ -701,15 +701,11 @@ range_before(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* An empty range is neither before nor after any other range */ if (empty1 || empty2) - ereport(ERROR, - (errcode(ERRCODE_DATA_EXCEPTION), - errmsg("input range is empty"))); - - if (range_cmp_bounds(fcinfo, &upper1, &lower2) < 0) - PG_RETURN_BOOL(true); - else PG_RETURN_BOOL(false); + + PG_RETURN_BOOL(range_cmp_bounds(fcinfo, &upper1, &lower2) < 0); } Datum @@ -732,15 +728,11 @@ range_after(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* An empty range is neither before nor after any other range */ if (empty1 || empty2) - ereport(ERROR, - (errcode(ERRCODE_DATA_EXCEPTION), - errmsg("input range is empty"))); - - if (range_cmp_bounds(fcinfo, &lower1, &upper2) > 0) - PG_RETURN_BOOL(true); - else PG_RETURN_BOOL(false); + + PG_RETURN_BOOL(range_cmp_bounds(fcinfo, &lower1, &upper2) > 0); } Datum @@ -764,10 +756,9 @@ range_adjacent(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* An empty range is not adjacent to any other range */ if (empty1 || empty2) - ereport(ERROR, - (errcode(ERRCODE_DATA_EXCEPTION), - errmsg("undefined for empty ranges"))); + PG_RETURN_BOOL(false); /* * For two ranges to be adjacent, the lower boundary of one range has to @@ -819,6 +810,7 @@ range_overlaps(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* An empty range does not overlap any other range */ if (empty1 || empty2) PG_RETURN_BOOL(false); @@ -853,6 +845,7 @@ range_overleft(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* An empty range is neither before nor after any other range */ if (empty1 || empty2) PG_RETURN_BOOL(false); @@ -882,6 +875,7 @@ range_overright(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* An empty range is neither before nor after any other range */ if (empty1 || empty2) PG_RETURN_BOOL(false); @@ -917,6 +911,7 @@ range_minus(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* if either is empty, r1 is the correct answer */ if (empty1 || empty2) PG_RETURN_RANGE(r1); @@ -971,6 +966,7 @@ range_union(PG_FUNCTION_ARGS) range_deserialize(fcinfo, r1, &lower1, &upper1, &empty1); range_deserialize(fcinfo, r2, &lower2, &upper2, &empty2); + /* if either is empty, the other is the correct answer */ if (empty1) PG_RETURN_RANGE(r2); if (empty2) @@ -1051,6 +1047,7 @@ range_cmp(PG_FUNCTION_ARGS) lower1.rngtypid != upper2.rngtypid) elog(ERROR, "range types do not match"); + /* For b-tree use, empty ranges sort before all else */ if (empty1 && empty2) PG_RETURN_INT32(0); else if (empty1) diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index 3514dece300..9b879486170 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -144,7 +144,6 @@ select '(!,[)'::textrange; (!,"[") (1 row) -drop type textrange; -- -- create some test data and test the operators -- @@ -529,6 +528,7 @@ select daterange('2000-01-10'::date, '2000-01-20'::date,'[]'); [01-10-2000,01-21-2000) (1 row) +-- test GiST index that's been built incrementally create table test_range_gist(ir int4range); create index test_range_gist_idx on test_range_gist using gist (ir); insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; @@ -538,10 +538,10 @@ insert into test_range_gist select 'empty'::int4range from generate_series(1,500 insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_series(1,100) g; insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g; insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; -BEGIN; -SET LOCAL enable_seqscan = t; -SET LOCAL enable_bitmapscan = f; -SET LOCAL enable_indexscan = f; +-- first, verify non-indexed results +SET enable_seqscan = t; +SET enable_indexscan = f; +SET enable_bitmapscan = f; select count(*) from test_range_gist where ir @> 'empty'::int4range; count ------- @@ -578,41 +578,40 @@ select count(*) from test_range_gist where ir <@ int4range(10,50); 1062 (1 row) -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir << int4range(100,500); +select count(*) from test_range_gist where ir << int4range(100,500); count ------- 189 (1 row) -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir >> int4range(100,500); +select count(*) from test_range_gist where ir >> int4range(100,500); count ------- 3554 (1 row) -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &< int4range(100,500); +select count(*) from test_range_gist where ir &< int4range(100,500); count ------- 1029 (1 row) -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &> int4range(100,500); +select count(*) from test_range_gist where ir &> int4range(100,500); count ------- 4794 (1 row) -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir -|- int4range(100,500); +select count(*) from test_range_gist where ir -|- int4range(100,500); count ------- 5 (1 row) -COMMIT; -BEGIN; -SET LOCAL enable_seqscan = f; -SET LOCAL enable_bitmapscan = f; -SET LOCAL enable_indexscan = t; +-- now check same queries using index +SET enable_seqscan = f; +SET enable_indexscan = t; +SET enable_bitmapscan = f; select count(*) from test_range_gist where ir @> 'empty'::int4range; count ------- @@ -679,13 +678,9 @@ select count(*) from test_range_gist where ir -|- int4range(100,500); 5 (1 row) -COMMIT; +-- now check same queries using a bulk-loaded index drop index test_range_gist_idx; create index test_range_gist_idx on test_range_gist using gist (ir); -BEGIN; -SET LOCAL enable_seqscan = f; -SET LOCAL enable_bitmapscan = f; -SET LOCAL enable_indexscan = t; select count(*) from test_range_gist where ir @> 'empty'::int4range; count ------- @@ -752,8 +747,9 @@ select count(*) from test_range_gist where ir -|- int4range(100,500); 5 (1 row) -COMMIT; -drop table test_range_gist; +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; -- -- Btree_gist is not included by default, so to test exclusion -- constraints with range types, use singleton int ranges for the "=" @@ -782,7 +778,6 @@ insert into test_range_excl values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:10)'); ERROR: conflicting key value violates exclusion constraint "test_range_excl_speaker_during_excl" DETAIL: Key (speaker, during)=([1,2), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:10:00 2010")) conflicts with existing key (speaker, during)=([1,2), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")). -drop table test_range_excl; -- test bigint ranges select int8range(10000000000::int8, 20000000000::int8,'(]'); int8range @@ -924,7 +919,6 @@ select arrayrange(ARRAY[1,2], ARRAY[2,1]); ["{1,2}","{2,1}") (1 row) -drop type arrayrange; -- -- OUT/INOUT/TABLE functions -- diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 38c88d977ad..cb468e58b91 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -151,6 +151,8 @@ SELECT relname, relhasindex student | f tenk1 | t tenk2 | t + test_range_excl | t + test_range_gist | t test_tsvector | f text_tbl | f time_tbl | f @@ -159,7 +161,7 @@ SELECT relname, relhasindex timetz_tbl | f tinterval_tbl | f varchar_tbl | f -(148 rows) +(150 rows) -- -- another sanity check: every system catalog that has OIDs should have diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source index 34bde31b9f1..45bc926407d 100644 --- a/src/test/regress/output/misc.source +++ b/src/test/regress/output/misc.source @@ -670,6 +670,8 @@ SELECT user_relns() AS user_relns subselect_tbl tenk1 tenk2 + test_range_excl + test_range_gist test_tsvector text_tbl time_tbl @@ -680,7 +682,7 @@ SELECT user_relns() AS user_relns toyemp varchar_tbl xacttest -(102 rows) +(104 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); name diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index 44885c870cf..573e85ebb25 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -35,8 +35,6 @@ select '([,z)'::textrange; select '(!,()'::textrange; select '(!,[)'::textrange; -drop type textrange; - -- -- create some test data and test the operators -- @@ -148,6 +146,7 @@ select daterange('2000-01-10'::date, '2000-01-20'::date,'[)'); select daterange('2000-01-10'::date, '2000-01-20'::date,'(]'); select daterange('2000-01-10'::date, '2000-01-20'::date,'[]'); +-- test GiST index that's been built incrementally create table test_range_gist(ir int4range); create index test_range_gist_idx on test_range_gist using gist (ir); @@ -159,10 +158,10 @@ insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_serie insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g; insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; -BEGIN; -SET LOCAL enable_seqscan = t; -SET LOCAL enable_bitmapscan = f; -SET LOCAL enable_indexscan = f; +-- first, verify non-indexed results +SET enable_seqscan = t; +SET enable_indexscan = f; +SET enable_bitmapscan = f; select count(*) from test_range_gist where ir @> 'empty'::int4range; select count(*) from test_range_gist where ir = int4range(10,20); @@ -170,17 +169,16 @@ select count(*) from test_range_gist where ir @> 10; select count(*) from test_range_gist where ir @> int4range(10,20); select count(*) from test_range_gist where ir && int4range(10,20); select count(*) from test_range_gist where ir <@ int4range(10,50); -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir << int4range(100,500); -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir >> int4range(100,500); -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &< int4range(100,500); -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &> int4range(100,500); -select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir -|- int4range(100,500); -COMMIT; - -BEGIN; -SET LOCAL enable_seqscan = f; -SET LOCAL enable_bitmapscan = f; -SET LOCAL enable_indexscan = t; +select count(*) from test_range_gist where ir << int4range(100,500); +select count(*) from test_range_gist where ir >> int4range(100,500); +select count(*) from test_range_gist where ir &< int4range(100,500); +select count(*) from test_range_gist where ir &> int4range(100,500); +select count(*) from test_range_gist where ir -|- int4range(100,500); + +-- now check same queries using index +SET enable_seqscan = f; +SET enable_indexscan = t; +SET enable_bitmapscan = f; select count(*) from test_range_gist where ir @> 'empty'::int4range; select count(*) from test_range_gist where ir = int4range(10,20); @@ -193,16 +191,11 @@ select count(*) from test_range_gist where ir >> int4range(100,500); select count(*) from test_range_gist where ir &< int4range(100,500); select count(*) from test_range_gist where ir &> int4range(100,500); select count(*) from test_range_gist where ir -|- int4range(100,500); -COMMIT; +-- now check same queries using a bulk-loaded index drop index test_range_gist_idx; create index test_range_gist_idx on test_range_gist using gist (ir); -BEGIN; -SET LOCAL enable_seqscan = f; -SET LOCAL enable_bitmapscan = f; -SET LOCAL enable_indexscan = t; - select count(*) from test_range_gist where ir @> 'empty'::int4range; select count(*) from test_range_gist where ir = int4range(10,20); select count(*) from test_range_gist where ir @> 10; @@ -214,9 +207,10 @@ select count(*) from test_range_gist where ir >> int4range(100,500); select count(*) from test_range_gist where ir &< int4range(100,500); select count(*) from test_range_gist where ir &> int4range(100,500); select count(*) from test_range_gist where ir -|- int4range(100,500); -COMMIT; -drop table test_range_gist; +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; -- -- Btree_gist is not included by default, so to test exclusion @@ -243,8 +237,6 @@ insert into test_range_excl insert into test_range_excl values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:10)'); -drop table test_range_excl; - -- test bigint ranges select int8range(10000000000::int8, 20000000000::int8,'(]'); -- test tstz ranges @@ -343,8 +335,6 @@ create type arrayrange as range (subtype=int4[]); select arrayrange(ARRAY[1,2], ARRAY[2,1]); -drop type arrayrange; - -- -- OUT/INOUT/TABLE functions -- |