david.g.johnston@xxxxxxxxx wrote:bryn@xxxxxxxxxxxx wrote: 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. |