From e23ca4e53d352e05951fb314dea347682794c25b Mon Sep 17 00:00:00 2001 From: Tomas Vondra 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