cursor with hold must be save to disk?

On 4/14/23 04:04, 黄宁 wrote:

i want to use cursor with hold ,but when I declare a curosr , it takes a
long time to save the result set to disk. can i save the query state in
memory? and fetch forward the next result.

From the docs:

https://www.postgresql.org/docs/current/sql-declare.html

A cursor created with WITH HOLD is closed when an explicit CLOSE command
is issued on it, or the session ends. In the current implementation, the
rows represented by a held cursor are copied into a temporary file or
memory area so that they remain available for subsequent transactions.

So I am going to guess the cursor query is holding a large amount of data.

To get a more specific answer you will need to provide:

1) Postgres version.

2) The complete DECLARE command being used.

3) An indication of the amount of data being retrieved.

4) The actual time for a 'long time'.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 2023-04-14 at 19:04 +0800, 黄宁 wrote:

i want to use cursor with hold ,but when I declare a curosr , it takes a long
time to save the result set to disk. can i save the query state in memory?
and fetch forward the next result.

The complete result set has to be materialized. It only spills to disk if it
is large. That cannot be avoided.

Yours,
Laurenz Albe

the Postgresql version is 13.6
and the DECLARE COMMAND IS

declare sdx_3a6c_8 no scroll binary cursor without hold for select
"roalkL"."smid","roalkL"."smgeometry" from "public"."roalkL" where
"roalkL"."smgeometry" &&
st_makeenvelope(321673.3153346270555630,3375950.6560412631370127,367212.1915803211741149,3402758.1912380573339760,32649)

the data might be 1GB,and we need get all in about 10 seconds.

Adrian Klaver <adrian.klaver@aklaver.com> 于2023年4月14日周五 23:11写道：