Skip to content

Commit cd47a4d

Browse files
bmomjianbmomjian
committed
With pg_autovacuum becoming increasingly popular it's important to
have a working stats collector. This test is able to discover the problem that was present in 7.4 Beta 2. Manfred Koizar
1 parent c346ca8 commit cd47a4d

File tree

4 files changed

+140
-2
lines changed

4 files changed

+140
-2
lines changed

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

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
--
2+
-- Test Statistics Collector
3+
--
4+
-- Must be run after tenk2 has been created (by create_table),
5+
-- populated (by create_misc) and indexed (by create_index).
6+
--
7+
-- conditio sine qua non
8+
SHOW stats_start_collector; -- must be on
9+
stats_start_collector
10+
-----------------------
11+
on
12+
(1 row)
13+
14+
-- save counters
15+
CREATE TEMP TABLE prevstats AS
16+
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
17+
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
18+
(b.idx_blks_read + b.idx_blks_hit) AS idx_blks
19+
FROM pg_catalog.pg_stat_user_tables AS t,
20+
pg_catalog.pg_statio_user_tables AS b
21+
WHERE t.relname='tenk2' AND b.relname='tenk2';
22+
-- enable statistics
23+
SET stats_block_level = on;
24+
SET stats_row_level = on;
25+
-- helper function
26+
CREATE FUNCTION sleep(interval) RETURNS integer AS '
27+
DECLARE
28+
endtime timestamp;
29+
BEGIN
30+
endtime := timeofday()::timestamp + $1;
31+
WHILE timeofday()::timestamp < endtime LOOP
32+
END LOOP;
33+
RETURN 0;
34+
END;
35+
' LANGUAGE 'plpgsql';
36+
-- do something
37+
SELECT count(*) FROM tenk2;
38+
count
39+
-------
40+
10000
41+
(1 row)
42+
43+
SELECT count(*) FROM tenk2 WHERE unique1 = 1;
44+
count
45+
-------
46+
1
47+
(1 row)
48+
49+
-- let stats collector catch up
50+
SELECT sleep('0:0:2'::interval);
51+
sleep
52+
-------
53+
0
54+
(1 row)
55+
56+
-- check effects
57+
SELECT st.seq_scan >= pr.seq_scan + 1,
58+
st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
59+
st.idx_scan >= pr.idx_scan + 1,
60+
st.idx_tup_fetch >= pr.idx_tup_fetch + 1
61+
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
62+
WHERE st.relname='tenk2' AND cl.relname='tenk2';
63+
?column? | ?column? | ?column? | ?column?
64+
----------+----------+----------+----------
65+
t | t | t | t
66+
(1 row)
67+
68+
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
69+
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
70+
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
71+
WHERE st.relname='tenk2' AND cl.relname='tenk2';
72+
?column? | ?column?
73+
----------+----------
74+
t | t
75+
(1 row)
76+
77+
-- clean up
78+
DROP FUNCTION sleep(interval);
79+
-- End of Stats Test

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -74,4 +74,4 @@ test: select_views portals_p2 rules foreign_key cluster
7474
# The sixth group of parallel test
7575
# ----------
7676
# "plpgsql" cannot run concurrently with "rules"
77-
test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism
77+
test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism stats

‎src/test/regress/serial_schedule

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.21 2003/08/26 18:32:23 momjian Exp $
1+
# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.22 2003/09/13 16:44:48 momjian Exp $
22
# This should probably be in an order similar to parallel_schedule.
33
test: boolean
44
test: char
@@ -93,3 +93,4 @@ test: truncate
9393
test: alter_table
9494
test: sequence
9595
test: polymorphism
96+
test: stats

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

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
--
2+
-- Test Statistics Collector
3+
--
4+
-- Must be run after tenk2 has been created (by create_table),
5+
-- populated (by create_misc) and indexed (by create_index).
6+
--
7+
8+
-- conditio sine qua non
9+
SHOW stats_start_collector; -- must be on
10+
11+
-- save counters
12+
CREATE TEMP TABLE prevstats AS
13+
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
14+
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
15+
(b.idx_blks_read + b.idx_blks_hit) AS idx_blks
16+
FROM pg_catalog.pg_stat_user_tables AS t,
17+
pg_catalog.pg_statio_user_tables AS b
18+
WHERE t.relname='tenk2' AND b.relname='tenk2';
19+
20+
-- enable statistics
21+
SET stats_block_level = on;
22+
SET stats_row_level = on;
23+
24+
-- helper function
25+
CREATE FUNCTION sleep(interval) RETURNS integer AS '
26+
DECLARE
27+
endtime timestamp;
28+
BEGIN
29+
endtime := timeofday()::timestamp + $1;
30+
WHILE timeofday()::timestamp < endtime LOOP
31+
END LOOP;
32+
RETURN 0;
33+
END;
34+
' LANGUAGE 'plpgsql';
35+
36+
-- do something
37+
SELECT count(*) FROM tenk2;
38+
SELECT count(*) FROM tenk2 WHERE unique1 = 1;
39+
40+
-- let stats collector catch up
41+
SELECT sleep('0:0:2'::interval);
42+
43+
-- check effects
44+
SELECT st.seq_scan >= pr.seq_scan + 1,
45+
st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
46+
st.idx_scan >= pr.idx_scan + 1,
47+
st.idx_tup_fetch >= pr.idx_tup_fetch + 1
48+
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
49+
WHERE st.relname='tenk2' AND cl.relname='tenk2';
50+
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
51+
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
52+
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
53+
WHERE st.relname='tenk2' AND cl.relname='tenk2';
54+
55+
-- clean up
56+
DROP FUNCTION sleep(interval);
57+
58+
-- End of Stats Test

0 commit comments

Comments
 (0)