Skip to content

Commit fe60b67

Browse files
tvondratvondra
committed
Move test for BRIN HOT behavior to stats.sql
The test added by 5753d4e relies on statistics collector, and so it may occasionally fail when the UDP packet gets lost. Some machines may be susceptible to this, probably depending on load etc. Move the test to stats.sql, which is known to already have this issue and people know to ignore it. Reported-by: Justin Pryzby Discussion: https://postgr.es/m/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg%40mail.gmail.com
1 parent 4c6145b commit fe60b67

File tree

4 files changed

+103
-97
lines changed

4 files changed

+103
-97
lines changed

‎src/test/regress/expected/brin.out

+1-50
Original file line numberDiff line numberDiff line change
@@ -567,56 +567,7 @@ SELECT * FROM brintest_3 WHERE b < '0';
567567

568568
DROP TABLE brintest_3;
569569
RESET enable_seqscan;
570-
-- test BRIN index doesn't block HOT update
571-
CREATE TABLE brin_hot (
572-
id integer PRIMARY KEY,
573-
val integer NOT NULL
574-
) WITH (autovacuum_enabled = off, fillfactor = 70);
575-
INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235);
576-
CREATE INDEX val_brin ON brin_hot using brin(val);
577-
CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$
578-
DECLARE
579-
start_time timestamptz := clock_timestamp();
580-
updated bool;
581-
BEGIN
582-
-- we don't want to wait forever; loop will exit after 30 seconds
583-
FOR i IN 1 .. 300 LOOP
584-
SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated;
585-
EXIT WHEN updated;
586-
587-
-- wait a little
588-
PERFORM pg_sleep_for('100 milliseconds');
589-
-- reset stats snapshot so we can test again
590-
PERFORM pg_stat_clear_snapshot();
591-
END LOOP;
592-
-- report time waited in postmaster log (where it won't change test output)
593-
RAISE log 'wait_for_hot_stats delayed % seconds',
594-
EXTRACT(epoch FROM clock_timestamp() - start_time);
595-
END
596-
$$ LANGUAGE plpgsql;
597-
UPDATE brin_hot SET val = -3 WHERE id = 42;
598-
-- We can't just call wait_for_hot_stats() at this point, because we only
599-
-- transmit stats when the session goes idle, and we probably didn't
600-
-- transmit the last couple of counts yet thanks to the rate-limiting logic
601-
-- in pgstat_report_stat(). But instead of waiting for the rate limiter's
602-
-- timeout to elapse, let's just start a new session. The old one will
603-
-- then send its stats before dying.
604-
\c -
605-
SELECT wait_for_hot_stats();
606-
wait_for_hot_stats
607-
--------------------
608-
609-
(1 row)
610-
611-
SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
612-
pg_stat_get_tuples_hot_updated
613-
--------------------------------
614-
1
615-
(1 row)
616-
617-
DROP TABLE brin_hot;
618-
DROP FUNCTION wait_for_hot_stats();
619-
-- Test handling of index predicates - updating attributes in precicates
570+
-- Test handling of index predicates - updating attributes in predicates
620571
-- should block HOT even for BRIN. We update a row that was not indexed
621572
-- due to the index predicate, and becomes indexable.
622573
CREATE TABLE brin_hot_2 (a int, b int);

‎src/test/regress/expected/stats.out

+51
Original file line numberDiff line numberDiff line change
@@ -201,4 +201,55 @@ FROM prevstats AS pr;
201201

202202
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
203203
DROP TABLE prevstats;
204+
-- test BRIN index doesn't block HOT update - we include this test here, as it
205+
-- relies on statistics collector and so it may occasionally fail, especially
206+
-- on slower systems
207+
CREATE TABLE brin_hot (
208+
id integer PRIMARY KEY,
209+
val integer NOT NULL
210+
) WITH (autovacuum_enabled = off, fillfactor = 70);
211+
INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235);
212+
CREATE INDEX val_brin ON brin_hot using brin(val);
213+
CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$
214+
DECLARE
215+
start_time timestamptz := clock_timestamp();
216+
updated bool;
217+
BEGIN
218+
-- we don't want to wait forever; loop will exit after 30 seconds
219+
FOR i IN 1 .. 300 LOOP
220+
SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated;
221+
EXIT WHEN updated;
222+
223+
-- wait a little
224+
PERFORM pg_sleep_for('100 milliseconds');
225+
-- reset stats snapshot so we can test again
226+
PERFORM pg_stat_clear_snapshot();
227+
END LOOP;
228+
-- report time waited in postmaster log (where it won't change test output)
229+
RAISE log 'wait_for_hot_stats delayed % seconds',
230+
EXTRACT(epoch FROM clock_timestamp() - start_time);
231+
END
232+
$$ LANGUAGE plpgsql;
233+
UPDATE brin_hot SET val = -3 WHERE id = 42;
234+
-- We can't just call wait_for_hot_stats() at this point, because we only
235+
-- transmit stats when the session goes idle, and we probably didn't
236+
-- transmit the last couple of counts yet thanks to the rate-limiting logic
237+
-- in pgstat_report_stat(). But instead of waiting for the rate limiter's
238+
-- timeout to elapse, let's just start a new session. The old one will
239+
-- then send its stats before dying.
240+
\c -
241+
SELECT wait_for_hot_stats();
242+
wait_for_hot_stats
243+
--------------------
244+
245+
(1 row)
246+
247+
SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
248+
pg_stat_get_tuples_hot_updated
249+
--------------------------------
250+
1
251+
(1 row)
252+
253+
DROP TABLE brin_hot;
254+
DROP FUNCTION wait_for_hot_stats();
204255
-- End of Stats Test

‎src/test/regress/sql/brin.sql

+1-47
Original file line numberDiff line numberDiff line change
@@ -510,53 +510,7 @@ SELECT * FROM brintest_3 WHERE b < '0';
510510
DROP TABLE brintest_3;
511511
RESET enable_seqscan;
512512

513-
-- test BRIN index doesn't block HOT update
514-
CREATE TABLE brin_hot (
515-
id integer PRIMARY KEY,
516-
val integer NOT NULL
517-
) WITH (autovacuum_enabled = off, fillfactor = 70);
518-
519-
INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235);
520-
CREATE INDEX val_brin ON brin_hot using brin(val);
521-
522-
CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$
523-
DECLARE
524-
start_time timestamptz := clock_timestamp();
525-
updated bool;
526-
BEGIN
527-
-- we don't want to wait forever; loop will exit after 30 seconds
528-
FOR i IN 1 .. 300 LOOP
529-
SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated;
530-
EXIT WHEN updated;
531-
532-
-- wait a little
533-
PERFORM pg_sleep_for('100 milliseconds');
534-
-- reset stats snapshot so we can test again
535-
PERFORM pg_stat_clear_snapshot();
536-
END LOOP;
537-
-- report time waited in postmaster log (where it won't change test output)
538-
RAISE log 'wait_for_hot_stats delayed % seconds',
539-
EXTRACT(epoch FROM clock_timestamp() - start_time);
540-
END
541-
$$ LANGUAGE plpgsql;
542-
543-
UPDATE brin_hot SET val = -3 WHERE id = 42;
544-
545-
-- We can't just call wait_for_hot_stats() at this point, because we only
546-
-- transmit stats when the session goes idle, and we probably didn't
547-
-- transmit the last couple of counts yet thanks to the rate-limiting logic
548-
-- in pgstat_report_stat(). But instead of waiting for the rate limiter's
549-
-- timeout to elapse, let's just start a new session. The old one will
550-
-- then send its stats before dying.
551-
\c -
552-
553-
SELECT wait_for_hot_stats();
554-
SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
555-
556-
DROP TABLE brin_hot;
557-
DROP FUNCTION wait_for_hot_stats();
558-
559-
-- Test handling of index predicates - updating attributes in precicates
513+
-- Test handling of index predicates - updating attributes in predicates
560514
-- should block HOT even for BRIN. We update a row that was not indexed
561515
-- due to the index predicate, and becomes indexable.
562516
CREATE TABLE brin_hot_2 (a int, b int);

‎src/test/regress/sql/stats.sql

+50
Original file line numberDiff line numberDiff line change
@@ -176,4 +176,54 @@ FROM prevstats AS pr;
176176

177177
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
178178
DROP TABLE prevstats;
179+
180+
-- test BRIN index doesn't block HOT update - we include this test here, as it
181+
-- relies on statistics collector and so it may occasionally fail, especially
182+
-- on slower systems
183+
CREATE TABLE brin_hot (
184+
id integer PRIMARY KEY,
185+
val integer NOT NULL
186+
) WITH (autovacuum_enabled = off, fillfactor = 70);
187+
188+
INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235);
189+
CREATE INDEX val_brin ON brin_hot using brin(val);
190+
191+
CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$
192+
DECLARE
193+
start_time timestamptz := clock_timestamp();
194+
updated bool;
195+
BEGIN
196+
-- we don't want to wait forever; loop will exit after 30 seconds
197+
FOR i IN 1 .. 300 LOOP
198+
SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated;
199+
EXIT WHEN updated;
200+
201+
-- wait a little
202+
PERFORM pg_sleep_for('100 milliseconds');
203+
-- reset stats snapshot so we can test again
204+
PERFORM pg_stat_clear_snapshot();
205+
END LOOP;
206+
-- report time waited in postmaster log (where it won't change test output)
207+
RAISE log 'wait_for_hot_stats delayed % seconds',
208+
EXTRACT(epoch FROM clock_timestamp() - start_time);
209+
END
210+
$$ LANGUAGE plpgsql;
211+
212+
UPDATE brin_hot SET val = -3 WHERE id = 42;
213+
214+
-- We can't just call wait_for_hot_stats() at this point, because we only
215+
-- transmit stats when the session goes idle, and we probably didn't
216+
-- transmit the last couple of counts yet thanks to the rate-limiting logic
217+
-- in pgstat_report_stat(). But instead of waiting for the rate limiter's
218+
-- timeout to elapse, let's just start a new session. The old one will
219+
-- then send its stats before dying.
220+
\c -
221+
222+
SELECT wait_for_hot_stats();
223+
SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
224+
225+
DROP TABLE brin_hot;
226+
DROP FUNCTION wait_for_hot_stats();
227+
228+
179229
-- End of Stats Test

0 commit comments

Comments
 (0)