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.
