Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / protocol-flow.txt

54.2. Message Flow #

   54.2.1. Start-up
   54.2.2. Simple Query
   54.2.3. Extended Query
   54.2.4. Pipelining
   54.2.5. Function Call
   54.2.6. COPY Operations
   54.2.7. Asynchronous Operations
   54.2.8. Canceling Requests in Progress
   54.2.9. Termination
   54.2.10. SSL Session Encryption
   54.2.11. GSSAPI Session Encryption

   This section describes the message flow and the semantics of each
   message type. (Details of the exact representation of each message
   appear in Section 54.7.) There are several different sub-protocols
   depending on the state of the connection: start-up, query, function
   call, COPY, 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.

54.2.1. Start-up #

   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 pg_hba.conf) to determine whether the connection is
   provisionally acceptable, and what additional authentication is
   required (if any).

   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.

   The authentication cycle ends with the server either rejecting the
   connection attempt (ErrorResponse), or sending AuthenticationOk.

   The possible messages from the server in this phase are:

   ErrorResponse
          The connection attempt has been rejected. The server then
          immediately closes the connection.

   AuthenticationOk
          The authentication exchange is successfully completed.

   AuthenticationKerberosV5
          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.

   AuthenticationCleartextPassword
          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.

   AuthenticationMD5Password
          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 concat('md5',
          md5(concat(md5(concat(password, username)), random-salt))).
          (Keep in mind the md5() function returns its result as a hex
          string.)

Warning

          Support for MD5-encrypted passwords is deprecated and will be
          removed in a future release of PostgreSQL. Refer to Section 20.5
          for details about migrating to another password type.

   AuthenticationGSS
          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.

   AuthenticationSSPI
          The frontend must now initiate an 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.

   AuthenticationGSSContinue
          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.

   AuthenticationSASL
          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 Section 54.3
          for details.

   AuthenticationSASLContinue
          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.

   AuthenticationSASLFinal
          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.

   NegotiateProtocolVersion
          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 _pq_.) in the
          startup packet.

          After this message, the authentication will continue using the
          version indicated by the server. If the client does not support
          the older version, it should immediately close the connection.
          If the server does not send this message, it supports the
          client's requested protocol version and all the protocol
          options.

   If the frontend does not support the authentication method requested by
   the server, then it should immediately close the connection.

   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.

   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.

   The possible messages from the backend in this phase are:

   BackendKeyData
          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.

          The PostgreSQL server will always send this message, but some
          third party backend implementations of the protocol that don't
          support query cancellation are known not to.

   ParameterStatus
          This message informs the frontend about the current (initial)
          setting of backend parameters, such as client_encoding or
          DateStyle. The frontend can ignore this message, or record the
          settings for its future use; see Section 54.2.7 for more
          details. The frontend should not respond to this message, but
          should continue listening for a ReadyForQuery message.

   ReadyForQuery
          Start-up is completed. The frontend can now issue commands.

   ErrorResponse
          Start-up failed. The connection is closed after sending this
          message.

   NoticeResponse
          A warning message has been issued. The frontend should display
          the message but continue listening for ReadyForQuery or
          ErrorResponse.

   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.

54.2.2. Simple Query #

   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.)

   The possible response messages from the backend are:

   CommandComplete
          An SQL command completed normally.

   CopyInResponse
          The backend is ready to copy data from the frontend to a table;
          see Section 54.2.6.

   CopyOutResponse
          The backend is ready to copy data from a table to the frontend;
          see Section 54.2.6.

   RowDescription
          Indicates that rows are about to be returned in response to a
          SELECT, FETCH, 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.

   DataRow
          One of the set of rows returned by a SELECT, FETCH, etc. query.

   EmptyQueryResponse
          An empty query string was recognized.

   ErrorResponse
          An error has occurred.

   ReadyForQuery
          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.

   NoticeResponse
          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.

   The response to a SELECT query (or other queries that return row sets,
   such as EXPLAIN or SHOW) normally consists of RowDescription, zero or
   more DataRow messages, and then CommandComplete. COPY to or from the
   frontend invokes special protocol as described in Section 54.2.6. All
   other query types normally produce only a CommandComplete message.

   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.

   If a completely empty (no contents other than whitespace) query string
   is received, the response is EmptyQueryResponse followed by
   ReadyForQuery.

   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.

   In simple Query mode, the format of retrieved values is always text,
   except when the given command is a FETCH from a cursor declared with
   the BINARY 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.

   A frontend must be prepared to accept ErrorResponse and NoticeResponse
   messages whenever it is expecting any other type of message. See also
   Section 54.2.7 concerning messages that the backend might generate due
   to outside events.

   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.

54.2.2.1. Multiple Statements in a Simple Query #

   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
INSERT INTO mytable VALUES(1);
SELECT 1/0;
INSERT INTO mytable VALUES(2);

   then the divide-by-zero failure in the SELECT will force rollback of
   the first INSERT. Furthermore, because execution of the message is
   abandoned at the first error, the second INSERT is never attempted at
   all.

   If instead the message contains
BEGIN;
INSERT INTO mytable VALUES(1);
COMMIT;
INSERT INTO mytable VALUES(2);
SELECT 1/0;

   then the first INSERT is committed by the explicit COMMIT command. The
   second INSERT and the SELECT are still treated as a single transaction,
   so that the divide-by-zero failure will roll back the second INSERT,
   but not the first one.

   This behavior is implemented by running the statements in a
   multi-statement Query message in an implicit transaction block 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).

   If the session is already in a transaction block, as a result of a
   BEGIN 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 COMMIT or ROLLBACK
   closing the existing transaction block, then any following statements
   are executed in an implicit transaction block. Conversely, if a BEGIN
   appears in a multi-statement Query message, then it starts a regular
   transaction block that will only be terminated by an explicit COMMIT or
   ROLLBACK, whether that appears in this Query message or a later one. If
   the BEGIN 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.

   A COMMIT or ROLLBACK appearing in an implicit transaction block is
   executed as normal, closing the implicit block; however, a warning will
   be issued since a COMMIT or ROLLBACK without a previous BEGIN might
   represent a mistake. If more statements follow, a new implicit
   transaction block will be started for them.

   Savepoints are not allowed in an implicit transaction block, since they
   would conflict with the behavior of automatically closing the block
   upon any error.

   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
BEGIN;
SELECT 1/0;
ROLLBACK;

   in a single Query message, the session will be left inside a failed
   regular transaction block, since the ROLLBACK is not reached after the
   divide-by-zero error. Another ROLLBACK will be needed to restore the
   session to a usable state.

   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
BEGIN;
INSERT INTO mytable VALUES(1);
COMMIT;
INSERT INTO mytable VALUES(2);
SELCT 1/0;

   then none of the statements would get run, resulting in the visible
   difference that the first INSERT is not committed. Errors detected at
   semantic analysis or later, such as a misspelled table or column name,
   do not have this effect.

   Lastly, note that all the statements within the Query message will
   observe the same value of statement_timestamp(), since that timestamp
   is updated only upon receipt of the Query message. This will result in
   them all observing the same value of transaction_timestamp() as well,
   except in cases where the query string ends a previously-started
   transaction and begins a new one.

54.2.3. Extended Query #

   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.

   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.

Note

   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 ($n) used in the query string. Another special case
   is that a parameter's type can be specified as void (that is, the OID
   of the void 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 void 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
   foo($1,$2,$3,$4) could match a function with two IN and two OUT
   arguments, if $3 and $4 are specified as having type void.

Note

   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.

   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 PREPARE and EXECUTE.

   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 void 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.

Note

   The choice between text and binary output is determined by the format
   codes given in Bind, regardless of the SQL command involved. The BINARY
   attribute in cursor declarations is irrelevant when using extended
   query protocol.

   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.

   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 DECLARE CURSOR and FETCH.

   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 “fetch all
   rows”). 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.

   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.

   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
   BEGIN/COMMIT transaction block (“close” 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 while processing Sync — this ensures that there is
   one and only one ReadyForQuery sent for each Sync.)

Note

   Sync does not cause a transaction block opened with BEGIN to be closed.
   It is possible to detect this situation since the ReadyForQuery message
   includes transaction status information.

   In addition to these fundamental, required operations, there are
   several optional operations that can be used with extended-query
   protocol.

   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.

   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.

Tip

   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.

   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.

   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.

Note

   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.

54.2.4. Pipelining #

   Use of the extended query protocol allows pipelining, which means
   sending a series of queries without waiting for earlier ones to
   complete. This reduces the number of network round trips needed to
   complete a given series of operations. However, the user must carefully
   consider the required behavior if one of the steps fails, since later
   queries will already be in flight to the server.

   One way to deal with that is to make the whole query series be a single
   transaction, that is wrap it in BEGIN ... COMMIT. However, this does
   not help if one wishes for some of the commands to commit independently
   of others.

   The extended query protocol provides another way to manage this
   concern, which is to omit sending Sync messages between steps that are
   dependent. Since, after an error, the backend will skip command
   messages until it finds Sync, this allows later commands in a pipeline
   to be skipped automatically when an earlier one fails, without the
   client having to manage that explicitly with BEGIN and COMMIT.
   Independently-committable segments of the pipeline can be separated by
   Sync messages.

   If the client has not issued an explicit BEGIN, then an implicit
   transaction block is started and each Sync ordinarily causes an
   implicit COMMIT if the preceding step(s) succeeded, or an implicit
   ROLLBACK if they failed. This implicit transaction block will only be
   detected by the server when the first command ends without a sync.
   There are a few DDL commands (such as CREATE DATABASE) that cannot be
   executed inside a transaction block. If one of these is executed in a
   pipeline, it will fail unless it is the first command after a Sync.
   Furthermore, upon success it will force an immediate commit to preserve
   database consistency. Thus a Sync immediately following one of these
   commands has no effect except to respond with ReadyForQuery.

   When using this method, completion of the pipeline must be determined
   by counting ReadyForQuery messages and waiting for that to reach the
   number of Syncs sent. Counting command completion responses is
   unreliable, since some of the commands may be skipped and thus not
   produce a completion message.

54.2.5. Function Call #

   The Function Call sub-protocol allows the client to request a direct
   call of any function that exists in the database's pg_proc system
   catalog. The client must have execute permission for the function.

Note

   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 SELECT function($1, ...). The
   Function Call cycle can then be replaced with Bind/Execute.

   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.

   The possible response messages from the backend are:

   ErrorResponse
          An error has occurred.

   FunctionCallResponse
          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.)

   ReadyForQuery
          Processing of the function call is complete. ReadyForQuery will
          always be sent, whether processing terminates successfully or
          with an error.

   NoticeResponse
          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.

54.2.6. COPY Operations #

   The COPY 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.

   Copy-in mode (data transfer to the server) is initiated when the
   backend executes a COPY FROM STDIN 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 COPY SQL statement to fail with an error). The backend then
   reverts to the command-processing mode it was in before the COPY
   started, which will be either simple or extended query protocol. It
   will next send either CommandComplete (if successful) or ErrorResponse
   (if not).

   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 COPY 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 COPY 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.

   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 COPY FROM STDIN.)

   Copy-out mode (data transfer from the server) is initiated when the
   backend executes a COPY TO STDOUT 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 COPY 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.

   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.

   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 Section 54.2.7). Otherwise, any message type other than
   CopyData or CopyDone may be treated as terminating copy-out mode.

   There is another Copy-related mode called copy-both, which allows
   high-speed bulk data transfer to and from the server. Copy-both mode is
   initiated when a backend in walsender mode executes a START_REPLICATION
   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 Section 54.4 for more information on
   the subprotocol transmitted over copy-both mode.

   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 COPY operation will use the same
   format, but the message design does not assume this.)

54.2.7. Asynchronous Operations #

   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.

   It is possible for NoticeResponse messages to be generated due to
   outside activity; for example, if the database administrator commands a
   “fast” 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.

   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 SET 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 SIGHUP signal to the server. Also, if a SET command is rolled
   back, an appropriate ParameterStatus message will be generated to
   report the current effective value.

   At present there is a hard-wired set of parameters for which
   ParameterStatus will be generated. They are:
   application_name              scram_iterations
   client_encoding               search_path
   DateStyle                     server_encoding
   default_transaction_read_only server_version
   in_hot_standby                session_authorization
   integer_datetimes             standard_conforming_strings
   IntervalStyle                 TimeZone
   is_superuser

   (default_transaction_read_only and in_hot_standby were not reported by
   releases before 14; scram_iterations was not reported by releases
   before 16; search_path was not reported by releases before 18.) Note
   that server_version, server_encoding and integer_datetimes 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.

   If a frontend issues a LISTEN command, then the backend will send a
   NotificationResponse message (not to be confused with NoticeResponse!)
   whenever a NOTIFY command is executed for the same channel name.

Note

   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.

54.2.8. Canceling Requests in Progress #

   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.

   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.

   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.)

   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.

   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.

   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.

54.2.9. Termination #

   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.

   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.

   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.

   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-SELECT query is being processed, the backend
   will probably finish the query before noticing the disconnection. If
   the query is outside any transaction block (BEGIN ... COMMIT sequence)
   then its results might be committed before the disconnection is
   recognized.

54.2.10. SSL Session Encryption #

   If PostgreSQL was built with SSL support, frontend/backend
   communications can be encrypted using SSL. This provides communication
   security in environments where attackers might be able to capture the
   session traffic. For more information on encrypting PostgreSQL sessions
   with SSL, see Section 18.9.

   To initiate an SSL-encrypted connection, the frontend initially sends
   an SSLRequest message rather than a StartupMessage. The server then
   responds with a single byte containing S or N, indicating that it is
   willing or unwilling to perform SSL, respectively. The frontend might
   close the connection at this point if it is dissatisfied with the
   response. To continue after S, perform an SSL startup handshake (not
   described here, part of the SSL 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 SSL-encrypted.
   To continue after N, send the usual StartupMessage and proceed without
   encryption. (Alternatively, it is permissible to issue a GSSENCRequest
   message after an N response to try to use GSSAPI encryption instead of
   SSL.)

   The frontend should also be prepared to handle an ErrorMessage response
   to SSLRequest from the server. The frontend should not display this
   error message to the user/application, since the server has not been
   authenticated (CVE-2024-10977). In this case the connection must be
   closed, but the frontend might choose to open a fresh connection and
   proceed without requesting SSL.

   When SSL encryption can be performed, the server is expected to send
   only the single S byte and then wait for the frontend to initiate an
   SSL handshake. If additional bytes are available to read at this point,
   it likely means that a man-in-the-middle is attempting to perform a
   buffer-stuffing attack (CVE-2021-23222). Frontends should be coded
   either to read exactly one byte from the socket before turning the
   socket over to their SSL library, or to treat it as a protocol
   violation if they find they have read additional bytes.

   Likewise the server expects the client to not begin the SSL negotiation
   until it receives the server's single byte response to the SSL request.
   If the client begins the SSL negotiation immediately without waiting
   for the server response to be received it can reduce connection latency
   by one round-trip. However this comes at the cost of not being able to
   handle the case where the server sends a negative response to the SSL
   request. In that case instead of continuing with either GSSAPI or an
   unencrypted connection or a protocol error the server will simply
   disconnect.

   An initial SSLRequest can also be used in a connection that is being
   opened to send a CancelRequest message.

   A second alternate way to initiate SSL encryption is available. The
   server will recognize connections which immediately begin SSL
   negotiation without any previous SSLRequest packets. Once the SSL
   connection is established the server will expect a normal
   startup-request packet and continue negotiation over the encrypted
   channel. In this case any other requests for encryption will be
   refused. This method is not preferred for general purpose tools as it
   cannot negotiate the best connection encryption available or handle
   unencrypted connections. However it is useful for environments where
   both the server and client are controlled together. In that case it
   avoids one round trip of latency and allows the use of network tools
   that depend on standard SSL connections. When using SSL connections in
   this style the client is required to use the ALPN extension defined by
   RFC 7301 to protect against protocol confusion attacks. The PostgreSQL
   protocol is "postgresql" as registered at IANA TLS ALPN Protocol IDs
   registry.

   While the protocol itself does not provide a way for the server to
   force SSL encryption, the administrator can configure the server to
   reject unencrypted sessions as a byproduct of authentication checking.

54.2.11. GSSAPI Session Encryption #

   If PostgreSQL was built with GSSAPI support, frontend/backend
   communications can be encrypted using GSSAPI. This provides
   communication security in environments where attackers might be able to
   capture the session traffic. For more information on encrypting
   PostgreSQL sessions with GSSAPI, see Section 18.10.

   To initiate a GSSAPI-encrypted connection, the frontend initially sends
   a GSSENCRequest message rather than a StartupMessage. The server then
   responds with a single byte containing G or N, indicating that it is
   willing or unwilling to perform GSSAPI encryption, respectively. The
   frontend might close the connection at this point if it is dissatisfied
   with the response. To continue after G, using the GSSAPI C bindings as
   discussed in RFC 2744 or equivalent, perform a GSSAPI initialization by
   calling gss_init_sec_context() 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
   gss_init_sec_context() to the server, prepend the length of the message
   as a four byte integer in network byte order. To continue after N, send
   the usual StartupMessage and proceed without encryption.
   (Alternatively, it is permissible to issue an SSLRequest message after
   an N response to try to use SSL encryption instead of GSSAPI.)

   The frontend should also be prepared to handle an ErrorMessage response
   to GSSENCRequest from the server. The frontend should not display this
   error message to the user/application, since the server has not been
   authenticated (CVE-2024-10977). In this case the connection must be
   closed, but the frontend might choose to open a fresh connection and
   proceed without requesting GSSAPI encryption.

   When GSSAPI encryption can be performed, the server is expected to send
   only the single G byte and then wait for the frontend to initiate a
   GSSAPI handshake. If additional bytes are available to read at this
   point, it likely means that a man-in-the-middle is attempting to
   perform a buffer-stuffing attack (CVE-2021-23222). Frontends should be
   coded either to read exactly one byte from the socket before turning
   the socket over to their GSSAPI library, or to treat it as a protocol
   violation if they find they have read additional bytes.

   An initial GSSENCRequest can also be used in a connection that is being
   opened to send a CancelRequest message.

   Once GSSAPI encryption has been successfully established, use
   gss_wrap() to encrypt the usual StartupMessage and all subsequent data,
   prepending the length of the result from gss_wrap() 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; gss_wrap_size_limit() 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
   gss_wrap() 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.

   While the protocol itself does not provide a way for the server to
   force GSSAPI encryption, the administrator can configure the server to
   reject unencrypted sessions as a byproduct of authentication checking.