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

Commitcd47a4d

Browse files
committed
With pg_autovacuum becoming increasingly popular it's important to
have a working stats collector. This test is able to discover theproblem that was present in 7.4 Beta 2.Manfred Koizar
1 parentc346ca8 commitcd47a4d

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 prevstatsAS
13+
SELECTt.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+
FROMpg_catalog.pg_stat_user_tablesAS t,
17+
pg_catalog.pg_statio_user_tablesAS b
18+
WHEREt.relname='tenk2'ANDb.relname='tenk2';
19+
20+
-- enable statistics
21+
SET stats_block_level=on;
22+
SET stats_row_level=on;
23+
24+
-- helper function
25+
CREATEFUNCTIONsleep(interval) RETURNSintegerAS'
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+
SELECTcount(*)FROM tenk2;
38+
SELECTcount(*)FROM tenk2WHERE unique1=1;
39+
40+
-- let stats collector catch up
41+
SELECT sleep('0:0:2'::interval);
42+
43+
-- check effects
44+
SELECTst.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_tablesAS st, pg_classAS cl, prevstatsAS pr
49+
WHEREst.relname='tenk2'ANDcl.relname='tenk2';
50+
SELECTst.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_tablesAS st, pg_classAS cl, prevstatsAS pr
53+
WHEREst.relname='tenk2'ANDcl.relname='tenk2';
54+
55+
-- clean up
56+
DROPFUNCTION sleep(interval);
57+
58+
-- End of Stats Test

0 commit comments

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