Re: some random() clarification needed - Mailing list pgsql-general

From David G. Johnston
Subject Re: some random() clarification needed
Date
Msg-id CAKFQuwYWmYrk8NVaCZoTNm5-aLgkX1OLo+_EpOKCcx3aDFpGiw@mail.gmail.com
Whole thread Raw
In response to Re: some random() clarification needed  (Marc Millas <marc.millas@mokadb.com>)
List pgsql-general
Please don't top-post.  Inline (with trim) is better but at minimum bottom-post.

On Tue, Jul 14, 2020 at 9:01 AM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,
your answer helps me understand my first problem.
so, I rewrote a simple loop so as to avoid the "volatile" behaviour.
(at least I was thinking I did... looks like I was wrong !)
step by step loop:
DO $$
BEGIN
  FOR counter IN 1..1000 LOOP
begin
declare
id1 integer =ceiling(random()*2582);
id3 date= '2000-01-01';
id2 date;
pren varchar;
begin
id2=id3 + (random()*7200)::integer;
SELECT prenom FROM prenoms WHERE id=id1 into pren;   
INSERT INTO testparttransac (datenaissance, prenoms) values(id2,pren);
  end;
end;
END LOOP;
END; $$;

I truncated the table, executed the loop with no errors, and expected that a select count(*) 
may answer 1000 !
no.
it varies, from less than 1000 (much less, something like 900)
and more than 1000 (up to 1094)

so... what s "volatile" in the loop ?

Everything...

You are setting id1 to the result of an expression inside the loop.  Everytime that statement gets executed within the loop a new random number is produced.

I mean, even "id 3 date = '2000-01-01'" is repeatedly casting (I think) the string to a date and assigning it to the variable even though that statement overall is effectively immutable.

David J.

pgsql-general by date:

Previous
From: Marc Millas
Date:
Subject: Re: some random() clarification needed
Next
From: Marc Millas
Date:
Subject: how to "explain" some ddl