Regression test for stats collector - Mailing list pgsql-patches

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
polymorphismstats 
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