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
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.