Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfe60b67

Browse files
committed
Move test for BRIN HOT behavior to stats.sql
The test added by5753d4e relies on statistics collector, and so itmay occasionally fail when the UDP packet gets lost. Some machines maybe susceptible to this, probably depending on load etc.Move the test to stats.sql, which is known to already have this issueand people know to ignore it.Reported-by: Justin PryzbyDiscussion:https://postgr.es/m/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg%40mail.gmail.com
1 parent4c6145b commitfe60b67

File tree

4 files changed

+103
-97
lines changed

4 files changed

+103
-97
lines changed

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

Lines changed: 1 addition & 50 deletions
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‎

Lines changed: 51 additions & 0 deletions
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‎

Lines changed: 1 addition & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -510,53 +510,7 @@ SELECT * FROM brintest_3 WHERE b < '0';
510510
DROPTABLE brintest_3;
511511
RESET enable_seqscan;
512512

513-
-- test BRIN index doesn't block HOT update
514-
CREATETABLEbrin_hot (
515-
idintegerPRIMARY KEY,
516-
valintegerNOT NULL
517-
) WITH (autovacuum_enabled= off, fillfactor=70);
518-
519-
INSERT INTO brin_hotSELECT*,0FROM generate_series(1,235);
520-
CREATEINDEXval_brinON brin_hot using brin(val);
521-
522-
CREATEFUNCTIONwait_for_hot_stats() RETURNS voidAS $$
523-
DECLARE
524-
start_timetimestamptz := clock_timestamp();
525-
updated bool;
526-
BEGIN
527-
-- we don't want to wait forever; loop will exit after 30 seconds
528-
FOR iIN1 ..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(epochFROM clock_timestamp()- start_time);
540-
END
541-
$$ LANGUAGE plpgsql;
542-
543-
UPDATE brin_hotSET val=-3WHERE 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-
DROPTABLE brin_hot;
557-
DROPFUNCTION 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
CREATETABLEbrin_hot_2 (aint, bint);

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -176,4 +176,54 @@ FROM prevstats AS pr;
176176

177177
DROPTABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
178178
DROPTABLE 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+
CREATETABLEbrin_hot (
184+
idintegerPRIMARY KEY,
185+
valintegerNOT NULL
186+
) WITH (autovacuum_enabled= off, fillfactor=70);
187+
188+
INSERT INTO brin_hotSELECT*,0FROM generate_series(1,235);
189+
CREATEINDEXval_brinON brin_hot using brin(val);
190+
191+
CREATEFUNCTIONwait_for_hot_stats() RETURNS voidAS $$
192+
DECLARE
193+
start_timetimestamptz := clock_timestamp();
194+
updated bool;
195+
BEGIN
196+
-- we don't want to wait forever; loop will exit after 30 seconds
197+
FOR iIN1 ..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(epochFROM clock_timestamp()- start_time);
209+
END
210+
$$ LANGUAGE plpgsql;
211+
212+
UPDATE brin_hotSET val=-3WHERE 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+
DROPTABLE brin_hot;
226+
DROPFUNCTION wait_for_hot_stats();
227+
228+
179229
-- End of Stats Test

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp