1 <!-- doc/src/sgml/protocol.sgml -->
3 <chapter id="protocol">
4 <title>Frontend/Backend Protocol</title>
6 <indexterm zone="protocol">
7 <primary>protocol</primary>
8 <secondary>frontend-backend</secondary>
12 <productname>PostgreSQL</productname> uses a message-based protocol
13 for communication between frontends and backends (clients and servers).
14 The protocol is supported over <acronym>TCP/IP</acronym> and also over
15 Unix-domain sockets. Port number 5432 has been registered with IANA as
16 the customary TCP port number for servers supporting this protocol, but
17 in practice any non-privileged port number can be used.
21 This document describes version 3.0 of the protocol, implemented in
22 <productname>PostgreSQL</productname> 7.4 and later. For descriptions
23 of the earlier protocol versions, see previous releases of the
24 <productname>PostgreSQL</productname> documentation. A single server
25 can support multiple protocol versions. The initial
26 startup-request message tells the server which protocol version the
27 client is attempting to use, and then the server follows that protocol
32 In order to serve multiple clients efficiently, the server launches
33 a new <quote>backend</> process for each client.
34 In the current implementation, a new child
35 process is created immediately after an incoming connection is detected.
36 This is transparent to the protocol, however. For purposes of the
37 protocol, the terms <quote>backend</> and <quote>server</> are
38 interchangeable; likewise <quote>frontend</> and <quote>client</>
42 <sect1 id="protocol-overview">
43 <title>Overview</title>
46 The protocol has separate phases for startup and normal operation.
47 In the startup phase, the frontend opens a connection to the server
48 and authenticates itself to the satisfaction of the server. (This might
49 involve a single message, or multiple messages depending on the
50 authentication method being used.) If all goes well, the server then sends
51 status information to the frontend, and finally enters normal operation.
52 Except for the initial startup-request message, this part of the
53 protocol is driven by the server.
57 During normal operation, the frontend sends queries and
58 other commands to the backend, and the backend sends back query results
59 and other responses. There are a few cases (such as <command>NOTIFY</>)
61 backend will send unsolicited messages, but for the most part this portion
62 of a session is driven by frontend requests.
66 Termination of the session is normally by frontend choice, but can be
67 forced by the backend in certain cases. In any case, when the backend
68 closes the connection, it will roll back any open (incomplete) transaction
73 Within normal operation, SQL commands can be executed through either of
74 two sub-protocols. In the <quote>simple query</> protocol, the frontend
75 just sends a textual query string, which is parsed and immediately
76 executed by the backend. In the <quote>extended query</> protocol,
77 processing of queries is separated into multiple steps: parsing,
78 binding of parameter values, and execution. This offers flexibility
79 and performance benefits, at the cost of extra complexity.
83 Normal operation has additional sub-protocols for special operations
84 such as <command>COPY</>.
87 <sect2 id="protocol-message-concepts">
88 <title>Messaging Overview</title>
91 All communication is through a stream of messages. The first byte of a
92 message identifies the message type, and the next four bytes specify the
93 length of the rest of the message (this length count includes itself, but
94 not the message-type byte). The remaining contents of the message are
95 determined by the message type. For historical reasons, the very first
96 message sent by the client (the startup message) has no initial
101 To avoid losing synchronization with the message stream, both servers and
102 clients typically read an entire message into a buffer (using the byte
103 count) before attempting to process its contents. This allows easy
104 recovery if an error is detected while processing the contents. In
105 extreme situations (such as not having enough memory to buffer the
106 message), the receiver can use the byte count to determine how much
107 input to skip before it resumes reading messages.
111 Conversely, both servers and clients must take care never to send an
112 incomplete message. This is commonly done by marshaling the entire message
113 in a buffer before beginning to send it. If a communications failure
114 occurs partway through sending or receiving a message, the only sensible
115 response is to abandon the connection, since there is little hope of
116 recovering message-boundary synchronization.
120 <sect2 id="protocol-query-concepts">
121 <title>Extended Query Overview</title>
124 In the extended-query protocol, execution of SQL commands is divided
125 into multiple steps. The state retained between steps is represented
126 by two types of objects: <firstterm>prepared statements</> and
127 <firstterm>portals</>. A prepared statement represents the result of
128 parsing and semantic analysis of a textual query string.
129 A prepared statement is not in itself ready to execute, because it might
130 lack specific values for <firstterm>parameters</>. A portal represents
131 a ready-to-execute or already-partially-executed statement, with any
132 missing parameter values filled in. (For <command>SELECT</> statements,
133 a portal is equivalent to an open cursor, but we choose to use a different
134 term since cursors don't handle non-<command>SELECT</> statements.)
138 The overall execution cycle consists of a <firstterm>parse</> step,
139 which creates a prepared statement from a textual query string; a
140 <firstterm>bind</> step, which creates a portal given a prepared
141 statement and values for any needed parameters; and an
142 <firstterm>execute</> step that runs a portal's query. In the case of
143 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
144 the execute step can be told to fetch only
145 a limited number of rows, so that multiple execute steps might be needed
146 to complete the operation.
150 The backend can keep track of multiple prepared statements and portals
151 (but note that these exist only within a session, and are never shared
152 across sessions). Existing prepared statements and portals are
153 referenced by names assigned when they were created. In addition,
154 an <quote>unnamed</> prepared statement and portal exist. Although these
155 behave largely the same as named objects, operations on them are optimized
156 for the case of executing a query only once and then discarding it,
157 whereas operations on named objects are optimized on the expectation
162 <sect2 id="protocol-format-codes">
163 <title>Formats and Format Codes</title>
166 Data of a particular data type might be transmitted in any of several
167 different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
168 the only supported formats are <quote>text</> and <quote>binary</>,
169 but the protocol makes provision for future extensions. The desired
170 format for any value is specified by a <firstterm>format code</>.
171 Clients can specify a format code for each transmitted parameter value
172 and for each column of a query result. Text has format code zero,
173 binary has format code one, and all other format codes are reserved
174 for future definition.
178 The text representation of values is whatever strings are produced
179 and accepted by the input/output conversion functions for the
180 particular data type. In the transmitted representation, there is
181 no trailing null character; the frontend must add one to received
182 values if it wants to process them as C strings.
183 (The text format does not allow embedded nulls, by the way.)
187 Binary representations for integers use network byte order (most
188 significant byte first). For other data types consult the documentation
189 or source code to learn about the binary representation. Keep in mind
190 that binary representations for complex data types might change across
191 server versions; the text format is usually the more portable choice.
196 <sect1 id="protocol-flow">
197 <title>Message Flow</title>
200 This section describes the message flow and the semantics of each
201 message type. (Details of the exact representation of each message
202 appear in <xref linkend="protocol-message-formats">.) There are
203 several different sub-protocols depending on the state of the
204 connection: start-up, query, function call,
205 <command>COPY</command>, and termination. There are also special
206 provisions for asynchronous operations (including notification
207 responses and command cancellation), which can occur at any time
208 after the start-up phase.
212 <title>Start-up</title>
215 To begin a session, a frontend opens a connection to the server and sends
216 a startup message. This message includes the names of the user and of the
217 database the user wants to connect to; it also identifies the particular
218 protocol version to be used. (Optionally, the startup message can include
219 additional settings for run-time parameters.)
220 The server then uses this information and
221 the contents of its configuration files (such as
222 <filename>pg_hba.conf</filename>) to determine
223 whether the connection is provisionally acceptable, and what additional
224 authentication is required (if any).
228 The server then sends an appropriate authentication request message,
229 to which the frontend must reply with an appropriate authentication
230 response message (such as a password).
231 For all authentication methods except GSSAPI and SSPI, there is at most
232 one request and one response. In some methods, no response
233 at all is needed from the frontend, and so no authentication request
234 occurs. For GSSAPI and SSPI, multiple exchanges of packets may be needed
235 to complete the authentication.
239 The authentication cycle ends with the server either rejecting the
240 connection attempt (ErrorResponse), or sending AuthenticationOk.
244 The possible messages from the server in this phase are:
248 <term>ErrorResponse</term>
251 The connection attempt has been rejected.
252 The server then immediately closes the connection.
258 <term>AuthenticationOk</term>
261 The authentication exchange is successfully completed.
267 <term>AuthenticationKerberosV5</term>
270 The frontend must now take part in a Kerberos V5
271 authentication dialog (not described here, part of the
272 Kerberos specification) with the server. If this is
273 successful, the server responds with an AuthenticationOk,
274 otherwise it responds with an ErrorResponse.
280 <term>AuthenticationCleartextPassword</term>
283 The frontend must now send a PasswordMessage containing the
284 password in clear-text form. If
285 this is the correct password, the server responds with an
286 AuthenticationOk, otherwise it responds with an ErrorResponse.
292 <term>AuthenticationMD5Password</term>
295 The frontend must now send a PasswordMessage containing the
296 password (with username) encrypted via MD5, then encrypted
297 again using the 4-byte random salt specified in the
298 AuthenticationMD5Password message. If this is the correct
299 password, the server responds with an AuthenticationOk,
300 otherwise it responds with an ErrorResponse. The actual
301 PasswordMessage can be computed in SQL as <literal>concat('md5',
302 md5(concat(md5(concat(password, username)), random-salt)))</>.
303 (Keep in mind the <function>md5()</> function returns its
304 result as a hex string.)
310 <term>AuthenticationSCMCredential</term>
313 This response is only possible for local Unix-domain connections
314 on platforms that support SCM credential messages. The frontend
315 must issue an SCM credential message and then send a single data
316 byte. (The contents of the data byte are uninteresting; it's
317 only used to ensure that the server waits long enough to receive
318 the credential message.) If the credential is acceptable,
319 the server responds with an
320 AuthenticationOk, otherwise it responds with an ErrorResponse.
321 (This message type is only issued by pre-9.1 servers. It may
322 eventually be removed from the protocol specification.)
328 <term>AuthenticationGSS</term>
331 The frontend must now initiate a GSSAPI negotiation. The frontend
332 will send a PasswordMessage with the first part of the GSSAPI
333 data stream in response to this. If further messages are needed,
334 the server will respond with AuthenticationGSSContinue.
340 <term>AuthenticationSSPI</term>
343 The frontend must now initiate a SSPI negotiation. The frontend
344 will send a PasswordMessage with the first part of the SSPI
345 data stream in response to this. If further messages are needed,
346 the server will respond with AuthenticationGSSContinue.
352 <term>AuthenticationGSSContinue</term>
355 This message contains the response data from the previous step
356 of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI
357 or a previous AuthenticationGSSContinue). If the GSSAPI
358 or SSPI data in this message
359 indicates more data is needed to complete the authentication,
360 the frontend must send that data as another PasswordMessage. If
361 GSSAPI or SSPI authentication is completed by this message, the server
362 will next send AuthenticationOk to indicate successful authentication
363 or ErrorResponse to indicate failure.
372 If the frontend does not support the authentication method
373 requested by the server, then it should immediately close the
378 After having received AuthenticationOk, the frontend must wait
379 for further messages from the server. In this phase a backend process
380 is being started, and the frontend is just an interested bystander.
381 It is still possible for the startup attempt
382 to fail (ErrorResponse), but in the normal case the backend will send
383 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
387 During this phase the backend will attempt to apply any additional
388 run-time parameter settings that were given in the startup message.
389 If successful, these values become session defaults. An error causes
390 ErrorResponse and exit.
394 The possible messages from the backend in this phase are:
398 <term>BackendKeyData</term>
401 This message provides secret-key data that the frontend must
402 save if it wants to be able to issue cancel requests later.
403 The frontend should not respond to this message, but should
404 continue listening for a ReadyForQuery message.
410 <term>ParameterStatus</term>
413 This message informs the frontend about the current (initial)
414 setting of backend parameters, such as <xref
415 linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
416 The frontend can ignore this message, or record the settings
417 for its future use; see <xref linkend="protocol-async"> for
418 more details. The frontend should not respond to this
419 message, but should continue listening for a ReadyForQuery
426 <term>ReadyForQuery</term>
429 Start-up is completed. The frontend can now issue commands.
435 <term>ErrorResponse</term>
438 Start-up failed. The connection is closed after sending this
445 <term>NoticeResponse</term>
448 A warning message has been issued. The frontend should
449 display the message but continue listening for ReadyForQuery
458 The ReadyForQuery message is the same one that the backend will
459 issue after each command cycle. Depending on the coding needs of
460 the frontend, it is reasonable to consider ReadyForQuery as
461 starting a command cycle, or to consider ReadyForQuery as ending the
462 start-up phase and each subsequent command cycle.
467 <title>Simple Query</title>
470 A simple query cycle is initiated by the frontend sending a Query message
471 to the backend. The message includes an SQL command (or commands)
472 expressed as a text string.
473 The backend then sends one or more response
474 messages depending on the contents of the query command string,
475 and finally a ReadyForQuery response message. ReadyForQuery
476 informs the frontend that it can safely send a new command.
477 (It is not actually necessary for the frontend to wait for
478 ReadyForQuery before issuing another command, but the frontend must
479 then take responsibility for figuring out what happens if the earlier
480 command fails and already-issued later commands succeed.)
484 The possible response messages from the backend are:
488 <term>CommandComplete</term>
491 An SQL command completed normally.
497 <term>CopyInResponse</term>
500 The backend is ready to copy data from the frontend to a
501 table; see <xref linkend="protocol-copy">.
507 <term>CopyOutResponse</term>
510 The backend is ready to copy data from a table to the
511 frontend; see <xref linkend="protocol-copy">.
517 <term>RowDescription</term>
520 Indicates that rows are about to be returned in response to
521 a <command>SELECT</command>, <command>FETCH</command>, etc query.
522 The contents of this message describe the column layout of the rows.
523 This will be followed by a DataRow message for each row being returned
533 One of the set of rows returned by
534 a <command>SELECT</command>, <command>FETCH</command>, etc query.
540 <term>EmptyQueryResponse</term>
543 An empty query string was recognized.
549 <term>ErrorResponse</term>
552 An error has occurred.
558 <term>ReadyForQuery</term>
561 Processing of the query string is complete. A separate
562 message is sent to indicate this because the query string might
563 contain multiple SQL commands. (CommandComplete marks the
564 end of processing one SQL command, not the whole string.)
565 ReadyForQuery will always be sent, whether processing
566 terminates successfully or with an error.
572 <term>NoticeResponse</term>
575 A warning message has been issued in relation to the query.
576 Notices are in addition to other responses, i.e., the backend
577 will continue processing the command.
586 The response to a <command>SELECT</> query (or other queries that
587 return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
588 normally consists of RowDescription, zero or more
589 DataRow messages, and then CommandComplete.
590 <command>COPY</> to or from the frontend invokes special protocol
591 as described in <xref linkend="protocol-copy">.
592 All other query types normally produce only
593 a CommandComplete message.
597 Since a query string could contain several queries (separated by
598 semicolons), there might be several such response sequences before the
599 backend finishes processing the query string. ReadyForQuery is issued
600 when the entire string has been processed and the backend is ready to
601 accept a new query string.
605 If a completely empty (no contents other than whitespace) query string
606 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
610 In the event of an error, ErrorResponse is issued followed by
611 ReadyForQuery. All further processing of the query string is aborted by
612 ErrorResponse (even if more queries remained in it). Note that this
613 might occur partway through the sequence of messages generated by an
618 In simple Query mode, the format of retrieved values is always text,
619 except when the given command is a <command>FETCH</> from a cursor
620 declared with the <literal>BINARY</> option. In that case, the
621 retrieved values are in binary format. The format codes given in
622 the RowDescription message tell which format is being used.
626 A frontend must be prepared to accept ErrorResponse and
627 NoticeResponse messages whenever it is expecting any other type of
628 message. See also <xref linkend="protocol-async"> concerning messages
629 that the backend might generate due to outside events.
633 Recommended practice is to code frontends in a state-machine style
634 that will accept any message type at any time that it could make sense,
635 rather than wiring in assumptions about the exact sequence of messages.
639 <sect2 id="protocol-flow-ext-query">
640 <title>Extended Query</title>
643 The extended query protocol breaks down the above-described simple
644 query protocol into multiple steps. The results of preparatory
645 steps can be re-used multiple times for improved efficiency.
646 Furthermore, additional features are available, such as the possibility
647 of supplying data values as separate parameters instead of having to
648 insert them directly into a query string.
652 In the extended protocol, the frontend first sends a Parse message,
653 which contains a textual query string, optionally some information
654 about data types of parameter placeholders, and the
655 name of a destination prepared-statement object (an empty string
656 selects the unnamed prepared statement). The response is
657 either ParseComplete or ErrorResponse. Parameter data types can be
658 specified by OID; if not given, the parser attempts to infer the
659 data types in the same way as it would do for untyped literal string
665 A parameter data type can be left unspecified by setting it to zero,
666 or by making the array of parameter type OIDs shorter than the
667 number of parameter symbols (<literal>$</><replaceable>n</>)
668 used in the query string. Another special case is that a parameter's
669 type can be specified as <type>void</> (that is, the OID of the
670 <type>void</> pseudotype). This is meant to allow parameter symbols
671 to be used for function parameters that are actually OUT parameters.
672 Ordinarily there is no context in which a <type>void</> parameter
673 could be used, but if such a parameter symbol appears in a function's
674 parameter list, it is effectively ignored. For example, a function
675 call such as <literal>foo($1,$2,$3,$4)</> could match a function with
676 two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
677 are specified as having type <type>void</>.
683 The query string contained in a Parse message cannot include more
684 than one SQL statement; else a syntax error is reported. This
685 restriction does not exist in the simple-query protocol, but it
686 does exist in the extended protocol, because allowing prepared
687 statements or portals to contain multiple commands would complicate
693 If successfully created, a named prepared-statement object lasts till
694 the end of the current session, unless explicitly destroyed. An unnamed
695 prepared statement lasts only until the next Parse statement specifying
696 the unnamed statement as destination is issued. (Note that a simple
697 Query message also destroys the unnamed statement.) Named prepared
698 statements must be explicitly closed before they can be redefined by
699 another Parse message, but this is not required for the unnamed statement.
700 Named prepared statements can also be created and accessed at the SQL
701 command level, using <command>PREPARE</> and <command>EXECUTE</>.
705 Once a prepared statement exists, it can be readied for execution using a
706 Bind message. The Bind message gives the name of the source prepared
707 statement (empty string denotes the unnamed prepared statement), the name
708 of the destination portal (empty string denotes the unnamed portal), and
709 the values to use for any parameter placeholders present in the prepared
711 supplied parameter set must match those needed by the prepared statement.
712 (If you declared any <type>void</> parameters in the Parse message,
713 pass NULL values for them in the Bind message.)
714 Bind also specifies the format to use for any data returned
715 by the query; the format can be specified overall, or per-column.
716 The response is either BindComplete or ErrorResponse.
721 The choice between text and binary output is determined by the format
722 codes given in Bind, regardless of the SQL command involved. The
723 <literal>BINARY</> attribute in cursor declarations is irrelevant when
724 using extended query protocol.
729 Query planning typically occurs when the Bind message is processed.
730 If the prepared statement has no parameters, or is executed repeatedly,
731 the server might save the created plan and re-use it during subsequent
732 Bind messages for the same prepared statement. However, it will do so
733 only if it finds that a generic plan can be created that is not much
734 less efficient than a plan that depends on the specific parameter values
735 supplied. This happens transparently so far as the protocol is concerned.
739 If successfully created, a named portal object lasts till the end of the
740 current transaction, unless explicitly destroyed. An unnamed portal is
741 destroyed at the end of the transaction, or as soon as the next Bind
742 statement specifying the unnamed portal as destination is issued. (Note
743 that a simple Query message also destroys the unnamed portal.) Named
744 portals must be explicitly closed before they can be redefined by another
745 Bind message, but this is not required for the unnamed portal.
746 Named portals can also be created and accessed at the SQL
747 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
751 Once a portal exists, it can be executed using an Execute message.
752 The Execute message specifies the portal name (empty string denotes the
754 a maximum result-row count (zero meaning <quote>fetch all rows</>).
755 The result-row count is only meaningful for portals
756 containing commands that return row sets; in other cases the command is
757 always executed to completion, and the row count is ignored.
759 responses to Execute are the same as those described above for queries
760 issued via simple query protocol, except that Execute doesn't cause
761 ReadyForQuery or RowDescription to be issued.
765 If Execute terminates before completing the execution of a portal
766 (due to reaching a nonzero result-row count), it will send a
767 PortalSuspended message; the appearance of this message tells the frontend
768 that another Execute should be issued against the same portal to
769 complete the operation. The CommandComplete message indicating
770 completion of the source SQL command is not sent until
771 the portal's execution is completed. Therefore, an Execute phase is
772 always terminated by the appearance of exactly one of these messages:
773 CommandComplete, EmptyQueryResponse (if the portal was created from
774 an empty query string), ErrorResponse, or PortalSuspended.
778 At completion of each series of extended-query messages, the frontend
779 should issue a Sync message. This parameterless message causes the
780 backend to close the current transaction if it's not inside a
781 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
782 meaning to commit if no error, or roll back if error). Then a
783 ReadyForQuery response is issued. The purpose of Sync is to provide
784 a resynchronization point for error recovery. When an error is detected
785 while processing any extended-query message, the backend issues
786 ErrorResponse, then reads and discards messages until a Sync is reached,
787 then issues ReadyForQuery and returns to normal message processing.
788 (But note that no skipping occurs if an error is detected
789 <emphasis>while</> processing Sync — this ensures that there is one
790 and only one ReadyForQuery sent for each Sync.)
795 Sync does not cause a transaction block opened with <command>BEGIN</>
796 to be closed. It is possible to detect this situation since the
797 ReadyForQuery message includes transaction status information.
802 In addition to these fundamental, required operations, there are several
803 optional operations that can be used with extended-query protocol.
807 The Describe message (portal variant) specifies the name of an existing
808 portal (or an empty string for the unnamed portal). The response is a
809 RowDescription message describing the rows that will be returned by
810 executing the portal; or a NoData message if the portal does not contain a
811 query that will return rows; or ErrorResponse if there is no such portal.
815 The Describe message (statement variant) specifies the name of an existing
816 prepared statement (or an empty string for the unnamed prepared
817 statement). The response is a ParameterDescription message describing the
818 parameters needed by the statement, followed by a RowDescription message
819 describing the rows that will be returned when the statement is eventually
820 executed (or a NoData message if the statement will not return rows).
821 ErrorResponse is issued if there is no such prepared statement. Note that
822 since Bind has not yet been issued, the formats to be used for returned
823 columns are not yet known to the backend; the format code fields in the
824 RowDescription message will be zeroes in this case.
829 In most scenarios the frontend should issue one or the other variant
830 of Describe before issuing Execute, to ensure that it knows how to
831 interpret the results it will get back.
836 The Close message closes an existing prepared statement or portal
837 and releases resources. It is not an error to issue Close against
838 a nonexistent statement or portal name. The response is normally
839 CloseComplete, but could be ErrorResponse if some difficulty is
840 encountered while releasing resources. Note that closing a prepared
841 statement implicitly closes any open portals that were constructed
846 The Flush message does not cause any specific output to be generated,
847 but forces the backend to deliver any data pending in its output
848 buffers. A Flush must be sent after any extended-query command except
849 Sync, if the frontend wishes to examine the results of that command before
850 issuing more commands. Without Flush, messages returned by the backend
851 will be combined into the minimum possible number of packets to minimize
857 The simple Query message is approximately equivalent to the series Parse,
858 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
859 statement and portal objects and no parameters. One difference is that
860 it will accept multiple SQL statements in the query string, automatically
861 performing the bind/describe/execute sequence for each one in succession.
862 Another difference is that it will not return ParseComplete, BindComplete,
863 CloseComplete, or NoData messages.
869 <title>Function Call</title>
872 The Function Call sub-protocol allows the client to request a direct
873 call of any function that exists in the database's
874 <structname>pg_proc</structname> system catalog. The client must have
875 execute permission for the function.
880 The Function Call sub-protocol is a legacy feature that is probably best
881 avoided in new code. Similar results can be accomplished by setting up
882 a prepared statement that does <literal>SELECT function($1, ...)</>.
883 The Function Call cycle can then be replaced with Bind/Execute.
888 A Function Call cycle is initiated by the frontend sending a
889 FunctionCall message to the backend. The backend then sends one
890 or more response messages depending on the results of the function
891 call, and finally a ReadyForQuery response message. ReadyForQuery
892 informs the frontend that it can safely send a new query or
897 The possible response messages from the backend are:
901 <term>ErrorResponse</term>
904 An error has occurred.
910 <term>FunctionCallResponse</term>
913 The function call was completed and returned the result given
915 (Note that the Function Call protocol can only handle a single
916 scalar result, not a row type or set of results.)
922 <term>ReadyForQuery</term>
925 Processing of the function call is complete. ReadyForQuery
926 will always be sent, whether processing terminates
927 successfully or with an error.
933 <term>NoticeResponse</term>
936 A warning message has been issued in relation to the function
937 call. Notices are in addition to other responses, i.e., the
938 backend will continue processing the command.
946 <sect2 id="protocol-copy">
947 <title>COPY Operations</title>
950 The <command>COPY</> command allows high-speed bulk data transfer
951 to or from the server. Copy-in and copy-out operations each switch
952 the connection into a distinct sub-protocol, which lasts until the
953 operation is completed.
957 Copy-in mode (data transfer to the server) is initiated when the
958 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
959 sends a CopyInResponse message to the frontend. The frontend should
960 then send zero or more CopyData messages, forming a stream of input
961 data. (The message boundaries are not required to have anything to do
962 with row boundaries, although that is often a reasonable choice.)
963 The frontend can terminate the copy-in mode by sending either a CopyDone
964 message (allowing successful termination) or a CopyFail message (which
965 will cause the <command>COPY</> SQL statement to fail with an
966 error). The backend then reverts to the command-processing mode it was
967 in before the <command>COPY</> started, which will be either simple or
968 extended query protocol. It will next send either CommandComplete
969 (if successful) or ErrorResponse (if not).
973 In the event of a backend-detected error during copy-in mode (including
974 receipt of a CopyFail message), the backend will issue an ErrorResponse
975 message. If the <command>COPY</> command was issued via an extended-query
976 message, the backend will now discard frontend messages until a Sync
977 message is received, then it will issue ReadyForQuery and return to normal
978 processing. If the <command>COPY</> command was issued in a simple
979 Query message, the rest of that message is discarded and ReadyForQuery
980 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
981 messages issued by the frontend will simply be dropped.
985 The backend will ignore Flush and Sync messages received during copy-in
986 mode. Receipt of any other non-copy message type constitutes an error
987 that will abort the copy-in state as described above. (The exception for
988 Flush and Sync is for the convenience of client libraries that always
989 send Flush or Sync after an Execute message, without checking whether
990 the command to be executed is a <command>COPY FROM STDIN</>.)
994 Copy-out mode (data transfer from the server) is initiated when the
995 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
996 sends a CopyOutResponse message to the frontend, followed by
997 zero or more CopyData messages (always one per row), followed by CopyDone.
998 The backend then reverts to the command-processing mode it was
999 in before the <command>COPY</> started, and sends CommandComplete.
1000 The frontend cannot abort the transfer (except by closing the connection
1001 or issuing a Cancel request),
1002 but it can discard unwanted CopyData and CopyDone messages.
1006 In the event of a backend-detected error during copy-out mode,
1007 the backend will issue an ErrorResponse message and revert to normal
1008 processing. The frontend should treat receipt of ErrorResponse as
1009 terminating the copy-out mode.
1013 It is possible for NoticeResponse and ParameterStatus messages to be
1014 interspersed between CopyData messages; frontends must handle these cases,
1015 and should be prepared for other asynchronous message types as well (see
1016 <xref linkend="protocol-async">). Otherwise, any message type other than
1017 CopyData or CopyDone may be treated as terminating copy-out mode.
1021 There is another Copy-related mode called copy-both, which allows
1022 high-speed bulk data transfer to <emphasis>and</> from the server.
1023 Copy-both mode is initiated when a backend in walsender mode
1024 executes a <command>START_REPLICATION</command> statement. The
1025 backend sends a CopyBothResponse message to the frontend. Both
1026 the backend and the frontend may then send CopyData messages
1027 until either end sends a CopyDone message. After the client
1028 sends a CopyDone message, the connection goes from copy-both mode to
1029 copy-out mode, and the client may not send any more CopyData messages.
1030 Similarly, when the server sends a CopyDone message, the connection
1031 goes into copy-in mode, and the server may not send any more CopyData
1032 messages. After both sides have sent a CopyDone message, the copy mode
1033 is terminated, and the backend reverts to the command-processing mode.
1034 See <xref linkend="protocol-replication"> for more information on the
1035 subprotocol transmitted over copy-both mode.
1039 The CopyInResponse, CopyOutResponse and CopyBothResponse messages
1040 include fields that inform the frontend of the number of columns
1041 per row and the format codes being used for each column. (As of
1042 the present implementation, all columns in a given <command>COPY</>
1043 operation will use the same format, but the message design does not
1049 <sect2 id="protocol-async">
1050 <title>Asynchronous Operations</title>
1053 There are several cases in which the backend will send messages that
1054 are not specifically prompted by the frontend's command stream.
1055 Frontends must be prepared to deal with these messages at any time,
1056 even when not engaged in a query.
1057 At minimum, one should check for these cases before beginning to
1058 read a query response.
1062 It is possible for NoticeResponse messages to be generated due to
1063 outside activity; for example, if the database administrator commands
1064 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1065 indicating this fact before closing the connection. Accordingly,
1066 frontends should always be prepared to accept and display NoticeResponse
1067 messages, even when the connection is nominally idle.
1071 ParameterStatus messages will be generated whenever the active
1072 value changes for any of the parameters the backend believes the
1073 frontend should know about. Most commonly this occurs in response
1074 to a <command>SET</> SQL command executed by the frontend, and
1075 this case is effectively synchronous — but it is also possible
1076 for parameter status changes to occur because the administrator
1077 changed a configuration file and then sent the
1078 <systemitem>SIGHUP</systemitem> signal to the server. Also,
1079 if a <command>SET</command> command is rolled back, an appropriate
1080 ParameterStatus message will be generated to report the current
1085 At present there is a hard-wired set of parameters for which
1086 ParameterStatus will be generated: they are
1087 <varname>server_version</>,
1088 <varname>server_encoding</>,
1089 <varname>client_encoding</>,
1090 <varname>application_name</>,
1091 <varname>is_superuser</>,
1092 <varname>session_authorization</>,
1093 <varname>DateStyle</>,
1094 <varname>IntervalStyle</>,
1095 <varname>TimeZone</>,
1096 <varname>integer_datetimes</>, and
1097 <varname>standard_conforming_strings</>.
1098 (<varname>server_encoding</>, <varname>TimeZone</>, and
1099 <varname>integer_datetimes</> were not reported by releases before 8.0;
1100 <varname>standard_conforming_strings</> was not reported by releases
1102 <varname>IntervalStyle</> was not reported by releases before 8.4;
1103 <varname>application_name</> was not reported by releases before 9.0.)
1105 <varname>server_version</>,
1106 <varname>server_encoding</> and
1107 <varname>integer_datetimes</>
1108 are pseudo-parameters that cannot change after startup.
1109 This set might change in the future, or even become configurable.
1110 Accordingly, a frontend should simply ignore ParameterStatus for
1111 parameters that it does not understand or care about.
1115 If a frontend issues a <command>LISTEN</command> command, then the
1116 backend will send a NotificationResponse message (not to be
1117 confused with NoticeResponse!) whenever a
1118 <command>NOTIFY</command> command is executed for the same
1124 At present, NotificationResponse can only be sent outside a
1125 transaction, and thus it will not occur in the middle of a
1126 command-response series, though it might occur just before ReadyForQuery.
1127 It is unwise to design frontend logic that assumes that, however.
1128 Good practice is to be able to accept NotificationResponse at any
1129 point in the protocol.
1135 <title>Canceling Requests in Progress</title>
1138 During the processing of a query, the frontend might request
1139 cancellation of the query. The cancel request is not sent
1140 directly on the open connection to the backend for reasons of
1141 implementation efficiency: we don't want to have the backend
1142 constantly checking for new input from the frontend during query
1143 processing. Cancel requests should be relatively infrequent, so
1144 we make them slightly cumbersome in order to avoid a penalty in
1149 To issue a cancel request, the frontend opens a new connection to
1150 the server and sends a CancelRequest message, rather than the
1151 StartupMessage message that would ordinarily be sent across a new
1152 connection. The server will process this request and then close
1153 the connection. For security reasons, no direct reply is made to
1154 the cancel request message.
1158 A CancelRequest message will be ignored unless it contains the
1159 same key data (PID and secret key) passed to the frontend during
1160 connection start-up. If the request matches the PID and secret
1161 key for a currently executing backend, the processing of the
1162 current query is aborted. (In the existing implementation, this is
1163 done by sending a special signal to the backend process that is
1164 processing the query.)
1168 The cancellation signal might or might not have any effect — for
1169 example, if it arrives after the backend has finished processing
1170 the query, then it will have no effect. If the cancellation is
1171 effective, it results in the current command being terminated
1172 early with an error message.
1176 The upshot of all this is that for reasons of both security and
1177 efficiency, the frontend has no direct way to tell whether a
1178 cancel request has succeeded. It must continue to wait for the
1179 backend to respond to the query. Issuing a cancel simply improves
1180 the odds that the current query will finish soon, and improves the
1181 odds that it will fail with an error message instead of
1186 Since the cancel request is sent across a new connection to the
1187 server and not across the regular frontend/backend communication
1188 link, it is possible for the cancel request to be issued by any
1189 process, not just the frontend whose query is to be canceled.
1190 This might provide additional flexibility when building
1191 multiple-process applications. It also introduces a security
1192 risk, in that unauthorized persons might try to cancel queries.
1193 The security risk is addressed by requiring a dynamically
1194 generated secret key to be supplied in cancel requests.
1199 <title>Termination</title>
1202 The normal, graceful termination procedure is that the frontend
1203 sends a Terminate message and immediately closes the connection.
1204 On receipt of this message, the backend closes the connection and
1209 In rare cases (such as an administrator-commanded database shutdown)
1210 the backend might disconnect without any frontend request to do so.
1211 In such cases the backend will attempt to send an error or notice message
1212 giving the reason for the disconnection before it closes the connection.
1216 Other termination scenarios arise from various failure cases, such as core
1217 dump at one end or the other, loss of the communications link, loss of
1218 message-boundary synchronization, etc. If either frontend or backend sees
1219 an unexpected closure of the connection, it should clean
1220 up and terminate. The frontend has the option of launching a new backend
1221 by recontacting the server if it doesn't want to terminate itself.
1222 Closing the connection is also advisable if an unrecognizable message type
1223 is received, since this probably indicates loss of message-boundary sync.
1227 For either normal or abnormal termination, any open transaction is
1228 rolled back, not committed. One should note however that if a
1229 frontend disconnects while a non-<command>SELECT</command> query
1230 is being processed, the backend will probably finish the query
1231 before noticing the disconnection. If the query is outside any
1232 transaction block (<command>BEGIN</> ... <command>COMMIT</>
1233 sequence) then its results might be committed before the
1234 disconnection is recognized.
1239 <title><acronym>SSL</acronym> Session Encryption</title>
1242 If <productname>PostgreSQL</> was built with
1243 <acronym>SSL</acronym> support, frontend/backend communications
1244 can be encrypted using <acronym>SSL</acronym>. This provides
1245 communication security in environments where attackers might be
1246 able to capture the session traffic. For more information on
1247 encrypting <productname>PostgreSQL</productname> sessions with
1248 <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1252 To initiate an <acronym>SSL</acronym>-encrypted connection, the
1253 frontend initially sends an SSLRequest message rather than a
1254 StartupMessage. The server then responds with a single byte
1255 containing <literal>S</> or <literal>N</>, indicating that it is
1256 willing or unwilling to perform <acronym>SSL</acronym>,
1257 respectively. The frontend might close the connection at this point
1258 if it is dissatisfied with the response. To continue after
1259 <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1260 (not described here, part of the <acronym>SSL</acronym>
1261 specification) with the server. If this is successful, continue
1262 with sending the usual StartupMessage. In this case the
1263 StartupMessage and all subsequent data will be
1264 <acronym>SSL</acronym>-encrypted. To continue after
1265 <literal>N</>, send the usual StartupMessage and proceed without
1270 The frontend should also be prepared to handle an ErrorMessage
1271 response to SSLRequest from the server. This would only occur if
1272 the server predates the addition of <acronym>SSL</acronym> support
1273 to <productname>PostgreSQL</>. (Such servers are now very ancient,
1274 and likely do not exist in the wild anymore.)
1275 In this case the connection must
1276 be closed, but the frontend might choose to open a fresh connection
1277 and proceed without requesting <acronym>SSL</acronym>.
1281 An initial SSLRequest can also be used in a connection that is being
1282 opened to send a CancelRequest message.
1286 While the protocol itself does not provide a way for the server to
1287 force <acronym>SSL</acronym> encryption, the administrator can
1288 configure the server to reject unencrypted sessions as a byproduct
1289 of authentication checking.
1294 <sect1 id="protocol-replication">
1295 <title>Streaming Replication Protocol</title>
1298 To initiate streaming replication, the frontend sends the
1299 <literal>replication</> parameter in the startup message. This tells the
1300 backend to go into walsender mode, wherein a small set of replication commands
1301 can be issued instead of SQL statements. Only the simple query protocol can be
1302 used in walsender mode.
1304 The commands accepted in walsender mode are:
1308 <term>IDENTIFY_SYSTEM</term>
1311 Requests the server to identify itself. Server replies with a result
1312 set of a single row, containing three fields:
1323 The unique system identifier identifying the cluster. This
1324 can be used to check that the base backup used to initialize the
1325 standby came from the same cluster.
1336 Current TimelineID. Also useful to check that the standby is
1337 consistent with the master.
1348 Current xlog write location. Useful to get a known location in the
1349 transaction log where streaming can start.
1360 <term>TIMELINE_HISTORY <replaceable class="parameter">tli</replaceable></term>
1363 Requests the server to send over the timeline history file for timeline
1364 <replaceable class="parameter">tli</replaceable>. Server replies with a
1365 result set of a single row, containing two fields:
1376 Filename of the timeline history file, e.g 00000002.history.
1387 Contents of the timeline history file.
1398 <term>START_REPLICATION <replaceable class="parameter">XXX/XXX</> TIMELINE <replaceable class="parameter">tli</></term>
1401 Instructs server to start streaming WAL, starting at
1402 WAL position <replaceable class="parameter">XXX/XXX</> on timeline
1403 <replaceable class="parameter">tli</>.
1404 The server can reply with an error, e.g. if the requested section of WAL
1405 has already been recycled. On success, server responds with a
1406 CopyBothResponse message, and then starts to stream WAL to the frontend.
1410 If the client requests a timeline that's not the latest, but is part of
1411 the history of the server, the server will stream all the WAL on that
1412 timeline starting from the requested startpoint, up to the point where
1413 the server switched to another timeline. If the client requests
1414 streaming at exactly the end of an old timeline, the server responds
1415 immediately with CommandComplete without entering COPY mode.
1419 After streaming all the WAL on a timeline that is not the latest one,
1420 the server will end streaming by exiting the COPY mode. When the client
1421 acknowledges this by also exiting COPY mode, the server sends a
1422 single-row, single-column result set indicating the next timeline in
1423 this server's history. That is followed by a CommandComplete message,
1424 and the server is ready to accept a new command.
1428 WAL data is sent as a series of CopyData messages. (This allows
1429 other information to be intermixed; in particular the server can send
1430 an ErrorResponse message if it encounters a failure after beginning
1431 to stream.) The payload of each CopyData message from server to the
1432 client contains a message of one of the following formats:
1450 Identifies the message as WAL data.
1460 The starting point of the WAL data in this message.
1470 The current end of WAL on the server.
1480 The server's system clock at the time of transmission, as
1481 microseconds since midnight on 2000-01-01.
1487 Byte<replaceable>n</replaceable>
1491 A section of the WAL data stream.
1494 A single WAL record is never split across two XLogData messages.
1495 When a WAL record crosses a WAL page boundary, and is therefore
1496 already split using continuation records, it can be split at the page
1497 boundary. In other words, the first main WAL record and its
1498 continuation records can be sent in different XLogData messages.
1508 Primary keepalive message (B)
1519 Identifies the message as a sender keepalive.
1529 The current end of WAL on the server.
1539 The server's system clock at the time of transmission, as
1540 microseconds since midnight on 2000-01-01.
1550 1 means that the client should reply to this message as soon as
1551 possible, to avoid a timeout disconnect. 0 otherwise.
1563 The receiving process can send replies back to the sender at any time,
1564 using one of the following message formats (also in the payload of a
1572 Standby status update (F)
1583 Identifies the message as a receiver status update.
1593 The location of the last WAL byte + 1 received and written to disk
1604 The location of the last WAL byte + 1 flushed to disk in
1615 The location of the last WAL byte + 1 applied in the standby.
1625 The client's system clock at the time of transmission, as
1626 microseconds since midnight on 2000-01-01.
1636 If 1, the client requests the server to reply to this message
1637 immediately. This can be used to ping the server, to test if
1638 the connection is still healthy.
1653 Hot Standby feedback message (F)
1664 Identifies the message as a Hot Standby feedback message.
1674 The client's system clock at the time of transmission, as
1675 microseconds since midnight on 2000-01-01.
1685 The standby's current xmin. This may be 0, if the standby is
1686 sending notification that Hot Standby feedback will no longer
1687 be sent on this connection. Later non-zero messages may
1688 reinitiate the feedback mechanism.
1698 The standby's current epoch.
1712 <term>BASE_BACKUP [<literal>LABEL</literal> <replaceable>'label'</replaceable>] [<literal>PROGRESS</literal>] [<literal>FAST</literal>] [<literal>WAL</literal>] [<literal>NOWAIT</literal>]</term>
1715 Instructs the server to start streaming a base backup.
1716 The system will automatically be put in backup mode before the backup
1717 is started, and taken out of it when the backup is complete. The
1718 following options are accepted:
1721 <term><literal>LABEL</literal> <replaceable>'label'</replaceable></term>
1724 Sets the label of the backup. If none is specified, a backup label
1725 of <literal>base backup</literal> will be used. The quoting rules
1726 for the label are the same as a standard SQL string with
1727 <xref linkend="guc-standard-conforming-strings"> turned on.
1733 <term><literal>PROGRESS</></term>
1736 Request information required to generate a progress report. This will
1737 send back an approximate size in the header of each tablespace, which
1738 can be used to calculate how far along the stream is done. This is
1739 calculated by enumerating all the file sizes once before the transfer
1740 is even started, and may as such have a negative impact on the
1741 performance - in particular it may take longer before the first data
1742 is streamed. Since the database files can change during the backup,
1743 the size is only approximate and may both grow and shrink between
1744 the time of approximation and the sending of the actual files.
1750 <term><literal>FAST</></term>
1753 Request a fast checkpoint.
1759 <term><literal>WAL</literal></term>
1762 Include the necessary WAL segments in the backup. This will include
1763 all the files between start and stop backup in the
1764 <filename>pg_xlog</filename> directory of the base directory tar
1771 <term><literal>NOWAIT</literal></term>
1774 By default, the backup will wait until the last required xlog
1775 segment has been archived, or emit a warning if log archiving is
1776 not enabled. Specifying <literal>NOWAIT</literal> disables both
1777 the waiting and the warning, leaving the client responsible for
1778 ensuring the required log is available.
1785 When the backup is started, the server will first send two
1786 ordinary result sets, followed by one or more CopyResponse
1790 The first ordinary result set contains the starting position of the
1791 backup, in a single row with two columns. The first column contains
1792 the start position given in XLogRecPtr format, and the second column
1793 contains the corresponding timeline ID.
1796 The second ordinary result set has one row for each tablespace.
1797 The fields in this row are:
1803 The oid of the tablespace, or <literal>NULL</> if it's the base
1809 <term>spclocation</term>
1812 The full path of the tablespace directory, or <literal>NULL</>
1813 if it's the base directory.
1821 The approximate size of the tablespace, if progress report has
1822 been requested; otherwise it's <literal>NULL</>.
1829 After the second regular result set, one or more CopyResponse results
1830 will be sent, one for PGDATA and one for each additional tablespace other
1831 than <literal>pg_default</> and <literal>pg_global</>. The data in
1832 the CopyResponse results will be a tar format (following the
1833 <quote>ustar interchange format</> specified in the POSIX 1003.1-2008
1834 standard) dump of the tablespace contents, except that the two trailing
1835 blocks of zeroes specified in the standard are omitted.
1836 After the tar data is complete, a final ordinary result set will be sent,
1837 containing the WAL end position of the backup, in the same format as
1842 The tar archive for the data directory and each tablespace will contain
1843 all files in the directories, regardless of whether they are
1844 <productname>PostgreSQL</> files or other files added to the same
1845 directory. The only excluded files are:
1846 <itemizedlist spacing="compact" mark="bullet">
1849 <filename>postmaster.pid</>
1854 <filename>postmaster.opts</>
1859 <filename>pg_xlog</>, including subdirectories. If the backup is run
1860 with WAL files included, a synthesized version of <filename>pg_xlog</filename> will be
1861 included, but it will only contain the files necessary for the
1862 backup to work, not the rest of the contents.
1866 Owner, group and file mode are set if the underlying file system on
1867 the server supports it.
1870 Once all tablespaces have been sent, a final regular result set will
1871 be sent. This result set contains the end position of the
1872 backup, given in XLogRecPtr format as a single column in a single row.
1882 <sect1 id="protocol-message-types">
1883 <title>Message Data Types</title>
1886 This section describes the base data types used in messages.
1892 Int<replaceable>n</replaceable>(<replaceable>i</replaceable>)
1896 An <replaceable>n</replaceable>-bit integer in network byte
1897 order (most significant byte first).
1898 If <replaceable>i</replaceable> is specified it
1899 is the exact value that will appear, otherwise the value
1900 is variable. Eg. Int16, Int32(42).
1907 Int<replaceable>n</replaceable>[<replaceable>k</replaceable>]
1911 An array of <replaceable>k</replaceable>
1912 <replaceable>n</replaceable>-bit integers, each in network
1913 byte order. The array length <replaceable>k</replaceable>
1914 is always determined by an earlier field in the message.
1922 String(<replaceable>s</replaceable>)
1926 A null-terminated string (C-style string). There is no
1927 specific length limitation on strings.
1928 If <replaceable>s</replaceable> is specified it is the exact
1929 value that will appear, otherwise the value is variable.
1930 Eg. String, String("user").
1935 <emphasis>There is no predefined limit</emphasis> on the length of a string
1936 that can be returned by the backend. Good coding strategy for a frontend
1937 is to use an expandable buffer so that anything that fits in memory can be
1938 accepted. If that's not feasible, read the full string and discard trailing
1939 characters that don't fit into your fixed-size buffer.
1947 Byte<replaceable>n</replaceable>(<replaceable>c</replaceable>)
1951 Exactly <replaceable>n</replaceable> bytes. If the field
1952 width <replaceable>n</replaceable> is not a constant, it is
1953 always determinable from an earlier field in the message.
1954 If <replaceable>c</replaceable> is specified it is the exact
1955 value. Eg. Byte2, Byte1('\n').
1964 <sect1 id="protocol-message-formats">
1965 <title>Message Formats</title>
1968 This section describes the detailed format of each message. Each is marked to
1969 indicate that it can be sent by a frontend (F), a backend (B), or both
1971 Notice that although each message includes a byte count at the beginning,
1972 the message format is defined so that the message end can be found without
1973 reference to the byte count. This aids validity checking. (The CopyData
1974 message is an exception, because it forms part of a data stream; the contents
1975 of any individual CopyData message cannot be interpretable on their own.)
1983 AuthenticationOk (B)
1995 Identifies the message as an authentication request.
2005 Length of message contents in bytes, including self.
2015 Specifies that the authentication was successful.
2028 AuthenticationKerberosV5 (B)
2040 Identifies the message as an authentication request.
2050 Length of message contents in bytes, including self.
2060 Specifies that Kerberos V5 authentication is required.
2072 AuthenticationCleartextPassword (B)
2084 Identifies the message as an authentication request.
2094 Length of message contents in bytes, including self.
2104 Specifies that a clear-text password is required.
2116 AuthenticationMD5Password (B)
2128 Identifies the message as an authentication request.
2138 Length of message contents in bytes, including self.
2148 Specifies that an MD5-encrypted password is required.
2158 The salt to use when encrypting the password.
2171 AuthenticationSCMCredential (B)
2183 Identifies the message as an authentication request.
2193 Length of message contents in bytes, including self.
2203 Specifies that an SCM credentials message is required.
2216 AuthenticationGSS (B)
2228 Identifies the message as an authentication request.
2238 Length of message contents in bytes, including self.
2248 Specifies that GSSAPI authentication is required.
2261 AuthenticationSSPI (B)
2273 Identifies the message as an authentication request.
2283 Length of message contents in bytes, including self.
2293 Specifies that SSPI authentication is required.
2304 AuthenticationGSSContinue (B)
2316 Identifies the message as an authentication request.
2326 Length of message contents in bytes, including self.
2336 Specifies that this message contains GSSAPI or SSPI data.
2342 Byte<replaceable>n</replaceable>
2346 GSSAPI or SSPI authentication data.
2371 Identifies the message as cancellation key data.
2372 The frontend must save these values if it wishes to be
2373 able to issue CancelRequest messages later.
2383 Length of message contents in bytes, including self.
2393 The process ID of this backend.
2403 The secret key of this backend.
2428 Identifies the message as a Bind command.
2438 Length of message contents in bytes, including self.
2448 The name of the destination portal
2449 (an empty string selects the unnamed portal).
2459 The name of the source prepared statement
2460 (an empty string selects the unnamed prepared statement).
2470 The number of parameter format codes that follow
2471 (denoted <replaceable>C</> below).
2472 This can be zero to indicate that there are no parameters
2473 or that the parameters all use the default format (text);
2474 or one, in which case the specified format code is applied
2475 to all parameters; or it can equal the actual number of
2482 Int16[<replaceable>C</>]
2486 The parameter format codes. Each must presently be
2487 zero (text) or one (binary).
2497 The number of parameter values that follow (possibly zero).
2498 This must match the number of parameters needed by the query.
2503 Next, the following pair of fields appear for each parameter:
2511 The length of the parameter value, in bytes (this count
2512 does not include itself). Can be zero.
2513 As a special case, -1 indicates a NULL parameter value.
2514 No value bytes follow in the NULL case.
2520 Byte<replaceable>n</replaceable>
2524 The value of the parameter, in the format indicated by the
2525 associated format code.
2526 <replaceable>n</replaceable> is the above length.
2531 After the last parameter, the following fields appear:
2539 The number of result-column format codes that follow
2540 (denoted <replaceable>R</> below).
2541 This can be zero to indicate that there are no result columns
2542 or that the result columns should all use the default format
2544 or one, in which case the specified format code is applied
2545 to all result columns (if any); or it can equal the actual
2546 number of result columns of the query.
2552 Int16[<replaceable>R</>]
2556 The result-column format codes. Each must presently be
2557 zero (text) or one (binary).
2581 Identifies the message as a Bind-complete indicator.
2591 Length of message contents in bytes, including self.
2616 Length of message contents in bytes, including self.
2626 The cancel request code. The value is chosen to contain
2627 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
2628 least 16 significant bits. (To avoid confusion, this code
2629 must not be the same as any protocol version number.)
2639 The process ID of the target backend.
2649 The secret key for the target backend.
2674 Identifies the message as a Close command.
2684 Length of message contents in bytes, including self.
2694 '<literal>S</>' to close a prepared statement; or
2695 '<literal>P</>' to close a portal.
2705 The name of the prepared statement or portal to close
2706 (an empty string selects the unnamed prepared statement
2731 Identifies the message as a Close-complete indicator.
2741 Length of message contents in bytes, including self.
2766 Identifies the message as a command-completed response.
2776 Length of message contents in bytes, including self.
2786 The command tag. This is usually a single
2787 word that identifies which SQL command was completed.
2791 For an <command>INSERT</command> command, the tag is
2792 <literal>INSERT <replaceable>oid</replaceable>
2793 <replaceable>rows</replaceable></literal>, where
2794 <replaceable>rows</replaceable> is the number of rows
2795 inserted. <replaceable>oid</replaceable> is the object ID
2796 of the inserted row if <replaceable>rows</replaceable> is 1
2797 and the target table has OIDs;
2798 otherwise <replaceable>oid</replaceable> is 0.
2802 For a <command>DELETE</command> command, the tag is
2803 <literal>DELETE <replaceable>rows</replaceable></literal> where
2804 <replaceable>rows</replaceable> is the number of rows deleted.
2808 For an <command>UPDATE</command> command, the tag is
2809 <literal>UPDATE <replaceable>rows</replaceable></literal> where
2810 <replaceable>rows</replaceable> is the number of rows updated.
2814 For a <command>SELECT</command> or <command>CREATE TABLE AS</command>
2815 command, the tag is <literal>SELECT <replaceable>rows</replaceable></literal>
2816 where <replaceable>rows</replaceable> is the number of rows retrieved.
2820 For a <command>MOVE</command> command, the tag is
2821 <literal>MOVE <replaceable>rows</replaceable></literal> where
2822 <replaceable>rows</replaceable> is the number of rows the
2823 cursor's position has been changed by.
2827 For a <command>FETCH</command> command, the tag is
2828 <literal>FETCH <replaceable>rows</replaceable></literal> where
2829 <replaceable>rows</replaceable> is the number of rows that
2830 have been retrieved from the cursor.
2834 For a <command>COPY</command> command, the tag is
2835 <literal>COPY <replaceable>rows</replaceable></literal> where
2836 <replaceable>rows</replaceable> is the number of rows copied.
2837 (Note: the row count appears only in
2838 <productname>PostgreSQL</productname> 8.2 and later.)
2852 CopyData (F & B)
2863 Identifies the message as <command>COPY</command> data.
2873 Length of message contents in bytes, including self.
2879 Byte<replaceable>n</replaceable>
2883 Data that forms part of a <command>COPY</command> data stream. Messages sent
2884 from the backend will always correspond to single data rows,
2885 but messages sent by frontends might divide the data stream
2898 CopyDone (F & B)
2910 Identifies the message as a <command>COPY</command>-complete indicator.
2920 Length of message contents in bytes, including self.
2945 Identifies the message as a <command>COPY</command>-failure indicator.
2955 Length of message contents in bytes, including self.
2965 An error message to report as the cause of failure.
2990 Identifies the message as a Start Copy In response.
2991 The frontend must now send copy-in data (if not
2992 prepared to do so, send a CopyFail message).
3002 Length of message contents in bytes, including self.
3012 0 indicates the overall <command>COPY</command> format is textual (rows
3013 separated by newlines, columns separated by separator
3015 1 indicates the overall copy format is binary (similar
3017 See <xref linkend="sql-copy">
3018 for more information.
3028 The number of columns in the data to be copied
3029 (denoted <replaceable>N</> below).
3035 Int16[<replaceable>N</>]
3039 The format codes to be used for each column.
3040 Each must presently be zero (text) or one (binary).
3041 All must be zero if the overall copy format is textual.
3066 Identifies the message as a Start Copy Out response.
3067 This message will be followed by copy-out data.
3077 Length of message contents in bytes, including self.
3087 0 indicates the overall <command>COPY</command> format
3088 is textual (rows separated by newlines, columns
3089 separated by separator characters, etc). 1 indicates
3090 the overall copy format is binary (similar to DataRow
3091 format). See <xref linkend="sql-copy"> for more information.
3101 The number of columns in the data to be copied
3102 (denoted <replaceable>N</> below).
3108 Int16[<replaceable>N</>]
3112 The format codes to be used for each column.
3113 Each must presently be zero (text) or one (binary).
3114 All must be zero if the overall copy format is textual.
3127 CopyBothResponse (B)
3139 Identifies the message as a Start Copy Both response.
3140 This message is used only for Streaming Replication.
3150 Length of message contents in bytes, including self.
3160 0 indicates the overall <command>COPY</command> format
3161 is textual (rows separated by newlines, columns
3162 separated by separator characters, etc). 1 indicates
3163 the overall copy format is binary (similar to DataRow
3164 format). See <xref linkend="sql-copy"> for more information.
3174 The number of columns in the data to be copied
3175 (denoted <replaceable>N</> below).
3181 Int16[<replaceable>N</>]
3185 The format codes to be used for each column.
3186 Each must presently be zero (text) or one (binary).
3187 All must be zero if the overall copy format is textual.
3211 Identifies the message as a data row.
3221 Length of message contents in bytes, including self.
3231 The number of column values that follow (possibly zero).
3236 Next, the following pair of fields appear for each column:
3244 The length of the column value, in bytes (this count
3245 does not include itself). Can be zero.
3246 As a special case, -1 indicates a NULL column value.
3247 No value bytes follow in the NULL case.
3253 Byte<replaceable>n</replaceable>
3257 The value of the column, in the format indicated by the
3258 associated format code.
3259 <replaceable>n</replaceable> is the above length.
3284 Identifies the message as a Describe command.
3294 Length of message contents in bytes, including self.
3304 '<literal>S</>' to describe a prepared statement; or
3305 '<literal>P</>' to describe a portal.
3315 The name of the prepared statement or portal to describe
3316 (an empty string selects the unnamed prepared statement
3329 EmptyQueryResponse (B)
3341 Identifies the message as a response to an empty query string.
3342 (This substitutes for CommandComplete.)
3352 Length of message contents in bytes, including self.
3377 Identifies the message as an error.
3387 Length of message contents in bytes, including self.
3392 The message body consists of one or more identified fields,
3393 followed by a zero byte as a terminator. Fields can appear in
3394 any order. For each field there is the following:
3402 A code identifying the field type; if zero, this is
3403 the message terminator and no string follows.
3404 The presently defined field types are listed in
3405 <xref linkend="protocol-error-fields">.
3406 Since more field types might be added in future,
3407 frontends should silently ignore fields of unrecognized
3443 Identifies the message as an Execute command.
3453 Length of message contents in bytes, including self.
3463 The name of the portal to execute
3464 (an empty string selects the unnamed portal).
3474 Maximum number of rows to return, if portal contains
3475 a query that returns rows (ignored otherwise). Zero
3476 denotes <quote>no limit</>.
3500 Identifies the message as a Flush command.
3510 Length of message contents in bytes, including self.
3535 Identifies the message as a function call.
3545 Length of message contents in bytes, including self.
3555 Specifies the object ID of the function to call.
3565 The number of argument format codes that follow
3566 (denoted <replaceable>C</> below).
3567 This can be zero to indicate that there are no arguments
3568 or that the arguments all use the default format (text);
3569 or one, in which case the specified format code is applied
3570 to all arguments; or it can equal the actual number of
3577 Int16[<replaceable>C</>]
3581 The argument format codes. Each must presently be
3582 zero (text) or one (binary).
3592 Specifies the number of arguments being supplied to the
3598 Next, the following pair of fields appear for each argument:
3606 The length of the argument value, in bytes (this count
3607 does not include itself). Can be zero.
3608 As a special case, -1 indicates a NULL argument value.
3609 No value bytes follow in the NULL case.
3615 Byte<replaceable>n</replaceable>
3619 The value of the argument, in the format indicated by the
3620 associated format code.
3621 <replaceable>n</replaceable> is the above length.
3626 After the last argument, the following field appears:
3634 The format code for the function result. Must presently be
3635 zero (text) or one (binary).
3648 FunctionCallResponse (B)
3660 Identifies the message as a function call result.
3670 Length of message contents in bytes, including self.
3680 The length of the function result value, in bytes (this count
3681 does not include itself). Can be zero.
3682 As a special case, -1 indicates a NULL function result.
3683 No value bytes follow in the NULL case.
3689 Byte<replaceable>n</replaceable>
3693 The value of the function result, in the format indicated by
3694 the associated format code.
3695 <replaceable>n</replaceable> is the above length.
3720 Identifies the message as a no-data indicator.
3730 Length of message contents in bytes, including self.
3755 Identifies the message as a notice.
3765 Length of message contents in bytes, including self.
3770 The message body consists of one or more identified fields,
3771 followed by a zero byte as a terminator. Fields can appear in
3772 any order. For each field there is the following:
3780 A code identifying the field type; if zero, this is
3781 the message terminator and no string follows.
3782 The presently defined field types are listed in
3783 <xref linkend="protocol-error-fields">.
3784 Since more field types might be added in future,
3785 frontends should silently ignore fields of unrecognized
3809 NotificationResponse (B)
3821 Identifies the message as a notification response.
3831 Length of message contents in bytes, including self.
3841 The process ID of the notifying backend process.
3851 The name of the channel that the notify has been raised on.
3861 The <quote>payload</> string passed from the notifying process.
3874 ParameterDescription (B)
3886 Identifies the message as a parameter description.
3896 Length of message contents in bytes, including self.
3906 The number of parameters used by the statement
3912 Then, for each parameter, there is the following:
3920 Specifies the object ID of the parameter data type.
3944 Identifies the message as a run-time parameter status report.
3954 Length of message contents in bytes, including self.
3964 The name of the run-time parameter being reported.
3974 The current value of the parameter.
3998 Identifies the message as a Parse command.
4008 Length of message contents in bytes, including self.
4018 The name of the destination prepared statement
4019 (an empty string selects the unnamed prepared statement).
4029 The query string to be parsed.
4039 The number of parameter data types specified
4040 (can be zero). Note that this is not an indication of
4041 the number of parameters that might appear in the
4042 query string, only the number that the frontend wants to
4043 prespecify types for.
4048 Then, for each parameter, there is the following:
4056 Specifies the object ID of the parameter data type.
4057 Placing a zero here is equivalent to leaving the type
4082 Identifies the message as a Parse-complete indicator.
4092 Length of message contents in bytes, including self.
4117 Identifies the message as a password response. Note that
4118 this is also used for GSSAPI and SSPI response messages
4119 (which is really a design error, since the contained data
4120 is not a null-terminated string in that case, but can be
4121 arbitrary binary data).
4131 Length of message contents in bytes, including self.
4141 The password (encrypted, if requested).
4165 Identifies the message as a portal-suspended indicator.
4166 Note this only appears if an Execute message's row-count limit
4177 Length of message contents in bytes, including self.
4202 Identifies the message as a simple query.
4212 Length of message contents in bytes, including self.
4222 The query string itself.
4247 Identifies the message type. ReadyForQuery is sent
4248 whenever the backend is ready for a new query cycle.
4258 Length of message contents in bytes, including self.
4268 Current backend transaction status indicator.
4269 Possible values are '<literal>I</>' if idle (not in
4270 a transaction block); '<literal>T</>' if in a transaction
4271 block; or '<literal>E</>' if in a failed transaction
4272 block (queries will be rejected until block is ended).
4297 Identifies the message as a row description.
4307 Length of message contents in bytes, including self.
4317 Specifies the number of fields in a row (can be zero).
4322 Then, for each field, there is the following:
4340 If the field can be identified as a column of a specific
4341 table, the object ID of the table; otherwise zero.
4351 If the field can be identified as a column of a specific
4352 table, the attribute number of the column; otherwise zero.
4362 The object ID of the field's data type.
4372 The data type size (see <varname>pg_type.typlen</>).
4373 Note that negative values denote variable-width types.
4383 The type modifier (see <varname>pg_attribute.atttypmod</>).
4384 The meaning of the modifier is type-specific.
4394 The format code being used for the field. Currently will
4395 be zero (text) or one (binary). In a RowDescription
4396 returned from the statement variant of Describe, the
4397 format code is not yet known and will always be zero.
4422 Length of message contents in bytes, including self.
4432 The <acronym>SSL</acronym> request code. The value is chosen to contain
4433 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
4434 least 16 significant bits. (To avoid confusion, this code
4435 must not be the same as any protocol version number.)
4460 Length of message contents in bytes, including self.
4470 The protocol version number. The most significant 16 bits are
4471 the major version number (3 for the protocol described here).
4472 The least significant 16 bits are the minor version number
4473 (0 for the protocol described here).
4478 The protocol version number is followed by one or more pairs of
4479 parameter name and value strings. A zero byte is required as a
4480 terminator after the last name/value pair.
4481 Parameters can appear in any
4482 order. <literal>user</> is required, others are optional.
4483 Each parameter is specified as:
4491 The parameter name. Currently recognized names are:
4500 The database user name to connect as. Required;
4501 there is no default.
4507 <literal>database</>
4511 The database to connect to. Defaults to the user name.
4521 Command-line arguments for the backend. (This is
4522 deprecated in favor of setting individual run-time
4529 In addition to the above, any run-time parameter that can be
4530 set at backend start time might be listed. Such settings
4531 will be applied during backend start (after parsing the
4532 command-line options if any). The values will act as
4543 The parameter value.
4568 Identifies the message as a Sync command.
4578 Length of message contents in bytes, including self.
4603 Identifies the message as a termination.
4613 Length of message contents in bytes, including self.
4629 <sect1 id="protocol-error-fields">
4630 <title>Error and Notice Message Fields</title>
4633 This section describes the fields that can appear in ErrorResponse and
4634 NoticeResponse messages. Each field type has a single-byte identification
4635 token. Note that any given field type should appear at most once per
4647 Severity: the field contents are
4648 <literal>ERROR</>, <literal>FATAL</>, or
4649 <literal>PANIC</> (in an error message), or
4650 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
4651 <literal>INFO</>, or <literal>LOG</> (in a notice message),
4652 or a localized translation of one of these. Always present.
4663 Code: the SQLSTATE code for the error (see <xref
4664 linkend="errcodes-appendix">). Not localizable. Always present.
4675 Message: the primary human-readable error message.
4676 This should be accurate but terse (typically one line).
4688 Detail: an optional secondary error message carrying more
4689 detail about the problem. Might run to multiple lines.
4700 Hint: an optional suggestion what to do about the problem.
4701 This is intended to differ from Detail in that it offers advice
4702 (potentially inappropriate) rather than hard facts.
4703 Might run to multiple lines.
4714 Position: the field value is a decimal ASCII integer, indicating
4715 an error cursor position as an index into the original query string.
4716 The first character has index 1, and positions are measured in
4717 characters not bytes.
4728 Internal position: this is defined the same as the <literal>P</>
4729 field, but it is used when the cursor position refers to an internally
4730 generated command rather than the one submitted by the client.
4731 The <literal>q</> field will always appear when this field appears.
4742 Internal query: the text of a failed internally-generated command.
4743 This could be, for example, a SQL query issued by a PL/pgSQL function.
4754 Where: an indication of the context in which the error occurred.
4755 Presently this includes a call stack traceback of active
4756 procedural language functions and internally-generated queries.
4757 The trace is one entry per line, most recent first.
4768 Schema name: if the error was associated with a specific database
4769 object, the name of the schema containing that object, if any.
4780 Table name: if the error was associated with a specific table, the
4781 name of the table. (When this field is present, the schema name field
4782 provides the name of the table's schema.)
4793 Column name: if the error was associated with a specific table column,
4794 the name of the column. (When this field is present, the schema and
4795 table name fields identify the table.)
4806 Datatype name: if the error was associated with a specific datatype,
4807 the name of the datatype. (When this field is present, the schema
4808 name field provides the name of the datatype's schema.)
4819 Constraint name: if the error was associated with a specific
4820 constraint, the name of the constraint. The table or domain that the
4821 constraint belongs to is reported using the fields listed above. (For
4822 this purpose, indexes are treated as constraints, even if they weren't
4823 created with constraint syntax.)
4834 File: the file name of the source-code location where the error
4846 Line: the line number of the source-code location where the error
4858 Routine: the name of the source-code routine reporting the error.
4867 The fields for schema name, table name, column name, datatype name, and
4868 constraint name are supplied only for a limited number of error types;
4869 see <xref linkend="errcodes-appendix">.
4874 The client is responsible for formatting displayed information to meet its
4875 needs; in particular it should break long lines as needed. Newline characters
4876 appearing in the error message fields should be treated as paragraph breaks,
4882 <sect1 id="protocol-changes">
4883 <title>Summary of Changes since Protocol 2.0</title>
4886 This section provides a quick checklist of changes, for the benefit of
4887 developers trying to update existing client libraries to protocol 3.0.
4891 The initial startup packet uses a flexible list-of-strings format
4892 instead of a fixed format. Notice that session default values for run-time
4893 parameters can now be specified directly in the startup packet. (Actually,
4894 you could do that before using the <literal>options</> field, but given the
4895 limited width of <literal>options</> and the lack of any way to quote
4896 whitespace in the values, it wasn't a very safe technique.)
4900 All messages now have a length count immediately following the message type
4901 byte (except for startup packets, which have no type byte). Also note that
4902 PasswordMessage now has a type byte.
4906 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
4907 messages now contain multiple fields, from which the client code can
4908 assemble an error message of the desired level of verbosity. Note that
4909 individual fields will typically not end with a newline, whereas the single
4910 string sent in the older protocol always did.
4914 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
4919 The distinction between BinaryRow and DataRow message types is gone; the
4920 single DataRow message type serves for returning data in all formats.
4921 Note that the layout of DataRow has changed to make it easier to parse.
4922 Also, the representation of binary values has changed: it is no longer
4923 directly tied to the server's internal representation.
4927 There is a new <quote>extended query</> sub-protocol, which adds the frontend
4928 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
4929 backend message types ParseComplete, BindComplete, PortalSuspended,
4930 ParameterDescription, NoData, and CloseComplete. Existing clients do not
4931 have to concern themselves with this sub-protocol, but making use of it
4932 might allow improvements in performance or functionality.
4936 <command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
4937 is a well-defined way to recover from errors during <command>COPY</command>. The special
4938 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4939 during <command>COPY OUT</command>.
4940 (It is still recognized as a terminator during <command>COPY IN</command>, but its use is
4941 deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
4942 The CopyInResponse and CopyOutResponse messages include fields indicating
4943 the number of columns and the format of each column.
4947 The layout of FunctionCall and FunctionCallResponse messages has changed.
4948 FunctionCall can now support passing NULL arguments to functions. It also
4949 can handle passing parameters and retrieving results in either text or
4950 binary format. There is no longer any reason to consider FunctionCall a
4951 potential security hole, since it does not offer direct access to internal
4952 server data representations.
4956 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4957 startup for all parameters it considers interesting to the client library.
4958 Subsequently, a ParameterStatus message is sent whenever the active value
4959 changes for any of these parameters.
4963 The RowDescription ('<literal>T</>') message carries new table OID and column
4964 number fields for each column of the described row. It also shows the format
4965 code for each column.
4969 The CursorResponse ('<literal>P</>') message is no longer generated by
4974 The NotificationResponse ('<literal>A</>') message has an additional string
4975 field, which can carry a <quote>payload</> string passed
4976 from the <command>NOTIFY</command> event sender.
4980 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4981 string parameter; this has been removed.