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

On Fri, Mar 31, 2023 at 12:35 PM Bryn Llewellyn <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.

David J.