MIME-Version: 1.0
References: 
 <CAOjiQ0C8O4g_KcVmOMq_+WJy+YCugBYe8A=RsZgHGP1nfhQG=g@mail.gmail.com>
 <d22379c8-7777-4ba1-9261-89ab5f5de2dd@manitou-mail.org>
In-Reply-To: <d22379c8-7777-4ba1-9261-89ab5f5de2dd@manitou-mail.org>
From: Tim Fors <tim4stheenchanter@gmail.com>
Date: Mon, 24 Nov 2025 13:58:43 -0500
Message-ID: 
 <CAOjiQ0CyG8xHu+suD-Pa7dS=PnHLmx7Y6c7cwCtTyZL0hbBo_g@mail.gmail.com>
Subject: Re: ECPG support for PQsetChunkedRowsMode()
To: Daniel Verite <daniel@manitou-mail.org>
Cc: pgsql-interfaces@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003e736106445bc1b3"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOjiQ0CyG8xHu%2BsuD-Pa7dS%3DPnHLmx7Y6c7cwCtTyZL0hbBo_g%40mail.gmail.com>
Precedence: bulk

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

Thanks again for your replies Daniel.

Let me provide a bit more background info: I work on a COBOL compiler on
Linux x86 which now has support for PGSQL.

We took the ECPG pre-processor and modified it to work with COBOL, and we
integrated it into the compiler so that translation of SQL statements is
done during the compilation of the COBOL program instead of requiring that
that translation be done as a separate step prior to compilation. We refer
to this as a *co*-processor as opposed to a pre-processor (which is what
ECPG is). Because it is based on a modified version of ECPG, it leverages
the ECPG library at runtime (i.e. when the COBOL program runs) to manage
all communication between the client and the server.

We have a business partner who is using our compiler and its PGSQL support.
Their application had previously used Oracle as the DBMS, but now also
supports PGSQL.

When it comes to *single-row FETCH statements*, the business partner is
reporting *considerably worse performance with PGSQL than with Oracle*.
They expect comparable performance without having to change their SQL
statements and COBOL code to do a fetch into a COBOL array and then iterate
through that array, nor use an SQLDA.

They have indicated that the difference in performance is because every
FETCH statement has to go to the server to retrieve a row, whereas with
Oracle they believe that a single-row FETCH actually retrieves multiple
rows from the server under the covers, and there is logic on the client
side to then perform the FETCH statements using the rows cached in the
client rather than having to go to the server each time. I am going to
verify this for myself, as well as examine how Db2 provides good
performance for single-row FETCH statements.

Further, they claim that the Oracle pre-processor is where that logic is
being inserted into the COBOL program i.e. under the covers that generated
code is handling the caching of rows on the client and supporting the
required FETCH semantics using that cache.

As a COBOL compiler developer, I'm very hesitant to try and modify our
co-processor to implement this kind of client-side cursor
management, caching rows on the client side while still providing all of
the expected semantics associated with the FETCH statement. IMO cursor
support is a crucial and non-trivial piece of the overall application
support that a DBMS needs to provide, and it is not wise for a particular
client to try and take these matters into their own hands. Row locking is
just one concern that would need to be addressed - the FETCH statement
would still be getting one row at a time but under the covers we'd be
retrieving multiple rows, and it's not clear what kind of locking
implications where might be, among a host of other possible "gotchas".

But we seem to be between a rock and a hard place because PGSQL does not
appear to have any support for single-row FETCH via cursors which would
yield the same performance as Oracle. This is why I was hoping that there
would be some way to influence PGSQL behaviour in this respect that I have
overlooked. Hence my question re: "chunked rows" mode, which based on your
reply does not do what I was looking for.

Since you mentioned SQLDA, is that the official PGSQL approach that the
business partner should be using? I know up front that they won't like that
answer, because it means a variation in their application code for PGSQL vs
Oracle, but if PGSQL provides no other way to improve single-row FETCH
performance then I will mention it to them and see what they say.

Please let me know if you have any more thoughts on this, or if more
clarification from me would help. And of course, if there's a more
appropriate mailing list for this issue let me know that too.

Thanks,

Tim

On Mon, Nov 24, 2025 at 7:41=E2=80=AFAM Daniel Verite <daniel@manitou-mail.=
org>
wrote:

>         Tim Fors wrote:
>
> > Note that ECPG supports using a C array as a host variable, and in
> > that case there's a bulk transfer of rows from the client to the
> > server, so performance is much better than getting the rows one at a
> > time using a cursor. However, AFAIK this is not standard coding
> > practice - one expects to be able to use the cursor to FETCH one
> > row at a time, but still offer good performance.
>
> It's just a preprocessor. If the C code has EXEC SQL FETCH NEXT, it's
> going to emit code sending a FETCH NEXT [1] SQL statement.
>
> But aside from the arrays you mention, it allows retrieving several
> rows at a time through a cursor, so the client-side can still reduce
> the round-trips to the server. See this example in [2]:
>  EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;
>
> [1]: https://www.postgresql.org/docs/current/sql-fetch.html
>
> [2]: https://www.postgresql.org/docs/current/ecpg-descriptors.html
>
>
> Best regards,
> --
> Daniel V=C3=A9rit=C3=A9
> https://postgresql.verite.pro/
>

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

<div dir=3D"ltr">Thanks again for your replies Daniel.<div><br></div><div>L=
et me provide a bit more background info: I work on a COBOL compiler on Lin=
ux x86 which now has support for PGSQL.=C2=A0</div><div><br></div><div>We t=
ook the ECPG pre-processor and modified it to work with COBOL, and we integ=
rated it into the compiler so that translation of SQL statements is done du=
ring the compilation of the COBOL program instead of requiring that that tr=
anslation be done as a separate step prior to compilation. We refer to this=
 as a <b>co</b>-processor as opposed to a pre-processor (which is what ECPG=
 is). Because it is based on a modified version of ECPG, it leverages the E=
CPG library at runtime (i.e. when the COBOL program runs) to manage all com=
munication between the client and the server.</div><div><br></div><div>We h=
ave a business partner who is using our compiler and its PGSQL support. The=
ir application had previously used Oracle as the DBMS, but now also support=
s=C2=A0PGSQL.=C2=A0</div><div><br></div><div>When it comes to <b>single-row=
 FETCH statements</b>, the business partner is reporting <b>considerably wo=
rse performance with PGSQL than with Oracle</b>. They expect comparable per=
formance without having to change their SQL statements and COBOL code to do=
 a fetch into a COBOL array and then iterate through that array, nor use an=
 SQLDA.</div><div><br></div><div>They have indicated that the difference in=
 performance is because every FETCH statement has to go to the server to re=
trieve a row, whereas with Oracle they believe that a single-row FETCH actu=
ally retrieves multiple rows from the server under the covers, and there is=
 logic on the client side to then perform the FETCH statements using the ro=
ws cached in the client rather than having to go to the server each time. I=
 am going to verify this for myself, as well as examine how Db2 provides go=
od performance for single-row FETCH statements.</div><div><br></div><div>Fu=
rther, they claim that the Oracle pre-processor is where that logic is bein=
g inserted into the COBOL program i.e. under the covers that generated code=
 is handling the caching of rows on the client and supporting the required =
FETCH semantics using that cache.=C2=A0</div><div><br></div><div>As a COBOL=
 compiler developer, I&#39;m very hesitant to try and modify our co-process=
or to implement this kind of client-side cursor management,=C2=A0caching ro=
ws on the client side while still providing all of the expected semantics a=
ssociated with the FETCH statement. IMO cursor support is a crucial and non=
-trivial piece of the overall application support that a DBMS needs to prov=
ide, and it is not wise for a particular client to try and take these matte=
rs into their own hands. Row locking is just one concern that would need to=
 be addressed - the FETCH statement would still be getting one row at a tim=
e but under the covers we&#39;d be retrieving multiple rows, and it&#39;s n=
ot clear what kind of locking implications where might be, among a host of =
other possible &quot;gotchas&quot;.</div><div><br></div><div>But we seem to=
 be between a rock and a hard place because PGSQL does not appear to have a=
ny support for single-row FETCH via cursors which would yield the same perf=
ormance as Oracle. This is why I was hoping that there would be some way to=
 influence PGSQL behaviour in this respect that I have overlooked. Hence my=
 question re: &quot;chunked rows&quot; mode, which based on your reply does=
 not do what I was looking for.=C2=A0</div><div><br></div><div>Since you me=
ntioned SQLDA, is that the official PGSQL approach that the business partne=
r should be using? I know up front that they won&#39;t like that answer, be=
cause it means a variation in their application code for PGSQL vs Oracle, b=
ut if PGSQL provides no other way to improve single-row FETCH performance t=
hen I will mention it to them and see what they say.</div><div><br></div><d=
iv>Please let me know if you have any more thoughts on this, or if more cla=
rification from me would help. And of course, if there&#39;s a more appropr=
iate mailing list for this issue let me know that too.</div><div><br></div>=
<div>Thanks,</div><div><br></div><div>Tim</div></div><br><div class=3D"gmai=
l_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon=
, Nov 24, 2025 at 7:41=E2=80=AFAM Daniel Verite &lt;<a href=3D"mailto:danie=
l@manitou-mail.org">daniel@manitou-mail.org</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex">=C2=A0 =C2=A0 =C2=A0 =C2=A0 Tim =
Fors wrote:<br>
<br>
&gt; Note that ECPG supports using a C array as a host variable, and in<br>
&gt; that case there&#39;s a bulk transfer of rows from the client to the<b=
r>
&gt; server, so performance is much better than getting the rows one at a<b=
r>
&gt; time using a cursor. However, AFAIK this is not standard coding<br>
&gt; practice - one expects to be able to use the cursor to FETCH one<br>
&gt; row at a time, but still offer good performance.<br>
<br>
It&#39;s just a preprocessor. If the C code has EXEC SQL FETCH NEXT, it&#39=
;s<br>
going to emit code sending a FETCH NEXT [1] SQL statement.<br>
<br>
But aside from the arrays you mention, it allows retrieving several<br>
rows at a time through a cursor, so the client-side can still reduce<br>
the round-trips to the server. See this example in [2]:<br>
=C2=A0EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;<br>
<br>
[1]: <a href=3D"https://www.postgresql.org/docs/current/sql-fetch.html" rel=
=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/s=
ql-fetch.html</a><br>
<br>
[2]: <a href=3D"https://www.postgresql.org/docs/current/ecpg-descriptors.ht=
ml" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/cu=
rrent/ecpg-descriptors.html</a><br>
<br>
<br>
Best regards,<br>
-- <br>
Daniel V=C3=A9rit=C3=A9 <br>
<a href=3D"https://postgresql.verite.pro/" rel=3D"noreferrer" target=3D"_bl=
ank">https://postgresql.verite.pro/</a><br>
</blockquote></div>

--0000000000003e736106445bc1b3--