Re: Re: BUG #8448: looping through query results exits at 10th step under some conditions

David Johnston <polobo(at)yahoo(dot)com> writes:
> So, is there an underlying use-case driving this complaint that should be
> considered by the community either to induce a fix to the behavior or just
> to solicit suggestions for better alternatives?

While I agree the specific test case presented isn't terribly compelling,
it's not that hard to think of other scenarios where somebody might be
surprised at the presence of prefetching. Consider perhaps the
requirement "give me the next value from sequence s1 that's divisible
by ten". On its face this doesn't seem like a terrible implementation:

for s in select nextval('s1') from generate_series(1,10) loop
if s % 10 = 0 then exit loop; end if;
end loop;

This will do what's asked for but it will typically increment the sequence
several extra times due to internal prefetching. Perhaps the application
can tolerate that, perhaps not. With a user-defined volatile function,
the unwanted side effects might be quite unacceptable.

Anyway, I'm not hugely motivated to change the code to fix this, but
I think we at least ought to document it.

regards, tom lane