Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: Re: psql's FETCH_COUNT (cursor) is not being respected for CTEs
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Jakub Wartak" <jakub.wartak@enterprisedb.com>
Cc: "PostgreSQL Hackers" <pgsql-hackers@postgresql.org>
In-Reply-To: 
 <CAKZiRmxsVTkO928CM+-ADvsMyePmU3L9DQCa9NwqjvLPcEe5QA@mail.gmail.com>
Date: Wed, 04 Jan 2023 16:22:03 +0100
Message-Id: <3a3451a2-7d05-4dd7-b7ac-e75d2accf327@manitou-mail.org>
Archived-At: 
 <https://www.postgresql.org/message-id/3a3451a2-7d05-4dd7-b7ac-e75d2accf327%40manitou-mail.org>
Precedence: bulk

	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=3D> begin;
BEGIN

test=3D> create table tbl(i int);
CREATE TABLE

test=3D> 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,
--=20
Daniel V=E9rit=E9
https://postgresql.verite.pro/
Twitter: @DanielVerite