Improvement to random regression test - Mailing list pgsql-patches

I have applied the following improvement to the random regression test.
The random test should fail less frequently.

The original code only tested two random counts.  This version tests
four.  It also uses the average of the counts, rather than testing each
one to make sure it is in a range 80-120.  I also added a random row
test.

--
  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
Index: src/test/regress/expected/random.out
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/expected/random.out,v
retrieving revision 1.4
diff -c -c -r1.4 random.out
*** src/test/regress/expected/random.out    13 Feb 2003 05:24:04 -0000    1.4
--- src/test/regress/expected/random.out    15 Mar 2004 15:40:11 -0000
***************
*** 2,35 ****
  -- RANDOM
  -- Test the random function
  --
! -- count the number of tuples originally
  SELECT count(*) FROM onek;
   count
  -------
    1000
  (1 row)

! -- select roughly 1/10 of the tuples
! -- Assume that the "onek" table has 1000 tuples
! --  and try to bracket the correct number so we
! --  have a regression test which can pass/fail
! -- - thomas 1998-08-17
  SELECT count(*) AS random INTO RANDOM_TBL
    FROM onek WHERE random() < 1.0/10;
  -- select again, the count should be different
  INSERT INTO RANDOM_TBL (random)
    SELECT count(*)
    FROM onek WHERE random() < 1.0/10;
! -- now test the results for randomness in the correct range
  SELECT random, count(random) FROM RANDOM_TBL
!   GROUP BY random HAVING count(random) > 1;
   random | count
  --------+-------
  (0 rows)

! SELECT random FROM RANDOM_TBL
!   WHERE random NOT BETWEEN 80 AND 120;
!  random
! --------
  (0 rows)

--- 2,52 ----
  -- RANDOM
  -- Test the random function
  --
! -- count the number of tuples originally, should be 1000
  SELECT count(*) FROM onek;
   count
  -------
    1000
  (1 row)

! -- pick three random rows, they shouldn't match
! (SELECT unique1 AS random
!   FROM onek ORDER BY random() LIMIT 1)
! INTERSECT
! (SELECT unique1 AS random
!   FROM onek ORDER BY random() LIMIT 1)
! INTERSECT
! (SELECT unique1 AS random
!   FROM onek ORDER BY random() LIMIT 1);
!  random
! --------
! (0 rows)
!
! -- count roughly 1/10 of the tuples
  SELECT count(*) AS random INTO RANDOM_TBL
    FROM onek WHERE random() < 1.0/10;
  -- select again, the count should be different
  INSERT INTO RANDOM_TBL (random)
    SELECT count(*)
    FROM onek WHERE random() < 1.0/10;
! -- select again, the count should be different
! INSERT INTO RANDOM_TBL (random)
!   SELECT count(*)
!   FROM onek WHERE random() < 1.0/10;
! -- select again, the count should be different
! INSERT INTO RANDOM_TBL (random)
!   SELECT count(*)
!   FROM onek WHERE random() < 1.0/10;
! -- now test that they are different counts
  SELECT random, count(random) FROM RANDOM_TBL
!   GROUP BY random HAVING count(random) > 3;
   random | count
  --------+-------
  (0 rows)

! SELECT AVG(random) FROM RANDOM_TBL
!   HAVING AVG(random) NOT BETWEEN 80 AND 120;
!  avg
! -----
  (0 rows)

Index: src/test/regress/sql/random.sql
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/sql/random.sql,v
retrieving revision 1.4
diff -c -c -r1.4 random.sql
*** src/test/regress/sql/random.sql    13 Feb 2003 05:24:04 -0000    1.4
--- src/test/regress/sql/random.sql    15 Mar 2004 15:40:11 -0000
***************
*** 3,16 ****
  -- Test the random function
  --

! -- count the number of tuples originally
  SELECT count(*) FROM onek;

! -- select roughly 1/10 of the tuples
! -- Assume that the "onek" table has 1000 tuples
! --  and try to bracket the correct number so we
! --  have a regression test which can pass/fail
! -- - thomas 1998-08-17
  SELECT count(*) AS random INTO RANDOM_TBL
    FROM onek WHERE random() < 1.0/10;

--- 3,22 ----
  -- Test the random function
  --

! -- count the number of tuples originally, should be 1000
  SELECT count(*) FROM onek;

! -- pick three random rows, they shouldn't match
! (SELECT unique1 AS random
!   FROM onek ORDER BY random() LIMIT 1)
! INTERSECT
! (SELECT unique1 AS random
!   FROM onek ORDER BY random() LIMIT 1)
! INTERSECT
! (SELECT unique1 AS random
!   FROM onek ORDER BY random() LIMIT 1);
!
! -- count roughly 1/10 of the tuples
  SELECT count(*) AS random INTO RANDOM_TBL
    FROM onek WHERE random() < 1.0/10;

***************
*** 19,28 ****
    SELECT count(*)
    FROM onek WHERE random() < 1.0/10;

! -- now test the results for randomness in the correct range
  SELECT random, count(random) FROM RANDOM_TBL
!   GROUP BY random HAVING count(random) > 1;

! SELECT random FROM RANDOM_TBL
!   WHERE random NOT BETWEEN 80 AND 120;

--- 25,44 ----
    SELECT count(*)
    FROM onek WHERE random() < 1.0/10;

! -- select again, the count should be different
! INSERT INTO RANDOM_TBL (random)
!   SELECT count(*)
!   FROM onek WHERE random() < 1.0/10;
!
! -- select again, the count should be different
! INSERT INTO RANDOM_TBL (random)
!   SELECT count(*)
!   FROM onek WHERE random() < 1.0/10;
!
! -- now test that they are different counts
  SELECT random, count(random) FROM RANDOM_TBL
!   GROUP BY random HAVING count(random) > 3;

! SELECT AVG(random) FROM RANDOM_TBL
!   HAVING AVG(random) NOT BETWEEN 80 AND 120;