[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor
From: Bryn Llewellyn <bryn () yugabyte ! com>
Date: 2023-03-31 21:53:14
Message-ID: 151C11C4-D783-433D-BF75-499607D6CAC3 () yugabyte ! com
[Download RAW message or body]
> david.g.johnston@gmail.com wrote:
>
> > bryn@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.
[Attachment #3 (unknown)]
<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: \
after-white-space;" class=""><i class=""></i><blockquote type="cite" class=""><i \
class="">da<a href="mailto:vid.g.johnston@gmail.com" \
class="">vid.g.johnston@gmail.com</a> wrote:<br class=""></i><br class=""><blockquote \
type="cite" class="">b<a href="mailto:ryn@yugabyte.com" \
class="">ryn@yugabyte.com</a> wrote:<div class=""><br class="">*Summary*<br \
class=""><br class="">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.<br class=""><br class="">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.<br \
class=""></div></blockquote><div class=""><br class="">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.<br class=""></div></blockquote><div \
class=""><br class="">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.</div><div class=""><br class=""></div><div \
class="">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.</div><div \
class=""><br class=""></div><div class="">And it's this that I'm asking \
about.</div><div class=""><br class=""></div><div class="">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()""</div><div class=""><br class=""></div><div class=""><font face="Consolas" \
class=""><span style="font-style: normal; font-size: 12px;" class="">create function \
series()<br class=""> returns table(v int)<br class=""> set \
search_path = s1, pg_catalog, pg_temp<br class=""> language plpgsql<br \
class="">as $body$<br class="">begin<br class=""> raise info 'series() \
invoked';<br class=""><br class=""> for v in (select generate_series(1, \
10))loop<br class=""> return next;<br class=""> end \
loop;<br class="">end;<br class="">$body$;</span></font></div><div class=""><br \
class=""></div><div class="">and</div><div class=""><br class=""></div><div \
class=""><font face="Consolas" class=""><span style="font-style: normal; font-size: \
12px;" class="">create function filter()<br class=""> returns boolean<br \
class=""> set search_path = pg_catalog, pg_temp<br \
class=""> language plpgsql<br class="">as $body$<br class="">begin<br \
class=""> raise info 'filter() invoked';<br class=""> return \
true;<br class="">end;<br class="">$body$;</span></font></div><div class=""><br \
class=""></div><div class="">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.</div><div class=""><br class=""></div><div class="">Here's \
what I saw:</div><div class=""><br class=""></div><div class=""><font face="Consolas" \
class=""><span style="font-style: normal; font-size: 14px;" class=""><b class="">* \
(1) open_holdable_cursor(holdable=>false, filter_series=>false, caption=>'') \
*</b></span></font></div><div class=""><br class=""></div><div class="">The \
"open_holdable_cursor()" call completes silently.</div><div class=""><br \
class=""></div><div class="">The first "cursor_rows()" invocation reports "series() \
invoked" once.</div><div class=""><br class=""></div><div \
class="">Subsequent "cursor_rows()" invocations produce their rows without that \
message.</div><div class=""><br class=""><font face="Consolas" class=""><span \
style="font-style: normal; font-size: 14px;" class=""><b class="">* (2) call \
open_holdable_cursor(holdable=>false, filter_series=>true, caption=>'') *<br \
class=""></b></span></font><br class=""></div><div class="">The \
"open_holdable_cursor()" call completes silently again.</div><div class=""><br \
class=""></div><div class=""><div class="">The first "cursor_rows()" invocation again \
reports "series() invoked" once. And then it reports "filter() invoked" ten \
times.</div><div class=""><br class=""></div><div class=""><div class="">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.</div><div class=""><br class=""></div><div class="">It's the \
same for the third invocation (and any more that I care to do).</div><div \
class=""><br class=""></div><div class=""><b class="">* (3) \
open_holdable_cursor(holdable=>true, filter_series=>false, caption=>'') \
*</b></div><div class=""><br class=""></div><div class="">The \
"open_holdable_cursor()" call now reports "series() invoked".</div><div class=""><br \
class=""></div><div class="">The first, and all subsequent, "cursor_rows()" \
invocations do not say "series() invoked".</div><div class=""><br \
class=""></div><div class=""><b class="">* \
(4) open_holdable_cursor(holdable=>true, filter_series=>true, \
caption=>'') *</b></div><div class=""><br class=""></div><div class="">The \
"open_holdable_cursor()" call now reports "series() invoked" followed \
by "filter() invoked" ten times.<br class=""><br class=""></div><div \
class=""><div class="">The first, and all subsequent, "cursor_rows()" invocations do \
not bring any "raise info" output because the *restricted* results are \
cached.</div></div></div></div><div class=""><br class=""></div><div class="">I hope \
that my question is clearer now.</div><div class=""><br class=""></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic