MIME-Version: 1.0
References: 
 <CAOjiQ0C7NEV9ZBFxbM6KgXnzquaUY3MrN1x8vz_tv7uXac0UwQ@mail.gmail.com>
 <689686.1764787308@sss.pgh.pa.us>
In-Reply-To: <689686.1764787308@sss.pgh.pa.us>
From: Tim Fors <tim4stheenchanter@gmail.com>
Date: Wed, 3 Dec 2025 14:45:54 -0500
Message-ID: 
 <CAOjiQ0Ca=A=bRQjdQMgWfZyav-HBMEhf_R9p72QoWFzZ2DC9vQ@mail.gmail.com>
Subject: Re: Client-server communication for FETCH
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000887eed0645117646"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOjiQ0Ca%3DA%3DbRQjdQMgWfZyav-HBMEhf_R9p72QoWFzZ2DC9vQ%40mail.gmail.com>
Precedence: bulk

--000000000000887eed0645117646
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thanks for the quick reply Tom.

Let me give you a bit of background info:

Oracle has a COBOL pre-processor for embedded SQL, and it supports an
option named PREFETCH:

The precompiler option PREFETCH allows for more efficient queries by
> pre-fetching rows. This decreases the number of server round-trips needed
> and reduces memory required. The number of rows set by the PREFETCH optio=
n
> value in a configuration file or on the command line is used for all
> queries involving explicit cursors, subject to the standard precedence
> rules. When used inline, the PREFETCH option must precede any of these
> cursor statements: =E2=80=A2 EXEC SQL OPEN cursor =E2=80=A2 EXEC SQL OPEN=
 cursor USING
> host_var_list =E2=80=A2 EXEC SQL OPEN cursor USING DESCRIPTOR desc_name W=
hen an
> OPEN is executed, the value of PREFETCH gives the number of rows to be
> prefetched when the query is executed. You can set the value from 0 (no
> pre-fetching) to 9999. The default value is 1.


The IBM compiler for COBOL on Linux x86 recently added support for PGSQL
(as a co-processor, integrated into the compiler). We have a business
partner with a COBOL application that contains a large number of single-row
FETCH statements, and they've noticed a significant performance degradation
when switching from Oracle to PGSQL, and it's almost certainly because
they're using that Oracle option PREFETCH. They are asserting that it's the
large amount of client-server exchanges to retrieve one row at a time that
is the problem in PGSQL.

As you can imagine, they're not wanting to rework their application(s) in
order to achieve the same performance - they want the single-row FETCH to
be as performant as Oracle. And I'm sure this same stance would be adopted
by other users of the compiler who want to use PGSQL as well. They have
loops in their programs which FETCH rows one at a time.  I was hoping that
the "chunked mode" you mentioned would achieve the equivalent of a
client-side cursor, but your answer indicates that it won't.

The IBM co-processor is a rewrite of ECPG, and therefore uses libecpg at
runtime, which of course uses libpq.

Do you have any further thoughts re: addressing this requirement
(client-side cursor support)? Is this a requirement that the PGSQL
community would consider implementing in libpq? Without it, it seems like a
huge impediment to adoption of PGSQL for COBOL applications, and as you
probably know, COBOL applications are still very pervasive. (C programs
using ECPG would of course hit the same issue, so it's not just a
COBOL-specific problem.)

Thanks,

Tim

On Wed, Dec 3, 2025 at 1:41=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Tim Fors <tim4stheenchanter@gmail.com> writes:
> > I have a simple C program using libpq V18 to FETCH one row at a time (s=
ee
> > below). Does the server send multiple rows to the client where they are
> > cached for satisfying the FETCH statements, or does it just send one ro=
w
> at
> > a time since that's all that each FETCH statement is asking for?
>
> It's sending one row at a time.  You could improve that by asking
> for more than one row ("FETCH n" not just "FETCH"), but you'd have
> to iterate over the rows returned by each command.
>
> Another alternative is to forget about using a cursor, tell the
> server to just execute the query, but use libpq's "chunked results"
> mode to process rows before all of the query result has arrived.
> See
>
> https://www.postgresql.org/docs/current/libpq-single-row-mode.html
>
> > I have done extensive searching to try and find the definitive answer t=
o
> > this. The searches indicate that libpq supports the concept of a
> > client-side cursor, where it has a cache of rows sent by the server and
> > uses that cache to perform each FETCH, but I'd like to be able to verif=
y
> > whether this is true or not.
>
> Don't know where you read that, but it's not true of libpq.  There
> might be other Postgres client libraries that can do that.
>
>                         regards, tom lane
>

--000000000000887eed0645117646
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thanks for the quick reply Tom.<div><br></div><div>Let me =
give you a bit of background info:</div><div><br></div><div>Oracle has a CO=
BOL pre-processor for embedded SQL, and it supports an option named PREFETC=
H:=C2=A0=C2=A0</div><div><br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">The precompiler option PREFETCH allows for more efficient querie=
s by pre-fetching rows. This
decreases the number of server round-trips needed and reduces memory requir=
ed. The
number of rows set by the PREFETCH option value in a configuration file or =
on the command
line is used for all queries involving explicit cursors, subject to the sta=
ndard precedence rules.
When used inline, the PREFETCH option must precede any of these cursor stat=
ements:
=E2=80=A2 EXEC SQL OPEN cursor
=E2=80=A2 EXEC SQL OPEN cursor USING host_var_list
=E2=80=A2 EXEC SQL OPEN cursor USING DESCRIPTOR desc_name
When an OPEN is executed, the value of PREFETCH gives the number of rows to=
 be prefetched when the query is executed. You can set the value from 0 (no=
 pre-fetching) to 9999.
The default value is 1.</blockquote><div><br></div><div>The IBM compiler fo=
r COBOL on Linux x86 recently added support for PGSQL (as a co-processor, i=
ntegrated into the compiler). We have a business partner with a COBOL appli=
cation that contains a large number of single-row FETCH statements, and the=
y&#39;ve noticed a significant performance degradation when switching from =
Oracle to PGSQL, and it&#39;s almost certainly because they&#39;re using th=
at Oracle option PREFETCH. They are asserting that it&#39;s the large amoun=
t of client-server exchanges to retrieve one row at a time that is the prob=
lem in PGSQL.</div><div><br></div><div>As you can imagine, they&#39;re not =
wanting to rework their application(s) in order to achieve the same perform=
ance - they want the single-row FETCH to be as performant as Oracle. And I&=
#39;m sure this same stance would be adopted by other users of the compiler=
 who want to use PGSQL as well. They have loops in their programs which FET=
CH rows one at a time.=C2=A0 I was hoping that the &quot;chunked mode&quot;=
 you mentioned would achieve the=C2=A0equivalent of a client-side cursor, b=
ut your answer indicates that it won&#39;t.</div><div><br></div><div>The IB=
M co-processor is a rewrite of ECPG, and therefore uses libecpg at runtime,=
 which of course uses libpq.</div><div><br></div><div>Do you have any furth=
er thoughts re: addressing this requirement (client-side cursor support)? I=
s this a requirement that the PGSQL community would consider implementing i=
n libpq? Without it, it seems like a huge impediment to adoption of PGSQL f=
or COBOL applications, and as you probably know, COBOL applications are sti=
ll very pervasive. (C programs using ECPG would of course hit the same issu=
e, so it&#39;s not just a COBOL-specific problem.)</div><div><br></div><div=
>Thanks,</div><div><br></div><div>Tim</div></div><br><div class=3D"gmail_qu=
ote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, De=
c 3, 2025 at 1:41=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.=
us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">Tim Fors &lt;<a href=3D"mailto:tim4stheenchanter@gmail=
.com" target=3D"_blank">tim4stheenchanter@gmail.com</a>&gt; writes:<br>
&gt; I have a simple C program using libpq V18 to FETCH one row at a time (=
see<br>
&gt; below). Does the server send multiple rows to the client where they ar=
e<br>
&gt; cached for satisfying the FETCH statements, or does it just send one r=
ow at<br>
&gt; a time since that&#39;s all that each FETCH statement is asking for?<b=
r>
<br>
It&#39;s sending one row at a time.=C2=A0 You could improve that by asking<=
br>
for more than one row (&quot;FETCH n&quot; not just &quot;FETCH&quot;), but=
 you&#39;d have<br>
to iterate over the rows returned by each command.<br>
<br>
Another alternative is to forget about using a cursor, tell the<br>
server to just execute the query, but use libpq&#39;s &quot;chunked results=
&quot;<br>
mode to process rows before all of the query result has arrived.<br>
See<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/libpq-single-row-mode.ht=
ml" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/cu=
rrent/libpq-single-row-mode.html</a><br>
<br>
&gt; I have done extensive searching to try and find the definitive answer =
to<br>
&gt; this. The searches indicate that libpq supports the concept of a<br>
&gt; client-side cursor, where it has a cache of rows sent by the server an=
d<br>
&gt; uses that cache to perform each FETCH, but I&#39;d like to be able to =
verify<br>
&gt; whether this is true or not.<br>
<br>
Don&#39;t know where you read that, but it&#39;s not true of libpq.=C2=A0 T=
here<br>
might be other Postgres client libraries that can do that.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000887eed0645117646--