Re: Regression test for stats collector - Mailing list pgsql-patches

Patch applied.  Thanks.

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


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

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

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