-<Chapter Id="protocol">
-<DocInfo>
-<Author>
-<FirstName>Phil</FirstName>
-<Surname>Thompson</Surname>
-</Author>
-<Date>1998-08-08</Date>
-</DocInfo>
-<Title>Frontend/Backend Protocol</Title>
-
-<Para>
-<Note>
-<Para>
-Written by <ULink url="mailto:phil@river-bank.demon.co.uk">Phil Thompson</ULink>.
-Updates for protocol 2.0 by <ULink url="mailto:tgl@sss.pgh.pa.us">Tom Lane</ULink>.
-</Para>
-</Note>
-
-<Para>
-<ProductName>Postgres</ProductName> uses a message-based protocol for communication between frontends
-and backends. The protocol is implemented over <Acronym>TCP/IP</Acronym> and also on Unix sockets.
-<ProductName>Postgres</ProductName> v6.3 introduced version numbers into the protocol.
-This was done in such
-a way as to still allow connections from earlier versions of frontends, but
-this document does not cover the protocol used by those earlier versions.
-
-<Para>
-This document describes version 2.0 of the protocol, implemented in
-<ProductName>Postgres</ProductName> v6.4 and later.
-
-<Para>
-Higher level features built on this protocol (for example, how <FileName>libpq</FileName> passes
-certain environment variables after the connection is established)
-are covered elsewhere.
-
-<Sect1>
-<Title>Overview</Title>
-
-<Para>
-The three major components are the frontend (running on the client) and the
-postmaster and backend (running on the server). The postmaster and backend
-have different roles but may be implemented by the same executable.
-
-<Para>
-A frontend sends a startup packet to the postmaster. This includes the names
-of the user and the database the user wants to connect to. The postmaster then
-uses this, and the information in the pg_hba.conf(5) file to determine what
-further authentication information it requires the frontend to send (if any)
-and responds to the frontend accordingly.
-
-<Para>
-The frontend then sends any required authentication information. Once the
-postmaster validates this it responds to the frontend that it is authenticated
-and hands over the connection to a backend. The backend then sends a message
-indicating successful startup (normal case) or failure (for example, an
-invalid database name).
-
-<Para>
-Subsequent communications are query and result packets exchanged between the
-frontend and the backend. The postmaster takes no further part in ordinary
-query/result communication. (However, the postmaster is involved when the
-frontend wishes to cancel a query currently being executed by its backend.
-Further details about that appear below.)
-
-<Para>
-When the frontend wishes to disconnect it sends an appropriate packet and
-closes the connection without waiting for a response for the backend.
-
-<Para>
-Packets are sent as a data stream. The first byte determines what should be
-expected in the rest of the packet. The exception is packets sent from a
-frontend to the postmaster, which comprise a packet length then the packet
-itself. The difference is historical.
-
-<Sect1>
-<Title>Protocol</Title>
-
-<Para>
-This section describes the message flow. There are four different types of
-flows depending on the state of the connection:
-startup, query, function call, and termination.
-There are also special provisions for notification responses and command
-cancellation, which can occur at any time after the startup phase.
-
-
-<Sect2>
-<Title>Startup</Title>
-
-<Para>
-Startup is divided into an authentication phase and a backend startup phase.
-
-<Para>
-Initially, the frontend sends a StartupPacket. The postmaster uses this info
-and the contents of the pg_hba.conf(5) file to determine what authentication
-method the frontend must use. The postmaster then responds with one of the
-following messages:
-
-<Para>
-<VariableList>
-<VarListEntry>
-<Term>
- ErrorResponse
-</Term>
-<ListItem>
-<Para>
- The postmaster then immediately closes the connection.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- AuthenticationOk
-</Term>
-<ListItem>
-<Para>
- The postmaster then hands over to the backend. The postmaster
- takes no further part in the communication.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- AuthenticationKerberosV4
-</Term>
-<ListItem>
-<Para>
- The frontend must then take part in a Kerberos V4
- authentication dialog (not described here) with the postmaster.
- If this is successful, the postmaster responds with an
- AuthenticationOk, otherwise it responds with an ErrorResponse.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- AuthenticationKerberosV5
-</Term>
-<ListItem>
-<Para>
- The frontend must then take part in a Kerberos V5
- authentication dialog (not described here) with the postmaster.
- If this is successful, the postmaster responds with an
- AuthenticationOk, otherwise it responds with an ErrorResponse.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- AuthenticationUnencryptedPassword
-</Term>
-<ListItem>
-<Para>
- The frontend must then send an UnencryptedPasswordPacket.
- If this is the correct password, the postmaster responds with
- an AuthenticationOk, otherwise it responds with an
- ErrorResponse.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- AuthenticationEncryptedPassword
-</Term>
-<ListItem>
-<Para>
- The frontend must then send an EncryptedPasswordPacket.
- If this is the correct password, the postmaster responds with
- an AuthenticationOk, otherwise it responds with an
- ErrorResponse.
-</Para>
-</ListItem>
-</VarListEntry>
-</VariableList>
-</Para>
-
-<Para>
-If the frontend does not support the authentication method requested by the
-postmaster, then it should immediately close the connection.
-
-<Para>
-After sending AuthenticationOk, the postmaster attempts to launch a backend
-process. Since this might fail, or the backend might encounter a failure
-during startup, the frontend must wait for the backend to acknowledge
-successful startup. The frontend should send no messages at this point.
-The possible messages from the backend during this phase are:
-
-<Para>
-<VariableList>
-<VarListEntry>
-<Term>
- BackendKeyData
-</Term>
-<ListItem>
-<Para>
- This message is issued after successful backend startup.
- It 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.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- ReadyForQuery
-</Term>
-<ListItem>
-<Para>
- Backend startup is successful. The frontend may now issue
- query or function call messages.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- ErrorResponse
-</Term>
-<ListItem>
-<Para>
- Backend startup failed. The connection is closed after
- sending this message.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- NoticeResponse
-</Term>
-<ListItem>
-<Para>
- A warning message has been issued. The frontend should
- display the message but continue listening for ReadyForQuery
- or ErrorResponse.
-</Para>
-</ListItem>
-</VarListEntry>
-</VariableList>
-</Para>
-
-<Para>
-The ReadyForQuery message is the same one that the backend will issue after
-each query cycle. Depending on the coding needs of the frontend, it is
-reasonable to consider ReadyForQuery as starting a query cycle (and then
-BackendKeyData indicates successful conclusion of the startup phase),
-or to consider ReadyForQuery as ending the startup phase and each subsequent
-query cycle.
-
-
-<Sect2>
-<Title>Query</Title>
-
-<Para>
-A Query cycle is initiated by the frontend sending a Query message to the
-backend. 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 may safely
-send a new query or function call.
-
-<Para>
-The possible response messages from the backend are:
-
-<Para>
-<VariableList>
-<VarListEntry>
-<Term>
- CompletedResponse
-</Term>
-<ListItem>
-<Para>
- An SQL command completed normally.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- CopyInResponse
-</Term>
-<ListItem>
-<Para>
- The backend is ready to copy data from the frontend to a
- relation. The frontend should then send a CopyDataRows
- message. The backend will then respond with a
- CompletedResponse message with a tag of "COPY".
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- CopyOutResponse
-</Term>
-<ListItem>
-<Para>
- The backend is ready to copy data from a relation to the
- frontend. It then sends a CopyDataRows message, and then a
- CompletedResponse message with a tag of "COPY".
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- CursorResponse
-</Term>
-<ListItem>
-<Para>
- The query was either an insert(l), delete(l), update(l),
- fetch(l) or a select(l) command.
- If the transaction has been
- aborted then the backend sends a CompletedResponse message with
- a tag of "*ABORT STATE*". Otherwise the following responses
- are sent.
-</Para>
-<Para>
- For an insert(l) command, the backend then sends a
- CompletedResponse message with a tag of "INSERT <Replaceable>oid</Replaceable> <Replaceable>rows</Replaceable>"
- where <Replaceable>rows</Replaceable> is the number of rows inserted, and <Replaceable>oid</Replaceable> is the
- object ID of the inserted row if <Replaceable>rows</Replaceable> is 1, otherwise <Replaceable>oid</Replaceable>
- is 0.
-</Para>
-<Para>
- For a delete(l) command, the backend then sends a
- CompletedResponse message with a tag of "DELETE <Replaceable>rows</Replaceable>" where
- <Replaceable>rows</Replaceable> is the number of rows deleted.
-</Para>
-<Para>
- For an update(l) command, the backend then sends a
- CompletedResponse message with a tag of "UPDATE <Replaceable>rows</Replaceable>" where
- <Replaceable>rows</Replaceable> is the number of rows deleted.
-</Para>
-<Para>
- For a fetch(l) or select(l) command, the backend sends a
- RowDescription message. This is then followed by an AsciiRow
- or BinaryRow message (depending on whether a binary cursor was
- specified) for each row being returned to the frontend.
- Finally, the backend sends a CompletedResponse message with a
- tag of "SELECT".
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- EmptyQueryResponse
-</Term>
-<ListItem>
-<Para>
- An empty query string was recognized. (The need to specially
- distinguish this case is historical.)
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- ErrorResponse
-</Term>
-<ListItem>
-<Para>
- An error has occurred.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- ReadyForQuery
-</Term>
-<ListItem>
-<Para>
- Processing of the query string is complete. A separate
- message is sent to indicate this because the query string
- may contain multiple SQL commands. (CompletedResponse 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.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- NoticeResponse
-</Term>
-<ListItem>
-<Para>
- A warning message has been issued in relation to the query.
- Notices are in addition to other responses, ie. the backend
- will continue processing the command.
-</Para>
-</ListItem>
-</VarListEntry>
-</VariableList>
-</Para>
-
-<Para>
-A frontend must be prepared to accept ErrorResponse and NoticeResponse
-messages whenever it is expecting any other type of message.
-
-<Para>
-Actually, it is possible for NoticeResponse to arrive even when the frontend
-is not expecting any kind of message, that is, the backend is nominally idle.
-(In particular, the backend can be commanded to terminate by its postmaster.
-In that case it will send a NoticeResponse before closing the connection.)
-It is recommended that the frontend check for such asynchronous notices just
-before issuing any new command.
-
-<Para>
-Also, if the frontend issues any listen(l) commands then it must be prepared
-to accept NotificationResponse messages at any time; see below.
-
-
-<Sect2>
-<Title>Function Call</Title>
-
-<Para>
-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 may safely send
-a new query or function call.
-
-<Para>
-The possible response messages from the backend are:
-
-<Para>
-<VariableList>
-<VarListEntry>
-<Term>
- ErrorResponse
-</Term>
-<ListItem>
-<Para>
- An error has occurred.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- FunctionResultResponse
-</Term>
-<ListItem>
-<Para>
- The function call was executed and returned a result.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- FunctionVoidResponse
-</Term>
-<ListItem>
-<Para>
- The function call was executed and returned no result.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- ReadyForQuery
-</Term>
-<ListItem>
-<Para>
- Processing of the function call is complete.
- ReadyForQuery will always be sent, whether processing
- terminates successfully or with an error.
-</Para>
-</ListItem>
-</VarListEntry>
-<VarListEntry>
-<Term>
- NoticeResponse
-</Term>
-<ListItem>
-<Para>
- A warning message has been issued in relation to the function
- call.
- Notices are in addition to other responses, ie. the backend
- will continue processing the command.
-</Para>
-</ListItem>
-</VarListEntry>
-</VariableList>
-</Para>
-
-<Para>
-A frontend must be prepared to accept ErrorResponse and NoticeResponse
-messages whenever it is expecting any other type of message. Also,
-if it issues any listen(l) commands then it must be prepared to accept
-NotificationResponse messages at any time; see below.
-
-
-<Sect2>
-<Title>Notification Responses</Title>
-
-<Para>
-If a frontend issues a listen(l) command, then the backend will send a
-NotificationResponse message (not to be confused with NoticeResponse!)
-whenever a notify(l) command is executed for the same notification name.
-
-<Para>
-Notification responses are permitted at any point in the protocol (after
-startup), except within another backend message. Thus, the frontend
-must be prepared to recognize a NotificationResponse message whenever it is
-expecting any message. Indeed, it should be able to handle
-NotificationResponse messages even when it is not engaged in a query.
-
-<Para>
-<VariableList>
-<VarListEntry>
-<Term>
- NotificationResponse
-</Term>
-<ListItem>
-<Para>
- A notify(l) command has been executed for a name for which
- a previous listen(l) command was executed. Notifications
- may be sent at any time.
-</Para>
-</ListItem>
-</VarListEntry>
-</VariableList>
-</Para>
-
-<Para>
-It may be worth pointing out that the names used in listen and notify
-commands need not have anything to do with names of relations (tables)
-in the SQL database. Notification names are simply arbitrarily chosen
-condition names.
-
-
-<Sect2>
-<Title>Cancelling Requests in Progress</Title>
-
-<Para>
-During the processing of a query, the frontend may request cancellation of the
-query by sending an appropriate request to the postmaster. The cancel request
-is not sent directly 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.
-
-<Para>
-To issue a cancel request, the frontend opens a new connection to the
-postmaster and sends a CancelRequest message, rather than the StartupPacket
-message that would ordinarily be sent across a new connection. The postmaster
-will process this request and then close the connection. For security
-reasons, no direct reply is made to the cancel request message.
-
-<Para>
-A CancelRequest message will be ignored unless it contains the same key data
-(PID and secret key) passed to the frontend during connection startup. If the
-request matches the PID and secret key for a currently executing backend, the
-postmaster signals the backend to abort processing of the current query.
-
-<Para>
-The cancellation signal may or may 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.
-
-<Para>
-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.
-
-<Para>
-Since the cancel request is sent to the postmaster 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 may have some benefits of flexibility in 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.
-
-
-<Sect2>
-<Title>Termination</Title>
-
-<Para>
-The normal, graceful termination procedure is that the frontend sends a
-Terminate message and immediately closes the connection. On receipt of the
-message, the backend immediately closes the connection and terminates.
-
-<Para>
-An ungraceful termination may occur due to software failure (i.e., core dump)
-at either end. 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 postmaster, if it doesn't want
-to terminate itself.
-
-
-<Sect1>
-<Title>Message Data Types</Title>
-
-<Para>
+<!-- doc/src/sgml/protocol.sgml -->
+
+<chapter id="protocol">
+ <title>Frontend/Backend Protocol</title>
+
+ <indexterm zone="protocol">
+ <primary>protocol</primary>
+ <secondary>frontend-backend</secondary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> uses a message-based protocol
+ for communication between frontends and backends (clients and servers).
+ The protocol is supported over <acronym>TCP/IP</acronym> and also over
+ Unix-domain sockets. Port number 5432 has been registered with IANA as
+ the customary TCP port number for servers supporting this protocol, but
+ in practice any non-privileged port number can be used.
+ </para>
+
+ <para>
+ This document describes version 3.0 of the protocol, implemented in
+ <productname>PostgreSQL</productname> 7.4 and later. For descriptions
+ of the earlier protocol versions, see previous releases of the
+ <productname>PostgreSQL</productname> documentation. A single server
+ can support multiple protocol versions. The initial
+ startup-request message tells the server which protocol version the
+ client is attempting to use, and then the server follows that protocol
+ if it is able.
+ </para>
+
+ <para>
+ In order to serve multiple clients efficiently, the server launches
+ a new <quote>backend</> process for each client.
+ In the current implementation, a new child
+ process is created immediately after an incoming connection is detected.
+ This is transparent to the protocol, however. For purposes of the
+ protocol, the terms <quote>backend</> and <quote>server</> are
+ interchangeable; likewise <quote>frontend</> and <quote>client</>
+ are interchangeable.
+ </para>
+
+ <sect1 id="protocol-overview">
+ <title>Overview</title>
+
+ <para>
+ The protocol has separate phases for startup and normal operation.
+ In the startup phase, the frontend opens a connection to the server
+ and authenticates itself to the satisfaction of the server. (This might
+ involve a single message, or multiple messages depending on the
+ authentication method being used.) If all goes well, the server then sends
+ status information to the frontend, and finally enters normal operation.
+ Except for the initial startup-request message, this part of the
+ protocol is driven by the server.
+ </para>
+
+ <para>
+ During normal operation, the frontend sends queries and
+ other commands to the backend, and the backend sends back query results
+ and other responses. There are a few cases (such as <command>NOTIFY</>)
+ wherein the
+ backend will send unsolicited messages, but for the most part this portion
+ of a session is driven by frontend requests.
+ </para>
+
+ <para>
+ Termination of the session is normally by frontend choice, but can be
+ forced by the backend in certain cases. In any case, when the backend
+ closes the connection, it will roll back any open (incomplete) transaction
+ before exiting.
+ </para>
+
+ <para>
+ Within normal operation, SQL commands can be executed through either of
+ two sub-protocols. In the <quote>simple query</> protocol, the frontend
+ just sends a textual query string, which is parsed and immediately
+ executed by the backend. In the <quote>extended query</> protocol,
+ processing of queries is separated into multiple steps: parsing,
+ binding of parameter values, and execution. This offers flexibility
+ and performance benefits, at the cost of extra complexity.
+ </para>
+
+ <para>
+ Normal operation has additional sub-protocols for special operations
+ such as <command>COPY</>.
+ </para>
+
+ <sect2 id="protocol-message-concepts">
+ <title>Messaging Overview</title>
+
+ <para>
+ All communication is through a stream of messages. The first byte of a
+ message identifies the message type, and the next four bytes specify the
+ length of the rest of the message (this length count includes itself, but
+ not the message-type byte). The remaining contents of the message are
+ determined by the message type. For historical reasons, the very first
+ message sent by the client (the startup message) has no initial
+ message-type byte.
+ </para>
+
+ <para>
+ To avoid losing synchronization with the message stream, both servers and
+ clients typically read an entire message into a buffer (using the byte
+ count) before attempting to process its contents. This allows easy
+ recovery if an error is detected while processing the contents. In
+ extreme situations (such as not having enough memory to buffer the
+ message), the receiver can use the byte count to determine how much
+ input to skip before it resumes reading messages.
+ </para>
+
+ <para>
+ Conversely, both servers and clients must take care never to send an
+ incomplete message. This is commonly done by marshaling the entire message
+ in a buffer before beginning to send it. If a communications failure
+ occurs partway through sending or receiving a message, the only sensible
+ response is to abandon the connection, since there is little hope of
+ recovering message-boundary synchronization.
+ </para>
+ </sect2>
+
+ <sect2 id="protocol-query-concepts">
+ <title>Extended Query Overview</title>
+
+ <para>
+ In the extended-query protocol, execution of SQL commands is divided
+ into multiple steps. The state retained between steps is represented
+ by two types of objects: <firstterm>prepared statements</> and
+ <firstterm>portals</>. A prepared statement represents the result of
+ parsing, semantic analysis, and (optionally) planning of a textual query
+ string.
+ A prepared statement is not necessarily ready to execute, because it might
+ lack specific values for <firstterm>parameters</>. A portal represents
+ a ready-to-execute or already-partially-executed statement, with any
+ missing parameter values filled in. (For <command>SELECT</> statements,
+ a portal is equivalent to an open cursor, but we choose to use a different
+ term since cursors don't handle non-<command>SELECT</> statements.)
+ </para>
+
+ <para>
+ The overall execution cycle consists of a <firstterm>parse</> step,
+ which creates a prepared statement from a textual query string; a
+ <firstterm>bind</> step, which creates a portal given a prepared
+ statement and values for any needed parameters; and an
+ <firstterm>execute</> step that runs a portal's query. In the case of
+ a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
+ the execute step can be told to fetch only
+ a limited number of rows, so that multiple execute steps might be needed
+ to complete the operation.
+ </para>
+
+ <para>
+ The backend can keep track of multiple prepared statements and portals
+ (but note that these exist only within a session, and are never shared
+ across sessions). Existing prepared statements and portals are
+ referenced by names assigned when they were created. In addition,
+ an <quote>unnamed</> prepared statement and portal exist. Although these
+ behave largely the same as named objects, operations on them are optimized
+ for the case of executing a query only once and then discarding it,
+ whereas operations on named objects are optimized on the expectation
+ of multiple uses.
+ </para>
+ </sect2>
+
+ <sect2 id="protocol-format-codes">
+ <title>Formats and Format Codes</title>
+
+ <para>
+ Data of a particular data type might be transmitted in any of several
+ different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
+ the only supported formats are <quote>text</> and <quote>binary</>,
+ but the protocol makes provision for future extensions. The desired
+ format for any value is specified by a <firstterm>format code</>.
+ Clients can specify a format code for each transmitted parameter value
+ and for each column of a query result. Text has format code zero,
+ binary has format code one, and all other format codes are reserved
+ for future definition.
+ </para>
+
+ <para>
+ The text representation of values is whatever strings are produced
+ and accepted by the input/output conversion functions for the
+ particular data type. In the transmitted representation, there is
+ no trailing null character; the frontend must add one to received
+ values if it wants to process them as C strings.
+ (The text format does not allow embedded nulls, by the way.)
+ </para>
+
+ <para>
+ Binary representations for integers use network byte order (most
+ significant byte first). For other data types consult the documentation
+ or source code to learn about the binary representation. Keep in mind
+ that binary representations for complex data types might change across
+ server versions; the text format is usually the more portable choice.
+ </para>
+ </sect2>
+ </sect1>
+
+ <sect1 id="protocol-flow">
+ <title>Message Flow</title>
+
+ <para>
+ This section describes the message flow and the semantics of each
+ message type. (Details of the exact representation of each message
+ appear in <xref linkend="protocol-message-formats">.) There are
+ several different sub-protocols depending on the state of the
+ connection: start-up, query, function call,
+ <command>COPY</command>, 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.
+ </para>
+
+ <sect2>
+ <title>Start-Up</title>
+
+ <para>
+ 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
+ <filename>pg_hba.conf</filename>) to determine
+ whether the connection is provisionally acceptable, and what additional
+ authentication is required (if any).
+ </para>
+
+ <para>
+ 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 and SSPI, 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 and SSPI, multiple exchanges of packets may be needed
+ to complete the authentication.
+ </para>
+
+ <para>
+ The authentication cycle ends with the server either rejecting the
+ connection attempt (ErrorResponse), or sending AuthenticationOk.
+ </para>
+
+ <para>
+ The possible messages from the server in this phase are:
+
+ <variablelist>
+ <varlistentry>
+ <term>ErrorResponse</term>
+ <listitem>
+ <para>
+ The connection attempt has been rejected.
+ The server then immediately closes the connection.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationOk</term>
+ <listitem>
+ <para>
+ The authentication exchange is successfully completed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationKerberosV5</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationCleartextPassword</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationMD5Password</term>
+ <listitem>
+ <para>
+ The frontend must now send a PasswordMessage containing the
+ password encrypted via MD5, using the 4-character salt
+ specified in the AuthenticationMD5Password message. If
+ this is the correct password, the server responds with an
+ AuthenticationOk, otherwise it responds with an ErrorResponse.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationSCMCredential</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationGSS</term>
+ <listitem>
+ <para>
+ The frontend must now initiate a GSSAPI negotiation. The frontend
+ will send a PasswordMessage with the first part of the GSSAPI
+ data stream in response to this. If further messages are needed,
+ the server will respond with AuthenticationGSSContinue.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>AuthenticationSSPI</term>
+ <listitem>
+ <para>
+ The frontend must now initiate a SSPI negotiation. The frontend
+ will send a PasswordMessage with the first part of the SSPI
+ data stream in response to this. If further messages are needed,
+ the server will respond with AuthenticationGSSContinue.
+ </para>
+ </listitem>
+
+ </varlistentry>
+ <varlistentry>
+ <term>AuthenticationGSSContinue</term>
+ <listitem>
+ <para>
+ 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 PasswordMessage. 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ If the frontend does not support the authentication method
+ requested by the server, then it should immediately close the
+ connection.
+ </para>
+
+ <para>
+ 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), but in the normal case the backend will send
+ some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ The possible messages from the backend in this phase are:
+
+ <variablelist>
+ <varlistentry>
+ <term>BackendKeyData</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ParameterStatus</term>
+ <listitem>
+ <para>
+ This message informs the frontend about the current (initial)
+ setting of backend parameters, such as <xref
+ linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
+ The frontend can ignore this message, or record the settings
+ for its future use; see <xref linkend="protocol-async"> for
+ more details. The frontend should not respond to this
+ message, but should continue listening for a ReadyForQuery
+ message.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ReadyForQuery</term>
+ <listitem>
+ <para>
+ Start-up is completed. The frontend can now issue commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ErrorResponse</term>
+ <listitem>
+ <para>
+ Start-up failed. The connection is closed after sending this
+ message.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>NoticeResponse</term>
+ <listitem>
+ <para>
+ A warning message has been issued. The frontend should
+ display the message but continue listening for ReadyForQuery
+ or ErrorResponse.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ 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.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Simple Query</title>
+
+ <para>
+ 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.)
+ </para>
+
+ <para>
+ The possible response messages from the backend are:
+
+ <variablelist>
+ <varlistentry>
+ <term>CommandComplete</term>
+ <listitem>
+ <para>
+ An SQL command completed normally.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>CopyInResponse</term>
+ <listitem>
+ <para>
+ The backend is ready to copy data from the frontend to a
+ table; see <xref linkend="protocol-copy">.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>CopyOutResponse</term>
+ <listitem>
+ <para>
+ The backend is ready to copy data from a table to the
+ frontend; see <xref linkend="protocol-copy">.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>RowDescription</term>
+ <listitem>
+ <para>
+ Indicates that rows are about to be returned in response to
+ a <command>SELECT</command>, <command>FETCH</command>, 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>DataRow</term>
+ <listitem>
+ <para>
+ One of the set of rows returned by
+ a <command>SELECT</command>, <command>FETCH</command>, etc query.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>EmptyQueryResponse</term>
+ <listitem>
+ <para>
+ An empty query string was recognized.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ErrorResponse</term>
+ <listitem>
+ <para>
+ An error has occurred.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ReadyForQuery</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>NoticeResponse</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ The response to a <command>SELECT</> query (or other queries that
+ return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
+ normally consists of RowDescription, zero or more
+ DataRow messages, and then CommandComplete.
+ <command>COPY</> to or from the frontend invokes special protocol
+ as described in <xref linkend="protocol-copy">.
+ All other query types normally produce only
+ a CommandComplete message.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ If a completely empty (no contents other than whitespace) query string
+ is received, the response is EmptyQueryResponse followed by ReadyForQuery.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ In simple Query mode, the format of retrieved values is always text,
+ except when the given command is a <command>FETCH</> from a cursor
+ declared with the <literal>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.
+ </para>
+
+ <para>
+ A frontend must be prepared to accept ErrorResponse and
+ NoticeResponse messages whenever it is expecting any other type of
+ message. See also <xref linkend="protocol-async"> concerning messages
+ that the backend might generate due to outside events.
+ </para>
+
+ <para>
+ 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.
+ </para>
+ </sect2>
+
+ <sect2 id="protocol-flow-ext-query">
+ <title>Extended Query</title>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <note>
+ <para>
+ 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 (<literal>$</><replaceable>n</>)
+ used in the query string. Another special case is that a parameter's
+ type can be specified as <type>void</> (that is, the OID of the
+ <type>void</> pseudotype). 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 <type>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 <literal>foo($1,$2,$3,$4)</> could match a function with
+ two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
+ are specified as having type <type>void</>.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ 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.
+ </para>
+ </note>
+
+ <para>
+ 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
+ a 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 <command>PREPARE</> and <command>EXECUTE</>.
+ </para>
+
+ <para>
+ 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 <type>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.
+ </para>
+
+ <note>
+ <para>
+ The choice between text and binary output is determined by the format
+ codes given in Bind, regardless of the SQL command involved. The
+ <literal>BINARY</> attribute in cursor declarations is irrelevant when
+ using extended query protocol.
+ </para>
+ </note>
+
+ <para>
+ Query planning for named prepared-statement objects occurs when the Parse
+ message is processed. If a query will be repeatedly executed with
+ different parameters, it might be beneficial to send a single Parse message
+ containing a parameterized query, followed by multiple Bind
+ and Execute messages. This will avoid replanning the query on each
+ execution.
+ </para>
+
+ <para>
+ The unnamed prepared statement is likewise planned during Parse processing
+ if the Parse message defines no parameters. But if there are parameters,
+ query planning occurs every time Bind parameters are supplied. This allows the
+ planner to make use of the actual values of the parameters provided by
+ each Bind message, rather than use generic estimates.
+ </para>
+
+ <note>
+ <para>
+ Query plans generated from a parameterized query might be less
+ efficient than query plans generated from an equivalent query with actual
+ parameter values substituted. The query planner cannot make decisions
+ based on actual parameter values (for example, index selectivity) when
+ planning a parameterized query assigned to a named prepared-statement
+ object. This possible penalty is avoided when using the unnamed
+ statement, since it is not planned until actual parameter values are
+ available. The cost is that planning must occur afresh for each Bind,
+ even if the query stays the same.
+ </para>
+ </note>
+
+ <para>
+ 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 a 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 <command>DECLARE CURSOR</> and <command>FETCH</>.
+ </para>
+
+ <para>
+ 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 <quote>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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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
+ <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>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
+ <emphasis>while</> processing Sync — this ensures that there is one
+ and only one ReadyForQuery sent for each Sync.)
+ </para>
+
+ <note>
+ <para>
+ Sync does not cause a transaction block opened with <command>BEGIN</>
+ to be closed. It is possible to detect this situation since the
+ ReadyForQuery message includes transaction status information.
+ </para>
+ </note>
+
+ <para>
+ In addition to these fundamental, required operations, there are several
+ optional operations that can be used with extended-query protocol.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <tip>
+ <para>
+ 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.
+ </para>
+ </tip>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <note>
+ <para>
+ 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.
+ </para>
+ </note>
+ </sect2>
+
+ <sect2>
+ <title>Function Call</title>
+
+ <para>
+ The Function Call sub-protocol allows the client to request a direct
+ call of any function that exists in the database's
+ <structname>pg_proc</structname> system catalog. The client must have
+ execute permission for the function.
+ </para>
+
+ <note>
+ <para>
+ 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 <literal>SELECT function($1, ...)</>.
+ The Function Call cycle can then be replaced with Bind/Execute.
+ </para>
+ </note>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ The possible response messages from the backend are:
+
+ <variablelist>
+ <varlistentry>
+ <term>ErrorResponse</term>
+ <listitem>
+ <para>
+ An error has occurred.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>FunctionCallResponse</term>
+ <listitem>
+ <para>
+ 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.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ReadyForQuery</term>
+ <listitem>
+ <para>
+ Processing of the function call is complete. ReadyForQuery
+ will always be sent, whether processing terminates
+ successfully or with an error.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>NoticeResponse</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </sect2>
+
+ <sect2 id="protocol-copy">
+ <title>COPY Operations</title>
+
+ <para>
+ The <command>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.
+ </para>
+
+ <para>
+ Copy-in mode (data transfer to the server) is initiated when the
+ backend executes a <command>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 <command>COPY</> SQL statement to fail with an
+ error). The backend then reverts to the command-processing mode it was
+ in before the <command>COPY</> started, which will be either simple or
+ extended query protocol. It will next send either CommandComplete
+ (if successful) or ErrorResponse (if not).
+ </para>
+
+ <para>
+ 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 <command>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 <command>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.
+ </para>
+
+ <para>
+ 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 <command>COPY FROM STDIN</>.)
+ </para>
+
+ <para>
+ Copy-out mode (data transfer from the server) is initiated when the
+ backend executes a <command>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 <command>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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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
+ <xref linkend="protocol-async">). Otherwise, any message type other than
+ CopyData or CopyDone may be treated as terminating copy-out mode.
+ </para>
+
+ <para>
+ There is another Copy-related mode called Copy-both, which allows
+ high-speed bulk data transfer to <emphasis>and</> from the server.
+ Copy-both mode is initiated when a backend in walsender mode
+ executes a <command>START_REPLICATION</command> statement. The
+ backend sends a CopyBothResponse message to the frontend. Both
+ the backend and the frontend may then send CopyData messages
+ until the connection is terminated. See <xref
+ linkend="protocol-replication">.
+ </para>
+
+ <para>
+ 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 <command>COPY</>
+ operation will use the same format, but the message design does not
+ assume this.)
+ </para>
+
+ </sect2>
+
+ <sect2 id="protocol-async">
+ <title>Asynchronous Operations</title>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ It is possible for NoticeResponse messages to be generated due to
+ outside activity; for example, if the database administrator commands
+ a <quote>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.
+ </para>
+
+ <para>
+ 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 <command>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
+ <systemitem>SIGHUP</systemitem> signal to the server. Also,
+ if a <command>SET</command> command is rolled back, an appropriate
+ ParameterStatus message will be generated to report the current
+ effective value.
+ </para>
+
+ <para>
+ At present there is a hard-wired set of parameters for which
+ ParameterStatus will be generated: they are
+ <literal>server_version</>,
+ <literal>server_encoding</>,
+ <literal>client_encoding</>,
+ <literal>application_name</>,
+ <literal>is_superuser</>,
+ <literal>session_authorization</>,
+ <literal>DateStyle</>,
+ <literal>IntervalStyle</>,
+ <literal>TimeZone</>,
+ <literal>integer_datetimes</>, and
+ <literal>standard_conforming_strings</>.
+ (<literal>server_encoding</>, <literal>TimeZone</>, and
+ <literal>integer_datetimes</> were not reported by releases before 8.0;
+ <literal>standard_conforming_strings</> was not reported by releases
+ before 8.1;
+ <literal>IntervalStyle</> was not reported by releases before 8.4;
+ <literal>application_name</> was not reported by releases before 9.0.)
+ Note that
+ <literal>server_version</>,
+ <literal>server_encoding</> and
+ <literal>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.
+ </para>
+
+ <para>
+ If a frontend issues a <command>LISTEN</command> command, then the
+ backend will send a NotificationResponse message (not to be
+ confused with NoticeResponse!) whenever a
+ <command>NOTIFY</command> command is executed for the same
+ channel name.
+ </para>
+
+ <note>
+ <para>
+ 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.
+ </para>
+ </note>
+ </sect2>
+
+ <sect2>
+ <title>Cancelling Requests in Progress</title>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.)
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Termination</title>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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-<command>SELECT</command> query
+ is being processed, the backend will probably finish the query
+ before noticing the disconnection. If the query is outside any
+ transaction block (<command>BEGIN</> ... <command>COMMIT</>
+ sequence) then its results might be committed before the
+ disconnection is recognized.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title><acronym>SSL</acronym> Session Encryption</title>
+
+ <para>
+ If <productname>PostgreSQL</> was built with
+ <acronym>SSL</acronym> support, frontend/backend communications
+ can be encrypted using <acronym>SSL</acronym>. This provides
+ communication security in environments where attackers might be
+ able to capture the session traffic. For more information on
+ encrypting <productname>PostgreSQL</productname> sessions with
+ <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
+ </para>
+
+ <para>
+ To initiate an <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 <literal>S</> or <literal>N</>, indicating that it is
+ willing or unwilling to perform <acronym>SSL</acronym>,
+ respectively. The frontend might close the connection at this point
+ if it is dissatisfied with the response. To continue after
+ <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
+ (not described here, part of the <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>SSL</acronym>-encrypted. To continue after
+ <literal>N</>, send the usual StartupMessage and proceed without
+ encryption.
+ </para>
+
+ <para>
+ 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>SSL</acronym> support
+ to <productname>PostgreSQL</>. In this case the connection must
+ be closed, but the frontend might choose to open a fresh connection
+ and proceed without requesting <acronym>SSL</acronym>.
+ </para>
+
+ <para>
+ An initial SSLRequest can also be used in a connection that is being
+ opened to send a CancelRequest message.
+ </para>
+
+ <para>
+ While the protocol itself does not provide a way for the server to
+ force <acronym>SSL</acronym> encryption, the administrator can
+ configure the server to reject unencrypted sessions as a byproduct
+ of authentication checking.
+ </para>
+ </sect2>
+ </sect1>
+
+<sect1 id="protocol-replication">
+<title>Streaming Replication Protocol</title>
+
+<para>
+To initiate streaming replication, the frontend sends the
+<literal>replication</> parameter in the startup message. This tells the
+backend to go into walsender mode, wherein a small set of replication commands
+can be issued instead of SQL statements. Only the simple query protocol can be
+used in walsender mode.
+
+The commands accepted in walsender mode are:
+
+<variablelist>
+ <varlistentry>
+ <term>IDENTIFY_SYSTEM</term>
+ <listitem>
+ <para>
+ Requests the server to identify itself. Server replies with a result
+ set of a single row, containing two fields:
+ </para>
+
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term>
+ systemid
+ </term>
+ <listitem>
+ <para>
+ The unique system identifier identifying the cluster. This
+ can be used to check that the base backup used to initialize the
+ standby came from the same cluster.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ timeline
+ </term>
+ <listitem>
+ <para>
+ Current TimelineID. Also useful to check that the standby is
+ consistent with the master.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>START_REPLICATION <replaceable>XXX</>/<replaceable>XXX</></term>
+ <listitem>
+ <para>
+ Instructs server to start streaming WAL, starting at
+ WAL position <replaceable>XXX</>/<replaceable>XXX</>.
+ The server can reply with an error, e.g. if the requested section of WAL
+ has already been recycled. On success, server responds with a
+ CopyBothResponse message, and then starts to stream WAL to the frontend.
+ WAL will continue to be streamed until the connection is broken;
+ no further commands will be accepted.
+ </para>
+
+ <para>
+ WAL data is sent as a series of CopyData messages. (This allows
+ other information to be intermixed; in particular the server can send
+ an ErrorResponse message if it encounters a failure after beginning
+ to stream.) The payload in each CopyData message follows this format:
+ </para>
+
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term>
+ XLogData (B)
+ </term>
+ <listitem>
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term>
+ Byte1('w')
+ </term>
+ <listitem>
+ <para>
+ Identifies the message as WAL data.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ Byte8
+ </term>
+ <listitem>
+ <para>
+ The starting point of the WAL data in this message, given in
+ XLogRecPtr format.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ Byte8
+ </term>
+ <listitem>
+ <para>
+ The current end of WAL on the server, given in
+ XLogRecPtr format.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ Byte8
+ </term>
+ <listitem>
+ <para>
+ The server's system clock at the time of transmission,
+ given in TimestampTz format.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ Byte<replaceable>n</replaceable>
+ </term>
+ <listitem>
+ <para>
+ A section of the WAL data stream.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ <para>
+ A single WAL record is never split across two CopyData messages.
+ When a WAL record crosses a WAL page boundary, and is therefore
+ already split using continuation records, it can be split at the page
+ boundary. In other words, the first main WAL record and its
+ continuation records can be sent in different CopyData messages.
+ </para>
+ <para>
+ Note that all fields within the WAL data and the above-described header
+ will be in the sending server's native format. Endianness, and the
+ format for the timestamp, are unpredictable unless the receiver has
+ verified that the sender's system identifier matches its own
+ <filename>pg_control</> contents.
+ </para>
+ <para>
+ If the WAL sender process is terminated normally (during postmaster
+ shutdown), it will send a CommandComplete message before exiting.
+ This might not happen during an abnormal shutdown, of course.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>BASE_BACKUP [<literal>LABEL</literal> <replaceable>'label'</replaceable>] [<literal>PROGRESS</literal>] [<literal>FAST</literal>]</term>
+ <listitem>
+ <para>
+ Instructs the server to start streaming a base backup.
+ The system will automatically be put in backup mode before the backup
+ is started, and taken out of it when the backup is complete. The
+ following options are accepted:
+ <variablelist>
+ <varlistentry>
+ <term><literal>LABEL</literal> <replaceable>'label'</replaceable></term>
+ <listitem>
+ <para>
+ Sets the label of the backup. If none is specified, a backup label
+ of <literal>base backup</literal> will be used. The quoting rules
+ for the label are the same as a standard SQL string with
+ <xref linkend="guc-standard-conforming-strings"> turned on.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>PROGRESS</></term>
+ <listitem>
+ <para>
+ Request information required to generate a progress report. This will
+ send back an approximate size in the header of each tablespace, which
+ can be used to calculate how far along the stream is done. This is
+ calculated by enumerating all the file sizes once before the transfer
+ is even started, and may as such have a negative impact on the
+ performance - in particular it may take longer before the first data
+ is streamed. Since the database files can change during the backup,
+ the size is only approximate and may both grow and shrink between
+ the time of approximation and the sending of the actual files.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FAST</></term>
+ <listitem>
+ <para>
+ Request a fast checkpoint.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ <para>
+ When the backup is started, the server will first send a header in
+ ordinary result set format, followed by one or more CopyResponse
+ results, one for PGDATA and one for each additional tablespace other
+ than <literal>pg_default</> and <literal>pg_global</>. The data in
+ the CopyResponse results will be a tar format (using ustar00
+ extensions) dump of the tablespace contents.
+ </para>
+ <para>
+ The header is an ordinary resultset with one row for each tablespace.
+ The fields in this row are:
+ <variablelist>
+ <varlistentry>
+ <term>spcoid</term>
+ <listitem>
+ <para>
+ The oid of the tablespace, or <literal>NULL</> if it's the base
+ directory.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>spclocation</term>
+ <listitem>
+ <para>
+ The full path of the tablespace directory, or <literal>NULL</>
+ if it's the base directory.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>size</term>
+ <listitem>
+ <para>
+ The approximate size of the tablespace, if progress report has
+ been requested; otherwise it's <literal>NULL</>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ <para>
+ The tar archive for the data directory and each tablespace will contain
+ all files in the directories, regardless of whether they are
+ <productname>PostgreSQL</> files or other files added to the same
+ directory. The only excluded files are:
+ <itemizedlist spacing="compact" mark="bullet">
+ <listitem>
+ <para>
+ <filename>postmaster.pid</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <filename>pg_xlog</> (including subdirectories)
+ </para>
+ </listitem>
+ </itemizedlist>
+ Owner, group and file mode are set if the underlying filesystem on
+ the server supports it.
+ </para>
+ </listitem>
+ </varlistentry>
+</variablelist>
+
+</para>
+
+</sect1>
+
+<sect1 id="protocol-message-types">
+<title>Message Data Types</title>
+
+<para>