Re: Regression test for stats collector

Uh, how do you force the stats collector to 'on' before the test is
run?

---------------------------------------------------------------------------

Manfred Koizar wrote:
> 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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073