psql's FETCH_COUNT (cursor) is not being respected for CTEs - Mailing list pgsql-hackers

From Jakub Wartak
Subject psql's FETCH_COUNT (cursor) is not being respected for CTEs
Date
Msg-id CAKZiRmxsVTkO928CM+-ADvsMyePmU3L9DQCa9NwqjvLPcEe5QA@mail.gmail.com
Whole thread Raw
Responses Re: psql's FETCH_COUNT (cursor) is not being respected for CTEs
List pgsql-hackers
Hi -hackers,

I've spent some time fighting against "out of memory" errors coming
out of psql when trying to use the cursor via FETCH_COUNT. It might be
a not so well known fact (?) that CTEs are not executed with cursor
when asked to do so, but instead silently executed with potential huge
memory allocation going on. Patch is attached. My one doubt is that
not every statement starting with "WITH" is WITH(..) SELECT of course.

Demo (one might also get the "out of memory for query result"):

postgres@hive:~$ psql -Ant --variable='FETCH_COUNT=100' -c "WITH data
AS (SELECT  generate_series(1, 20000000) as Total) select repeat('a',
100) || data.Total || repeat('b', 800) as total_pat from data;"
Killed
postgres@hive:~$ tail -4 /var/log/postgresql/postgresql-14-main.log
[..]
2023-01-04 12:46:20.193 CET [32936] postgres@postgres LOG:  could not
send data to client: Broken pipe
[..]
2023-01-04 12:46:20.195 CET [32936] postgres@postgres FATAL:
connection to client lost

With the patch:
postgres@hive:~$ /tmp/psql16-with-patch -Ant
--variable='FETCH_COUNT=100' -c "WITH data AS (SELECT
generate_series(1, 20000000) as Total) select repeat('a', 100) ||
data.Total || repeat('b', 800) as total_pat from data;" | wc -l
20000000
postgres@hive:~$

Regards,
-Jakub Wartak.

Attachment

pgsql-hackers by date:

Previous
From: Ankit Kumar Pandey
Date:
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal order
Next
From: vignesh C
Date:
Subject: Re: Using AF_UNIX sockets always for tests on Windows