Skip to content

Commit 4643a2b

Browse files
tglsfdctglsfdc
committed
Support retrieval of results in chunks with libpq.
This patch generalizes libpq's existing single-row mode to allow individual partial-result PGresults to contain up to N rows, rather than always one row. This reduces malloc overhead compared to plain single-row mode, and it is very useful for psql's FETCH_COUNT feature, since otherwise we'd have to add code (and cycles) to either merge single-row PGresults into a bigger one or teach psql's results-printing logic to accept arrays of PGresults. To avoid API breakage, PQsetSingleRowMode() remains the same, and we add a new function PQsetChunkedRowsMode() to invoke the more general case. Also, PGresults obtained the old way continue to carry the PGRES_SINGLE_TUPLE status code, while if PQsetChunkedRowsMode() is used then their status code is PGRES_TUPLES_CHUNK. The underlying logic is the same either way, though. Daniel Vérité, reviewed by Laurenz Albe and myself (and whacked around a bit by me, so any remaining bugs are my fault) Discussion: https://postgr.es/m/CAKZiRmxsVTkO928CM+-ADvsMyePmU3L9DQCa9NwqjvLPcEe5QA@mail.gmail.com
1 parent 92641d8 commit 4643a2b

File tree

10 files changed

+243
-86
lines changed

10 files changed

+243
-86
lines changed

‎doc/src/sgml/libpq.sgml

Lines changed: 80 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -3588,6 +3588,20 @@ ExecStatusType PQresultStatus(const PGresult *res);
35883588
</listitem>
35893589
</varlistentry>
35903590

3591+
<varlistentry id="libpq-pgres-tuples-chunk">
3592+
<term><literal>PGRES_TUPLES_CHUNK</literal></term>
3593+
<listitem>
3594+
<para>
3595+
The <structname>PGresult</structname> contains several result tuples
3596+
from the current command. This status occurs only when
3597+
chunked mode has been selected for the query
3598+
(see <xref linkend="libpq-single-row-mode"/>).
3599+
The number of tuples will not exceed the limit passed to
3600+
<xref linkend="libpq-PQsetChunkedRowsMode"/>.
3601+
</para>
3602+
</listitem>
3603+
</varlistentry>
3604+
35913605
<varlistentry id="libpq-pgres-pipeline-sync">
35923606
<term><literal>PGRES_PIPELINE_SYNC</literal></term>
35933607
<listitem>
@@ -3617,8 +3631,9 @@ ExecStatusType PQresultStatus(const PGresult *res);
36173631

36183632
</variablelist>
36193633

3620-
If the result status is <literal>PGRES_TUPLES_OK</literal> or
3621-
<literal>PGRES_SINGLE_TUPLE</literal>, then
3634+
If the result status is <literal>PGRES_TUPLES_OK</literal>,
3635+
<literal>PGRES_SINGLE_TUPLE</literal>, or
3636+
<literal>PGRES_TUPLES_CHUNK</literal>, then
36223637
the functions described below can be used to retrieve the rows
36233638
returned by the query. Note that a <command>SELECT</command>
36243639
command that happens to retrieve zero rows still shows
@@ -4030,7 +4045,9 @@ void PQclear(PGresult *res);
40304045
These functions are used to extract information from a
40314046
<structname>PGresult</structname> object that represents a successful
40324047
query result (that is, one that has status
4033-
<literal>PGRES_TUPLES_OK</literal> or <literal>PGRES_SINGLE_TUPLE</literal>).
4048+
<literal>PGRES_TUPLES_OK</literal>,
4049+
<literal>PGRES_SINGLE_TUPLE</literal>, or
4050+
<literal>PGRES_TUPLES_CHUNK</literal>).
40344051
They can also be used to extract
40354052
information from a successful Describe operation: a Describe's result
40364053
has all the same column information that actual execution of the query
@@ -5235,7 +5252,8 @@ PGresult *PQgetResult(PGconn *conn);
52355252
<para>
52365253
Another frequently-desired feature that can be obtained with
52375254
<xref linkend="libpq-PQsendQuery"/> and <xref linkend="libpq-PQgetResult"/>
5238-
is retrieving large query results a row at a time. This is discussed
5255+
is retrieving large query results a limited number of rows at a time.
5256+
This is discussed
52395257
in <xref linkend="libpq-single-row-mode"/>.
52405258
</para>
52415259

@@ -5599,15 +5617,6 @@ int PQflush(PGconn *conn);
55995617
queries in the pipeline; see <xref linkend="libpq-pipeline-interleave"/>.
56005618
</para>
56015619

5602-
<para>
5603-
To enter single-row mode, call <function>PQsetSingleRowMode</function>
5604-
before retrieving results with <function>PQgetResult</function>.
5605-
This mode selection is effective only for the query currently
5606-
being processed. For more information on the use of
5607-
<function>PQsetSingleRowMode</function>,
5608-
refer to <xref linkend="libpq-single-row-mode"/>.
5609-
</para>
5610-
56115620
<para>
56125621
<function>PQgetResult</function> behaves the same as for normal
56135622
asynchronous processing except that it may contain the new
@@ -5972,36 +5981,49 @@ UPDATE mytable SET x = x + 1 WHERE id = 42;
59725981
</sect2>
59735982
</sect1>
59745983

5984+
<!-- keep this not-too-apropos sect1 ID for stability of doc URLs -->
59755985
<sect1 id="libpq-single-row-mode">
5976-
<title>Retrieving Query Results Row-by-Row</title>
5986+
<title>Retrieving Query Results in Chunks</title>
59775987

59785988
<indexterm zone="libpq-single-row-mode">
59795989
<primary>libpq</primary>
59805990
<secondary>single-row mode</secondary>
59815991
</indexterm>
59825992

5993+
<indexterm zone="libpq-single-row-mode">
5994+
<primary>libpq</primary>
5995+
<secondary>chunked mode</secondary>
5996+
</indexterm>
5997+
59835998
<para>
59845999
Ordinarily, <application>libpq</application> collects an SQL command's
59856000
entire result and returns it to the application as a single
59866001
<structname>PGresult</structname>. This can be unworkable for commands
59876002
that return a large number of rows. For such cases, applications can use
59886003
<xref linkend="libpq-PQsendQuery"/> and <xref linkend="libpq-PQgetResult"/> in
5989-
<firstterm>single-row mode</firstterm>. In this mode, the result row(s) are
5990-
returned to the application one at a time, as they are received from the
5991-
server.
6004+
<firstterm>single-row mode</firstterm> or <firstterm>chunked
6005+
mode</firstterm>. In these modes, result row(s) are returned to the
6006+
application as they are received from the server, one at a time for
6007+
single-row mode or in groups for chunked mode.
59926008
</para>
59936009

59946010
<para>
5995-
To enter single-row mode, call <xref linkend="libpq-PQsetSingleRowMode"/>
6011+
To enter one of these modes, call <xref linkend="libpq-PQsetSingleRowMode"/>
6012+
or <xref linkend="libpq-PQsetChunkedRowsMode"/>
59966013
immediately after a successful call of <xref linkend="libpq-PQsendQuery"/>
59976014
(or a sibling function). This mode selection is effective only for the
59986015
currently executing query. Then call <xref linkend="libpq-PQgetResult"/>
59996016
repeatedly, until it returns null, as documented in <xref
60006017
linkend="libpq-async"/>. If the query returns any rows, they are returned
6001-
as individual <structname>PGresult</structname> objects, which look like
6018+
as one or more <structname>PGresult</structname> objects, which look like
60026019
normal query results except for having status code
6003-
<literal>PGRES_SINGLE_TUPLE</literal> instead of
6004-
<literal>PGRES_TUPLES_OK</literal>. After the last row, or immediately if
6020+
<literal>PGRES_SINGLE_TUPLE</literal> for single-row mode or
6021+
<literal>PGRES_TUPLES_CHUNK</literal> for chunked mode, instead of
6022+
<literal>PGRES_TUPLES_OK</literal>. There is exactly one result row in
6023+
each <literal>PGRES_SINGLE_TUPLE</literal> object, while
6024+
a <literal>PGRES_TUPLES_CHUNK</literal> object contains at least one
6025+
row but not more than the specified number of rows per chunk.
6026+
After the last row, or immediately if
60056027
the query returns zero rows, a zero-row object with status
60066028
<literal>PGRES_TUPLES_OK</literal> is returned; this is the signal that no
60076029
more rows will arrive. (But note that it is still necessary to continue
@@ -6013,9 +6035,9 @@ UPDATE mytable SET x = x + 1 WHERE id = 42;
60136035
</para>
60146036

60156037
<para>
6016-
When using pipeline mode, single-row mode needs to be activated for each
6017-
query in the pipeline before retrieving results for that query
6018-
with <function>PQgetResult</function>.
6038+
When using pipeline mode, single-row or chunked mode needs to be
6039+
activated for each query in the pipeline before retrieving results for
6040+
that query with <function>PQgetResult</function>.
60196041
See <xref linkend="libpq-pipeline-mode"/> for more information.
60206042
</para>
60216043

@@ -6046,6 +6068,36 @@ int PQsetSingleRowMode(PGconn *conn);
60466068
</para>
60476069
</listitem>
60486070
</varlistentry>
6071+
6072+
<varlistentry id="libpq-PQsetChunkedRowsMode">
6073+
<term><function>PQsetChunkedRowsMode</function><indexterm><primary>PQsetChunkedRowsMode</primary></indexterm></term>
6074+
6075+
<listitem>
6076+
<para>
6077+
Select chunked mode for the currently-executing query.
6078+
6079+
<synopsis>
6080+
int PQsetChunkedRowsMode(PGconn *conn, int chunkSize);
6081+
</synopsis>
6082+
</para>
6083+
6084+
<para>
6085+
This function is similar to
6086+
<xref linkend="libpq-PQsetSingleRowMode"/>, except that it
6087+
specifies retrieval of up to <replaceable>chunkSize</replaceable> rows
6088+
per <structname>PGresult</structname>, not necessarily just one row.
6089+
This function can only be called immediately after
6090+
<xref linkend="libpq-PQsendQuery"/> or one of its sibling functions,
6091+
before any other operation on the connection such as
6092+
<xref linkend="libpq-PQconsumeInput"/> or
6093+
<xref linkend="libpq-PQgetResult"/>. If called at the correct time,
6094+
the function activates chunked mode for the current query and
6095+
returns 1. Otherwise the mode stays unchanged and the function
6096+
returns 0. In any case, the mode reverts to normal after
6097+
completion of the current query.
6098+
</para>
6099+
</listitem>
6100+
</varlistentry>
60496101
</variablelist>
60506102
</para>
60516103

@@ -6054,9 +6106,10 @@ int PQsetSingleRowMode(PGconn *conn);
60546106
While processing a query, the server may return some rows and then
60556107
encounter an error, causing the query to be aborted. Ordinarily,
60566108
<application>libpq</application> discards any such rows and reports only the
6057-
error. But in single-row mode, those rows will have already been
6058-
returned to the application. Hence, the application will see some
6059-
<literal>PGRES_SINGLE_TUPLE</literal> <structname>PGresult</structname>
6109+
error. But in single-row or chunked mode, some rows may have already
6110+
been returned to the application. Hence, the application will see some
6111+
<literal>PGRES_SINGLE_TUPLE</literal> or <literal>PGRES_TUPLES_CHUNK</literal>
6112+
<structname>PGresult</structname>
60606113
objects followed by a <literal>PGRES_FATAL_ERROR</literal> object. For
60616114
proper transactional behavior, the application must be designed to
60626115
discard or undo whatever has been done with the previously-processed

‎src/backend/replication/libpqwalreceiver/libpqwalreceiver.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1248,8 +1248,9 @@ libpqrcv_exec(WalReceiverConn *conn, const char *query,
12481248

12491249
switch (PQresultStatus(pgres))
12501250
{
1251-
case PGRES_SINGLE_TUPLE:
12521251
case PGRES_TUPLES_OK:
1252+
case PGRES_SINGLE_TUPLE:
1253+
case PGRES_TUPLES_CHUNK:
12531254
walres->status = WALRCV_OK_TUPLES;
12541255
libpqrcv_processTuples(pgres, walres, nRetTypes, retTypes);
12551256
break;

‎src/bin/pg_amcheck/pg_amcheck.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -991,6 +991,7 @@ should_processing_continue(PGresult *res)
991991
case PGRES_SINGLE_TUPLE:
992992
case PGRES_PIPELINE_SYNC:
993993
case PGRES_PIPELINE_ABORTED:
994+
case PGRES_TUPLES_CHUNK:
994995
return false;
995996
}
996997
return true;

‎src/interfaces/libpq/exports.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -203,3 +203,4 @@ PQcancelErrorMessage 200
203203
PQcancelReset 201
204204
PQcancelFinish 202
205205
PQsocketPoll 203
206+
PQsetChunkedRowsMode 204

0 commit comments

Comments
 (0)