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.
Servus
Manfred
diff -ruN ../base/src/test/regress/expected/stats.out
src/test/regress/expected/stats.out
--- ../base/src/test/regress/expected/stats.out 1970-01-01 01:00:00.000000000 +0100
+++ src/test/regress/expected/stats.out 2003-09-10 21:01:49.000000000 +0200
@@ -0,0 +1,79 @@
+--
+-- Test Statistics Collector
+--
+-- Must be run after tenk2 has been created (by create_table),
+-- populated (by create_misc) and indexed (by create_index).
+--
+-- conditio sine qua non
+SHOW stats_start_collector; -- must be on
+ stats_start_collector
+-----------------------
+ on
+(1 row)
+
+-- save counters
+CREATE TEMP TABLE prevstats AS
+SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
+ (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ FROM pg_catalog.pg_stat_user_tables AS t,
+ pg_catalog.pg_statio_user_tables AS b
+ WHERE t.relname='tenk2' AND b.relname='tenk2';
+-- enable statistics
+SET stats_block_level = on;
+SET stats_row_level = on;
+-- helper function
+CREATE FUNCTION sleep(interval) RETURNS integer AS '
+DECLARE
+ endtime timestamp;
+BEGIN
+ endtime := timeofday()::timestamp + $1;
+ WHILE timeofday()::timestamp < endtime LOOP
+ END LOOP;
+ RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+-- do something
+SELECT count(*) FROM tenk2;
+ count
+-------
+ 10000
+(1 row)
+
+SELECT count(*) FROM tenk2 WHERE unique1 = 1;
+ count
+-------
+ 1
+(1 row)
+
+-- let stats collector catch up
+SELECT sleep('0:0:2'::interval);
+ sleep
+-------
+ 0
+(1 row)
+
+-- check effects
+SELECT st.seq_scan >= pr.seq_scan + 1,
+ st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
+ st.idx_scan >= pr.idx_scan + 1,
+ st.idx_tup_fetch >= pr.idx_tup_fetch + 1
+ FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ t | t | t | t
+(1 row)
+
+SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
+ st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
+ FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ ?column? | ?column?
+----------+----------
+ t | t
+(1 row)
+
+-- clean up
+DROP FUNCTION sleep(interval);
+-- End of Stats Test
diff -ruN ../base/src/test/regress/parallel_schedule src/test/regress/parallel_schedule
--- ../base/src/test/regress/parallel_schedule 2003-09-02 20:48:55.000000000 +0200
+++ src/test/regress/parallel_schedule 2003-09-10 21:02:40.000000000 +0200
@@ -74,4 +74,4 @@
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
-test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion
truncate alter_table sequence polymorphism
+test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion
truncate alter_table sequence polymorphism stats
diff -ruN ../base/src/test/regress/serial_schedule src/test/regress/serial_schedule
--- ../base/src/test/regress/serial_schedule 2003-09-02 20:48:55.000000000 +0200
+++ src/test/regress/serial_schedule 2003-09-10 21:03:16.000000000 +0200
@@ -94,3 +94,4 @@
test: alter_table
test: sequence
test: polymorphism
+test: stats
diff -ruN ../base/src/test/regress/sql/stats.sql src/test/regress/sql/stats.sql
--- ../base/src/test/regress/sql/stats.sql 1970-01-01 01:00:00.000000000 +0100
+++ src/test/regress/sql/stats.sql 2003-09-10 21:01:49.000000000 +0200
@@ -0,0 +1,58 @@
+--
+-- Test Statistics Collector
+--
+-- Must be run after tenk2 has been created (by create_table),
+-- populated (by create_misc) and indexed (by create_index).
+--
+
+-- conditio sine qua non
+SHOW stats_start_collector; -- must be on
+
+-- save counters
+CREATE TEMP TABLE prevstats AS
+SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
+ (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ FROM pg_catalog.pg_stat_user_tables AS t,
+ pg_catalog.pg_statio_user_tables AS b
+ WHERE t.relname='tenk2' AND b.relname='tenk2';
+
+-- enable statistics
+SET stats_block_level = on;
+SET stats_row_level = on;
+
+-- helper function
+CREATE FUNCTION sleep(interval) RETURNS integer AS '
+DECLARE
+ endtime timestamp;
+BEGIN
+ endtime := timeofday()::timestamp + $1;
+ WHILE timeofday()::timestamp < endtime LOOP
+ END LOOP;
+ RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+
+-- do something
+SELECT count(*) FROM tenk2;
+SELECT count(*) FROM tenk2 WHERE unique1 = 1;
+
+-- let stats collector catch up
+SELECT sleep('0:0:2'::interval);
+
+-- check effects
+SELECT st.seq_scan >= pr.seq_scan + 1,
+ st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
+ st.idx_scan >= pr.idx_scan + 1,
+ st.idx_tup_fetch >= pr.idx_tup_fetch + 1
+ FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
+ st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
+ FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+
+-- clean up
+DROP FUNCTION sleep(interval);
+
+-- End of Stats Test
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
