Обсуждение: ECPG support for PQsetChunkedRowsMode()
Hello,
If this is not the right mailing list for this question please redirect me to the right one.
The "chunked rows mode" appears to have been added to libpq in V17.
Does ECPG support this mode, and if not, are there plans to do so in the near future?
Also, is there any data available on performance improvements that one might see by using this mode?
Thanks,
Tim
Tim Fors wrote: > The "chunked rows mode" appears to have been added to > libpq in V17. > > Does ECPG support this mode No. It's not clear how it would fit. This mode implies the use of PQsendQuery(), which does not seem to have an equivalent in ECPG. > are there plans to do so in the near future? Postgres as a project does not have a roadmap, but what's being worked on at any point in time can generally be tracked at https://commitfest.postgresql.org > Also, is there any data available on performance improvements that > one might see by using this mode? In [1], the time to retrieve rows by psql was compared between the the chunked rows mode and the cursor-based implementation, the latter being what would typically be used by an ECPG app. [1]: https://postgr.es/m/202bae70-f60d-4d24-a8d8-50b4ed638887@manitou-mail.org Best regards, -- Daniel Vérité https://postgresql.verite.pro/
Thanks for the info Daniel.
The reason I'm asking in the context of ECPG is this: When using a cursor to FETCH one row of a result set at a time, it seems that the server literally sends just one record to the client at a time, so using a cursor in this context results in a lot of communication between the client and server in order to process a large number of rows. I'm wondering if this is accurate and if so, is there a way to improve performance by enabling some kind of buffering or caching on the client side, while still allowing the cursor to function as normal.
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.
When I saw the addition of this "chunked rows mode" I thought perhaps it would enable faster cursor usage. Since your answer indicates that it would not, do you have any other recommendations to limit the effect of network latency in order to speed up retrieval of rows via a cursor?
Thanks,
Tim
On Wed, Nov 19, 2025 at 1:44 PM Daniel Verite <daniel@manitou-mail.org> wrote:
Tim Fors wrote:
> The "chunked rows mode" appears to have been added to
> libpq in V17.
>
> Does ECPG support this mode
No. It's not clear how it would fit. This mode implies the use
of PQsendQuery(), which does not seem to have an equivalent in
ECPG.
> are there plans to do so in the near future?
Postgres as a project does not have a roadmap, but
what's being worked on at any point in time can
generally be tracked at https://commitfest.postgresql.org
> Also, is there any data available on performance improvements that
> one might see by using this mode?
In [1], the time to retrieve rows by psql was compared between the
the chunked rows mode and the cursor-based implementation, the
latter being what would typically be used by an ECPG app.
[1]:
https://postgr.es/m/202bae70-f60d-4d24-a8d8-50b4ed638887@manitou-mail.org
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
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érité https://postgresql.verite.pro/
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 AM 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érité
https://postgresql.verite.pro/