diff options
Diffstat (limited to 'doc/src/sgml/html/libpq-pipeline-mode.html')
| -rw-r--r-- | doc/src/sgml/html/libpq-pipeline-mode.html | 327 |
1 files changed, 327 insertions, 0 deletions
diff --git a/doc/src/sgml/html/libpq-pipeline-mode.html b/doc/src/sgml/html/libpq-pipeline-mode.html new file mode 100644 index 0000000..c97dfa0 --- /dev/null +++ b/doc/src/sgml/html/libpq-pipeline-mode.html @@ -0,0 +1,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.5 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.5 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>
\ No newline at end of file |