Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1piMgV-0004Yf-QO for pgsql-general@arkaria.postgresql.org; Fri, 31 Mar 2023 21:53:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1piMgU-0006LE-5H for pgsql-general@arkaria.postgresql.org; Fri, 31 Mar 2023 21:53:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1piMgT-0006KY-GO for pgsql-general@lists.postgresql.org; Fri, 31 Mar 2023 21:53:21 +0000 Received: from mail-pj1-x1032.google.com ([2607:f8b0:4864:20::1032]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1piMgP-0004qm-Go for pgsql-general@lists.postgresql.org; Fri, 31 Mar 2023 21:53:20 +0000 Received: by mail-pj1-x1032.google.com with SMTP id r7-20020a17090b050700b002404be7920aso22830774pjz.5 for ; Fri, 31 Mar 2023 14:53:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; t=1680299596; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=Wxuc+0KWcObJCYKZ4+Mr2F3LzJfmElGdKedDx8XmGrs=; b=OV8Rma7vwMHwHoXLD4WKdsJV37xydNClq4AkTbsv4gPyImUS/zUr2VMoEDPLyoZwWr UPuYP5zj0N/afZSiTRJcEaEAF9+Ny1ymARYJt+oeJSD0FLzu5/LzCYVZKZO/+nRUSt1a 7FN+V6d/IIzccZVxkAP2bez6ORVWmIyjuBf34= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680299596; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Wxuc+0KWcObJCYKZ4+Mr2F3LzJfmElGdKedDx8XmGrs=; b=IdkzJvsJboNVfZA3iaFzEUBgdqrhESR1FsXC0rdAKDyItVcHvQz9rAFM8/PPAJwAeV j4vFRd9UrhjDLLHO0PTKv29e8eaAFgcpqlGAbNWWhA3+Lvp2aCNCCyqL5SSz9NuQLokQ CmvITn01hifyrdKAskhOnYek1mQWQd014OUw95wwfvRCA62UGurpokZ8euwD1/SznI2k X+4sNX/4bjgfQ19N+DZSPuUPtNGkKWBl0OJmpuJWklcoFggUfDGtLyXLYLF5T5/Cocgx dP/mIauce5rqF4wYk1oW4assmkuga/bqe+2zoLSQtPeXPgc+09Za5Re5Txrx2gm7UjfK h4dw== X-Gm-Message-State: AAQBX9cpEJ1DkerQOVd2JyRvrhrShw/nBOWSGFXpvyVxO9snhN5BxBgV MqYrQdGqz57guOmnsHg3K1VWSA== X-Google-Smtp-Source: AKy350ZkNzkib9ziI3UHGHiaz2BIzL2TxtNaeUJ3hA5Zvw5h4XYmpKz+QbXIU1Ub6wF/kn5Z3JaT9w== X-Received: by 2002:a17:902:fa4e:b0:1a2:57c4:2a7a with SMTP id lb14-20020a170902fa4e00b001a257c42a7amr14893870plb.29.1680299596356; Fri, 31 Mar 2023 14:53:16 -0700 (PDT) Received: from smtpclient.apple ([2601:646:101:7fda:451b:f395:1620:c6d3]) by smtp.gmail.com with ESMTPSA id je5-20020a170903264500b0019ee042602bsm2040602plb.92.2023.03.31.14.53.15 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 31 Mar 2023 14:53:15 -0700 (PDT) From: Bryn Llewellyn Message-Id: <151C11C4-D783-433D-BF75-499607D6CAC3@yugabyte.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_A5B99DF7-DF26-4CD0-BBA5-FD66BD3736D1" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.14\)) Subject: Re: My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor Date: Fri, 31 Mar 2023 14:53:14 -0700 In-Reply-To: Cc: pgsql-general list , laurenz.albe@cybertec.at To: "David G. Johnston" References: <76A5F3E1-DB5F-4DA7-AF29-687D511E3948@yugabyte.com> X-Mailer: Apple Mail (2.3654.120.0.1.14) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_A5B99DF7-DF26-4CD0-BBA5-FD66BD3736D1 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > david.g.johnston@gmail.com wrote: >=20 >> bryn@yugabyte.com wrote: >>=20 >> *Summary* >>=20 >> 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=E2=80=99s = the *restricted* result set that=E2=80=99s cached. >>=20 >> 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=E2=80=94= and this seems to be a bad thing for space use and for speed. >=20 > 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=E2=80=94= 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 =3D 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 =3D 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 =E2=80=94= of course. Here's what I saw: * (1) open_holdable_cursor(holdable=3D>false, filter_series=3D>false, = caption=3D>'') * 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=3D>false, filter_series=3D>true, = caption=3D>'') * 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=3D>true, filter_series=3D>false, = caption=3D>'') * 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=3D>true, filter_series=3D>true, = caption=3D>'') * 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. --Apple-Mail=_A5B99DF7-DF26-4CD0-BBA5-FD66BD3736D1 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=E2=80=99s the *restricted* = result set that=E2=80=99s 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=E2=80=94and 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=E2=80=94up 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 =3D 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 =3D 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 =E2=80=94 of = course.

Here's = what I saw:

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

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=3D>false, filter_series=3D>true, = caption=3D>'') *

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=3D>true, = filter_series=3D>false, caption=3D>'') *

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=3D>true, = filter_series=3D>true, caption=3D>'') *
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.

= --Apple-Mail=_A5B99DF7-DF26-4CD0-BBA5-FD66BD3736D1--