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 ? BTW the testparttransac table is partitioned on datenaissance, with a default partition. thanks, regards, Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 14, 2020 at 5:24 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jul 14, 2020 at 8:15 AM Marc Millas <marc.mil...@mokadb.com> > wrote: > >> select id, prenom from prenoms where id=ceiling(random()*2582); >> >> expecting to get, allways, one line. >> But its not the case. >> around 15% of time I get 0 lines which is already quite strange to me. >> but 10% of time, I get a random number of lines, until now up to 4. >> even weirder (to me !) >> >> so, can someone please clarify ? >> >> > You are basically asking: > > For each row in my table compare the id to some random number and if they > match return that row, otherwise skip it. The random number being compared > to is different for each row because random() is volatile and thus > evaluated for each row. > > David J. >
