Обсуждение: strange infinite loop in plpgsql
I've been reading the online docs, but... code like this somehow ends up in an indefinite loop: CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$ DECLARE timeout int; day date; BEGIN day := current_date + 1; LOOP SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow FROM day) IN (0,6); EXIT WHEN NOT FOUND; timeout := timeout + 86400; END LOOP; END; $$ LANGUAGE plpgsql; It's Saturday at our place, and the "days" table has only one record for tomorrow's date. I hope it's been a very very long day for me :-) Thank you for your help.
rihad <rihad@mail.ru> writes: > LOOP > SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow > FROM day) IN (0,6); > EXIT WHEN NOT FOUND; > timeout := timeout + 86400; > END LOOP; If the EXTRACT condition is true, then the SELECT will always succeed. This code will get even more whacko once you have more than one row in "days", because it'll pick a random one of the rows in that case (in practice, the physically first one). I think you need something more like LOOP IF EXTRACT(dow FROM day) IN (0,6) THEN -- don't bother to consult table on weekends day := day + 1; ELSE SELECT date+1 INTO day FROM days WHERE date=day; EXIT WHEN NOT FOUND; END IF; timeout := timeout + 86400; END LOOP; BTW, you forgot to initialize "timeout". regards, tom lane
Tom Lane wrote: > rihad <rihad@mail.ru> writes: >> LOOP >> SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow >> FROM day) IN (0,6); >> EXIT WHEN NOT FOUND; >> timeout := timeout + 86400; >> END LOOP; > > If the EXTRACT condition is true, then the SELECT will always succeed. Isn't the new "day" re-evaluated on every loop iteration? I'm totally confused. > This code will get even more whacko once you have more than one row > in "days", because it'll pick a random one of the rows in that case > (in practice, the physically first one). I think you need something > more like > > LOOP > IF EXTRACT(dow FROM day) IN (0,6) THEN > -- don't bother to consult table on weekends > day := day + 1; > ELSE > SELECT date+1 INTO day FROM days WHERE date=day; > EXIT WHEN NOT FOUND; > END IF; > timeout := timeout + 86400; > END LOOP; > > BTW, you forgot to initialize "timeout". > Sorry, I hand-cooked this fast from the working code. I guess it defaults to NULL instead of "random bits", which of course wouldn't save me either, but the real (somewhat bigger) code eventually does RETURN LEAST(timeout, expiration_timeout); skipping any nulls.
>> rihad <rihad@mail.ru> writes: >>> LOOP >>> SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow >>> FROM day) IN (0,6); >>> EXIT WHEN NOT FOUND; >>> timeout := timeout + 86400; >>> END LOOP; >> >> If the EXTRACT condition is true, then the SELECT will always succeed. > > Isn't the new "day" re-evaluated on every loop iteration? I'm totally > confused. It's morning again over at our place, so... of course it's not! I stupidly did a SELECT date+1 instead of SELECT day+1, resulting in an infinite loop. A hard to spot bug, especially when your eyes are half closed. Good thing is that the solution came to me while I was still in bed a minute ago :-) But your idea later on of separating EXTRACT outside the disk-touching code might be a nice cpu-memory trade-off that I hadn't thought of (or wasn't aware). It turns out any SQL "thing" can be in an IF or WHILE etc. ? I'll go read the docs more attentively.
Isn't "EXTRACT(dow FROM day) IN (0,6)" always true thus making select return a row every time?
On 11/10/07, rihad < rihad@mail.ru> wrote:
I've been reading the online docs, but... code like this somehow ends up
in an indefinite loop:
CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$
DECLARE
timeout int;
day date;
BEGIN
day := current_date + 1;
LOOP
SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow
FROM day) IN (0,6);
EXIT WHEN NOT FOUND;
timeout := timeout + 86400;
END LOOP;
END; $$ LANGUAGE plpgsql;
It's Saturday at our place, and the "days" table has only one record for
tomorrow's date.
I hope it's been a very very long day for me :-) Thank you for your help.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend