diff options
Diffstat (limited to 'doc/src/sgml/html/protocol-flow.html')
| -rw-r--r-- | doc/src/sgml/html/protocol-flow.html | 904 |
1 files changed, 904 insertions, 0 deletions
diff --git a/doc/src/sgml/html/protocol-flow.html b/doc/src/sgml/html/protocol-flow.html new file mode 100644 index 0000000..b4f633c --- /dev/null +++ b/doc/src/sgml/html/protocol-flow.html @@ -0,0 +1,904 @@ +<?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>52.2. Message Flow</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 V1.79.1" /><link rel="prev" href="protocol-overview.html" title="52.1. Overview" /><link rel="next" href="sasl-authentication.html" title="52.3. SASL Authentication" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">52.2. Message Flow</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="protocol-overview.html" title="52.1. Overview">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="protocol.html" title="Chapter 52. Frontend/Backend Protocol">Up</a></td><th width="60%" align="center">Chapter 52. Frontend/Backend Protocol</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sasl-authentication.html" title="52.3. SASL Authentication">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="PROTOCOL-FLOW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">52.2. Message Flow</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.3">52.2.1. Start-up</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.4">52.2.2. Simple Query</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY">52.2.3. Extended Query</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.6">52.2.4. Function Call</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#PROTOCOL-COPY">52.2.5. COPY Operations</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#PROTOCOL-ASYNC">52.2.6. Asynchronous Operations</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.9">52.2.7. Canceling Requests in Progress</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.10">52.2.8. Termination</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.11">52.2.9. <acronym class="acronym">SSL</acronym> Session Encryption</a></span></dt><dt><span class="sect2"><a href="protocol-flow.html#id-1.10.5.7.12">52.2.10. <acronym class="acronym">GSSAPI</acronym> Session Encryption</a></span></dt></dl></div><p> + This section describes the message flow and the semantics of each + message type. (Details of the exact representation of each message + appear in <a class="xref" href="protocol-message-formats.html" title="52.7. Message Formats">Section 52.7</a>.) There are + several different sub-protocols depending on the state of the + connection: start-up, query, function call, + <code class="command">COPY</code>, and termination. There are also special + provisions for asynchronous operations (including notification + responses and command cancellation), which can occur at any time + after the start-up phase. + </p><div class="sect2" id="id-1.10.5.7.3"><div class="titlepage"><div><div><h3 class="title">52.2.1. Start-up</h3></div></div></div><p> + To begin a session, a frontend opens a connection to the server and sends + a startup message. This message includes the names of the user and of the + database the user wants to connect to; it also identifies the particular + protocol version to be used. (Optionally, the startup message can include + additional settings for run-time parameters.) + The server then uses this information and + the contents of its configuration files (such as + <code class="filename">pg_hba.conf</code>) to determine + whether the connection is provisionally acceptable, and what additional + authentication is required (if any). + </p><p> + The server then sends an appropriate authentication request message, + to which the frontend must reply with an appropriate authentication + response message (such as a password). + For all authentication methods except GSSAPI, SSPI and SASL, there is at + most one request and one response. In some methods, no response + at all is needed from the frontend, and so no authentication request + occurs. For GSSAPI, SSPI and SASL, multiple exchanges of packets may be + needed to complete the authentication. + </p><p> + The authentication cycle ends with the server either rejecting the + connection attempt (ErrorResponse), or sending AuthenticationOk. + </p><p> + The possible messages from the server in this phase are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">ErrorResponse</span></dt><dd><p> + The connection attempt has been rejected. + The server then immediately closes the connection. + </p></dd><dt><span class="term">AuthenticationOk</span></dt><dd><p> + The authentication exchange is successfully completed. + </p></dd><dt><span class="term">AuthenticationKerberosV5</span></dt><dd><p> + The frontend must now take part in a Kerberos V5 + authentication dialog (not described here, part of the + Kerberos specification) with the server. If this is + successful, the server responds with an AuthenticationOk, + otherwise it responds with an ErrorResponse. This is no + longer supported. + </p></dd><dt><span class="term">AuthenticationCleartextPassword</span></dt><dd><p> + The frontend must now send a PasswordMessage containing the + password in clear-text form. If + this is the correct password, the server responds with an + AuthenticationOk, otherwise it responds with an ErrorResponse. + </p></dd><dt><span class="term">AuthenticationMD5Password</span></dt><dd><p> + The frontend must now send a PasswordMessage containing the + password (with user name) encrypted via MD5, then encrypted + again using the 4-byte random salt specified in the + AuthenticationMD5Password message. If this is the correct + password, the server responds with an AuthenticationOk, + otherwise it responds with an ErrorResponse. The actual + PasswordMessage can be computed in SQL as <code class="literal">concat('md5', + md5(concat(md5(concat(password, username)), random-salt)))</code>. + (Keep in mind the <code class="function">md5()</code> function returns its + result as a hex string.) + </p></dd><dt><span class="term">AuthenticationSCMCredential</span></dt><dd><p> + This response is only possible for local Unix-domain connections + on platforms that support SCM credential messages. The frontend + must issue an SCM credential message and then send a single data + byte. (The contents of the data byte are uninteresting; it's + only used to ensure that the server waits long enough to receive + the credential message.) If the credential is acceptable, + the server responds with an + AuthenticationOk, otherwise it responds with an ErrorResponse. + (This message type is only issued by pre-9.1 servers. It may + eventually be removed from the protocol specification.) + </p></dd><dt><span class="term">AuthenticationGSS</span></dt><dd><p> + The frontend must now initiate a GSSAPI negotiation. The frontend + will send a GSSResponse message with the first part of the GSSAPI + data stream in response to this. If further messages are needed, + the server will respond with AuthenticationGSSContinue. + </p></dd><dt><span class="term">AuthenticationSSPI</span></dt><dd><p> + The frontend must now initiate a SSPI negotiation. The frontend + will send a GSSResponse with the first part of the SSPI + data stream in response to this. If further messages are needed, + the server will respond with AuthenticationGSSContinue. + </p></dd><dt><span class="term">AuthenticationGSSContinue</span></dt><dd><p> + This message contains the response data from the previous step + of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI + or a previous AuthenticationGSSContinue). If the GSSAPI + or SSPI data in this message + indicates more data is needed to complete the authentication, + the frontend must send that data as another GSSResponse message. If + GSSAPI or SSPI authentication is completed by this message, the server + will next send AuthenticationOk to indicate successful authentication + or ErrorResponse to indicate failure. + </p></dd><dt><span class="term">AuthenticationSASL</span></dt><dd><p> + The frontend must now initiate a SASL negotiation, using one of the + SASL mechanisms listed in the message. The frontend will send a + SASLInitialResponse with the name of the selected mechanism, and the + first part of the SASL data stream in response to this. If further + messages are needed, the server will respond with + AuthenticationSASLContinue. See <a class="xref" href="sasl-authentication.html" title="52.3. SASL Authentication">Section 52.3</a> + for details. + </p></dd><dt><span class="term">AuthenticationSASLContinue</span></dt><dd><p> + This message contains challenge data from the previous step of SASL + negotiation (AuthenticationSASL, or a previous + AuthenticationSASLContinue). The frontend must respond with a + SASLResponse message. + </p></dd><dt><span class="term">AuthenticationSASLFinal</span></dt><dd><p> + SASL authentication has completed with additional mechanism-specific + data for the client. The server will next send AuthenticationOk to + indicate successful authentication, or an ErrorResponse to indicate + failure. This message is sent only if the SASL mechanism specifies + additional data to be sent from server to client at completion. + </p></dd><dt><span class="term">NegotiateProtocolVersion</span></dt><dd><p> + The server does not support the minor protocol version requested + by the client, but does support an earlier version of the protocol; + this message indicates the highest supported minor version. This + message will also be sent if the client requested unsupported protocol + options (i.e., beginning with <code class="literal">_pq_.</code>) in the + startup packet. This message will be followed by an ErrorResponse or + a message indicating the success or failure of authentication. + </p></dd></dl></div><p> + </p><p> + If the frontend does not support the authentication method + requested by the server, then it should immediately close the + connection. + </p><p> + After having received AuthenticationOk, the frontend must wait + for further messages from the server. In this phase a backend process + is being started, and the frontend is just an interested bystander. + It is still possible for the startup attempt + to fail (ErrorResponse) or the server to decline support for the requested + minor protocol version (NegotiateProtocolVersion), but in the normal case + the backend will send some ParameterStatus messages, BackendKeyData, and + finally ReadyForQuery. + </p><p> + During this phase the backend will attempt to apply any additional + run-time parameter settings that were given in the startup message. + If successful, these values become session defaults. An error causes + ErrorResponse and exit. + </p><p> + The possible messages from the backend in this phase are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">BackendKeyData</span></dt><dd><p> + This message provides secret-key data that the frontend must + save if it wants to be able to issue cancel requests later. + The frontend should not respond to this message, but should + continue listening for a ReadyForQuery message. + </p></dd><dt><span class="term">ParameterStatus</span></dt><dd><p> + This message informs the frontend about the current (initial) + setting of backend parameters, such as <a class="xref" href="runtime-config-client.html#GUC-CLIENT-ENCODING">client_encoding</a> or <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a>. + The frontend can ignore this message, or record the settings + for its future use; see <a class="xref" href="protocol-flow.html#PROTOCOL-ASYNC" title="52.2.6. Asynchronous Operations">Section 52.2.6</a> for + more details. The frontend should not respond to this + message, but should continue listening for a ReadyForQuery + message. + </p></dd><dt><span class="term">ReadyForQuery</span></dt><dd><p> + Start-up is completed. The frontend can now issue commands. + </p></dd><dt><span class="term">ErrorResponse</span></dt><dd><p> + Start-up failed. The connection is closed after sending this + message. + </p></dd><dt><span class="term">NoticeResponse</span></dt><dd><p> + A warning message has been issued. The frontend should + display the message but continue listening for ReadyForQuery + or ErrorResponse. + </p></dd></dl></div><p> + </p><p> + The ReadyForQuery message is the same one that the backend will + issue after each command cycle. Depending on the coding needs of + the frontend, it is reasonable to consider ReadyForQuery as + starting a command cycle, or to consider ReadyForQuery as ending the + start-up phase and each subsequent command cycle. + </p></div><div class="sect2" id="id-1.10.5.7.4"><div class="titlepage"><div><div><h3 class="title">52.2.2. Simple Query</h3></div></div></div><p> + A simple query cycle is initiated by the frontend sending a Query message + to the backend. The message includes an SQL command (or commands) + expressed as a text string. + The backend then sends one or more response + messages depending on the contents of the query command string, + and finally a ReadyForQuery response message. ReadyForQuery + informs the frontend that it can safely send a new command. + (It is not actually necessary for the frontend to wait for + ReadyForQuery before issuing another command, but the frontend must + then take responsibility for figuring out what happens if the earlier + command fails and already-issued later commands succeed.) + </p><p> + The possible response messages from the backend are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">CommandComplete</span></dt><dd><p> + An SQL command completed normally. + </p></dd><dt><span class="term">CopyInResponse</span></dt><dd><p> + The backend is ready to copy data from the frontend to a + table; see <a class="xref" href="protocol-flow.html#PROTOCOL-COPY" title="52.2.5. COPY Operations">Section 52.2.5</a>. + </p></dd><dt><span class="term">CopyOutResponse</span></dt><dd><p> + The backend is ready to copy data from a table to the + frontend; see <a class="xref" href="protocol-flow.html#PROTOCOL-COPY" title="52.2.5. COPY Operations">Section 52.2.5</a>. + </p></dd><dt><span class="term">RowDescription</span></dt><dd><p> + Indicates that rows are about to be returned in response to + a <code class="command">SELECT</code>, <code class="command">FETCH</code>, etc query. + The contents of this message describe the column layout of the rows. + This will be followed by a DataRow message for each row being returned + to the frontend. + </p></dd><dt><span class="term">DataRow</span></dt><dd><p> + One of the set of rows returned by + a <code class="command">SELECT</code>, <code class="command">FETCH</code>, etc query. + </p></dd><dt><span class="term">EmptyQueryResponse</span></dt><dd><p> + An empty query string was recognized. + </p></dd><dt><span class="term">ErrorResponse</span></dt><dd><p> + An error has occurred. + </p></dd><dt><span class="term">ReadyForQuery</span></dt><dd><p> + Processing of the query string is complete. A separate + message is sent to indicate this because the query string might + contain multiple SQL commands. (CommandComplete marks the + end of processing one SQL command, not the whole string.) + ReadyForQuery will always be sent, whether processing + terminates successfully or with an error. + </p></dd><dt><span class="term">NoticeResponse</span></dt><dd><p> + A warning message has been issued in relation to the query. + Notices are in addition to other responses, i.e., the backend + will continue processing the command. + </p></dd></dl></div><p> + </p><p> + The response to a <code class="command">SELECT</code> query (or other queries that + return row sets, such as <code class="command">EXPLAIN</code> or <code class="command">SHOW</code>) + normally consists of RowDescription, zero or more + DataRow messages, and then CommandComplete. + <code class="command">COPY</code> to or from the frontend invokes special protocol + as described in <a class="xref" href="protocol-flow.html#PROTOCOL-COPY" title="52.2.5. COPY Operations">Section 52.2.5</a>. + All other query types normally produce only + a CommandComplete message. + </p><p> + Since a query string could contain several queries (separated by + semicolons), there might be several such response sequences before the + backend finishes processing the query string. ReadyForQuery is issued + when the entire string has been processed and the backend is ready to + accept a new query string. + </p><p> + If a completely empty (no contents other than whitespace) query string + is received, the response is EmptyQueryResponse followed by ReadyForQuery. + </p><p> + In the event of an error, ErrorResponse is issued followed by + ReadyForQuery. All further processing of the query string is aborted by + ErrorResponse (even if more queries remained in it). Note that this + might occur partway through the sequence of messages generated by an + individual query. + </p><p> + In simple Query mode, the format of retrieved values is always text, + except when the given command is a <code class="command">FETCH</code> from a cursor + declared with the <code class="literal">BINARY</code> option. In that case, the + retrieved values are in binary format. The format codes given in + the RowDescription message tell which format is being used. + </p><p> + A frontend must be prepared to accept ErrorResponse and + NoticeResponse messages whenever it is expecting any other type of + message. See also <a class="xref" href="protocol-flow.html#PROTOCOL-ASYNC" title="52.2.6. Asynchronous Operations">Section 52.2.6</a> concerning messages + that the backend might generate due to outside events. + </p><p> + Recommended practice is to code frontends in a state-machine style + that will accept any message type at any time that it could make sense, + rather than wiring in assumptions about the exact sequence of messages. + </p><div class="sect3" id="PROTOCOL-FLOW-MULTI-STATEMENT"><div class="titlepage"><div><div><h4 class="title">52.2.2.1. Multiple Statements in a Simple Query</h4></div></div></div><p> + When a simple Query message contains more than one SQL statement + (separated by semicolons), those statements are executed as a single + transaction, unless explicit transaction control commands are included + to force a different behavior. For example, if the message contains +</p><pre class="programlisting"> +INSERT INTO mytable VALUES(1); +SELECT 1/0; +INSERT INTO mytable VALUES(2); +</pre><p> + then the divide-by-zero failure in the <code class="command">SELECT</code> will force + rollback of the first <code class="command">INSERT</code>. Furthermore, because + execution of the message is abandoned at the first error, the second + <code class="command">INSERT</code> is never attempted at all. + </p><p> + If instead the message contains +</p><pre class="programlisting"> +BEGIN; +INSERT INTO mytable VALUES(1); +COMMIT; +INSERT INTO mytable VALUES(2); +SELECT 1/0; +</pre><p> + then the first <code class="command">INSERT</code> is committed by the + explicit <code class="command">COMMIT</code> command. The second <code class="command">INSERT</code> + and the <code class="command">SELECT</code> are still treated as a single transaction, + so that the divide-by-zero failure will roll back the + second <code class="command">INSERT</code>, but not the first one. + </p><p> + This behavior is implemented by running the statements in a + multi-statement Query message in an <em class="firstterm">implicit transaction + block</em> unless there is some explicit transaction block for them to + run in. The main difference between an implicit transaction block and + a regular one is that an implicit block is closed automatically at the + end of the Query message, either by an implicit commit if there was no + error, or an implicit rollback if there was an error. This is similar + to the implicit commit or rollback that happens for a statement + executed by itself (when not in a transaction block). + </p><p> + If the session is already in a transaction block, as a result of + a <code class="command">BEGIN</code> in some previous message, then the Query message + simply continues that transaction block, whether the message contains + one statement or several. However, if the Query message contains + a <code class="command">COMMIT</code> or <code class="command">ROLLBACK</code> closing the existing + transaction block, then any following statements are executed in an + implicit transaction block. + Conversely, if a <code class="command">BEGIN</code> appears in a multi-statement Query + message, then it starts a regular transaction block that will only be + terminated by an explicit <code class="command">COMMIT</code> or <code class="command">ROLLBACK</code>, + whether that appears in this Query message or a later one. + If the <code class="command">BEGIN</code> follows some statements that were executed as + an implicit transaction block, those statements are not immediately + committed; in effect, they are retroactively included into the new + regular transaction block. + </p><p> + A <code class="command">COMMIT</code> or <code class="command">ROLLBACK</code> appearing in an implicit + transaction block is executed as normal, closing the implicit block; + however, a warning will be issued since a <code class="command">COMMIT</code> + or <code class="command">ROLLBACK</code> without a previous <code class="command">BEGIN</code> might + represent a mistake. If more statements follow, a new implicit + transaction block will be started for them. + </p><p> + Savepoints are not allowed in an implicit transaction block, since + they would conflict with the behavior of automatically closing the + block upon any error. + </p><p> + Remember that, regardless of any transaction control commands that may + be present, execution of the Query message stops at the first error. + Thus for example given +</p><pre class="programlisting"> +BEGIN; +SELECT 1/0; +ROLLBACK; +</pre><p> + in a single Query message, the session will be left inside a failed + regular transaction block, since the <code class="command">ROLLBACK</code> is not + reached after the divide-by-zero error. Another <code class="command">ROLLBACK</code> + will be needed to restore the session to a usable state. + </p><p> + Another behavior of note is that initial lexical and syntactic + analysis is done on the entire query string before any of it is + executed. Thus simple errors (such as a misspelled keyword) in later + statements can prevent execution of any of the statements. This + is normally invisible to users since the statements would all roll + back anyway when done as an implicit transaction block. However, + it can be visible when attempting to do multiple transactions within a + multi-statement Query. For instance, if a typo turned our previous + example into +</p><pre class="programlisting"> +BEGIN; +INSERT INTO mytable VALUES(1); +COMMIT; +INSERT INTO mytable VALUES(2); +SELCT 1/0; +</pre><p> + then none of the statements would get run, resulting in the visible + difference that the first <code class="command">INSERT</code> is not committed. + Errors detected at semantic analysis or later, such as a misspelled + table or column name, do not have this effect. + </p></div></div><div class="sect2" id="PROTOCOL-FLOW-EXT-QUERY"><div class="titlepage"><div><div><h3 class="title">52.2.3. Extended Query</h3></div></div></div><p> + The extended query protocol breaks down the above-described simple + query protocol into multiple steps. The results of preparatory + steps can be re-used multiple times for improved efficiency. + Furthermore, additional features are available, such as the possibility + of supplying data values as separate parameters instead of having to + insert them directly into a query string. + </p><p> + In the extended protocol, the frontend first sends a Parse message, + which contains a textual query string, optionally some information + about data types of parameter placeholders, and the + name of a destination prepared-statement object (an empty string + selects the unnamed prepared statement). The response is + either ParseComplete or ErrorResponse. Parameter data types can be + specified by OID; if not given, the parser attempts to infer the + data types in the same way as it would do for untyped literal string + constants. + </p><div class="note"><h3 class="title">Note</h3><p> + A parameter data type can be left unspecified by setting it to zero, + or by making the array of parameter type OIDs shorter than the + number of parameter symbols (<code class="literal">$</code><em class="replaceable"><code>n</code></em>) + used in the query string. Another special case is that a parameter's + type can be specified as <code class="type">void</code> (that is, the OID of the + <code class="type">void</code> pseudo-type). This is meant to allow parameter symbols + to be used for function parameters that are actually OUT parameters. + Ordinarily there is no context in which a <code class="type">void</code> parameter + could be used, but if such a parameter symbol appears in a function's + parameter list, it is effectively ignored. For example, a function + call such as <code class="literal">foo($1,$2,$3,$4)</code> could match a function with + two IN and two OUT arguments, if <code class="literal">$3</code> and <code class="literal">$4</code> + are specified as having type <code class="type">void</code>. + </p></div><div class="note"><h3 class="title">Note</h3><p> + The query string contained in a Parse message cannot include more + than one SQL statement; else a syntax error is reported. This + restriction does not exist in the simple-query protocol, but it + does exist in the extended protocol, because allowing prepared + statements or portals to contain multiple commands would complicate + the protocol unduly. + </p></div><p> + If successfully created, a named prepared-statement object lasts till + the end of the current session, unless explicitly destroyed. An unnamed + prepared statement lasts only until the next Parse statement specifying + the unnamed statement as destination is issued. (Note that a simple + Query message also destroys the unnamed statement.) Named prepared + statements must be explicitly closed before they can be redefined by + another Parse message, but this is not required for the unnamed statement. + Named prepared statements can also be created and accessed at the SQL + command level, using <code class="command">PREPARE</code> and <code class="command">EXECUTE</code>. + </p><p> + Once a prepared statement exists, it can be readied for execution using a + Bind message. The Bind message gives the name of the source prepared + statement (empty string denotes the unnamed prepared statement), the name + of the destination portal (empty string denotes the unnamed portal), and + the values to use for any parameter placeholders present in the prepared + statement. The + supplied parameter set must match those needed by the prepared statement. + (If you declared any <code class="type">void</code> parameters in the Parse message, + pass NULL values for them in the Bind message.) + Bind also specifies the format to use for any data returned + by the query; the format can be specified overall, or per-column. + The response is either BindComplete or ErrorResponse. + </p><div class="note"><h3 class="title">Note</h3><p> + The choice between text and binary output is determined by the format + codes given in Bind, regardless of the SQL command involved. The + <code class="literal">BINARY</code> attribute in cursor declarations is irrelevant when + using extended query protocol. + </p></div><p> + Query planning typically occurs when the Bind message is processed. + If the prepared statement has no parameters, or is executed repeatedly, + the server might save the created plan and re-use it during subsequent + Bind messages for the same prepared statement. However, it will do so + only if it finds that a generic plan can be created that is not much + less efficient than a plan that depends on the specific parameter values + supplied. This happens transparently so far as the protocol is concerned. + </p><p> + If successfully created, a named portal object lasts till the end of the + current transaction, unless explicitly destroyed. An unnamed portal is + destroyed at the end of the transaction, or as soon as the next Bind + statement specifying the unnamed portal as destination is issued. (Note + that a simple Query message also destroys the unnamed portal.) Named + portals must be explicitly closed before they can be redefined by another + Bind message, but this is not required for the unnamed portal. + Named portals can also be created and accessed at the SQL + command level, using <code class="command">DECLARE CURSOR</code> and <code class="command">FETCH</code>. + </p><p> + Once a portal exists, it can be executed using an Execute message. + The Execute message specifies the portal name (empty string denotes the + unnamed portal) and + a maximum result-row count (zero meaning <span class="quote">“<span class="quote">fetch all rows</span>”</span>). + The result-row count is only meaningful for portals + containing commands that return row sets; in other cases the command is + always executed to completion, and the row count is ignored. + The possible + responses to Execute are the same as those described above for queries + issued via simple query protocol, except that Execute doesn't cause + ReadyForQuery or RowDescription to be issued. + </p><p> + If Execute terminates before completing the execution of a portal + (due to reaching a nonzero result-row count), it will send a + PortalSuspended message; the appearance of this message tells the frontend + that another Execute should be issued against the same portal to + complete the operation. The CommandComplete message indicating + completion of the source SQL command is not sent until + the portal's execution is completed. Therefore, an Execute phase is + always terminated by the appearance of exactly one of these messages: + CommandComplete, EmptyQueryResponse (if the portal was created from + an empty query string), ErrorResponse, or PortalSuspended. + </p><p> + At completion of each series of extended-query messages, the frontend + should issue a Sync message. This parameterless message causes the + backend to close the current transaction if it's not inside a + <code class="command">BEGIN</code>/<code class="command">COMMIT</code> transaction block (<span class="quote">“<span class="quote">close</span>”</span> + meaning to commit if no error, or roll back if error). Then a + ReadyForQuery response is issued. The purpose of Sync is to provide + a resynchronization point for error recovery. When an error is detected + while processing any extended-query message, the backend issues + ErrorResponse, then reads and discards messages until a Sync is reached, + then issues ReadyForQuery and returns to normal message processing. + (But note that no skipping occurs if an error is detected + <span class="emphasis"><em>while</em></span> processing Sync — this ensures that there is one + and only one ReadyForQuery sent for each Sync.) + </p><div class="note"><h3 class="title">Note</h3><p> + Sync does not cause a transaction block opened with <code class="command">BEGIN</code> + to be closed. It is possible to detect this situation since the + ReadyForQuery message includes transaction status information. + </p></div><p> + In addition to these fundamental, required operations, there are several + optional operations that can be used with extended-query protocol. + </p><p> + The Describe message (portal variant) specifies the name of an existing + portal (or an empty string for the unnamed portal). The response is a + RowDescription message describing the rows that will be returned by + executing the portal; or a NoData message if the portal does not contain a + query that will return rows; or ErrorResponse if there is no such portal. + </p><p> + The Describe message (statement variant) specifies the name of an existing + prepared statement (or an empty string for the unnamed prepared + statement). The response is a ParameterDescription message describing the + parameters needed by the statement, followed by a RowDescription message + describing the rows that will be returned when the statement is eventually + executed (or a NoData message if the statement will not return rows). + ErrorResponse is issued if there is no such prepared statement. Note that + since Bind has not yet been issued, the formats to be used for returned + columns are not yet known to the backend; the format code fields in the + RowDescription message will be zeroes in this case. + </p><div class="tip"><h3 class="title">Tip</h3><p> + In most scenarios the frontend should issue one or the other variant + of Describe before issuing Execute, to ensure that it knows how to + interpret the results it will get back. + </p></div><p> + The Close message closes an existing prepared statement or portal + and releases resources. It is not an error to issue Close against + a nonexistent statement or portal name. The response is normally + CloseComplete, but could be ErrorResponse if some difficulty is + encountered while releasing resources. Note that closing a prepared + statement implicitly closes any open portals that were constructed + from that statement. + </p><p> + The Flush message does not cause any specific output to be generated, + but forces the backend to deliver any data pending in its output + buffers. A Flush must be sent after any extended-query command except + Sync, if the frontend wishes to examine the results of that command before + issuing more commands. Without Flush, messages returned by the backend + will be combined into the minimum possible number of packets to minimize + network overhead. + </p><div class="note"><h3 class="title">Note</h3><p> + The simple Query message is approximately equivalent to the series Parse, + Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared + statement and portal objects and no parameters. One difference is that + it will accept multiple SQL statements in the query string, automatically + performing the bind/describe/execute sequence for each one in succession. + Another difference is that it will not return ParseComplete, BindComplete, + CloseComplete, or NoData messages. + </p></div></div><div class="sect2" id="id-1.10.5.7.6"><div class="titlepage"><div><div><h3 class="title">52.2.4. Function Call</h3></div></div></div><p> + The Function Call sub-protocol allows the client to request a direct + call of any function that exists in the database's + <code class="structname">pg_proc</code> system catalog. The client must have + execute permission for the function. + </p><div class="note"><h3 class="title">Note</h3><p> + The Function Call sub-protocol is a legacy feature that is probably best + avoided in new code. Similar results can be accomplished by setting up + a prepared statement that does <code class="literal">SELECT function($1, ...)</code>. + The Function Call cycle can then be replaced with Bind/Execute. + </p></div><p> + A Function Call cycle is initiated by the frontend sending a + FunctionCall message to the backend. The backend then sends one + or more response messages depending on the results of the function + call, and finally a ReadyForQuery response message. ReadyForQuery + informs the frontend that it can safely send a new query or + function call. + </p><p> + The possible response messages from the backend are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">ErrorResponse</span></dt><dd><p> + An error has occurred. + </p></dd><dt><span class="term">FunctionCallResponse</span></dt><dd><p> + The function call was completed and returned the result given + in the message. + (Note that the Function Call protocol can only handle a single + scalar result, not a row type or set of results.) + </p></dd><dt><span class="term">ReadyForQuery</span></dt><dd><p> + Processing of the function call is complete. ReadyForQuery + will always be sent, whether processing terminates + successfully or with an error. + </p></dd><dt><span class="term">NoticeResponse</span></dt><dd><p> + A warning message has been issued in relation to the function + call. Notices are in addition to other responses, i.e., the + backend will continue processing the command. + </p></dd></dl></div><p> + </p></div><div class="sect2" id="PROTOCOL-COPY"><div class="titlepage"><div><div><h3 class="title">52.2.5. COPY Operations</h3></div></div></div><p> + The <code class="command">COPY</code> command allows high-speed bulk data transfer + to or from the server. Copy-in and copy-out operations each switch + the connection into a distinct sub-protocol, which lasts until the + operation is completed. + </p><p> + Copy-in mode (data transfer to the server) is initiated when the + backend executes a <code class="command">COPY FROM STDIN</code> SQL statement. The backend + sends a CopyInResponse message to the frontend. The frontend should + then send zero or more CopyData messages, forming a stream of input + data. (The message boundaries are not required to have anything to do + with row boundaries, although that is often a reasonable choice.) + The frontend can terminate the copy-in mode by sending either a CopyDone + message (allowing successful termination) or a CopyFail message (which + will cause the <code class="command">COPY</code> SQL statement to fail with an + error). The backend then reverts to the command-processing mode it was + in before the <code class="command">COPY</code> started, which will be either simple or + extended query protocol. It will next send either CommandComplete + (if successful) or ErrorResponse (if not). + </p><p> + In the event of a backend-detected error during copy-in mode (including + receipt of a CopyFail message), the backend will issue an ErrorResponse + message. If the <code class="command">COPY</code> command was issued via an extended-query + message, the backend will now discard frontend messages until a Sync + message is received, then it will issue ReadyForQuery and return to normal + processing. If the <code class="command">COPY</code> command was issued in a simple + Query message, the rest of that message is discarded and ReadyForQuery + is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail + messages issued by the frontend will simply be dropped. + </p><p> + The backend will ignore Flush and Sync messages received during copy-in + mode. Receipt of any other non-copy message type constitutes an error + that will abort the copy-in state as described above. (The exception for + Flush and Sync is for the convenience of client libraries that always + send Flush or Sync after an Execute message, without checking whether + the command to be executed is a <code class="command">COPY FROM STDIN</code>.) + </p><p> + Copy-out mode (data transfer from the server) is initiated when the + backend executes a <code class="command">COPY TO STDOUT</code> SQL statement. The backend + sends a CopyOutResponse message to the frontend, followed by + zero or more CopyData messages (always one per row), followed by CopyDone. + The backend then reverts to the command-processing mode it was + in before the <code class="command">COPY</code> started, and sends CommandComplete. + The frontend cannot abort the transfer (except by closing the connection + or issuing a Cancel request), + but it can discard unwanted CopyData and CopyDone messages. + </p><p> + In the event of a backend-detected error during copy-out mode, + the backend will issue an ErrorResponse message and revert to normal + processing. The frontend should treat receipt of ErrorResponse as + terminating the copy-out mode. + </p><p> + It is possible for NoticeResponse and ParameterStatus messages to be + interspersed between CopyData messages; frontends must handle these cases, + and should be prepared for other asynchronous message types as well (see + <a class="xref" href="protocol-flow.html#PROTOCOL-ASYNC" title="52.2.6. Asynchronous Operations">Section 52.2.6</a>). Otherwise, any message type other than + CopyData or CopyDone may be treated as terminating copy-out mode. + </p><p> + There is another Copy-related mode called copy-both, which allows + high-speed bulk data transfer to <span class="emphasis"><em>and</em></span> from the server. + Copy-both mode is initiated when a backend in walsender mode + executes a <code class="command">START_REPLICATION</code> statement. The + backend sends a CopyBothResponse message to the frontend. Both + the backend and the frontend may then send CopyData messages + until either end sends a CopyDone message. After the client + sends a CopyDone message, the connection goes from copy-both mode to + copy-out mode, and the client may not send any more CopyData messages. + Similarly, when the server sends a CopyDone message, the connection + goes into copy-in mode, and the server may not send any more CopyData + messages. After both sides have sent a CopyDone message, the copy mode + is terminated, and the backend reverts to the command-processing mode. + In the event of a backend-detected error during copy-both mode, + the backend will issue an ErrorResponse message, discard frontend messages + until a Sync message is received, and then issue ReadyForQuery and return + to normal processing. The frontend should treat receipt of ErrorResponse + as terminating the copy in both directions; no CopyDone should be sent + in this case. See <a class="xref" href="protocol-replication.html" title="52.4. Streaming Replication Protocol">Section 52.4</a> for more + information on the subprotocol transmitted over copy-both mode. + </p><p> + The CopyInResponse, CopyOutResponse and CopyBothResponse messages + include fields that inform the frontend of the number of columns + per row and the format codes being used for each column. (As of + the present implementation, all columns in a given <code class="command">COPY</code> + operation will use the same format, but the message design does not + assume this.) + </p></div><div class="sect2" id="PROTOCOL-ASYNC"><div class="titlepage"><div><div><h3 class="title">52.2.6. Asynchronous Operations</h3></div></div></div><p> + There are several cases in which the backend will send messages that + are not specifically prompted by the frontend's command stream. + Frontends must be prepared to deal with these messages at any time, + even when not engaged in a query. + At minimum, one should check for these cases before beginning to + read a query response. + </p><p> + It is possible for NoticeResponse messages to be generated due to + outside activity; for example, if the database administrator commands + a <span class="quote">“<span class="quote">fast</span>”</span> database shutdown, the backend will send a NoticeResponse + indicating this fact before closing the connection. Accordingly, + frontends should always be prepared to accept and display NoticeResponse + messages, even when the connection is nominally idle. + </p><p> + ParameterStatus messages will be generated whenever the active + value changes for any of the parameters the backend believes the + frontend should know about. Most commonly this occurs in response + to a <code class="command">SET</code> SQL command executed by the frontend, and + this case is effectively synchronous — but it is also possible + for parameter status changes to occur because the administrator + changed a configuration file and then sent the + <span class="systemitem">SIGHUP</span> signal to the server. Also, + if a <code class="command">SET</code> command is rolled back, an appropriate + ParameterStatus message will be generated to report the current + effective value. + </p><p> + At present there is a hard-wired set of parameters for which + ParameterStatus will be generated: they are + <code class="varname">server_version</code>, + <code class="varname">server_encoding</code>, + <code class="varname">client_encoding</code>, + <code class="varname">application_name</code>, + <code class="varname">is_superuser</code>, + <code class="varname">session_authorization</code>, + <code class="varname">DateStyle</code>, + <code class="varname">IntervalStyle</code>, + <code class="varname">TimeZone</code>, + <code class="varname">integer_datetimes</code>, and + <code class="varname">standard_conforming_strings</code>. + (<code class="varname">server_encoding</code>, <code class="varname">TimeZone</code>, and + <code class="varname">integer_datetimes</code> were not reported by releases before 8.0; + <code class="varname">standard_conforming_strings</code> was not reported by releases + before 8.1; + <code class="varname">IntervalStyle</code> was not reported by releases before 8.4; + <code class="varname">application_name</code> was not reported by releases before 9.0.) + Note that + <code class="varname">server_version</code>, + <code class="varname">server_encoding</code> and + <code class="varname">integer_datetimes</code> + are pseudo-parameters that cannot change after startup. + This set might change in the future, or even become configurable. + Accordingly, a frontend should simply ignore ParameterStatus for + parameters that it does not understand or care about. + </p><p> + If a frontend issues a <code class="command">LISTEN</code> command, then the + backend will send a NotificationResponse message (not to be + confused with NoticeResponse!) whenever a + <code class="command">NOTIFY</code> command is executed for the same + channel name. + </p><div class="note"><h3 class="title">Note</h3><p> + At present, NotificationResponse can only be sent outside a + transaction, and thus it will not occur in the middle of a + command-response series, though it might occur just before ReadyForQuery. + It is unwise to design frontend logic that assumes that, however. + Good practice is to be able to accept NotificationResponse at any + point in the protocol. + </p></div></div><div class="sect2" id="id-1.10.5.7.9"><div class="titlepage"><div><div><h3 class="title">52.2.7. Canceling Requests in Progress</h3></div></div></div><p> + During the processing of a query, the frontend might request + cancellation of the query. The cancel request is not sent + directly on the open connection to the backend for reasons of + implementation efficiency: we don't want to have the backend + constantly checking for new input from the frontend during query + processing. Cancel requests should be relatively infrequent, so + we make them slightly cumbersome in order to avoid a penalty in + the normal case. + </p><p> + To issue a cancel request, the frontend opens a new connection to + the server and sends a CancelRequest message, rather than the + StartupMessage message that would ordinarily be sent across a new + connection. The server will process this request and then close + the connection. For security reasons, no direct reply is made to + the cancel request message. + </p><p> + A CancelRequest message will be ignored unless it contains the + same key data (PID and secret key) passed to the frontend during + connection start-up. If the request matches the PID and secret + key for a currently executing backend, the processing of the + current query is aborted. (In the existing implementation, this is + done by sending a special signal to the backend process that is + processing the query.) + </p><p> + The cancellation signal might or might not have any effect — for + example, if it arrives after the backend has finished processing + the query, then it will have no effect. If the cancellation is + effective, it results in the current command being terminated + early with an error message. + </p><p> + The upshot of all this is that for reasons of both security and + efficiency, the frontend has no direct way to tell whether a + cancel request has succeeded. It must continue to wait for the + backend to respond to the query. Issuing a cancel simply improves + the odds that the current query will finish soon, and improves the + odds that it will fail with an error message instead of + succeeding. + </p><p> + Since the cancel request is sent across a new connection to the + server and not across the regular frontend/backend communication + link, it is possible for the cancel request to be issued by any + process, not just the frontend whose query is to be canceled. + This might provide additional flexibility when building + multiple-process applications. It also introduces a security + risk, in that unauthorized persons might try to cancel queries. + The security risk is addressed by requiring a dynamically + generated secret key to be supplied in cancel requests. + </p></div><div class="sect2" id="id-1.10.5.7.10"><div class="titlepage"><div><div><h3 class="title">52.2.8. Termination</h3></div></div></div><p> + The normal, graceful termination procedure is that the frontend + sends a Terminate message and immediately closes the connection. + On receipt of this message, the backend closes the connection and + terminates. + </p><p> + In rare cases (such as an administrator-commanded database shutdown) + the backend might disconnect without any frontend request to do so. + In such cases the backend will attempt to send an error or notice message + giving the reason for the disconnection before it closes the connection. + </p><p> + Other termination scenarios arise from various failure cases, such as core + dump at one end or the other, loss of the communications link, loss of + message-boundary synchronization, etc. If either frontend or backend sees + an unexpected closure of the connection, it should clean + up and terminate. The frontend has the option of launching a new backend + by recontacting the server if it doesn't want to terminate itself. + Closing the connection is also advisable if an unrecognizable message type + is received, since this probably indicates loss of message-boundary sync. + </p><p> + For either normal or abnormal termination, any open transaction is + rolled back, not committed. One should note however that if a + frontend disconnects while a non-<code class="command">SELECT</code> query + is being processed, the backend will probably finish the query + before noticing the disconnection. If the query is outside any + transaction block (<code class="command">BEGIN</code> ... <code class="command">COMMIT</code> + sequence) then its results might be committed before the + disconnection is recognized. + </p></div><div class="sect2" id="id-1.10.5.7.11"><div class="titlepage"><div><div><h3 class="title">52.2.9. <acronym class="acronym">SSL</acronym> Session Encryption</h3></div></div></div><p> + If <span class="productname">PostgreSQL</span> was built with + <acronym class="acronym">SSL</acronym> support, frontend/backend communications + can be encrypted using <acronym class="acronym">SSL</acronym>. This provides + communication security in environments where attackers might be + able to capture the session traffic. For more information on + encrypting <span class="productname">PostgreSQL</span> sessions with + <acronym class="acronym">SSL</acronym>, see <a class="xref" href="ssl-tcp.html" title="18.9. Secure TCP/IP Connections with SSL">Section 18.9</a>. + </p><p> + To initiate an <acronym class="acronym">SSL</acronym>-encrypted connection, the + frontend initially sends an SSLRequest message rather than a + StartupMessage. The server then responds with a single byte + containing <code class="literal">S</code> or <code class="literal">N</code>, indicating that it is + willing or unwilling to perform <acronym class="acronym">SSL</acronym>, + respectively. The frontend might close the connection at this point + if it is dissatisfied with the response. To continue after + <code class="literal">S</code>, perform an <acronym class="acronym">SSL</acronym> startup handshake + (not described here, part of the <acronym class="acronym">SSL</acronym> + specification) with the server. If this is successful, continue + with sending the usual StartupMessage. In this case the + StartupMessage and all subsequent data will be + <acronym class="acronym">SSL</acronym>-encrypted. To continue after + <code class="literal">N</code>, send the usual StartupMessage and proceed without + encryption. + (Alternatively, it is permissible to issue a GSSENCRequest message + after an <code class="literal">N</code> response to try to + use <acronym class="acronym">GSSAPI</acronym> encryption instead + of <acronym class="acronym">SSL</acronym>.) + </p><p> + The frontend should also be prepared to handle an ErrorMessage + response to SSLRequest from the server. This would only occur if + the server predates the addition of <acronym class="acronym">SSL</acronym> support + to <span class="productname">PostgreSQL</span>. (Such servers are now very ancient, + and likely do not exist in the wild anymore.) + In this case the connection must + be closed, but the frontend might choose to open a fresh connection + and proceed without requesting <acronym class="acronym">SSL</acronym>. + </p><p> + An initial SSLRequest can also be used in a connection that is being + opened to send a CancelRequest message. + </p><p> + While the protocol itself does not provide a way for the server to + force <acronym class="acronym">SSL</acronym> encryption, the administrator can + configure the server to reject unencrypted sessions as a byproduct + of authentication checking. + </p></div><div class="sect2" id="id-1.10.5.7.12"><div class="titlepage"><div><div><h3 class="title">52.2.10. <acronym class="acronym">GSSAPI</acronym> Session Encryption</h3></div></div></div><p> + If <span class="productname">PostgreSQL</span> was built with + <acronym class="acronym">GSSAPI</acronym> support, frontend/backend communications + can be encrypted using <acronym class="acronym">GSSAPI</acronym>. This provides + communication security in environments where attackers might be + able to capture the session traffic. For more information on + encrypting <span class="productname">PostgreSQL</span> sessions with + <acronym class="acronym">GSSAPI</acronym>, see <a class="xref" href="gssapi-enc.html" title="18.10. Secure TCP/IP Connections with GSSAPI Encryption">Section 18.10</a>. + </p><p> + To initiate a <acronym class="acronym">GSSAPI</acronym>-encrypted connection, the + frontend initially sends a GSSENCRequest message rather than a + StartupMessage. The server then responds with a single byte + containing <code class="literal">G</code> or <code class="literal">N</code>, indicating that it + is willing or unwilling to perform <acronym class="acronym">GSSAPI</acronym> encryption, + respectively. The frontend might close the connection at this point + if it is dissatisfied with the response. To continue after + <code class="literal">G</code>, using the GSSAPI C bindings as discussed in RFC2744 + or equivalent, perform a <acronym class="acronym">GSSAPI</acronym> initialization by + calling <code class="function">gss_init_sec_context()</code> in a loop and sending + the result to the server, starting with an empty input and then with each + result from the server, until it returns no output. When sending the + results of <code class="function">gss_init_sec_context()</code> to the server, + prepend the length of the message as a four byte integer in network byte + order. + To continue after + <code class="literal">N</code>, send the usual StartupMessage and proceed without + encryption. + (Alternatively, it is permissible to issue an SSLRequest message + after an <code class="literal">N</code> response to try to + use <acronym class="acronym">SSL</acronym> encryption instead + of <acronym class="acronym">GSSAPI</acronym>.) + </p><p> + The frontend should also be prepared to handle an ErrorMessage + response to GSSENCRequest from the server. This would only occur if + the server predates the addition of <acronym class="acronym">GSSAPI</acronym> encryption + support to <span class="productname">PostgreSQL</span>. In this case the + connection must be closed, but the frontend might choose to open a fresh + connection and proceed without requesting <acronym class="acronym">GSSAPI</acronym> + encryption. + </p><p> + An initial GSSENCRequest can also be used in a connection that is being + opened to send a CancelRequest message. + </p><p> + Once <acronym class="acronym">GSSAPI</acronym> encryption has been successfully + established, use <code class="function">gss_wrap()</code> to + encrypt the usual StartupMessage and all subsequent data, prepending the + length of the result from <code class="function">gss_wrap()</code> as a four byte + integer in network byte order to the actual encrypted payload. Note that + the server will only accept encrypted packets from the client which are less + than 16kB; <code class="function">gss_wrap_size_limit()</code> should be used by the + client to determine the size of the unencrypted message which will fit + within this limit and larger messages should be broken up into multiple + <code class="function">gss_wrap()</code> calls. Typical segments are 8kB of + unencrypted data, resulting in encrypted packets of slightly larger than 8kB + but well within the 16kB maximum. The server can be expected to not send + encrypted packets of larger than 16kB to the client. + </p><p> + While the protocol itself does not provide a way for the server to + force <acronym class="acronym">GSSAPI</acronym> encryption, the administrator can + configure the server to reject unencrypted sessions as a byproduct + of authentication checking. + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="protocol-overview.html" title="52.1. Overview">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="protocol.html" title="Chapter 52. Frontend/Backend Protocol">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sasl-authentication.html" title="52.3. SASL Authentication">Next</a></td></tr><tr><td width="40%" align="left" valign="top">52.1. Overview </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 52.3. SASL Authentication</td></tr></table></div></body></html>
\ No newline at end of file |