Обсуждение: 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.