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