Re: My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> *Summary*
>>
>> My tests show that, when a WITHOUT HOLD cursor has to cache results (see Note 1), then the WHERE clause (if present) is stripped off the cursor's defining SELECT statement and the entire unrestricted result set is cached. But when a WITH HOLD cursor is used, then it’s the *restricted* result set that’s cached.
>>
>> I do see that this wouldn't have a detectable effect when the cursor's defining query doesn't involve any volatile functions. But it does seem that too much data is cached in the "not holdable" case—and this seems to be a bad thing for space use and for speed.
>
> IIUC, all you've demonstrated here is the (sometimes) case for the WITHOUT HOLD cursor where a cache is not used (i.e., the typical case). In this situation the executor, when asked to rewind back to the beginning, goes and restarts execution at the beginning (executor nodes form a tree, it is probable that certain nodes are more efficient at this "start over" thing that others - e.g., I suspect a materialize node sitting in the tree would prevent a sequential scan node from being asked to "start over"), which necessarily involves potentially re-evaluating volatile functions/expressions as noted.

Forgive me. I don't understand your reply. I do understand (having read Laurenz's blog post) that sometimes the execution plan for the cursor's defining SELECT cannot be run backwards. I'm not sure that it matters whether this case is typical or not. It's enough that it can occur. And this is the case that I'm interested in. Laurenz says that in this case, for a WITHOUT HOLD cursor, the results must be cached to allow scrollability. And the results of my tests are consistent with this—up to a point.

However, my results show that for the WITHOUT HOLD case, the restriction that the cursor's SELECT might have is *not* applied to what's cached. But the restriction *is* applied when the WITH HOLD cache is populated.

And it's this that I'm asking about.

Forget that I ever said "volatile". I just edited the code that I included in my previous post. I globally replaced "rndm_series" with "series". And I globally replaced "rndm_filter" with "filter". I also removed the "create procedure init_rndm()" statement and removed the calls of the procedure. Here are the new implementations of "series()" and "filter()""

create function series()
returns table(v int)
set search_path = s1, pg_catalog, pg_temp
language plpgsql
as $body$
begin
raise info 'series() invoked';

for v in (select generate_series(1, 10))loop
return next;
end loop;
end;
$body$;

and

create function filter()
returns boolean
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
begin
raise info 'filter() invoked';
return true;
end;
$body$;

Then I ran the four tests by hand because I don't know how to spool the "raise info" output to a file. In all cases, the "cursor_rows()" invocation just reports the ten rows with values in 1 through 10 — of course.

Here's what I saw:

* (1) open_holdable_cursor(holdable=>false, filter_series=>false, caption=>'') *

The "open_holdable_cursor()" call completes silently.

The first "cursor_rows()" invocation reports "series() invoked" once.

Subsequent "cursor_rows()" invocations produce their rows without that message.

* (2) call open_holdable_cursor(holdable=>false, filter_series=>true, caption=>'') *

The "open_holdable_cursor()" call completes silently again.

The first "cursor_rows()" invocation again reports "series() invoked" once. And then it reports "filter() invoked" ten times.

The second "cursor_rows()" invocation again does *not* report "series() invoked". But it *does* report "filter() invoked" ten times. This tells me that its the *unrestricted* results that are cached.

It's the same for the third invocation (and any more that I care to do).

* (3) open_holdable_cursor(holdable=>true, filter_series=>false, caption=>'') *

The "open_holdable_cursor()" call now reports "series() invoked".

The first, and all subsequent, "cursor_rows()" invocations do not say "series() invoked".

* (4) open_holdable_cursor(holdable=>true, filter_series=>true, caption=>'') *

The "open_holdable_cursor()" call now reports "series() invoked" followed by "filter() invoked" ten times.

The first, and all subsequent, "cursor_rows()" invocations do not bring any "raise info" output because the *restricted* results are cached.

I hope that my question is clearer now.