Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEhjF-0005xc-FD for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Jan 2023 02:17:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEhjD-0005co-EZ for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Jan 2023 02:17:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEhjD-0005cf-2z for pgsql-hackers@lists.postgresql.org; Mon, 09 Jan 2023 02:17:35 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEhj6-0006ZN-Eq for pgsql-hackers@postgresql.org; Mon, 09 Jan 2023 02:17:33 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3092HLK83996999; Sun, 8 Jan 2023 21:17:22 -0500 From: Tom Lane To: Paul Ramsey cc: Fabien COELHO , Michael Paquier , Justin Pryzby , pgsql-hackers@postgresql.org Subject: Re: [PATCH] random_normal function In-reply-to: <3983784.1673223640@sss.pgh.pa.us> References: <20221208224623.GQ27893@telsasoft.com> <20221231035804.GJ1153@telsasoft.com> <1682143.1672713508@sss.pgh.pa.us> <1776287.1672764097@sss.pgh.pa.us> <3983784.1673223640@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sun, 08 Jan 2023 19:20:40 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3996959.1673230590.0@sss.pgh.pa.us> Date: Sun, 08 Jan 2023 21:17:21 -0500 Message-ID: <3996998.1673230641@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3996959.1673230590.1@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 ------- =_aaaaaaaaaa0 Content-Type: text/plain; name="test-random-test.sql"; charset="us-ascii" Content-ID: <3996959.1673230590.2@sss.pgh.pa.us> Content-Description: test-random-test.sql \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 $$; ------- =_aaaaaaaaaa0--