From: Peter Eisentraut <peter@eisentraut.org>
To: Tomas Vondra <tomas.vondra@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Improve BRIN minmax-multi opclass test coverage
Date: Thu, 23 Nov 2023 08:46:14 +0100
Message-ID: <98683303-b841-42a1-8ccc-9655b116ce24@eisentraut.org> (raw)
In-Reply-To: <a35eaf0f-b19c-7797-e296-7b7e2adc473e@eisentraut.org>
References: <E1qFsXm-001Uqd-Tv@gemulon.postgresql.org>
<a35eaf0f-b19c-7797-e296-7b7e2adc473e@eisentraut.org>
On 22.08.23 08:43, Peter Eisentraut wrote:
> These new tests introduced a few new calls of the md5() function. This
> should be avoided (see commit 208bf364a9). You can replace these easily
> with the fipshash() function instead, but I think you then also need to
> change some of the constants in the tests. Could you look at this again?
I have created a patch that updates the test accordingly. It replaces
the md5() function with fipshash() and adjusts the test values to
correspond to the values generated in the original tests.
Attachments:0001-Use-fipshash-in-brin_multi-test.patch (text/plain, 8.3K) download
From 8f33ef19f5e360ce013bf94c13b7a6ebf3be3ee9 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Thu, 23 Nov 2023 08:43:07 +0100
Subject: [PATCH] Use fipshash in brin_multi test
This fixes some md5() calls that snuck in in 0457109344 after we had
removed them all in 208bf364a9.
Discussion: https://www.postgresql.org/message-id/a35eaf0f-b19c-7797-e296-7b7e2adc473e@eisentraut.org
---
src/test/regress/expected/brin_multi.out | 32 ++++++++++++------------
src/test/regress/sql/brin_multi.sql | 28 ++++++++++-----------
2 files changed, 30 insertions(+), 30 deletions(-)
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.outindex 8f4c95b9e6..7df42865da 100644--- a/src/test/regress/expected/brin_multi.out+++ b/src/test/regress/expected/brin_multi.out@@ -740,40 +740,40 @@ RESET enable_seqscan;
-- do some inequality tests for varlena data types
CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10);
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=5);
SET enable_seqscan=off;
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';
count
-------
195
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';
count
-------
792
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f369cb89-fc62-7e66-8987-007d121ed1ea';
count
-------
961
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'aea92132-c4cb-eb26-3e6a-c2bf6c183b5d';
count
-------
- 272+ 273
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'cfcd2084-95d5-65ef-66e7-dff9f98764da';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '5feceb66-ffc8-6f38-d952-786c6d696c79';
count
-------
12
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525ffdc56';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '86e50149-6586-6131-2a9e-0b35558d84f6';
count
-------
13
@@ -784,38 +784,38 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525
-- approach of adding rows into existing ranges
TRUNCATE brin_test_multi_2;
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';
count
-------
195
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';
count
-------
792
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f369cb89-fc62-7e66-8987-007d121ed1ea';
count
-------
961
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'aea92132-c4cb-eb26-3e6a-c2bf6c183b5d';
count
-------
- 272+ 273
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'cfcd2084-95d5-65ef-66e7-dff9f98764da';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '5feceb66-ffc8-6f38-d952-786c6d696c79';
count
-------
12
(1 row)
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525ffdc56';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '86e50149-6586-6131-2a9e-0b35558d84f6';
count
-------
13
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sqlindex 116106d30f..c5a8484584 100644--- a/src/test/regress/sql/brin_multi.sql+++ b/src/test/regress/sql/brin_multi.sql@@ -545,23 +545,23 @@ CREATE INDEX brin_test_multi_1_idx_2 ON brin_test_multi_1 USING brin (b int8_min
-- do some inequality tests for varlena data types
CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10);
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=5);
SET enable_seqscan=off;
-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f369cb89-fc62-7e66-8987-007d121ed1ea';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'aea92132-c4cb-eb26-3e6a-c2bf6c183b5d';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'cfcd2084-95d5-65ef-66e7-dff9f98764da';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '5feceb66-ffc8-6f38-d952-786c6d696c79';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525ffdc56';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '86e50149-6586-6131-2a9e-0b35558d84f6';
-- now do the same, but insert the rows with the indexes already created
@@ -570,19 +570,19 @@ CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minma
TRUNCATE brin_test_multi_2;
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '3d914f93-48c9-cc0f-f8a7-9716700b9fcd';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f369cb89-fc62-7e66-8987-007d121ed1ea';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'aea92132-c4cb-eb26-3e6a-c2bf6c183b5d';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'cfcd2084-95d5-65ef-66e7-dff9f98764da';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '5feceb66-ffc8-6f38-d952-786c6d696c79';-SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525ffdc56';+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = '86e50149-6586-6131-2a9e-0b35558d84f6';
DROP TABLE brin_test_multi_2;
RESET enable_seqscan;
--
2.42.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: peter@eisentraut.org, tomas.vondra@postgresql.org
Subject: Re: pgsql: Improve BRIN minmax-multi opclass test coverage
In-Reply-To: <98683303-b841-42a1-8ccc-9655b116ce24@eisentraut.org>
References: <E1qFsXm-001Uqd-Tv@gemulon.postgresql.org> <a35eaf0f-b19c-7797-e296-7b7e2adc473e@eisentraut.org> <98683303-b841-42a1-8ccc-9655b116ce24@eisentraut.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox