1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>34.5. Pipeline Mode</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="libpq-async.html" title="34.4. Asynchronous Command Processing" /><link rel="next" href="libpq-single-row-mode.html" title="34.6. Retrieving Query Results Row-by-Row" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">34.5. Pipeline Mode</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="libpq-async.html" title="34.4. Asynchronous Command Processing">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="libpq.html" title="Chapter 34. libpq — C Library">Up</a></td><th width="60%" align="center">Chapter 34. <span class="application">libpq</span> — C Library</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="libpq-single-row-mode.html" title="34.6. Retrieving Query Results Row-by-Row">Next</a></td></tr></table><hr /></div><div class="sect1" id="LIBPQ-PIPELINE-MODE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">34.5. Pipeline Mode</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-USING">34.5.1. Using Pipeline Mode</a></span></dt><dt><span class="sect2"><a href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-FUNCTIONS">34.5.2. Functions Associated with Pipeline Mode</a></span></dt><dt><span class="sect2"><a href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-TIPS">34.5.3. When to Use Pipeline Mode</a></span></dt></dl></div><a id="id-1.7.3.12.2" class="indexterm"></a><a id="id-1.7.3.12.3" class="indexterm"></a><a id="id-1.7.3.12.4" class="indexterm"></a><p>
<span class="application">libpq</span> pipeline mode allows applications to
send a query without having to read the result of the previously
sent query. Taking advantage of the pipeline mode, a client will wait
less for the server, since multiple queries/results can be
sent/received in a single network transaction.
</p><p>
While pipeline mode provides a significant performance boost, writing
clients using the pipeline mode is more complex because it involves
managing a queue of pending queries and finding which result
corresponds to which query in the queue.
</p><p>
Pipeline mode also generally consumes more memory on both the client and server,
though careful and aggressive management of the send/receive queue can mitigate
this. This applies whether or not the connection is in blocking or non-blocking
mode.
</p><p>
While <span class="application">libpq</span>'s pipeline API was introduced in
<span class="productname">PostgreSQL</span> 14, it is a client-side feature
which doesn't require special server support and works on any server
that supports the v3 extended query protocol. For more information see
<a class="xref" href="protocol-flow.html#PROTOCOL-FLOW-PIPELINING" title="55.2.4. Pipelining">Section 55.2.4</a>.
</p><div class="sect2" id="LIBPQ-PIPELINE-USING"><div class="titlepage"><div><div><h3 class="title">34.5.1. Using Pipeline Mode</h3></div></div></div><p>
To issue pipelines, the application must switch the connection
into pipeline mode,
which is done with <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PQENTERPIPELINEMODE"><code class="function">PQenterPipelineMode</code></a>.
<a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PQPIPELINESTATUS"><code class="function">PQpipelineStatus</code></a> can be used
to test whether pipeline mode is active.
In pipeline mode, only <a class="link" href="libpq-async.html" title="34.4. Asynchronous Command Processing">asynchronous operations</a>
that utilize the extended query protocol
are permitted, command strings containing multiple SQL commands are
disallowed, and so is <code class="literal">COPY</code>.
Using synchronous command execution functions
such as <code class="function">PQfn</code>,
<code class="function">PQexec</code>,
<code class="function">PQexecParams</code>,
<code class="function">PQprepare</code>,
<code class="function">PQexecPrepared</code>,
<code class="function">PQdescribePrepared</code>,
<code class="function">PQdescribePortal</code>,
is an error condition.
<code class="function">PQsendQuery</code> is
also disallowed, because it uses the simple query protocol.
Once all dispatched commands have had their results processed, and
the end pipeline result has been consumed, the application may return
to non-pipelined mode with <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PQEXITPIPELINEMODE"><code class="function">PQexitPipelineMode</code></a>.
</p><div class="note"><h3 class="title">Note</h3><p>
It is best to use pipeline mode with <span class="application">libpq</span> in
<a class="link" href="libpq-async.html#LIBPQ-PQSETNONBLOCKING">non-blocking mode</a>. If used
in blocking mode it is possible for a client/server deadlock to occur.
<a href="#ftn.id-1.7.3.12.9.3.1.3" class="footnote"><sup class="footnote" id="id-1.7.3.12.9.3.1.3">[15]</sup></a>
</p></div><div class="sect3" id="LIBPQ-PIPELINE-SENDING"><div class="titlepage"><div><div><h4 class="title">34.5.1.1. Issuing Queries</h4></div></div></div><p>
After entering pipeline mode, the application dispatches requests using
<a class="xref" href="libpq-async.html#LIBPQ-PQSENDQUERYPARAMS"><code class="function">PQsendQueryParams</code></a>
or its prepared-query sibling
<a class="xref" href="libpq-async.html#LIBPQ-PQSENDQUERYPREPARED"><code class="function">PQsendQueryPrepared</code></a>.
These requests are queued on the client-side until flushed to the server;
this occurs when <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PQPIPELINESYNC"><code class="function">PQpipelineSync</code></a> is used to
establish a synchronization point in the pipeline,
or when <a class="xref" href="libpq-async.html#LIBPQ-PQFLUSH"><code class="function">PQflush</code></a> is called.
The functions <a class="xref" href="libpq-async.html#LIBPQ-PQSENDPREPARE"><code class="function">PQsendPrepare</code></a>,
<a class="xref" href="libpq-async.html#LIBPQ-PQSENDDESCRIBEPREPARED"><code class="function">PQsendDescribePrepared</code></a>, and
<a class="xref" href="libpq-async.html#LIBPQ-PQSENDDESCRIBEPORTAL"><code class="function">PQsendDescribePortal</code></a> also work in pipeline mode.
Result processing is described below.
</p><p>
The server executes statements, and returns results, in the order the
client sends them. The server will begin executing the commands in the
pipeline immediately, not waiting for the end of the pipeline.
Note that results are buffered on the server side; the server flushes
that buffer when a synchronization point is established with
<code class="function">PQpipelineSync</code>, or when
<code class="function">PQsendFlushRequest</code> is called.
If any statement encounters an error, the server aborts the current
transaction and does not execute any subsequent command in the queue
until the next synchronization point;
a <code class="literal">PGRES_PIPELINE_ABORTED</code> result is produced for
each such command.
(This remains true even if the commands in the pipeline would rollback
the transaction.)
Query processing resumes after the synchronization point.
</p><p>
It's fine for one operation to depend on the results of a
prior one; for example, one query may define a table that the next
query in the same pipeline uses. Similarly, an application may
create a named prepared statement and execute it with later
statements in the same pipeline.
</p></div><div class="sect3" id="LIBPQ-PIPELINE-RESULTS"><div class="titlepage"><div><div><h4 class="title">34.5.1.2. Processing Results</h4></div></div></div><p>
To process the result of one query in a pipeline, the application calls
<code class="function">PQgetResult</code> repeatedly and handles each result
until <code class="function">PQgetResult</code> returns null.
The result from the next query in the pipeline may then be retrieved using
<code class="function">PQgetResult</code> again and the cycle repeated.
The application handles individual statement results as normal.
When the results of all the queries in the pipeline have been
returned, <code class="function">PQgetResult</code> returns a result
containing the status value <code class="literal">PGRES_PIPELINE_SYNC</code>
</p><p>
The client may choose to defer result processing until the complete
pipeline has been sent, or interleave that with sending further
queries in the pipeline; see <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-INTERLEAVE" title="34.5.1.4. Interleaving Result Processing and Query Dispatch">Section 34.5.1.4</a>.
</p><p>
To enter single-row mode, call <code class="function">PQsetSingleRowMode</code>
before retrieving results with <code class="function">PQgetResult</code>.
This mode selection is effective only for the query currently
being processed. For more information on the use of
<code class="function">PQsetSingleRowMode</code>,
refer to <a class="xref" href="libpq-single-row-mode.html" title="34.6. Retrieving Query Results Row-by-Row">Section 34.6</a>.
</p><p>
<code class="function">PQgetResult</code> behaves the same as for normal
asynchronous processing except that it may contain the new
<code class="type">PGresult</code> types <code class="literal">PGRES_PIPELINE_SYNC</code>
and <code class="literal">PGRES_PIPELINE_ABORTED</code>.
<code class="literal">PGRES_PIPELINE_SYNC</code> is reported exactly once for each
<code class="function">PQpipelineSync</code> at the corresponding point
in the pipeline.
<code class="literal">PGRES_PIPELINE_ABORTED</code> is emitted in place of a normal
query result for the first error and all subsequent results
until the next <code class="literal">PGRES_PIPELINE_SYNC</code>;
see <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-ERRORS" title="34.5.1.3. Error Handling">Section 34.5.1.3</a>.
</p><p>
<code class="function">PQisBusy</code>, <code class="function">PQconsumeInput</code>, etc
operate as normal when processing pipeline results. In particular,
a call to <code class="function">PQisBusy</code> in the middle of a pipeline
returns 0 if the results for all the queries issued so far have been
consumed.
</p><p>
<span class="application">libpq</span> does not provide any information to the
application about the query currently being processed (except that
<code class="function">PQgetResult</code> returns null to indicate that we start
returning the results of next query). The application must keep track
of the order in which it sent queries, to associate them with their
corresponding results.
Applications will typically use a state machine or a FIFO queue for this.
</p></div><div class="sect3" id="LIBPQ-PIPELINE-ERRORS"><div class="titlepage"><div><div><h4 class="title">34.5.1.3. Error Handling</h4></div></div></div><p>
From the client's perspective, after <code class="function">PQresultStatus</code>
returns <code class="literal">PGRES_FATAL_ERROR</code>,
the pipeline is flagged as aborted.
<code class="function">PQresultStatus</code> will report a
<code class="literal">PGRES_PIPELINE_ABORTED</code> result for each remaining queued
operation in an aborted pipeline. The result for
<code class="function">PQpipelineSync</code> is reported as
<code class="literal">PGRES_PIPELINE_SYNC</code> to signal the end of the aborted pipeline
and resumption of normal result processing.
</p><p>
The client <span class="emphasis"><em>must</em></span> process results with
<code class="function">PQgetResult</code> during error recovery.
</p><p>
If the pipeline used an implicit transaction, then operations that have
already executed are rolled back and operations that were queued to follow
the failed operation are skipped entirely. The same behavior holds if the
pipeline starts and commits a single explicit transaction (i.e. the first
statement is <code class="literal">BEGIN</code> and the last is
<code class="literal">COMMIT</code>) except that the session remains in an aborted
transaction state at the end of the pipeline. If a pipeline contains
<span class="emphasis"><em>multiple explicit transactions</em></span>, all transactions that
committed prior to the error remain committed, the currently in-progress
transaction is aborted, and all subsequent operations are skipped completely,
including subsequent transactions. If a pipeline synchronization point
occurs with an explicit transaction block in aborted state, the next pipeline
will become aborted immediately unless the next command puts the transaction
in normal mode with <code class="command">ROLLBACK</code>.
</p><div class="note"><h3 class="title">Note</h3><p>
The client must not assume that work is committed when it
<span class="emphasis"><em>sends</em></span> a <code class="literal">COMMIT</code> — only when the
corresponding result is received to confirm the commit is complete.
Because errors arrive asynchronously, the application needs to be able to
restart from the last <span class="emphasis"><em>received</em></span> committed change and
resend work done after that point if something goes wrong.
</p></div></div><div class="sect3" id="LIBPQ-PIPELINE-INTERLEAVE"><div class="titlepage"><div><div><h4 class="title">34.5.1.4. Interleaving Result Processing and Query Dispatch</h4></div></div></div><p>
To avoid deadlocks on large pipelines the client should be structured
around a non-blocking event loop using operating system facilities
such as <code class="function">select</code>, <code class="function">poll</code>,
<code class="function">WaitForMultipleObjectEx</code>, etc.
</p><p>
The client application should generally maintain a queue of work
remaining to be dispatched and a queue of work that has been dispatched
but not yet had its results processed. When the socket is writable
it should dispatch more work. When the socket is readable it should
read results and process them, matching them up to the next entry in
its corresponding results queue. Based on available memory, results from the
socket should be read frequently: there's no need to wait until the
pipeline end to read the results. Pipelines should be scoped to logical
units of work, usually (but not necessarily) one transaction per pipeline.
There's no need to exit pipeline mode and re-enter it between pipelines,
or to wait for one pipeline to finish before sending the next.
</p><p>
An example using <code class="function">select()</code> and a simple state
machine to track sent and received work is in
<code class="filename">src/test/modules/libpq_pipeline/libpq_pipeline.c</code>
in the PostgreSQL source distribution.
</p></div></div><div class="sect2" id="LIBPQ-PIPELINE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">34.5.2. Functions Associated with Pipeline Mode</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQPIPELINESTATUS"><span class="term"><code class="function">PQpipelineStatus</code><a id="id-1.7.3.12.10.2.1.1.2" class="indexterm"></a></span></dt><dd><p>
Returns the current pipeline mode status of the
<span class="application">libpq</span> connection.
</p><pre class="synopsis">
PGpipelineStatus PQpipelineStatus(const PGconn *conn);
</pre><p>
</p><p>
<code class="function">PQpipelineStatus</code> can return one of the following values:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">
<code class="literal">PQ_PIPELINE_ON</code>
</span></dt><dd><p>
The <span class="application">libpq</span> connection is in
pipeline mode.
</p></dd><dt><span class="term">
<code class="literal">PQ_PIPELINE_OFF</code>
</span></dt><dd><p>
The <span class="application">libpq</span> connection is
<span class="emphasis"><em>not</em></span> in pipeline mode.
</p></dd><dt><span class="term">
<code class="literal">PQ_PIPELINE_ABORTED</code>
</span></dt><dd><p>
The <span class="application">libpq</span> connection is in pipeline
mode and an error occurred while processing the current pipeline.
The aborted flag is cleared when <code class="function">PQgetResult</code>
returns a result of type <code class="literal">PGRES_PIPELINE_SYNC</code>.
</p></dd></dl></div><p>
</p></dd><dt id="LIBPQ-PQENTERPIPELINEMODE"><span class="term"><code class="function">PQenterPipelineMode</code><a id="id-1.7.3.12.10.2.2.1.2" class="indexterm"></a></span></dt><dd><p>
Causes a connection to enter pipeline mode if it is currently idle or
already in pipeline mode.
</p><pre class="synopsis">
int PQenterPipelineMode(PGconn *conn);
</pre><p>
</p><p>
Returns 1 for success.
Returns 0 and has no effect if the connection is not currently
idle, i.e., it has a result ready, or it is waiting for more
input from the server, etc.
This function does not actually send anything to the server,
it just changes the <span class="application">libpq</span> connection
state.
</p></dd><dt id="LIBPQ-PQEXITPIPELINEMODE"><span class="term"><code class="function">PQexitPipelineMode</code><a id="id-1.7.3.12.10.2.3.1.2" class="indexterm"></a></span></dt><dd><p>
Causes a connection to exit pipeline mode if it is currently in pipeline mode
with an empty queue and no pending results.
</p><pre class="synopsis">
int PQexitPipelineMode(PGconn *conn);
</pre><p>
</p><p>
Returns 1 for success. Returns 1 and takes no action if not in
pipeline mode. If the current statement isn't finished processing,
or <code class="function">PQgetResult</code> has not been called to collect
results from all previously sent query, returns 0 (in which case,
use <a class="xref" href="libpq-status.html#LIBPQ-PQERRORMESSAGE"><code class="function">PQerrorMessage</code></a> to get more information
about the failure).
</p></dd><dt id="LIBPQ-PQPIPELINESYNC"><span class="term"><code class="function">PQpipelineSync</code><a id="id-1.7.3.12.10.2.4.1.2" class="indexterm"></a></span></dt><dd><p>
Marks a synchronization point in a pipeline by sending a
<a class="link" href="protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY" title="55.2.3. Extended Query">sync message</a>
and flushing the send buffer. This serves as
the delimiter of an implicit transaction and an error recovery
point; see <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-ERRORS" title="34.5.1.3. Error Handling">Section 34.5.1.3</a>.
</p><pre class="synopsis">
int PQpipelineSync(PGconn *conn);
</pre><p>
</p><p>
Returns 1 for success. Returns 0 if the connection is not in
pipeline mode or sending a
<a class="link" href="protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY" title="55.2.3. Extended Query">sync message</a>
failed.
</p></dd><dt id="LIBPQ-PQSENDFLUSHREQUEST"><span class="term"><code class="function">PQsendFlushRequest</code><a id="id-1.7.3.12.10.2.5.1.2" class="indexterm"></a></span></dt><dd><p>
Sends a request for the server to flush its output buffer.
</p><pre class="synopsis">
int PQsendFlushRequest(PGconn *conn);
</pre><p>
</p><p>
Returns 1 for success. Returns 0 on any failure.
</p><p>
The server flushes its output buffer automatically as a result of
<code class="function">PQpipelineSync</code> being called, or
on any request when not in pipeline mode; this function is useful
to cause the server to flush its output buffer in pipeline mode
without establishing a synchronization point.
Note that the request is not itself flushed to the server automatically;
use <code class="function">PQflush</code> if necessary.
</p></dd></dl></div></div><div class="sect2" id="LIBPQ-PIPELINE-TIPS"><div class="titlepage"><div><div><h3 class="title">34.5.3. When to Use Pipeline Mode</h3></div></div></div><p>
Much like asynchronous query mode, there is no meaningful performance
overhead when using pipeline mode. It increases client application complexity,
and extra caution is required to prevent client/server deadlocks, but
pipeline mode can offer considerable performance improvements, in exchange for
increased memory usage from leaving state around longer.
</p><p>
Pipeline mode is most useful when the server is distant, i.e., network latency
(<span class="quote">“<span class="quote">ping time</span>”</span>) is high, and also when many small operations
are being performed in rapid succession. There is usually less benefit
in using pipelined commands when each query takes many multiples of the client/server
round-trip time to execute. A 100-statement operation run on a server
300 ms round-trip-time away would take 30 seconds in network latency alone
without pipelining; with pipelining it may spend as little as 0.3 s waiting for
results from the server.
</p><p>
Use pipelined commands when your application does lots of small
<code class="literal">INSERT</code>, <code class="literal">UPDATE</code> and
<code class="literal">DELETE</code> operations that can't easily be transformed
into operations on sets, or into a <code class="literal">COPY</code> operation.
</p><p>
Pipeline mode is not useful when information from one operation is required by
the client to produce the next operation. In such cases, the client
would have to introduce a synchronization point and wait for a full client/server
round-trip to get the results it needs. However, it's often possible to
adjust the client design to exchange the required information server-side.
Read-modify-write cycles are especially good candidates; for example:
</p><pre class="programlisting">
BEGIN;
SELECT x FROM mytable WHERE id = 42 FOR UPDATE;
-- result: x=2
-- client adds 1 to x:
UPDATE mytable SET x = 3 WHERE id = 42;
COMMIT;
</pre><p>
could be much more efficiently done with:
</p><pre class="programlisting">
UPDATE mytable SET x = x + 1 WHERE id = 42;
</pre><p>
</p><p>
Pipelining is less useful, and more complex, when a single pipeline contains
multiple transactions (see <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PIPELINE-ERRORS" title="34.5.1.3. Error Handling">Section 34.5.1.3</a>).
</p></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.7.3.12.9.3.1.3" class="footnote"><p><a href="#id-1.7.3.12.9.3.1.3" class="para"><sup class="para">[15] </sup></a>
The client will block trying to send queries to the server, but the
server will block trying to send results to the client from queries
it has already processed. This only occurs when the client sends
enough queries to fill both its output buffer and the server's receive
buffer before it switches to processing input from the server,
but it's hard to predict exactly when that will happen.
</p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="libpq-async.html" title="34.4. Asynchronous Command Processing">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="libpq.html" title="Chapter 34. libpq — C Library">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="libpq-single-row-mode.html" title="34.6. Retrieving Query Results Row-by-Row">Next</a></td></tr><tr><td width="40%" align="left" valign="top">34.4. Asynchronous Command Processing </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 34.6. Retrieving Query Results Row-by-Row</td></tr></table></div></body></html>
|
