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.
0001-psql-allow-CTE-queries-to-be-executed-also-using-cur.patch
Description: Binary data