From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul Ramsey <pramsey@cleverelephant.ca>
Cc: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PATCH] random_normal function
Date: Sun, 08 Jan 2023 21:17:21 -0500
Message-ID: <3996998.1673230641@sss.pgh.pa.us> (raw)
In-Reply-To: <3983784.1673223640@sss.pgh.pa.us>
References: <CACowWR0DqHAvOKUCNxTrASFkWsDLqKMd6WiXvVvaWg4pV1BMnQ@mail.gmail.com>
<20221208224623.GQ27893@telsasoft.com>
<F103485B-F7B3-40CF-A149-17BA70AF2B61@cleverelephant.ca>
<20221231035804.GJ1153@telsasoft.com>
<Y7OR5nh8qetbwgXX@paquier.xyz>
<1682143.1672713508@sss.pgh.pa.us>
<1776287.1672764097@sss.pgh.pa.us>
<3983784.1673223640@sss.pgh.pa.us>
I wrote:
> Also, I tried running the new random.sql regression cases over
> and over, and found that the "not all duplicates" test fails about
> one time in 100000 or so. We could probably tolerate that given
> that the random test is marked "ignore" in parallel_schedule, but
> I thought it best to add one more iteration so we could knock the
> odds down.
Hmm ... it occurred to me to try the same check on the existing
random() tests (attached), and darn if they don't fail even more
often, usually within 50K iterations. So maybe we should rethink
that whole thing.
regards, tom lane
Attachments:test-random-test.sql (text/plain, 1013B) download
\timing on
create table if not exists random_tbl (random bigint);
do $$
begin
for i in 1..1000000 loop
TRUNCATE random_tbl;
INSERT INTO RANDOM_TBL (random)
SELECT count(*) AS random
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
if (select true FROM RANDOM_TBL
GROUP BY random HAVING count(random) > 3) then
raise notice 'duplicates at iteration %', i;
exit;
end if;
-- approximately check expected distribution
if (select true FROM RANDOM_TBL
HAVING AVG(random) NOT BETWEEN 80 AND 120) then
raise notice 'range at iteration %', i;
exit;
end if;
end loop;
end $$;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pramsey@cleverelephant.ca, coelho@cri.ensmp.fr, michael@paquier.xyz, pryzby@telsasoft.com
Subject: Re: [PATCH] random_normal function
In-Reply-To: <3996998.1673230641@sss.pgh.pa.us>
References: <CACowWR0DqHAvOKUCNxTrASFkWsDLqKMd6WiXvVvaWg4pV1BMnQ@mail.gmail.com> <20221208224623.GQ27893@telsasoft.com> <F103485B-F7B3-40CF-A149-17BA70AF2B61@cleverelephant.ca> <20221231035804.GJ1153@telsasoft.com> <Y7OR5nh8qetbwgXX@paquier.xyz> <1682143.1672713508@sss.pgh.pa.us> <1776287.1672764097@sss.pgh.pa.us> <3983784.1673223640@sss.pgh.pa.us> <3996998.1673230641@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox