mirror of
https://git.postgresql.org/git/postgresql.git
synced 2025-03-19 20:00:51 +08:00
657f5f223e
This removes md5() function calls from these test suites: - bloom - test_decoding - isolation - recovery - subscription This covers all remaining test suites where md5() calls were just used to generate some random data and can be replaced by appropriately adapted sha256() calls. This will eventually allow these tests to pass in OpenSSL FIPS mode (which does not allow MD5 use). See also 208bf364a9. Unlike for the main regression tests, I didn't write a fipshash() wrapper here, because that would have been too repetitive and wouldn't really save much here. In some cases it was easier to remove one layer of indirection by changing column types from text to bytea. Reviewed-by: Daniel Gustafsson <daniel@yesql.se> Discussion: https://www.postgresql.org/message-id/flat/f9b480b5-e473-d2d1-223a-4b9db30a229a@eisentraut.org
96 lines
2.8 KiB
SQL
96 lines
2.8 KiB
SQL
CREATE EXTENSION bloom;
|
|
|
|
CREATE TABLE tst (
|
|
i int4,
|
|
t text
|
|
);
|
|
|
|
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
|
|
CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (col1 = 3);
|
|
ALTER INDEX bloomidx SET (length=80);
|
|
|
|
SET enable_seqscan=on;
|
|
SET enable_bitmapscan=off;
|
|
SET enable_indexscan=off;
|
|
|
|
SELECT count(*) FROM tst WHERE i = 7;
|
|
SELECT count(*) FROM tst WHERE t = '5';
|
|
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
|
|
|
|
SET enable_seqscan=off;
|
|
SET enable_bitmapscan=on;
|
|
SET enable_indexscan=on;
|
|
|
|
EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7;
|
|
EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE t = '5';
|
|
EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
|
|
|
|
SELECT count(*) FROM tst WHERE i = 7;
|
|
SELECT count(*) FROM tst WHERE t = '5';
|
|
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
|
|
|
|
DELETE FROM tst;
|
|
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
|
|
VACUUM ANALYZE tst;
|
|
|
|
SELECT count(*) FROM tst WHERE i = 7;
|
|
SELECT count(*) FROM tst WHERE t = '5';
|
|
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
|
|
|
|
DELETE FROM tst WHERE i > 1 OR t = '5';
|
|
VACUUM tst;
|
|
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
|
|
|
|
SELECT count(*) FROM tst WHERE i = 7;
|
|
SELECT count(*) FROM tst WHERE t = '5';
|
|
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
|
|
|
|
VACUUM FULL tst;
|
|
|
|
SELECT count(*) FROM tst WHERE i = 7;
|
|
SELECT count(*) FROM tst WHERE t = '5';
|
|
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
|
|
|
|
-- Try an unlogged table too
|
|
|
|
CREATE UNLOGGED TABLE tstu (
|
|
i int4,
|
|
t text
|
|
);
|
|
|
|
INSERT INTO tstu SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
|
|
CREATE INDEX bloomidxu ON tstu USING bloom (i, t) WITH (col2 = 4);
|
|
|
|
SET enable_seqscan=off;
|
|
SET enable_bitmapscan=on;
|
|
SET enable_indexscan=on;
|
|
|
|
EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE i = 7;
|
|
EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE t = '5';
|
|
EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE i = 7 AND t = '5';
|
|
|
|
SELECT count(*) FROM tstu WHERE i = 7;
|
|
SELECT count(*) FROM tstu WHERE t = '5';
|
|
SELECT count(*) FROM tstu WHERE i = 7 AND t = '5';
|
|
|
|
RESET enable_seqscan;
|
|
RESET enable_bitmapscan;
|
|
RESET enable_indexscan;
|
|
|
|
-- Run amvalidator function on our opclasses
|
|
SELECT opcname, amvalidate(opc.oid)
|
|
FROM pg_opclass opc JOIN pg_am am ON am.oid = opcmethod
|
|
WHERE amname = 'bloom'
|
|
ORDER BY 1;
|
|
|
|
--
|
|
-- relation options
|
|
--
|
|
DROP INDEX bloomidx;
|
|
CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1=4);
|
|
SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
|
|
-- check for min and max values
|
|
\set VERBOSITY terse
|
|
CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
|
|
CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);
|