> david.g.johns...@gmail.com wrote:
>
>> b...@yugabyte.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.
