From: Daniel Verite <daniel@manitou-mail.org>
To: Jakub Wartak <jakub.wartak@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: psql's FETCH_COUNT (cursor) is not being respected for CTEs
Date: Wed, 04 Jan 2023 16:22:03 +0100
Message-ID: <3a3451a2-7d05-4dd7-b7ac-e75d2accf327@manitou-mail.org> (raw)
In-Reply-To: <CAKZiRmxsVTkO928CM+-ADvsMyePmU3L9DQCa9NwqjvLPcEe5QA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: daniel@manitou-mail.org, jakub.wartak@enterprisedb.com
Subject: Re: psql's FETCH_COUNT (cursor) is not being respected for CTEs
In-Reply-To: <3a3451a2-7d05-4dd7-b7ac-e75d2accf327@manitou-mail.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox