Skip to content

Commit 09d5177

Browse files
tglsfdctglsfdc
committed
Upgrade the random.sql regression test.
We had some pretty ad-hoc and inefficient code here. To make matters worse, it didn't test the properties of the random() function very thoroughly, and it had a test failure rate of one in every few tens of thousands of runs. Replace the script altogether with new test cases that prove much more about random()'s output, run faster, and can be calculated to have test failure rates on the order of 1e-9. Having done that, the failure rate of this script should be negligible in comparison to other causes of test failures, so remove the "ignore" marker for it in parallel_schedule. (If it does fail, we'd like to know about that, so "ignore" was always pretty counterproductive.) Tom Lane and Dean Rasheed Discussion: https://postgr.es/m/4173840.1673290336@sss.pgh.pa.us
1 parent 78ee60e commit 09d5177

File tree

3 files changed

+205
-126
lines changed

3 files changed

+205
-126
lines changed

‎src/test/regress/expected/random.out

Lines changed: 135 additions & 70 deletions
Original file line numberDiff line numberDiff line change
@@ -1,81 +1,146 @@
11
--
22
-- RANDOM
3-
-- Test the random function
3+
-- Test random() and allies
44
--
5-
-- count the number of tuples originally, should be 1000
6-
SELECT count(*) FROM onek;
7-
count
8-
-------
9-
1000
10-
(1 row)
11-
12-
-- pick three random rows, they shouldn't match
13-
(SELECT unique1 AS random
14-
FROM onek ORDER BY random() LIMIT 1)
15-
INTERSECT
16-
(SELECT unique1 AS random
17-
FROM onek ORDER BY random() LIMIT 1)
18-
INTERSECT
19-
(SELECT unique1 AS random
20-
FROM onek ORDER BY random() LIMIT 1);
21-
random
22-
--------
5+
-- Tests in this file may have a small probability of failure,
6+
-- since we are dealing with randomness. Try to keep the failure
7+
-- risk for any one test case under 1e-9.
8+
--
9+
-- There should be no duplicates in 1000 random() values.
10+
-- (Assuming 52 random bits in the float8 results, we could
11+
-- take as many as 3000 values and still have less than 1e-9 chance
12+
-- of failure, per https://en.wikipedia.org/wiki/Birthday_problem)
13+
SELECT r, count(*)
14+
FROM (SELECT random() r FROM generate_series(1, 1000)) ss
15+
GROUP BY r HAVING count(*) > 1;
16+
r | count
17+
---+-------
2318
(0 rows)
2419

25-
-- count roughly 1/10 of the tuples
26-
CREATE TABLE RANDOM_TBL AS
27-
SELECT count(*) AS random
28-
FROM onek WHERE random() < 1.0/10;
29-
-- select again, the count should be different
30-
INSERT INTO RANDOM_TBL (random)
31-
SELECT count(*)
32-
FROM onek WHERE random() < 1.0/10;
33-
-- select again, the count should be different
34-
INSERT INTO RANDOM_TBL (random)
35-
SELECT count(*)
36-
FROM onek WHERE random() < 1.0/10;
37-
-- select again, the count should be different
38-
INSERT INTO RANDOM_TBL (random)
39-
SELECT count(*)
40-
FROM onek WHERE random() < 1.0/10;
41-
-- now test that they are different counts
42-
SELECT random, count(random) FROM RANDOM_TBL
43-
GROUP BY random HAVING count(random) > 3;
44-
random | count
45-
--------+-------
46-
(0 rows)
20+
-- The range should be [0, 1). We can expect that at least one out of 2000
21+
-- random values is in the lowest or highest 1% of the range with failure
22+
-- probability less than about 1e-9.
23+
SELECT count(*) FILTER (WHERE r < 0 OR r >= 1) AS out_of_range,
24+
(count(*) FILTER (WHERE r < 0.01)) > 0 AS has_small,
25+
(count(*) FILTER (WHERE r > 0.99)) > 0 AS has_large
26+
FROM (SELECT random() r FROM generate_series(1, 2000)) ss;
27+
out_of_range | has_small | has_large
28+
--------------+-----------+-----------
29+
0 | t | t
30+
(1 row)
4731

48-
SELECT AVG(random) FROM RANDOM_TBL
49-
HAVING AVG(random) NOT BETWEEN 80 AND 120;
50-
avg
51-
-----
52-
(0 rows)
32+
-- Check for uniform distribution using the Kolmogorov-Smirnov test.
33+
CREATE FUNCTION ks_test_uniform_random()
34+
RETURNS boolean AS
35+
$$
36+
DECLARE
37+
n int := 1000; -- Number of samples
38+
c float8 := 1.94947; -- Critical value for 99.9% confidence
39+
ok boolean;
40+
BEGIN
41+
ok := (
42+
WITH samples AS (
43+
SELECT random() r FROM generate_series(1, n) ORDER BY 1
44+
), indexed_samples AS (
45+
SELECT (row_number() OVER())-1.0 i, r FROM samples
46+
)
47+
SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
48+
);
49+
RETURN ok;
50+
END
51+
$$
52+
LANGUAGE plpgsql;
53+
-- As written, ks_test_uniform_random() returns true about 99.9%
54+
-- of the time. To get down to a roughly 1e-9 test failure rate,
55+
-- just run it 3 times and accept if any one of them passes.
56+
SELECT ks_test_uniform_random() OR
57+
ks_test_uniform_random() OR
58+
ks_test_uniform_random() AS uniform;
59+
uniform
60+
---------
61+
t
62+
(1 row)
5363

5464
-- now test random_normal()
55-
TRUNCATE random_tbl;
56-
INSERT INTO random_tbl (random)
57-
SELECT count(*)
58-
FROM onek WHERE random_normal(0, 1) < 0;
59-
INSERT INTO random_tbl (random)
60-
SELECT count(*)
61-
FROM onek WHERE random_normal(0) < 0;
62-
INSERT INTO random_tbl (random)
63-
SELECT count(*)
64-
FROM onek WHERE random_normal() < 0;
65-
INSERT INTO random_tbl (random)
66-
SELECT count(*)
67-
FROM onek WHERE random_normal(stddev => 1, mean => 0) < 0;
68-
-- expect similar, but not identical values
69-
SELECT random, count(random) FROM random_tbl
70-
GROUP BY random HAVING count(random) > 3;
71-
random | count
72-
--------+-------
65+
-- As above, there should be no duplicates in 1000 random_normal() values.
66+
SELECT r, count(*)
67+
FROM (SELECT random_normal() r FROM generate_series(1, 1000)) ss
68+
GROUP BY r HAVING count(*) > 1;
69+
r | count
70+
---+-------
7371
(0 rows)
7472

75-
-- approximately check expected distribution
76-
SELECT AVG(random) FROM random_tbl
77-
HAVING AVG(random) NOT BETWEEN 400 AND 600;
78-
avg
79-
-----
80-
(0 rows)
73+
-- ... unless we force the range (standard deviation) to zero.
74+
-- This is a good place to check that the mean input does something, too.
75+
SELECT r, count(*)
76+
FROM (SELECT random_normal(10, 0) r FROM generate_series(1, 100)) ss
77+
GROUP BY r;
78+
r | count
79+
----+-------
80+
10 | 100
81+
(1 row)
82+
83+
SELECT r, count(*)
84+
FROM (SELECT random_normal(-10, 0) r FROM generate_series(1, 100)) ss
85+
GROUP BY r;
86+
r | count
87+
-----+-------
88+
-10 | 100
89+
(1 row)
90+
91+
-- setseed() should produce a reproducible series of random() values.
92+
SELECT setseed(0.5);
93+
setseed
94+
---------
95+
96+
(1 row)
97+
98+
SELECT random() FROM generate_series(1, 10);
99+
random
100+
---------------------
101+
0.9851677175347999
102+
0.825301858027981
103+
0.12974610012450416
104+
0.16356291958601088
105+
0.6476186144084
106+
0.8822771983038762
107+
0.1404566845227775
108+
0.15619865764623442
109+
0.5145227426983392
110+
0.7712969548127826
111+
(10 rows)
112+
113+
-- Likewise for random_normal(); however, since its implementation relies
114+
-- on libm functions that have different roundoff behaviors on different
115+
-- machines, we have to round off the results a bit to get consistent output.
116+
SET extra_float_digits = 0;
117+
SELECT random_normal() FROM generate_series(1, 10);
118+
random_normal
119+
--------------------
120+
0.208534644938377
121+
0.264530240540963
122+
-0.606752467900428
123+
0.825799427852654
124+
1.70111611735357
125+
-0.223445463716189
126+
0.249712419190998
127+
-1.2494722990669
128+
0.125627152043677
129+
0.475391614544013
130+
(10 rows)
131+
132+
SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);
133+
r
134+
-------------------
135+
1.00605972811732
136+
1.09685453015002
137+
1.02869206132007
138+
0.909475676712336
139+
0.983724763134265
140+
0.939344549577623
141+
1.18713500206363
142+
0.962257684292933
143+
0.914441206800407
144+
0.964031055575433
145+
(10 rows)
81146

‎src/test/regress/parallel_schedule

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -61,9 +61,6 @@ test: create_aggregate create_function_sql create_cast constraints triggers sele
6161
# ----------
6262
test: sanity_check
6363

64-
# Note: the ignore: line does not skip random, just mark it as ignorable
65-
ignore: random
66-
6764
# ----------
6865
# Another group of parallel tests
6966
# aggregates depends on create_aggregate

‎src/test/regress/sql/random.sql

Lines changed: 70 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -1,68 +1,85 @@
11
--
22
-- RANDOM
3-
-- Test the random function
3+
-- Test random() and allies
4+
--
5+
-- Tests in this file may have a small probability of failure,
6+
-- since we are dealing with randomness. Try to keep the failure
7+
-- risk for any one test case under 1e-9.
48
--
59

6-
-- count the number of tuples originally, should be 1000
7-
SELECT count(*) FROM onek;
10+
-- There should be no duplicates in 1000 random() values.
11+
-- (Assuming 52 random bits in the float8 results, we could
12+
-- take as many as 3000 values and still have less than 1e-9 chance
13+
-- of failure, per https://en.wikipedia.org/wiki/Birthday_problem)
14+
SELECT r, count(*)
15+
FROM (SELECT random() r FROM generate_series(1, 1000)) ss
16+
GROUP BY r HAVING count(*) > 1;
817

9-
-- pick three random rows, they shouldn't match
10-
(SELECT unique1 AS random
11-
FROM onek ORDER BY random() LIMIT 1)
12-
INTERSECT
13-
(SELECT unique1 AS random
14-
FROM onek ORDER BY random() LIMIT 1)
15-
INTERSECT
16-
(SELECT unique1 AS random
17-
FROM onek ORDER BY random() LIMIT 1);
18+
-- The range should be [0, 1). We can expect that at least one out of 2000
19+
-- random values is in the lowest or highest 1% of the range with failure
20+
-- probability less than about 1e-9.
1821

19-
-- count roughly 1/10 of the tuples
20-
CREATE TABLE RANDOM_TBL AS
21-
SELECT count(*) AS random
22-
FROM onek WHERE random() < 1.0/10;
22+
SELECT count(*) FILTER (WHERE r < 0 OR r >= 1) AS out_of_range,
23+
(count(*) FILTER (WHERE r < 0.01)) > 0 AS has_small,
24+
(count(*) FILTER (WHERE r > 0.99)) > 0 AS has_large
25+
FROM (SELECT random() r FROM generate_series(1, 2000)) ss;
2326

24-
-- select again, the count should be different
25-
INSERT INTO RANDOM_TBL (random)
26-
SELECT count(*)
27-
FROM onek WHERE random() < 1.0/10;
27+
-- Check for uniform distribution using the Kolmogorov-Smirnov test.
2828

29-
-- select again, the count should be different
30-
INSERT INTO RANDOM_TBL (random)
31-
SELECT count(*)
32-
FROM onek WHERE random() < 1.0/10;
29+
CREATE FUNCTION ks_test_uniform_random()
30+
RETURNS boolean AS
31+
$$
32+
DECLARE
33+
n int := 1000; -- Number of samples
34+
c float8 := 1.94947; -- Critical value for 99.9% confidence
35+
ok boolean;
36+
BEGIN
37+
ok := (
38+
WITH samples AS (
39+
SELECT random() r FROM generate_series(1, n) ORDER BY 1
40+
), indexed_samples AS (
41+
SELECT (row_number() OVER())-1.0 i, r FROM samples
42+
)
43+
SELECT max(abs(i/n-r)) < c / sqrt(n) FROM indexed_samples
44+
);
45+
RETURN ok;
46+
END
47+
$$
48+
LANGUAGE plpgsql;
3349

34-
-- select again, the count should be different
35-
INSERT INTO RANDOM_TBL (random)
36-
SELECT count(*)
37-
FROM onek WHERE random() < 1.0/10;
50+
-- As written, ks_test_uniform_random() returns true about 99.9%
51+
-- of the time. To get down to a roughly 1e-9 test failure rate,
52+
-- just run it 3 times and accept if any one of them passes.
53+
SELECT ks_test_uniform_random() OR
54+
ks_test_uniform_random() OR
55+
ks_test_uniform_random() AS uniform;
3856

39-
-- now test that they are different counts
40-
SELECT random, count(random) FROM RANDOM_TBL
41-
GROUP BY random HAVING count(random) > 3;
57+
-- now test random_normal()
4258

43-
SELECT AVG(random) FROM RANDOM_TBL
44-
HAVING AVG(random) NOT BETWEEN 80 AND 120;
59+
-- As above, there should be no duplicates in 1000 random_normal() values.
60+
SELECT r, count(*)
61+
FROM (SELECT random_normal() r FROM generate_series(1, 1000)) ss
62+
GROUP BY r HAVING count(*) > 1;
4563

46-
-- now test random_normal()
64+
-- ... unless we force the range (standard deviation) to zero.
65+
-- This is a good place to check that the mean input does something, too.
66+
SELECT r, count(*)
67+
FROM (SELECT random_normal(10, 0) r FROM generate_series(1, 100)) ss
68+
GROUP BY r;
69+
SELECT r, count(*)
70+
FROM (SELECT random_normal(-10, 0) r FROM generate_series(1, 100)) ss
71+
GROUP BY r;
72+
73+
-- setseed() should produce a reproducible series of random() values.
74+
75+
SELECT setseed(0.5);
4776

48-
TRUNCATE random_tbl;
49-
INSERT INTO random_tbl (random)
50-
SELECT count(*)
51-
FROM onek WHERE random_normal(0, 1) < 0;
52-
INSERT INTO random_tbl (random)
53-
SELECT count(*)
54-
FROM onek WHERE random_normal(0) < 0;
55-
INSERT INTO random_tbl (random)
56-
SELECT count(*)
57-
FROM onek WHERE random_normal() < 0;
58-
INSERT INTO random_tbl (random)
59-
SELECT count(*)
60-
FROM onek WHERE random_normal(stddev => 1, mean => 0) < 0;
77+
SELECT random() FROM generate_series(1, 10);
6178

62-
-- expect similar, but not identical values
63-
SELECT random, count(random) FROM random_tbl
64-
GROUP BY random HAVING count(random) > 3;
79+
-- Likewise for random_normal(); however, since its implementation relies
80+
-- on libm functions that have different roundoff behaviors on different
81+
-- machines, we have to round off the results a bit to get consistent output.
82+
SET extra_float_digits = 0;
6583

66-
-- approximately check expected distribution
67-
SELECT AVG(random) FROM random_tbl
68-
HAVING AVG(random) NOT BETWEEN 400 AND 600;
84+
SELECT random_normal() FROM generate_series(1, 10);
85+
SELECT random_normal(mean => 1, stddev => 0.1) r FROM generate_series(1, 10);

0 commit comments

Comments
 (0)