Jakub Wartak wrote: > 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.
Yes, that's why WITH queries are currently filtered out by the FETCH_COUNT feature. Case in point: test=> begin; BEGIN test=> create table tbl(i int); CREATE TABLE test=> declare psql_cursor cursor for with r(i) as (values (1)) insert into tbl(i) select i from r; ERROR: syntax error at or near "insert" LINE 3: insert into tbl(i) select i from r; So the fix you're proposing would fail on that kind of queries. A solution would be for psql to use PQsetSingleRowMode() to retrieve results row-by-row, as opposed to using a cursor, and then allocate memory for only FETCH_COUNT rows at a time. Incidentally it solves other problems like queries containing multiple statements, that also fail to work properly with cursors, or UPDATE/INSERT... RETURNING.. on large number of rows that could also benefit from pagination in memory. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
