Hi,
Here's a couple cleaned-up patches fixing the various discussed here.
I've tried to always add a regression test demonstrating the issue
first, and then fix it in the next patch.
In particular, this deals with these issues:
1) overflows in distance calculation for large timestamp values (0002)
2) incorrect subtraction in distance for date values (0003)
3) incorrect distance for infinite date/timestamp values (0005)
4) failing distance for extreme interval values (0007)
All the problems except "2" have been discussed earlier, but this seems
a bit more serious than the other issues, as it's easier to hit. It
subtracts the values in the opposite order (smaller - larger), so the
distances are negated. Which means we actually merge the values from the
most distant ones, and thus are "guaranteed" to build very a very
inefficient summary. People with multi-minmax indexes on "date" columns
probably will need to reindex.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From e23ca4e53d352e05951fb314dea347682794c25b Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 18:18:53 +0200
Subject: [PATCH 1/8] Tests for overflows with dates and timestamps in BRIN
When calculating distances for date and timestamp values for BRIN
minmax-multi indexes, we need to be careful about overflows for extreme
values. In that case the distance is negative, resulting in building of
inefficient summaries.
The new regression tests check this for date and timestamp data types.
It adds tables with data close to the allowed min/max values, and builds
a minmax-multi index on it.
---
src/test/regress/expected/brin_multi.out | 61 ++++++++++++++++++++++
src/test/regress/sql/brin_multi.sql | 65 ++++++++++++++++++++++++
2 files changed, 126 insertions(+)
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index 9f46934c9be..d5bd600f8fd 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -823,3 +823,64 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525
DROP TABLE brin_test_multi_2;
RESET enable_seqscan;
+-- test overflows during CREATE INDEX with extreme timestamp values
+CREATE TABLE brin_timestamp_test(a TIMESTAMPTZ);
+SET datestyle TO iso;
+INSERT INTO brin_timestamp_test VALUES
+('4713-01-01 00:00:01 BC'), ('4713-01-01 00:00:02 BC'), ('4713-01-01 00:00:03 BC'),
+('4713-01-01 00:00:04 BC'), ('4713-01-01 00:00:05 BC'), ('4713-01-01 00:00:06 BC'),
+('4713-01-01 00:00:07 BC'), ('4713-01-01 00:00:08 BC'), ('4713-01-01 00:00:09 BC'),
+('4713-01-01 00:00:10 BC'), ('4713-01-01 00:00:11 BC'), ('4713-01-01 00:00:12 BC'),
+('4713-01-01 00:00:13 BC'), ('4713-01-01 00:00:14 BC'), ('4713-01-01 00:00:15 BC'),
+('4713-01-01 00:00:16 BC'), ('4713-01-01 00:00:17 BC'), ('4713-01-01 00:00:18 BC'),
+('4713-01-01 00:00:19 BC'), ('4713-01-01 00:00:20 BC'), ('4713-01-01 00:00:21 BC'),
+('4713-01-01 00:00:22 BC'), ('4713-01-01 00:00:23 BC'), ('4713-01-01 00:00:24 BC'),
+('4713-01-01 00:00:25 BC'), ('4713-01-01 00:00:26 BC'), ('4713-01-01 00:00:27 BC'),
+('4713-01-01 00:00:28 BC'), ('4713-01-01 00:00:29 BC'), ('4713-01-01 00:00:30 BC'),
+('294276-12-01 00:00:01'), ('294276-12-01 00:00:02'), ('294276-12-01 00:00:03'),
+('294276-12-01 00:00:04'), ('294276-12-01 00:00:05'), ('294276-12-01 00:00:06'),
+('294276-12-01 00:00:07'), ('294276-12-01 00:00:08'), ('294276-12-01 00:00:09'),
+('294276-12-01 00:00:10'), ('294276-12-01 00:00:11'), ('294276-12-01 00:00:12'),
+('294276-12-01 00:00:13'), ('294276-12-01 00:00:14'), ('294276-12-01 00:00:15'),
+('294276-12-01 00:00:16'), ('294276-12-01 00:00:17'), ('294276-12-01 00:00:18'),
+('294276-12-01 00:00:19'), ('294276-12-01 00:00:20'), ('294276-12-01 00:00:21'),
+('294276-12-01 00:00:22'), ('294276-12-01 00:00:23'), ('294276-12-01 00:00:24'),
+('294276-12-01 00:00:25'), ('294276-12-01 00:00:26'), ('294276-12-01 00:00:27'),
+('294276-12-01 00:00:28'), ('294276-12-01 00:00:29'), ('294276-12-01 00:00:30');
+CREATE INDEX ON brin_timestamp_test USING brin (a timestamptz_minmax_multi_ops) WITH (pages_per_range=1);
+DROP TABLE brin_timestamp_test;
+-- test overflows during CREATE INDEX with extreme date values
+CREATE TABLE brin_date_test(a DATE);
+INSERT INTO brin_date_test VALUES
+('4713-01-01 BC'), ('4713-01-02 BC'), ('4713-01-03 BC'), ('4713-01-04 BC'),
+('4713-01-05 BC'), ('4713-01-06 BC'), ('4713-01-07 BC'), ('4713-01-08 BC'),
+('4713-01-09 BC'), ('4713-01-10 BC'), ('4713-01-11 BC'), ('4713-01-12 BC'),
+('4713-01-13 BC'), ('4713-01-14 BC'), ('4713-01-15 BC'), ('4713-01-16 BC'),
+('4713-01-17 BC'), ('4713-01-18 BC'), ('4713-01-19 BC'), ('4713-01-20 BC'),
+('4713-01-21 BC'), ('4713-01-22 BC'), ('4713-01-23 BC'), ('4713-01-24 BC'),
+('4713-01-25 BC'), ('4713-01-26 BC'), ('4713-01-27 BC'), ('4713-01-28 BC'),
+('4713-01-29 BC'), ('4713-01-30 BC'), ('4713-01-31 BC'),
+('5874897-12-01'), ('5874897-12-02'), ('5874897-12-03'), ('5874897-12-04'),
+('5874897-12-05'), ('5874897-12-06'), ('5874897-12-07'), ('5874897-12-08'),
+('5874897-12-09'), ('5874897-12-10'), ('5874897-12-11'), ('5874897-12-12'),
+('5874897-12-13'), ('5874897-12-14'), ('5874897-12-15'), ('5874897-12-16'),
+('5874897-12-17'), ('5874897-12-18'), ('5874897-12-19'), ('5874897-12-20'),
+('5874897-12-21'), ('5874897-12-22'), ('5874897-12-23'), ('5874897-12-24'),
+('5874897-12-25'), ('5874897-12-26'), ('5874897-12-27'), ('5874897-12-28'),
+('5874897-12-29'), ('5874897-12-30'), ('5874897-12-31');
+CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+-- make sure the ranges were built correctly and 2023-01-01 eliminates all
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '2023-01-01'::date)
+ -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '2023-01-01'::date)
+(4 rows)
+
+DROP TABLE brin_date_test;
+RESET enable_seqscan;
+RESET datestyle;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index d50dbdee682..63d35eacd2d 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -586,3 +586,68 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525
DROP TABLE brin_test_multi_2;
RESET enable_seqscan;
+
+-- test overflows during CREATE INDEX with extreme timestamp values
+CREATE TABLE brin_timestamp_test(a TIMESTAMPTZ);
+
+SET datestyle TO iso;
+
+INSERT INTO brin_timestamp_test VALUES
+('4713-01-01 00:00:01 BC'), ('4713-01-01 00:00:02 BC'), ('4713-01-01 00:00:03 BC'),
+('4713-01-01 00:00:04 BC'), ('4713-01-01 00:00:05 BC'), ('4713-01-01 00:00:06 BC'),
+('4713-01-01 00:00:07 BC'), ('4713-01-01 00:00:08 BC'), ('4713-01-01 00:00:09 BC'),
+('4713-01-01 00:00:10 BC'), ('4713-01-01 00:00:11 BC'), ('4713-01-01 00:00:12 BC'),
+('4713-01-01 00:00:13 BC'), ('4713-01-01 00:00:14 BC'), ('4713-01-01 00:00:15 BC'),
+('4713-01-01 00:00:16 BC'), ('4713-01-01 00:00:17 BC'), ('4713-01-01 00:00:18 BC'),
+('4713-01-01 00:00:19 BC'), ('4713-01-01 00:00:20 BC'), ('4713-01-01 00:00:21 BC'),
+('4713-01-01 00:00:22 BC'), ('4713-01-01 00:00:23 BC'), ('4713-01-01 00:00:24 BC'),
+('4713-01-01 00:00:25 BC'), ('4713-01-01 00:00:26 BC'), ('4713-01-01 00:00:27 BC'),
+('4713-01-01 00:00:28 BC'), ('4713-01-01 00:00:29 BC'), ('4713-01-01 00:00:30 BC'),
+
+('294276-12-01 00:00:01'), ('294276-12-01 00:00:02'), ('294276-12-01 00:00:03'),
+('294276-12-01 00:00:04'), ('294276-12-01 00:00:05'), ('294276-12-01 00:00:06'),
+('294276-12-01 00:00:07'), ('294276-12-01 00:00:08'), ('294276-12-01 00:00:09'),
+('294276-12-01 00:00:10'), ('294276-12-01 00:00:11'), ('294276-12-01 00:00:12'),
+('294276-12-01 00:00:13'), ('294276-12-01 00:00:14'), ('294276-12-01 00:00:15'),
+('294276-12-01 00:00:16'), ('294276-12-01 00:00:17'), ('294276-12-01 00:00:18'),
+('294276-12-01 00:00:19'), ('294276-12-01 00:00:20'), ('294276-12-01 00:00:21'),
+('294276-12-01 00:00:22'), ('294276-12-01 00:00:23'), ('294276-12-01 00:00:24'),
+('294276-12-01 00:00:25'), ('294276-12-01 00:00:26'), ('294276-12-01 00:00:27'),
+('294276-12-01 00:00:28'), ('294276-12-01 00:00:29'), ('294276-12-01 00:00:30');
+
+CREATE INDEX ON brin_timestamp_test USING brin (a timestamptz_minmax_multi_ops) WITH (pages_per_range=1);
+DROP TABLE brin_timestamp_test;
+
+-- test overflows during CREATE INDEX with extreme date values
+CREATE TABLE brin_date_test(a DATE);
+
+INSERT INTO brin_date_test VALUES
+('4713-01-01 BC'), ('4713-01-02 BC'), ('4713-01-03 BC'), ('4713-01-04 BC'),
+('4713-01-05 BC'), ('4713-01-06 BC'), ('4713-01-07 BC'), ('4713-01-08 BC'),
+('4713-01-09 BC'), ('4713-01-10 BC'), ('4713-01-11 BC'), ('4713-01-12 BC'),
+('4713-01-13 BC'), ('4713-01-14 BC'), ('4713-01-15 BC'), ('4713-01-16 BC'),
+('4713-01-17 BC'), ('4713-01-18 BC'), ('4713-01-19 BC'), ('4713-01-20 BC'),
+('4713-01-21 BC'), ('4713-01-22 BC'), ('4713-01-23 BC'), ('4713-01-24 BC'),
+('4713-01-25 BC'), ('4713-01-26 BC'), ('4713-01-27 BC'), ('4713-01-28 BC'),
+('4713-01-29 BC'), ('4713-01-30 BC'), ('4713-01-31 BC'),
+
+('5874897-12-01'), ('5874897-12-02'), ('5874897-12-03'), ('5874897-12-04'),
+('5874897-12-05'), ('5874897-12-06'), ('5874897-12-07'), ('5874897-12-08'),
+('5874897-12-09'), ('5874897-12-10'), ('5874897-12-11'), ('5874897-12-12'),
+('5874897-12-13'), ('5874897-12-14'), ('5874897-12-15'), ('5874897-12-16'),
+('5874897-12-17'), ('5874897-12-18'), ('5874897-12-19'), ('5874897-12-20'),
+('5874897-12-21'), ('5874897-12-22'), ('5874897-12-23'), ('5874897-12-24'),
+('5874897-12-25'), ('5874897-12-26'), ('5874897-12-27'), ('5874897-12-28'),
+('5874897-12-29'), ('5874897-12-30'), ('5874897-12-31');
+
+CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+-- make sure the ranges were built correctly and 2023-01-01 eliminates all
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+
+DROP TABLE brin_date_test;
+RESET enable_seqscan;
+RESET datestyle;
--
2.41.0
From 3b45932614c9b0d3afaeddd06d9f3f53a521ba32 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 18:20:45 +0200
Subject: [PATCH 2/8] Fix overflow in brin_minmax_multi_distance_timestamp
When calculating the distance between timestamp values, make sure to
convert the int64 values to double first. This prevents an overflow when
calculating the difference.
---
src/backend/access/brin/brin_minmax_multi.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f8b2a3f9bc6..8c72a0a0366 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2144,7 +2144,7 @@ brin_minmax_multi_distance_timestamp(PG_FUNCTION_ARGS)
if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
PG_RETURN_FLOAT8(0);
- delta = dt2 - dt1;
+ delta = (float8) dt2 - (float8) dt1;
Assert(delta >= 0);
--
2.41.0
From 48dc7638395512168c6beeeb38fa15037769540c Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 18:31:29 +0200
Subject: [PATCH 3/8] Fix calculation in brin_minmax_multi_distance_date
When calculating the distance between dates, make sure to subtract the
values in the right order. This can't overflow, because the min/max
values are not sufficiently far away.
---
src/backend/access/brin/brin_minmax_multi.c | 7 ++++++-
1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 8c72a0a0366..cadfb4481ef 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2081,13 +2081,18 @@ brin_minmax_multi_distance_uuid(PG_FUNCTION_ARGS)
Datum
brin_minmax_multi_distance_date(PG_FUNCTION_ARGS)
{
+ float8 delta = 0;
DateADT dateVal1 = PG_GETARG_DATEADT(0);
DateADT dateVal2 = PG_GETARG_DATEADT(1);
if (DATE_NOT_FINITE(dateVal1) || DATE_NOT_FINITE(dateVal2))
PG_RETURN_FLOAT8(0);
- PG_RETURN_FLOAT8(dateVal1 - dateVal2);
+ delta = (dateVal2 - dateVal1);
+
+ Assert(delta >= 0);
+
+ PG_RETURN_FLOAT8(delta);
}
/*
--
2.41.0
From 7f608536adeaecc0817edfb81b85c91e09e543a9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 19:15:40 +0200
Subject: [PATCH 4/8] Add tests for inifite date/timestamp values
Make sure that infinite values in date/timestamp columns are treated as
if in infinite distance. This means the values should not be merged with
any other values, leaving them as outliers. The test queries a value in
the "gap" and checks the range was eliminated by the BRIN index.
---
src/test/regress/expected/brin_multi.out | 72 ++++++++++++++++++++++++
src/test/regress/sql/brin_multi.sql | 54 ++++++++++++++++++
2 files changed, 126 insertions(+)
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index d5bd600f8fd..fad0c536b9a 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -881,6 +881,78 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
Index Cond: (a = '2023-01-01'::date)
(4 rows)
+DROP TABLE brin_date_test;
+RESET enable_seqscan;
+-- test handling of infinite timestamp values
+CREATE TABLE brin_timestamp_test(a TIMESTAMP);
+INSERT INTO brin_timestamp_test VALUES
+ ('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
+ -> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
+ -> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
+(4 rows)
+
+DROP TABLE brin_timestamp_test;
+RESET enable_seqscan;
+-- test handling of infinite date values
+CREATE TABLE brin_date_test(a DATE);
+INSERT INTO brin_date_test VALUES
+('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '2023-01-01'::date)
+ -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '2023-01-01'::date)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '1900-01-01'::date)
+ -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '1900-01-01'::date)
+(4 rows)
+
DROP TABLE brin_date_test;
RESET enable_seqscan;
RESET datestyle;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 63d35eacd2d..25f939325f9 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -648,6 +648,60 @@ SET enable_seqscan = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+DROP TABLE brin_date_test;
+RESET enable_seqscan;
+
+-- test handling of infinite timestamp values
+CREATE TABLE brin_timestamp_test(a TIMESTAMP);
+
+INSERT INTO brin_timestamp_test VALUES
+ ('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+
+CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
+
+DROP TABLE brin_timestamp_test;
+RESET enable_seqscan;
+
+-- test handling of infinite date values
+CREATE TABLE brin_date_test(a DATE);
+
+INSERT INTO brin_date_test VALUES
+('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+
+CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
+
DROP TABLE brin_date_test;
RESET enable_seqscan;
RESET datestyle;
--
2.41.0
From 1a8109457a631e5da94182189d60e7f826387ce4 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 19:19:38 +0200
Subject: [PATCH 5/8] Fix handling of infinity date/timestamp values
We don't need explicit handling of infinite date/timestamp values when
calculating distances, because those values are represented as extreme
but regular values (e.g. INT64_MIN/MAX for the timestamp type).
We don't need an exact distance, just a value that is much larger than
distanced between regular values. With the added cast to double values,
we can simply calculate the "regular" distance.
---
src/backend/access/brin/brin_minmax_multi.c | 8 +-------
1 file changed, 1 insertion(+), 7 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index cadfb4481ef..37706e5bf28 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2085,10 +2085,7 @@ brin_minmax_multi_distance_date(PG_FUNCTION_ARGS)
DateADT dateVal1 = PG_GETARG_DATEADT(0);
DateADT dateVal2 = PG_GETARG_DATEADT(1);
- if (DATE_NOT_FINITE(dateVal1) || DATE_NOT_FINITE(dateVal2))
- PG_RETURN_FLOAT8(0);
-
- delta = (dateVal2 - dateVal1);
+ delta = (float8) dateVal2 - (float8) dateVal1;
Assert(delta >= 0);
@@ -2146,9 +2143,6 @@ brin_minmax_multi_distance_timestamp(PG_FUNCTION_ARGS)
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
- PG_RETURN_FLOAT8(0);
-
delta = (float8) dt2 - (float8) dt1;
Assert(delta >= 0);
--
2.41.0
From aefd39fcb7be90aee8d55c23d71730e27ecf66b3 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 20:32:33 +0200
Subject: [PATCH 6/8] Add test for BRIN minmax-multi with extreme interval
values
Tries to build index on interval columns with extreme values, to make
sure the distance calculation works.
---
src/test/regress/expected/brin_multi.out | 21 +++++++++++++++++++++
src/test/regress/sql/brin_multi.sql | 24 ++++++++++++++++++++++++
2 files changed, 45 insertions(+)
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index fad0c536b9a..e07042cd8a1 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -956,3 +956,24 @@ SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
DROP TABLE brin_date_test;
RESET enable_seqscan;
RESET datestyle;
+-- test handling of overflow for interval values
+CREATE TABLE brin_interval_test(a INTERVAL);
+INSERT INTO brin_interval_test VALUES
+('-177999985 years'), ('177999985 years'),
+('-177999986 years'), ('177999986 years'),
+('-177999987 years'), ('177999987 years'),
+('-177999988 years'), ('177999988 years'),
+('-177999989 years'), ('177999989 years'),
+('-177999990 years'), ('177999990 years'),
+('-177999991 years'), ('177999991 years'),
+('-177999992 years'), ('177999992 years'),
+('-177999993 years'), ('177999993 years'),
+('-177999994 years'), ('177999994 years'),
+('-177999995 years'), ('177999995 years'),
+('-177999996 years'), ('177999996 years'),
+('-177999997 years'), ('177999997 years'),
+('-177999998 years'), ('177999998 years'),
+('-177999999 years'), ('177999999 years'),
+('-178000000 years'), ('178000000 years');
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+DROP TABLE brin_interval_test;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 25f939325f9..302b41e7fd0 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -705,3 +705,27 @@ SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
DROP TABLE brin_date_test;
RESET enable_seqscan;
RESET datestyle;
+
+-- test handling of overflow for interval values
+CREATE TABLE brin_interval_test(a INTERVAL);
+
+INSERT INTO brin_interval_test VALUES
+('-177999985 years'), ('177999985 years'),
+('-177999986 years'), ('177999986 years'),
+('-177999987 years'), ('177999987 years'),
+('-177999988 years'), ('177999988 years'),
+('-177999989 years'), ('177999989 years'),
+('-177999990 years'), ('177999990 years'),
+('-177999991 years'), ('177999991 years'),
+('-177999992 years'), ('177999992 years'),
+('-177999993 years'), ('177999993 years'),
+('-177999994 years'), ('177999994 years'),
+('-177999995 years'), ('177999995 years'),
+('-177999996 years'), ('177999996 years'),
+('-177999997 years'), ('177999997 years'),
+('-177999998 years'), ('177999998 years'),
+('-177999999 years'), ('177999999 years'),
+('-178000000 years'), ('178000000 years');
+
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+DROP TABLE brin_interval_test;
--
2.41.0
From 40566b81ff8d34f9a400d9b426b0301e8fbd577f Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 20:38:01 +0200
Subject: [PATCH 7/8] Fix distance calculation for extreme interval values
Make sure we can calculate distance even for extreme interval values,
which for extreme values was triggering 'interval out of range' errors.
Instead of building a new interval, calculate the distance directly.
---
src/backend/access/brin/brin_minmax_multi.c | 33 +++------------------
1 file changed, 4 insertions(+), 29 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 37706e5bf28..9811451b542 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2160,45 +2160,20 @@ brin_minmax_multi_distance_interval(PG_FUNCTION_ARGS)
Interval *ia = PG_GETARG_INTERVAL_P(0);
Interval *ib = PG_GETARG_INTERVAL_P(1);
- Interval *result;
int64 dayfraction;
int64 days;
- result = (Interval *) palloc(sizeof(Interval));
-
- result->month = ib->month - ia->month;
- /* overflow check copied from int4mi */
- if (!SAMESIGN(ib->month, ia->month) &&
- !SAMESIGN(result->month, ib->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
-
- result->day = ib->day - ia->day;
- if (!SAMESIGN(ib->day, ia->day) &&
- !SAMESIGN(result->day, ib->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
-
- result->time = ib->time - ia->time;
- if (!SAMESIGN(ib->time, ia->time) &&
- !SAMESIGN(result->time, ib->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
-
/*
* Delta is (fractional) number of days between the intervals. Assume
* months have 30 days for consistency with interval_cmp_internal. We
* don't need to be exact, in the worst case we'll build a bit less
* efficient ranges. But we should not contradict interval_cmp.
*/
- dayfraction = result->time % USECS_PER_DAY;
- days = result->time / USECS_PER_DAY;
- days += result->month * INT64CONST(30);
- days += result->day;
+ dayfraction = (ib->time % USECS_PER_DAY) - (ia->time % USECS_PER_DAY);
+ days = (ib->time / USECS_PER_DAY) - (ia->time / USECS_PER_DAY);
+ days += (int64) ib->day - (int64) ia->day;
+ days += ((int64) ib->month - (int64) ia->month) * INT64CONST(30);
/* convert to double precision */
delta = (double) days + dayfraction / (double) USECS_PER_DAY;
--
2.41.0
From 401bed518f89454eac4bf0a8b871ccae57cd53f9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Tue, 17 Oct 2023 20:49:01 +0200
Subject: [PATCH 8/8] Add more tests for BRIN on interval values
Make sure we don't build inefficient ranges on large interval values.
---
src/test/regress/expected/brin_multi.out | 49 ++++++++++++++++++++++++
src/test/regress/sql/brin_multi.sql | 40 +++++++++++++++++++
2 files changed, 89 insertions(+)
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index e07042cd8a1..d361b344d42 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -977,3 +977,52 @@ INSERT INTO brin_interval_test VALUES
('-178000000 years'), ('178000000 years');
CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
DROP TABLE brin_interval_test;
+-- test handling of long intervals
+CREATE TABLE brin_interval_test(a INTERVAL);
+INSERT INTO brin_interval_test VALUES
+('-1 years'), ('1 years'),
+('-2 years'), ('2 years'),
+('-3 years'), ('3 years'),
+('-4 years'), ('4 years'),
+('-5 years'), ('5 years'),
+('-6 years'), ('6 years'),
+('-7 years'), ('7 years'),
+('-8 years'), ('8 years'),
+('-9 years'), ('9 years'),
+('-10 years'), ('10 years'),
+('-11 years'), ('11 years'),
+('-12 years'), ('12 years'),
+('-13 years'), ('13 years'),
+('-14 years'), ('14 years'),
+('-15 years'), ('15 years'),
+('-16 years'), ('16 years'),
+('-17 years'), ('17 years'),
+('-18 years'), ('18 years'),
+('-19 years'), ('19 years'),
+('-20 years'), ('20 years'),
+('-178000000 years'), ('178000000 years');
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '@ 30 years ago'::interval)
+ -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '@ 30 years ago'::interval)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '@ 30 years'::interval)
+ -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '@ 30 years'::interval)
+(4 rows)
+
+DROP TABLE brin_interval_test;
+RESET enable_seqscan;
+RESET datestyle;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 302b41e7fd0..e22ce0c138b 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -729,3 +729,43 @@ INSERT INTO brin_interval_test VALUES
CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
DROP TABLE brin_interval_test;
+
+-- test handling of long intervals
+CREATE TABLE brin_interval_test(a INTERVAL);
+
+INSERT INTO brin_interval_test VALUES
+('-1 years'), ('1 years'),
+('-2 years'), ('2 years'),
+('-3 years'), ('3 years'),
+('-4 years'), ('4 years'),
+('-5 years'), ('5 years'),
+('-6 years'), ('6 years'),
+('-7 years'), ('7 years'),
+('-8 years'), ('8 years'),
+('-9 years'), ('9 years'),
+('-10 years'), ('10 years'),
+('-11 years'), ('11 years'),
+('-12 years'), ('12 years'),
+('-13 years'), ('13 years'),
+('-14 years'), ('14 years'),
+('-15 years'), ('15 years'),
+('-16 years'), ('16 years'),
+('-17 years'), ('17 years'),
+('-18 years'), ('18 years'),
+('-19 years'), ('19 years'),
+('-20 years'), ('20 years'),
+('-178000000 years'), ('178000000 years');
+
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
+
+DROP TABLE brin_interval_test;
+RESET enable_seqscan;
+RESET datestyle;
--
2.41.0
