From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: some random() clarification needed |
Date: | 2020-07-14 16:08:33 |
Message-ID: | CAKFQuwYWmYrk8NVaCZoTNm5-aLgkX1OLo+_EpOKCcx3aDFpGiw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)millas(at)mokadb(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Millas | 2020-07-14 16:36:05 | how to "explain" some ddl |
Previous Message | Marc Millas | 2020-07-14 16:01:28 | Re: some random() clarification needed |