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, semantic analysis, and (optionally) planning of a textual query
130 A prepared statement is not necessarily ready to execute, because it might
131 lack specific values for <firstterm>parameters</>. A portal represents
132 a ready-to-execute or already-partially-executed statement, with any
133 missing parameter values filled in. (For <command>SELECT</> statements,
134 a portal is equivalent to an open cursor, but we choose to use a different
135 term since cursors don't handle non-<command>SELECT</> statements.)
139 The overall execution cycle consists of a <firstterm>parse</> step,
140 which creates a prepared statement from a textual query string; a
141 <firstterm>bind</> step, which creates a portal given a prepared
142 statement and values for any needed parameters; and an
143 <firstterm>execute</> step that runs a portal's query. In the case of
144 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
145 the execute step can be told to fetch only
146 a limited number of rows, so that multiple execute steps might be needed
147 to complete the operation.
151 The backend can keep track of multiple prepared statements and portals
152 (but note that these exist only within a session, and are never shared
153 across sessions). Existing prepared statements and portals are
154 referenced by names assigned when they were created. In addition,
155 an <quote>unnamed</> prepared statement and portal exist. Although these
156 behave largely the same as named objects, operations on them are optimized
157 for the case of executing a query only once and then discarding it,
158 whereas operations on named objects are optimized on the expectation
163 <sect2 id="protocol-format-codes">
164 <title>Formats and Format Codes</title>
167 Data of a particular data type might be transmitted in any of several
168 different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
169 the only supported formats are <quote>text</> and <quote>binary</>,
170 but the protocol makes provision for future extensions. The desired
171 format for any value is specified by a <firstterm>format code</>.
172 Clients can specify a format code for each transmitted parameter value
173 and for each column of a query result. Text has format code zero,
174 binary has format code one, and all other format codes are reserved
175 for future definition.
179 The text representation of values is whatever strings are produced
180 and accepted by the input/output conversion functions for the
181 particular data type. In the transmitted representation, there is
182 no trailing null character; the frontend must add one to received
183 values if it wants to process them as C strings.
184 (The text format does not allow embedded nulls, by the way.)
188 Binary representations for integers use network byte order (most
189 significant byte first). For other data types consult the documentation
190 or source code to learn about the binary representation. Keep in mind
191 that binary representations for complex data types might change across
192 server versions; the text format is usually the more portable choice.
197 <sect1 id="protocol-flow">
198 <title>Message Flow</title>
201 This section describes the message flow and the semantics of each
202 message type. (Details of the exact representation of each message
203 appear in <xref linkend="protocol-message-formats">.) There are
204 several different sub-protocols depending on the state of the
205 connection: start-up, query, function call,
206 <command>COPY</command>, and termination. There are also special
207 provisions for asynchronous operations (including notification
208 responses and command cancellation), which can occur at any time
209 after the start-up phase.
213 <title>Start-Up</title>
216 To begin a session, a frontend opens a connection to the server and sends
217 a startup message. This message includes the names of the user and of the
218 database the user wants to connect to; it also identifies the particular
219 protocol version to be used. (Optionally, the startup message can include
220 additional settings for run-time parameters.)
221 The server then uses this information and
222 the contents of its configuration files (such as
223 <filename>pg_hba.conf</filename>) to determine
224 whether the connection is provisionally acceptable, and what additional
225 authentication is required (if any).
229 The server then sends an appropriate authentication request message,
230 to which the frontend must reply with an appropriate authentication
231 response message (such as a password).
232 For all authentication methods except GSSAPI and SSPI, there is at most
233 one request and one response. In some methods, no response
234 at all is needed from the frontend, and so no authentication request
235 occurs. For GSSAPI and SSPI, multiple exchanges of packets may be needed
236 to complete the authentication.
240 The authentication cycle ends with the server either rejecting the
241 connection attempt (ErrorResponse), or sending AuthenticationOk.
245 The possible messages from the server in this phase are:
249 <term>ErrorResponse</term>
252 The connection attempt has been rejected.
253 The server then immediately closes the connection.
259 <term>AuthenticationOk</term>
262 The authentication exchange is successfully completed.
268 <term>AuthenticationKerberosV5</term>
271 The frontend must now take part in a Kerberos V5
272 authentication dialog (not described here, part of the
273 Kerberos specification) with the server. If this is
274 successful, the server responds with an AuthenticationOk,
275 otherwise it responds with an ErrorResponse.
281 <term>AuthenticationCleartextPassword</term>
284 The frontend must now send a PasswordMessage containing the
285 password in clear-text form. If
286 this is the correct password, the server responds with an
287 AuthenticationOk, otherwise it responds with an ErrorResponse.
293 <term>AuthenticationMD5Password</term>
296 The frontend must now send a PasswordMessage containing the
297 password encrypted via MD5, using the 4-character salt
298 specified in the AuthenticationMD5Password message. If
299 this is the correct password, the server responds with an
300 AuthenticationOk, otherwise it responds with an ErrorResponse.
306 <term>AuthenticationSCMCredential</term>
309 This response is only possible for local Unix-domain connections
310 on platforms that support SCM credential messages. The frontend
311 must issue an SCM credential message and then send a single data
312 byte. (The contents of the data byte are uninteresting; it's
313 only used to ensure that the server waits long enough to receive
314 the credential message.) If the credential is acceptable,
315 the server responds with an
316 AuthenticationOk, otherwise it responds with an ErrorResponse.
322 <term>AuthenticationGSS</term>
325 The frontend must now initiate a GSSAPI negotiation. The frontend
326 will send a PasswordMessage with the first part of the GSSAPI
327 data stream in response to this. If further messages are needed,
328 the server will respond with AuthenticationGSSContinue.
334 <term>AuthenticationSSPI</term>
337 The frontend must now initiate a SSPI negotiation. The frontend
338 will send a PasswordMessage with the first part of the SSPI
339 data stream in response to this. If further messages are needed,
340 the server will respond with AuthenticationGSSContinue.
346 <term>AuthenticationGSSContinue</term>
349 This message contains the response data from the previous step
350 of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI
351 or a previous AuthenticationGSSContinue). If the GSSAPI
352 or SSPI data in this message
353 indicates more data is needed to complete the authentication,
354 the frontend must send that data as another PasswordMessage. If
355 GSSAPI or SSPI authentication is completed by this message, the server
356 will next send AuthenticationOk to indicate successful authentication
357 or ErrorResponse to indicate failure.
366 If the frontend does not support the authentication method
367 requested by the server, then it should immediately close the
372 After having received AuthenticationOk, the frontend must wait
373 for further messages from the server. In this phase a backend process
374 is being started, and the frontend is just an interested bystander.
375 It is still possible for the startup attempt
376 to fail (ErrorResponse), but in the normal case the backend will send
377 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
381 During this phase the backend will attempt to apply any additional
382 run-time parameter settings that were given in the startup message.
383 If successful, these values become session defaults. An error causes
384 ErrorResponse and exit.
388 The possible messages from the backend in this phase are:
392 <term>BackendKeyData</term>
395 This message provides secret-key data that the frontend must
396 save if it wants to be able to issue cancel requests later.
397 The frontend should not respond to this message, but should
398 continue listening for a ReadyForQuery message.
404 <term>ParameterStatus</term>
407 This message informs the frontend about the current (initial)
408 setting of backend parameters, such as <xref
409 linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
410 The frontend can ignore this message, or record the settings
411 for its future use; see <xref linkend="protocol-async"> for
412 more details. The frontend should not respond to this
413 message, but should continue listening for a ReadyForQuery
420 <term>ReadyForQuery</term>
423 Start-up is completed. The frontend can now issue commands.
429 <term>ErrorResponse</term>
432 Start-up failed. The connection is closed after sending this
439 <term>NoticeResponse</term>
442 A warning message has been issued. The frontend should
443 display the message but continue listening for ReadyForQuery
452 The ReadyForQuery message is the same one that the backend will
453 issue after each command cycle. Depending on the coding needs of
454 the frontend, it is reasonable to consider ReadyForQuery as
455 starting a command cycle, or to consider ReadyForQuery as ending the
456 start-up phase and each subsequent command cycle.
461 <title>Simple Query</title>
464 A simple query cycle is initiated by the frontend sending a Query message
465 to the backend. The message includes an SQL command (or commands)
466 expressed as a text string.
467 The backend then sends one or more response
468 messages depending on the contents of the query command string,
469 and finally a ReadyForQuery response message. ReadyForQuery
470 informs the frontend that it can safely send a new command.
471 (It is not actually necessary for the frontend to wait for
472 ReadyForQuery before issuing another command, but the frontend must
473 then take responsibility for figuring out what happens if the earlier
474 command fails and already-issued later commands succeed.)
478 The possible response messages from the backend are:
482 <term>CommandComplete</term>
485 An SQL command completed normally.
491 <term>CopyInResponse</term>
494 The backend is ready to copy data from the frontend to a
495 table; see <xref linkend="protocol-copy">.
501 <term>CopyOutResponse</term>
504 The backend is ready to copy data from a table to the
505 frontend; see <xref linkend="protocol-copy">.
511 <term>RowDescription</term>
514 Indicates that rows are about to be returned in response to
515 a <command>SELECT</command>, <command>FETCH</command>, etc query.
516 The contents of this message describe the column layout of the rows.
517 This will be followed by a DataRow message for each row being returned
527 One of the set of rows returned by
528 a <command>SELECT</command>, <command>FETCH</command>, etc query.
534 <term>EmptyQueryResponse</term>
537 An empty query string was recognized.
543 <term>ErrorResponse</term>
546 An error has occurred.
552 <term>ReadyForQuery</term>
555 Processing of the query string is complete. A separate
556 message is sent to indicate this because the query string might
557 contain multiple SQL commands. (CommandComplete marks the
558 end of processing one SQL command, not the whole string.)
559 ReadyForQuery will always be sent, whether processing
560 terminates successfully or with an error.
566 <term>NoticeResponse</term>
569 A warning message has been issued in relation to the query.
570 Notices are in addition to other responses, i.e., the backend
571 will continue processing the command.
580 The response to a <command>SELECT</> query (or other queries that
581 return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
582 normally consists of RowDescription, zero or more
583 DataRow messages, and then CommandComplete.
584 <command>COPY</> to or from the frontend invokes special protocol
585 as described in <xref linkend="protocol-copy">.
586 All other query types normally produce only
587 a CommandComplete message.
591 Since a query string could contain several queries (separated by
592 semicolons), there might be several such response sequences before the
593 backend finishes processing the query string. ReadyForQuery is issued
594 when the entire string has been processed and the backend is ready to
595 accept a new query string.
599 If a completely empty (no contents other than whitespace) query string
600 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
604 In the event of an error, ErrorResponse is issued followed by
605 ReadyForQuery. All further processing of the query string is aborted by
606 ErrorResponse (even if more queries remained in it). Note that this
607 might occur partway through the sequence of messages generated by an
612 In simple Query mode, the format of retrieved values is always text,
613 except when the given command is a <command>FETCH</> from a cursor
614 declared with the <literal>BINARY</> option. In that case, the
615 retrieved values are in binary format. The format codes given in
616 the RowDescription message tell which format is being used.
620 A frontend must be prepared to accept ErrorResponse and
621 NoticeResponse messages whenever it is expecting any other type of
622 message. See also <xref linkend="protocol-async"> concerning messages
623 that the backend might generate due to outside events.
627 Recommended practice is to code frontends in a state-machine style
628 that will accept any message type at any time that it could make sense,
629 rather than wiring in assumptions about the exact sequence of messages.
633 <sect2 id="protocol-flow-ext-query">
634 <title>Extended Query</title>
637 The extended query protocol breaks down the above-described simple
638 query protocol into multiple steps. The results of preparatory
639 steps can be re-used multiple times for improved efficiency.
640 Furthermore, additional features are available, such as the possibility
641 of supplying data values as separate parameters instead of having to
642 insert them directly into a query string.
646 In the extended protocol, the frontend first sends a Parse message,
647 which contains a textual query string, optionally some information
648 about data types of parameter placeholders, and the
649 name of a destination prepared-statement object (an empty string
650 selects the unnamed prepared statement). The response is
651 either ParseComplete or ErrorResponse. Parameter data types can be
652 specified by OID; if not given, the parser attempts to infer the
653 data types in the same way as it would do for untyped literal string
659 A parameter data type can be left unspecified by setting it to zero,
660 or by making the array of parameter type OIDs shorter than the
661 number of parameter symbols (<literal>$</><replaceable>n</>)
662 used in the query string. Another special case is that a parameter's
663 type can be specified as <type>void</> (that is, the OID of the
664 <type>void</> pseudotype). This is meant to allow parameter symbols
665 to be used for function parameters that are actually OUT parameters.
666 Ordinarily there is no context in which a <type>void</> parameter
667 could be used, but if such a parameter symbol appears in a function's
668 parameter list, it is effectively ignored. For example, a function
669 call such as <literal>foo($1,$2,$3,$4)</> could match a function with
670 two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
671 are specified as having type <type>void</>.
677 The query string contained in a Parse message cannot include more
678 than one SQL statement; else a syntax error is reported. This
679 restriction does not exist in the simple-query protocol, but it
680 does exist in the extended protocol, because allowing prepared
681 statements or portals to contain multiple commands would complicate
687 If successfully created, a named prepared-statement object lasts till
688 the end of the current session, unless explicitly destroyed. An unnamed
689 prepared statement lasts only until the next Parse statement specifying
690 the unnamed statement as destination is issued. (Note that a simple
691 Query message also destroys the unnamed statement.) Named prepared
692 statements must be explicitly closed before they can be redefined by
693 a Parse message, but this is not required for the unnamed statement.
694 Named prepared statements can also be created and accessed at the SQL
695 command level, using <command>PREPARE</> and <command>EXECUTE</>.
699 Once a prepared statement exists, it can be readied for execution using a
700 Bind message. The Bind message gives the name of the source prepared
701 statement (empty string denotes the unnamed prepared statement), the name
702 of the destination portal (empty string denotes the unnamed portal), and
703 the values to use for any parameter placeholders present in the prepared
705 supplied parameter set must match those needed by the prepared statement.
706 (If you declared any <type>void</> parameters in the Parse message,
707 pass NULL values for them in the Bind message.)
708 Bind also specifies the format to use for any data returned
709 by the query; the format can be specified overall, or per-column.
710 The response is either BindComplete or ErrorResponse.
715 The choice between text and binary output is determined by the format
716 codes given in Bind, regardless of the SQL command involved. The
717 <literal>BINARY</> attribute in cursor declarations is irrelevant when
718 using extended query protocol.
723 Query planning for named prepared-statement objects occurs when the Parse
724 message is processed. If a query will be repeatedly executed with
725 different parameters, it might be beneficial to send a single Parse message
726 containing a parameterized query, followed by multiple Bind
727 and Execute messages. This will avoid replanning the query on each
732 The unnamed prepared statement is likewise planned during Parse processing
733 if the Parse message defines no parameters. But if there are parameters,
734 query planning occurs every time Bind parameters are supplied. This allows the
735 planner to make use of the actual values of the parameters provided by
736 each Bind message, rather than use generic estimates.
741 Query plans generated from a parameterized query might be less
742 efficient than query plans generated from an equivalent query with actual
743 parameter values substituted. The query planner cannot make decisions
744 based on actual parameter values (for example, index selectivity) when
745 planning a parameterized query assigned to a named prepared-statement
746 object. This possible penalty is avoided when using the unnamed
747 statement, since it is not planned until actual parameter values are
748 available. The cost is that planning must occur afresh for each Bind,
749 even if the query stays the same.
754 If successfully created, a named portal object lasts till the end of the
755 current transaction, unless explicitly destroyed. An unnamed portal is
756 destroyed at the end of the transaction, or as soon as the next Bind
757 statement specifying the unnamed portal as destination is issued. (Note
758 that a simple Query message also destroys the unnamed portal.) Named
759 portals must be explicitly closed before they can be redefined by a Bind
760 message, but this is not required for the unnamed portal.
761 Named portals can also be created and accessed at the SQL
762 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
766 Once a portal exists, it can be executed using an Execute message.
767 The Execute message specifies the portal name (empty string denotes the
769 a maximum result-row count (zero meaning <quote>fetch all rows</>).
770 The result-row count is only meaningful for portals
771 containing commands that return row sets; in other cases the command is
772 always executed to completion, and the row count is ignored.
774 responses to Execute are the same as those described above for queries
775 issued via simple query protocol, except that Execute doesn't cause
776 ReadyForQuery or RowDescription to be issued.
780 If Execute terminates before completing the execution of a portal
781 (due to reaching a nonzero result-row count), it will send a
782 PortalSuspended message; the appearance of this message tells the frontend
783 that another Execute should be issued against the same portal to
784 complete the operation. The CommandComplete message indicating
785 completion of the source SQL command is not sent until
786 the portal's execution is completed. Therefore, an Execute phase is
787 always terminated by the appearance of exactly one of these messages:
788 CommandComplete, EmptyQueryResponse (if the portal was created from
789 an empty query string), ErrorResponse, or PortalSuspended.
793 At completion of each series of extended-query messages, the frontend
794 should issue a Sync message. This parameterless message causes the
795 backend to close the current transaction if it's not inside a
796 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
797 meaning to commit if no error, or roll back if error). Then a
798 ReadyForQuery response is issued. The purpose of Sync is to provide
799 a resynchronization point for error recovery. When an error is detected
800 while processing any extended-query message, the backend issues
801 ErrorResponse, then reads and discards messages until a Sync is reached,
802 then issues ReadyForQuery and returns to normal message processing.
803 (But note that no skipping occurs if an error is detected
804 <emphasis>while</> processing Sync — this ensures that there is one
805 and only one ReadyForQuery sent for each Sync.)
810 Sync does not cause a transaction block opened with <command>BEGIN</>
811 to be closed. It is possible to detect this situation since the
812 ReadyForQuery message includes transaction status information.
817 In addition to these fundamental, required operations, there are several
818 optional operations that can be used with extended-query protocol.
822 The Describe message (portal variant) specifies the name of an existing
823 portal (or an empty string for the unnamed portal). The response is a
824 RowDescription message describing the rows that will be returned by
825 executing the portal; or a NoData message if the portal does not contain a
826 query that will return rows; or ErrorResponse if there is no such portal.
830 The Describe message (statement variant) specifies the name of an existing
831 prepared statement (or an empty string for the unnamed prepared
832 statement). The response is a ParameterDescription message describing the
833 parameters needed by the statement, followed by a RowDescription message
834 describing the rows that will be returned when the statement is eventually
835 executed (or a NoData message if the statement will not return rows).
836 ErrorResponse is issued if there is no such prepared statement. Note that
837 since Bind has not yet been issued, the formats to be used for returned
838 columns are not yet known to the backend; the format code fields in the
839 RowDescription message will be zeroes in this case.
844 In most scenarios the frontend should issue one or the other variant
845 of Describe before issuing Execute, to ensure that it knows how to
846 interpret the results it will get back.
851 The Close message closes an existing prepared statement or portal
852 and releases resources. It is not an error to issue Close against
853 a nonexistent statement or portal name. The response is normally
854 CloseComplete, but could be ErrorResponse if some difficulty is
855 encountered while releasing resources. Note that closing a prepared
856 statement implicitly closes any open portals that were constructed
861 The Flush message does not cause any specific output to be generated,
862 but forces the backend to deliver any data pending in its output
863 buffers. A Flush must be sent after any extended-query command except
864 Sync, if the frontend wishes to examine the results of that command before
865 issuing more commands. Without Flush, messages returned by the backend
866 will be combined into the minimum possible number of packets to minimize
872 The simple Query message is approximately equivalent to the series Parse,
873 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
874 statement and portal objects and no parameters. One difference is that
875 it will accept multiple SQL statements in the query string, automatically
876 performing the bind/describe/execute sequence for each one in succession.
877 Another difference is that it will not return ParseComplete, BindComplete,
878 CloseComplete, or NoData messages.
884 <title>Function Call</title>
887 The Function Call sub-protocol allows the client to request a direct
888 call of any function that exists in the database's
889 <structname>pg_proc</structname> system catalog. The client must have
890 execute permission for the function.
895 The Function Call sub-protocol is a legacy feature that is probably best
896 avoided in new code. Similar results can be accomplished by setting up
897 a prepared statement that does <literal>SELECT function($1, ...)</>.
898 The Function Call cycle can then be replaced with Bind/Execute.
903 A Function Call cycle is initiated by the frontend sending a
904 FunctionCall message to the backend. The backend then sends one
905 or more response messages depending on the results of the function
906 call, and finally a ReadyForQuery response message. ReadyForQuery
907 informs the frontend that it can safely send a new query or
912 The possible response messages from the backend are:
916 <term>ErrorResponse</term>
919 An error has occurred.
925 <term>FunctionCallResponse</term>
928 The function call was completed and returned the result given
930 (Note that the Function Call protocol can only handle a single
931 scalar result, not a row type or set of results.)
937 <term>ReadyForQuery</term>
940 Processing of the function call is complete. ReadyForQuery
941 will always be sent, whether processing terminates
942 successfully or with an error.
948 <term>NoticeResponse</term>
951 A warning message has been issued in relation to the function
952 call. Notices are in addition to other responses, i.e., the
953 backend will continue processing the command.
961 <sect2 id="protocol-copy">
962 <title>COPY Operations</title>
965 The <command>COPY</> command allows high-speed bulk data transfer
966 to or from the server. Copy-in and copy-out operations each switch
967 the connection into a distinct sub-protocol, which lasts until the
968 operation is completed.
972 Copy-in mode (data transfer to the server) is initiated when the
973 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
974 sends a CopyInResponse message to the frontend. The frontend should
975 then send zero or more CopyData messages, forming a stream of input
976 data. (The message boundaries are not required to have anything to do
977 with row boundaries, although that is often a reasonable choice.)
978 The frontend can terminate the copy-in mode by sending either a CopyDone
979 message (allowing successful termination) or a CopyFail message (which
980 will cause the <command>COPY</> SQL statement to fail with an
981 error). The backend then reverts to the command-processing mode it was
982 in before the <command>COPY</> started, which will be either simple or
983 extended query protocol. It will next send either CommandComplete
984 (if successful) or ErrorResponse (if not).
988 In the event of a backend-detected error during copy-in mode (including
989 receipt of a CopyFail message), the backend will issue an ErrorResponse
990 message. If the <command>COPY</> command was issued via an extended-query
991 message, the backend will now discard frontend messages until a Sync
992 message is received, then it will issue ReadyForQuery and return to normal
993 processing. If the <command>COPY</> command was issued in a simple
994 Query message, the rest of that message is discarded and ReadyForQuery
995 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
996 messages issued by the frontend will simply be dropped.
1000 The backend will ignore Flush and Sync messages received during copy-in
1001 mode. Receipt of any other non-copy message type constitutes an error
1002 that will abort the copy-in state as described above. (The exception for
1003 Flush and Sync is for the convenience of client libraries that always
1004 send Flush or Sync after an Execute message, without checking whether
1005 the command to be executed is a <command>COPY FROM STDIN</>.)
1009 Copy-out mode (data transfer from the server) is initiated when the
1010 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
1011 sends a CopyOutResponse message to the frontend, followed by
1012 zero or more CopyData messages (always one per row), followed by CopyDone.
1013 The backend then reverts to the command-processing mode it was
1014 in before the <command>COPY</> started, and sends CommandComplete.
1015 The frontend cannot abort the transfer (except by closing the connection
1016 or issuing a Cancel request),
1017 but it can discard unwanted CopyData and CopyDone messages.
1021 In the event of a backend-detected error during copy-out mode,
1022 the backend will issue an ErrorResponse message and revert to normal
1023 processing. The frontend should treat receipt of ErrorResponse as
1024 terminating the copy-out mode.
1028 It is possible for NoticeResponse and ParameterStatus messages to be
1029 interspersed between CopyData messages; frontends must handle these cases,
1030 and should be prepared for other asynchronous message types as well (see
1031 <xref linkend="protocol-async">). Otherwise, any message type other than
1032 CopyData or CopyDone may be treated as terminating copy-out mode.
1036 There is another Copy-related mode called Copy-both, which allows
1037 high-speed bulk data transfer to <emphasis>and</> from the server.
1038 Copy-both mode is initiated when a backend in walsender mode
1039 executes a <command>START_REPLICATION</command> statement. The
1040 backend sends a CopyBothResponse message to the frontend. Both
1041 the backend and the frontend may then send CopyData messages
1042 until the connection is terminated. See <xref
1043 linkend="protocol-replication">.
1047 The CopyInResponse, CopyOutResponse and CopyBothResponse messages
1048 include fields that inform the frontend of the number of columns
1049 per row and the format codes being used for each column. (As of
1050 the present implementation, all columns in a given <command>COPY</>
1051 operation will use the same format, but the message design does not
1057 <sect2 id="protocol-async">
1058 <title>Asynchronous Operations</title>
1061 There are several cases in which the backend will send messages that
1062 are not specifically prompted by the frontend's command stream.
1063 Frontends must be prepared to deal with these messages at any time,
1064 even when not engaged in a query.
1065 At minimum, one should check for these cases before beginning to
1066 read a query response.
1070 It is possible for NoticeResponse messages to be generated due to
1071 outside activity; for example, if the database administrator commands
1072 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1073 indicating this fact before closing the connection. Accordingly,
1074 frontends should always be prepared to accept and display NoticeResponse
1075 messages, even when the connection is nominally idle.
1079 ParameterStatus messages will be generated whenever the active
1080 value changes for any of the parameters the backend believes the
1081 frontend should know about. Most commonly this occurs in response
1082 to a <command>SET</> SQL command executed by the frontend, and
1083 this case is effectively synchronous — but it is also possible
1084 for parameter status changes to occur because the administrator
1085 changed a configuration file and then sent the
1086 <systemitem>SIGHUP</systemitem> signal to the server. Also,
1087 if a <command>SET</command> command is rolled back, an appropriate
1088 ParameterStatus message will be generated to report the current
1093 At present there is a hard-wired set of parameters for which
1094 ParameterStatus will be generated: they are
1095 <literal>server_version</>,
1096 <literal>server_encoding</>,
1097 <literal>client_encoding</>,
1098 <literal>application_name</>,
1099 <literal>is_superuser</>,
1100 <literal>session_authorization</>,
1101 <literal>DateStyle</>,
1102 <literal>IntervalStyle</>,
1103 <literal>TimeZone</>,
1104 <literal>integer_datetimes</>, and
1105 <literal>standard_conforming_strings</>.
1106 (<literal>server_encoding</>, <literal>TimeZone</>, and
1107 <literal>integer_datetimes</> were not reported by releases before 8.0;
1108 <literal>standard_conforming_strings</> was not reported by releases
1110 <literal>IntervalStyle</> was not reported by releases before 8.4;
1111 <literal>application_name</> was not reported by releases before 9.0.)
1113 <literal>server_version</>,
1114 <literal>server_encoding</> and
1115 <literal>integer_datetimes</>
1116 are pseudo-parameters that cannot change after startup.
1117 This set might change in the future, or even become configurable.
1118 Accordingly, a frontend should simply ignore ParameterStatus for
1119 parameters that it does not understand or care about.
1123 If a frontend issues a <command>LISTEN</command> command, then the
1124 backend will send a NotificationResponse message (not to be
1125 confused with NoticeResponse!) whenever a
1126 <command>NOTIFY</command> command is executed for the same
1132 At present, NotificationResponse can only be sent outside a
1133 transaction, and thus it will not occur in the middle of a
1134 command-response series, though it might occur just before ReadyForQuery.
1135 It is unwise to design frontend logic that assumes that, however.
1136 Good practice is to be able to accept NotificationResponse at any
1137 point in the protocol.
1143 <title>Cancelling Requests in Progress</title>
1146 During the processing of a query, the frontend might request
1147 cancellation of the query. The cancel request is not sent
1148 directly on the open connection to the backend for reasons of
1149 implementation efficiency: we don't want to have the backend
1150 constantly checking for new input from the frontend during query
1151 processing. Cancel requests should be relatively infrequent, so
1152 we make them slightly cumbersome in order to avoid a penalty in
1157 To issue a cancel request, the frontend opens a new connection to
1158 the server and sends a CancelRequest message, rather than the
1159 StartupMessage message that would ordinarily be sent across a new
1160 connection. The server will process this request and then close
1161 the connection. For security reasons, no direct reply is made to
1162 the cancel request message.
1166 A CancelRequest message will be ignored unless it contains the
1167 same key data (PID and secret key) passed to the frontend during
1168 connection start-up. If the request matches the PID and secret
1169 key for a currently executing backend, the processing of the
1170 current query is aborted. (In the existing implementation, this is
1171 done by sending a special signal to the backend process that is
1172 processing the query.)
1176 The cancellation signal might or might not have any effect — for
1177 example, if it arrives after the backend has finished processing
1178 the query, then it will have no effect. If the cancellation is
1179 effective, it results in the current command being terminated
1180 early with an error message.
1184 The upshot of all this is that for reasons of both security and
1185 efficiency, the frontend has no direct way to tell whether a
1186 cancel request has succeeded. It must continue to wait for the
1187 backend to respond to the query. Issuing a cancel simply improves
1188 the odds that the current query will finish soon, and improves the
1189 odds that it will fail with an error message instead of
1194 Since the cancel request is sent across a new connection to the
1195 server and not across the regular frontend/backend communication
1196 link, it is possible for the cancel request to be issued by any
1197 process, not just the frontend whose query is to be canceled.
1198 This might provide additional flexibility when building
1199 multiple-process applications. It also introduces a security
1200 risk, in that unauthorized persons might try to cancel queries.
1201 The security risk is addressed by requiring a dynamically
1202 generated secret key to be supplied in cancel requests.
1207 <title>Termination</title>
1210 The normal, graceful termination procedure is that the frontend
1211 sends a Terminate message and immediately closes the connection.
1212 On receipt of this message, the backend closes the connection and
1217 In rare cases (such as an administrator-commanded database shutdown)
1218 the backend might disconnect without any frontend request to do so.
1219 In such cases the backend will attempt to send an error or notice message
1220 giving the reason for the disconnection before it closes the connection.
1224 Other termination scenarios arise from various failure cases, such as core
1225 dump at one end or the other, loss of the communications link, loss of
1226 message-boundary synchronization, etc. If either frontend or backend sees
1227 an unexpected closure of the connection, it should clean
1228 up and terminate. The frontend has the option of launching a new backend
1229 by recontacting the server if it doesn't want to terminate itself.
1230 Closing the connection is also advisable if an unrecognizable message type
1231 is received, since this probably indicates loss of message-boundary sync.
1235 For either normal or abnormal termination, any open transaction is
1236 rolled back, not committed. One should note however that if a
1237 frontend disconnects while a non-<command>SELECT</command> query
1238 is being processed, the backend will probably finish the query
1239 before noticing the disconnection. If the query is outside any
1240 transaction block (<command>BEGIN</> ... <command>COMMIT</>
1241 sequence) then its results might be committed before the
1242 disconnection is recognized.
1247 <title><acronym>SSL</acronym> Session Encryption</title>
1250 If <productname>PostgreSQL</> was built with
1251 <acronym>SSL</acronym> support, frontend/backend communications
1252 can be encrypted using <acronym>SSL</acronym>. This provides
1253 communication security in environments where attackers might be
1254 able to capture the session traffic. For more information on
1255 encrypting <productname>PostgreSQL</productname> sessions with
1256 <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1260 To initiate an <acronym>SSL</acronym>-encrypted connection, the
1261 frontend initially sends an SSLRequest message rather than a
1262 StartupMessage. The server then responds with a single byte
1263 containing <literal>S</> or <literal>N</>, indicating that it is
1264 willing or unwilling to perform <acronym>SSL</acronym>,
1265 respectively. The frontend might close the connection at this point
1266 if it is dissatisfied with the response. To continue after
1267 <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1268 (not described here, part of the <acronym>SSL</acronym>
1269 specification) with the server. If this is successful, continue
1270 with sending the usual StartupMessage. In this case the
1271 StartupMessage and all subsequent data will be
1272 <acronym>SSL</acronym>-encrypted. To continue after
1273 <literal>N</>, send the usual StartupMessage and proceed without
1278 The frontend should also be prepared to handle an ErrorMessage
1279 response to SSLRequest from the server. This would only occur if
1280 the server predates the addition of <acronym>SSL</acronym> support
1281 to <productname>PostgreSQL</>. In this case the connection must
1282 be closed, but the frontend might choose to open a fresh connection
1283 and proceed without requesting <acronym>SSL</acronym>.
1287 An initial SSLRequest can also be used in a connection that is being
1288 opened to send a CancelRequest message.
1292 While the protocol itself does not provide a way for the server to
1293 force <acronym>SSL</acronym> encryption, the administrator can
1294 configure the server to reject unencrypted sessions as a byproduct
1295 of authentication checking.
1300 <sect1 id="protocol-replication">
1301 <title>Streaming Replication Protocol</title>
1304 To initiate streaming replication, the frontend sends the
1305 <literal>replication</> parameter in the startup message. This tells the
1306 backend to go into walsender mode, wherein a small set of replication commands
1307 can be issued instead of SQL statements. Only the simple query protocol can be
1308 used in walsender mode.
1310 The commands accepted in walsender mode are:
1314 <term>IDENTIFY_SYSTEM</term>
1317 Requests the server to identify itself. Server replies with a result
1318 set of a single row, containing two fields:
1329 The unique system identifier identifying the cluster. This
1330 can be used to check that the base backup used to initialize the
1331 standby came from the same cluster.
1342 Current TimelineID. Also useful to check that the standby is
1343 consistent with the master.
1353 <term>START_REPLICATION <replaceable>XXX</>/<replaceable>XXX</></term>
1356 Instructs server to start streaming WAL, starting at
1357 WAL position <replaceable>XXX</>/<replaceable>XXX</>.
1358 The server can reply with an error, e.g. if the requested section of WAL
1359 has already been recycled. On success, server responds with a
1360 CopyBothResponse message, and then starts to stream WAL to the frontend.
1361 WAL will continue to be streamed until the connection is broken;
1362 no further commands will be accepted.
1366 WAL data is sent as a series of CopyData messages. (This allows
1367 other information to be intermixed; in particular the server can send
1368 an ErrorResponse message if it encounters a failure after beginning
1369 to stream.) The payload in each CopyData message follows this format:
1387 Identifies the message as WAL data.
1397 The starting point of the WAL data in this message, given in
1408 The current end of WAL on the server, given in
1419 The server's system clock at the time of transmission,
1420 given in TimestampTz format.
1426 Byte<replaceable>n</replaceable>
1430 A section of the WAL data stream.
1441 A single WAL record is never split across two CopyData messages.
1442 When a WAL record crosses a WAL page boundary, and is therefore
1443 already split using continuation records, it can be split at the page
1444 boundary. In other words, the first main WAL record and its
1445 continuation records can be sent in different CopyData messages.
1448 Note that all fields within the WAL data and the above-described header
1449 will be in the sending server's native format. Endianness, and the
1450 format for the timestamp, are unpredictable unless the receiver has
1451 verified that the sender's system identifier matches its own
1452 <filename>pg_control</> contents.
1455 If the WAL sender process is terminated normally (during postmaster
1456 shutdown), it will send a CommandComplete message before exiting.
1457 This might not happen during an abnormal shutdown, of course.
1463 <term>BASE_BACKUP <replaceable>options</><literal>;</><replaceable>label</></term>
1466 Instructs the server to start streaming a base backup.
1467 The system will automatically be put in backup mode with the label
1468 specified in <replaceable>label</> before the backup is started, and
1469 taken out of it when the backup is complete. The following options
1473 <term><literal>PROGRESS</></term>
1476 Request information required to generate a progress report. This will
1477 send back an approximate size in the header of each tablespace, which
1478 can be used to calculate how far along the stream is done. This is
1479 calculated by enumerating all the file sizes once before the transfer
1480 is even started, and may as such have a negative impact on the
1481 performance - in particular it may take longer before the first data
1482 is streamed. Since the database files can change during the backup,
1483 the size is only approximate and may both grow and shrink between
1484 the time of approximation and the sending of the actual files.
1491 When the backup is started, the server will first send a header in
1492 ordinary result set format, followed by one or more CopyResponse
1493 results, one for PGDATA and one for each additional tablespace other
1494 than <literal>pg_default</> and <literal>pg_global</>. The data in
1495 the CopyResponse results will be a tar format (using ustar00
1496 extensions) dump of the tablespace contents.
1499 The header is an ordinary resultset with one row for each tablespace.
1500 The fields in this row are:
1506 The oid of the tablespace, or <literal>NULL</> if it's the base
1512 <term>spclocation</term>
1515 The full path of the tablespace directory, or <literal>NULL</>
1516 if it's the base directory.
1524 The approximate size of the tablespace, if progress report has
1525 been requested; otherwise it's <literal>NULL</>.
1532 The tar archive for the data directory and each tablespace will contain
1533 all files in the directories, regardless of whether they are
1534 <productname>PostgreSQL</> files or other files added to the same
1535 directory. The only excluded files are:
1536 <itemizedlist spacing="compact" mark="bullet">
1539 <filename>postmaster.pid</>
1544 <filename>pg_xlog</> (including subdirectories)
1548 Owner, group and file mode are set if the underlying filesystem on
1549 the server supports it.
1559 <sect1 id="protocol-message-types">
1560 <title>Message Data Types</title>
1563 This section describes the base data types used in messages.
1569 Int<replaceable>n</replaceable>(<replaceable>i</replaceable>)
1573 An <replaceable>n</replaceable>-bit integer in network byte
1574 order (most significant byte first).
1575 If <replaceable>i</replaceable> is specified it
1576 is the exact value that will appear, otherwise the value
1577 is variable. Eg. Int16, Int32(42).
1584 Int<replaceable>n</replaceable>[<replaceable>k</replaceable>]
1588 An array of <replaceable>k</replaceable>
1589 <replaceable>n</replaceable>-bit integers, each in network
1590 byte order. The array length <replaceable>k</replaceable>
1591 is always determined by an earlier field in the message.
1599 String(<replaceable>s</replaceable>)
1603 A null-terminated string (C-style string). There is no
1604 specific length limitation on strings.
1605 If <replaceable>s</replaceable> is specified it is the exact
1606 value that will appear, otherwise the value is variable.
1607 Eg. String, String("user").
1612 <emphasis>There is no predefined limit</emphasis> on the length of a string
1613 that can be returned by the backend. Good coding strategy for a frontend
1614 is to use an expandable buffer so that anything that fits in memory can be
1615 accepted. If that's not feasible, read the full string and discard trailing
1616 characters that don't fit into your fixed-size buffer.
1624 Byte<replaceable>n</replaceable>(<replaceable>c</replaceable>)
1628 Exactly <replaceable>n</replaceable> bytes. If the field
1629 width <replaceable>n</replaceable> is not a constant, it is
1630 always determinable from an earlier field in the message.
1631 If <replaceable>c</replaceable> is specified it is the exact
1632 value. Eg. Byte2, Byte1('\n').
1641 <sect1 id="protocol-message-formats">
1642 <title>Message Formats</title>
1645 This section describes the detailed format of each message. Each is marked to
1646 indicate that it can be sent by a frontend (F), a backend (B), or both
1648 Notice that although each message includes a byte count at the beginning,
1649 the message format is defined so that the message end can be found without
1650 reference to the byte count. This aids validity checking. (The CopyData
1651 message is an exception, because it forms part of a data stream; the contents
1652 of any individual CopyData message cannot be interpretable on their own.)
1660 AuthenticationOk (B)
1672 Identifies the message as an authentication request.
1682 Length of message contents in bytes, including self.
1692 Specifies that the authentication was successful.
1705 AuthenticationKerberosV5 (B)
1717 Identifies the message as an authentication request.
1727 Length of message contents in bytes, including self.
1737 Specifies that Kerberos V5 authentication is required.
1749 AuthenticationCleartextPassword (B)
1761 Identifies the message as an authentication request.
1771 Length of message contents in bytes, including self.
1781 Specifies that a clear-text password is required.
1793 AuthenticationMD5Password (B)
1805 Identifies the message as an authentication request.
1815 Length of message contents in bytes, including self.
1825 Specifies that an MD5-encrypted password is required.
1835 The salt to use when encrypting the password.
1848 AuthenticationSCMCredential (B)
1860 Identifies the message as an authentication request.
1870 Length of message contents in bytes, including self.
1880 Specifies that an SCM credentials message is required.
1893 AuthenticationGSS (B)
1905 Identifies the message as an authentication request.
1915 Length of message contents in bytes, including self.
1925 Specifies that GSSAPI authentication is required.
1938 AuthenticationSSPI (B)
1950 Identifies the message as an authentication request.
1960 Length of message contents in bytes, including self.
1970 Specifies that SSPI authentication is required.
1981 AuthenticationGSSContinue (B)
1993 Identifies the message as an authentication request.
2003 Length of message contents in bytes, including self.
2013 Specifies that this message contains GSSAPI or SSPI data.
2019 Byte<replaceable>n</replaceable>
2023 GSSAPI or SSPI authentication data.
2048 Identifies the message as cancellation key data.
2049 The frontend must save these values if it wishes to be
2050 able to issue CancelRequest messages later.
2060 Length of message contents in bytes, including self.
2070 The process ID of this backend.
2080 The secret key of this backend.
2105 Identifies the message as a Bind command.
2115 Length of message contents in bytes, including self.
2125 The name of the destination portal
2126 (an empty string selects the unnamed portal).
2136 The name of the source prepared statement
2137 (an empty string selects the unnamed prepared statement).
2147 The number of parameter format codes that follow
2148 (denoted <replaceable>C</> below).
2149 This can be zero to indicate that there are no parameters
2150 or that the parameters all use the default format (text);
2151 or one, in which case the specified format code is applied
2152 to all parameters; or it can equal the actual number of
2159 Int16[<replaceable>C</>]
2163 The parameter format codes. Each must presently be
2164 zero (text) or one (binary).
2174 The number of parameter values that follow (possibly zero).
2175 This must match the number of parameters needed by the query.
2180 Next, the following pair of fields appear for each parameter:
2188 The length of the parameter value, in bytes (this count
2189 does not include itself). Can be zero.
2190 As a special case, -1 indicates a NULL parameter value.
2191 No value bytes follow in the NULL case.
2197 Byte<replaceable>n</replaceable>
2201 The value of the parameter, in the format indicated by the
2202 associated format code.
2203 <replaceable>n</replaceable> is the above length.
2208 After the last parameter, the following fields appear:
2216 The number of result-column format codes that follow
2217 (denoted <replaceable>R</> below).
2218 This can be zero to indicate that there are no result columns
2219 or that the result columns should all use the default format
2221 or one, in which case the specified format code is applied
2222 to all result columns (if any); or it can equal the actual
2223 number of result columns of the query.
2229 Int16[<replaceable>R</>]
2233 The result-column format codes. Each must presently be
2234 zero (text) or one (binary).
2258 Identifies the message as a Bind-complete indicator.
2268 Length of message contents in bytes, including self.
2293 Length of message contents in bytes, including self.
2303 The cancel request code. The value is chosen to contain
2304 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
2305 least 16 significant bits. (To avoid confusion, this code
2306 must not be the same as any protocol version number.)
2316 The process ID of the target backend.
2326 The secret key for the target backend.
2351 Identifies the message as a Close command.
2361 Length of message contents in bytes, including self.
2371 '<literal>S</>' to close a prepared statement; or
2372 '<literal>P</>' to close a portal.
2382 The name of the prepared statement or portal to close
2383 (an empty string selects the unnamed prepared statement
2408 Identifies the message as a Close-complete indicator.
2418 Length of message contents in bytes, including self.
2443 Identifies the message as a command-completed response.
2453 Length of message contents in bytes, including self.
2463 The command tag. This is usually a single
2464 word that identifies which SQL command was completed.
2468 For an <command>INSERT</command> command, the tag is
2469 <literal>INSERT <replaceable>oid</replaceable>
2470 <replaceable>rows</replaceable></literal>, where
2471 <replaceable>rows</replaceable> is the number of rows
2472 inserted. <replaceable>oid</replaceable> is the object ID
2473 of the inserted row if <replaceable>rows</replaceable> is 1
2474 and the target table has OIDs;
2475 otherwise <replaceable>oid</replaceable> is 0.
2479 For a <command>DELETE</command> command, the tag is
2480 <literal>DELETE <replaceable>rows</replaceable></literal> where
2481 <replaceable>rows</replaceable> is the number of rows deleted.
2485 For an <command>UPDATE</command> command, the tag is
2486 <literal>UPDATE <replaceable>rows</replaceable></literal> where
2487 <replaceable>rows</replaceable> is the number of rows updated.
2491 For a <command>SELECT</command> or <command>CREATE TABLE AS</command>
2492 command, the tag is <literal>SELECT <replaceable>rows</replaceable></literal>
2493 where <replaceable>rows</replaceable> is the number of rows retrieved.
2497 For a <command>MOVE</command> command, the tag is
2498 <literal>MOVE <replaceable>rows</replaceable></literal> where
2499 <replaceable>rows</replaceable> is the number of rows the
2500 cursor's position has been changed by.
2504 For a <command>FETCH</command> command, the tag is
2505 <literal>FETCH <replaceable>rows</replaceable></literal> where
2506 <replaceable>rows</replaceable> is the number of rows that
2507 have been retrieved from the cursor.
2511 For a <command>COPY</command> command, the tag is
2512 <literal>COPY <replaceable>rows</replaceable></literal> where
2513 <replaceable>rows</replaceable> is the number of rows copied.
2514 (Note: the row count appears only in
2515 <productname>PostgreSQL</productname> 8.2 and later.)
2529 CopyData (F & B)
2540 Identifies the message as <command>COPY</command> data.
2550 Length of message contents in bytes, including self.
2556 Byte<replaceable>n</replaceable>
2560 Data that forms part of a <command>COPY</command> data stream. Messages sent
2561 from the backend will always correspond to single data rows,
2562 but messages sent by frontends might divide the data stream
2575 CopyDone (F & B)
2587 Identifies the message as a <command>COPY</command>-complete indicator.
2597 Length of message contents in bytes, including self.
2622 Identifies the message as a <command>COPY</command>-failure indicator.
2632 Length of message contents in bytes, including self.
2642 An error message to report as the cause of failure.
2667 Identifies the message as a Start Copy In response.
2668 The frontend must now send copy-in data (if not
2669 prepared to do so, send a CopyFail message).
2679 Length of message contents in bytes, including self.
2689 0 indicates the overall <command>COPY</command> format is textual (rows
2690 separated by newlines, columns separated by separator
2692 1 indicates the overall copy format is binary (similar
2694 See <xref linkend="sql-copy">
2695 for more information.
2705 The number of columns in the data to be copied
2706 (denoted <replaceable>N</> below).
2712 Int16[<replaceable>N</>]
2716 The format codes to be used for each column.
2717 Each must presently be zero (text) or one (binary).
2718 All must be zero if the overall copy format is textual.
2743 Identifies the message as a Start Copy Out response.
2744 This message will be followed by copy-out data.
2754 Length of message contents in bytes, including self.
2764 0 indicates the overall <command>COPY</command> format
2765 is textual (rows separated by newlines, columns
2766 separated by separator characters, etc). 1 indicates
2767 the overall copy format is binary (similar to DataRow
2768 format). See <xref linkend="sql-copy"> for more information.
2778 The number of columns in the data to be copied
2779 (denoted <replaceable>N</> below).
2785 Int16[<replaceable>N</>]
2789 The format codes to be used for each column.
2790 Each must presently be zero (text) or one (binary).
2791 All must be zero if the overall copy format is textual.
2804 CopyBothResponse (B)
2816 Identifies the message as a Start Copy Both response.
2817 This message is used only for Streaming Replication.
2827 Length of message contents in bytes, including self.
2837 0 indicates the overall <command>COPY</command> format
2838 is textual (rows separated by newlines, columns
2839 separated by separator characters, etc). 1 indicates
2840 the overall copy format is binary (similar to DataRow
2841 format). See <xref linkend="sql-copy"> for more information.
2851 The number of columns in the data to be copied
2852 (denoted <replaceable>N</> below).
2858 Int16[<replaceable>N</>]
2862 The format codes to be used for each column.
2863 Each must presently be zero (text) or one (binary).
2864 All must be zero if the overall copy format is textual.
2888 Identifies the message as a data row.
2898 Length of message contents in bytes, including self.
2908 The number of column values that follow (possibly zero).
2913 Next, the following pair of fields appear for each column:
2921 The length of the column value, in bytes (this count
2922 does not include itself). Can be zero.
2923 As a special case, -1 indicates a NULL column value.
2924 No value bytes follow in the NULL case.
2930 Byte<replaceable>n</replaceable>
2934 The value of the column, in the format indicated by the
2935 associated format code.
2936 <replaceable>n</replaceable> is the above length.
2961 Identifies the message as a Describe command.
2971 Length of message contents in bytes, including self.
2981 '<literal>S</>' to describe a prepared statement; or
2982 '<literal>P</>' to describe a portal.
2992 The name of the prepared statement or portal to describe
2993 (an empty string selects the unnamed prepared statement
3006 EmptyQueryResponse (B)
3018 Identifies the message as a response to an empty query string.
3019 (This substitutes for CommandComplete.)
3029 Length of message contents in bytes, including self.
3054 Identifies the message as an error.
3064 Length of message contents in bytes, including self.
3069 The message body consists of one or more identified fields,
3070 followed by a zero byte as a terminator. Fields can appear in
3071 any order. For each field there is the following:
3079 A code identifying the field type; if zero, this is
3080 the message terminator and no string follows.
3081 The presently defined field types are listed in
3082 <xref linkend="protocol-error-fields">.
3083 Since more field types might be added in future,
3084 frontends should silently ignore fields of unrecognized
3120 Identifies the message as an Execute command.
3130 Length of message contents in bytes, including self.
3140 The name of the portal to execute
3141 (an empty string selects the unnamed portal).
3151 Maximum number of rows to return, if portal contains
3152 a query that returns rows (ignored otherwise). Zero
3153 denotes <quote>no limit</>.
3177 Identifies the message as a Flush command.
3187 Length of message contents in bytes, including self.
3212 Identifies the message as a function call.
3222 Length of message contents in bytes, including self.
3232 Specifies the object ID of the function to call.
3242 The number of argument format codes that follow
3243 (denoted <replaceable>C</> below).
3244 This can be zero to indicate that there are no arguments
3245 or that the arguments all use the default format (text);
3246 or one, in which case the specified format code is applied
3247 to all arguments; or it can equal the actual number of
3254 Int16[<replaceable>C</>]
3258 The argument format codes. Each must presently be
3259 zero (text) or one (binary).
3269 Specifies the number of arguments being supplied to the
3275 Next, the following pair of fields appear for each argument:
3283 The length of the argument value, in bytes (this count
3284 does not include itself). Can be zero.
3285 As a special case, -1 indicates a NULL argument value.
3286 No value bytes follow in the NULL case.
3292 Byte<replaceable>n</replaceable>
3296 The value of the argument, in the format indicated by the
3297 associated format code.
3298 <replaceable>n</replaceable> is the above length.
3303 After the last argument, the following field appears:
3311 The format code for the function result. Must presently be
3312 zero (text) or one (binary).
3325 FunctionCallResponse (B)
3337 Identifies the message as a function call result.
3347 Length of message contents in bytes, including self.
3357 The length of the function result value, in bytes (this count
3358 does not include itself). Can be zero.
3359 As a special case, -1 indicates a NULL function result.
3360 No value bytes follow in the NULL case.
3366 Byte<replaceable>n</replaceable>
3370 The value of the function result, in the format indicated by
3371 the associated format code.
3372 <replaceable>n</replaceable> is the above length.
3397 Identifies the message as a no-data indicator.
3407 Length of message contents in bytes, including self.
3432 Identifies the message as a notice.
3442 Length of message contents in bytes, including self.
3447 The message body consists of one or more identified fields,
3448 followed by a zero byte as a terminator. Fields can appear in
3449 any order. For each field there is the following:
3457 A code identifying the field type; if zero, this is
3458 the message terminator and no string follows.
3459 The presently defined field types are listed in
3460 <xref linkend="protocol-error-fields">.
3461 Since more field types might be added in future,
3462 frontends should silently ignore fields of unrecognized
3486 NotificationResponse (B)
3498 Identifies the message as a notification response.
3508 Length of message contents in bytes, including self.
3518 The process ID of the notifying backend process.
3528 The name of the channel that the notify has been raised on.
3538 The <quote>payload</> string passed from the notifying process.
3551 ParameterDescription (B)
3563 Identifies the message as a parameter description.
3573 Length of message contents in bytes, including self.
3583 The number of parameters used by the statement
3589 Then, for each parameter, there is the following:
3597 Specifies the object ID of the parameter data type.
3621 Identifies the message as a run-time parameter status report.
3631 Length of message contents in bytes, including self.
3641 The name of the run-time parameter being reported.
3651 The current value of the parameter.
3675 Identifies the message as a Parse command.
3685 Length of message contents in bytes, including self.
3695 The name of the destination prepared statement
3696 (an empty string selects the unnamed prepared statement).
3706 The query string to be parsed.
3716 The number of parameter data types specified
3717 (can be zero). Note that this is not an indication of
3718 the number of parameters that might appear in the
3719 query string, only the number that the frontend wants to
3720 prespecify types for.
3725 Then, for each parameter, there is the following:
3733 Specifies the object ID of the parameter data type.
3734 Placing a zero here is equivalent to leaving the type
3759 Identifies the message as a Parse-complete indicator.
3769 Length of message contents in bytes, including self.
3794 Identifies the message as a password response. Note that
3795 this is also used for GSSAPI and SSPI response messages
3796 (which is really a design error, since the contained data
3797 is not a null-terminated string in that case, but can be
3798 arbitrary binary data).
3808 Length of message contents in bytes, including self.
3818 The password (encrypted, if requested).
3842 Identifies the message as a portal-suspended indicator.
3843 Note this only appears if an Execute message's row-count limit
3854 Length of message contents in bytes, including self.
3879 Identifies the message as a simple query.
3889 Length of message contents in bytes, including self.
3899 The query string itself.
3924 Identifies the message type. ReadyForQuery is sent
3925 whenever the backend is ready for a new query cycle.
3935 Length of message contents in bytes, including self.
3945 Current backend transaction status indicator.
3946 Possible values are '<literal>I</>' if idle (not in
3947 a transaction block); '<literal>T</>' if in a transaction
3948 block; or '<literal>E</>' if in a failed transaction
3949 block (queries will be rejected until block is ended).
3974 Identifies the message as a row description.
3984 Length of message contents in bytes, including self.
3994 Specifies the number of fields in a row (can be zero).
3999 Then, for each field, there is the following:
4017 If the field can be identified as a column of a specific
4018 table, the object ID of the table; otherwise zero.
4028 If the field can be identified as a column of a specific
4029 table, the attribute number of the column; otherwise zero.
4039 The object ID of the field's data type.
4049 The data type size (see <varname>pg_type.typlen</>).
4050 Note that negative values denote variable-width types.
4060 The type modifier (see <varname>pg_attribute.atttypmod</>).
4061 The meaning of the modifier is type-specific.
4071 The format code being used for the field. Currently will
4072 be zero (text) or one (binary). In a RowDescription
4073 returned from the statement variant of Describe, the
4074 format code is not yet known and will always be zero.
4099 Length of message contents in bytes, including self.
4109 The <acronym>SSL</acronym> request code. The value is chosen to contain
4110 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
4111 least 16 significant bits. (To avoid confusion, this code
4112 must not be the same as any protocol version number.)
4137 Length of message contents in bytes, including self.
4147 The protocol version number. The most significant 16 bits are
4148 the major version number (3 for the protocol described here).
4149 The least significant 16 bits are the minor version number
4150 (0 for the protocol described here).
4155 The protocol version number is followed by one or more pairs of
4156 parameter name and value strings. A zero byte is required as a
4157 terminator after the last name/value pair.
4158 Parameters can appear in any
4159 order. <literal>user</> is required, others are optional.
4160 Each parameter is specified as:
4168 The parameter name. Currently recognized names are:
4177 The database user name to connect as. Required;
4178 there is no default.
4184 <literal>database</>
4188 The database to connect to. Defaults to the user name.
4198 Command-line arguments for the backend. (This is
4199 deprecated in favor of setting individual run-time
4206 In addition to the above, any run-time parameter that can be
4207 set at backend start time might be listed. Such settings
4208 will be applied during backend start (after parsing the
4209 command-line options if any). The values will act as
4220 The parameter value.
4245 Identifies the message as a Sync command.
4255 Length of message contents in bytes, including self.
4280 Identifies the message as a termination.
4290 Length of message contents in bytes, including self.
4306 <sect1 id="protocol-error-fields">
4307 <title>Error and Notice Message Fields</title>
4310 This section describes the fields that can appear in ErrorResponse and
4311 NoticeResponse messages. Each field type has a single-byte identification
4312 token. Note that any given field type should appear at most once per
4324 Severity: the field contents are
4325 <literal>ERROR</>, <literal>FATAL</>, or
4326 <literal>PANIC</> (in an error message), or
4327 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
4328 <literal>INFO</>, or <literal>LOG</> (in a notice message),
4329 or a localized translation of one of these. Always present.
4340 Code: the SQLSTATE code for the error (see <xref
4341 linkend="errcodes-appendix">). Not localizable. Always present.
4352 Message: the primary human-readable error message.
4353 This should be accurate but terse (typically one line).
4365 Detail: an optional secondary error message carrying more
4366 detail about the problem. Might run to multiple lines.
4377 Hint: an optional suggestion what to do about the problem.
4378 This is intended to differ from Detail in that it offers advice
4379 (potentially inappropriate) rather than hard facts.
4380 Might run to multiple lines.
4391 Position: the field value is a decimal ASCII integer, indicating
4392 an error cursor position as an index into the original query string.
4393 The first character has index 1, and positions are measured in
4394 characters not bytes.
4405 Internal position: this is defined the same as the <literal>P</>
4406 field, but it is used when the cursor position refers to an internally
4407 generated command rather than the one submitted by the client.
4408 The <literal>q</> field will always appear when this field appears.
4419 Internal query: the text of a failed internally-generated command.
4420 This could be, for example, a SQL query issued by a PL/pgSQL function.
4431 Where: an indication of the context in which the error occurred.
4432 Presently this includes a call stack traceback of active
4433 procedural language functions and internally-generated queries.
4434 The trace is one entry per line, most recent first.
4445 File: the file name of the source-code location where the error
4457 Line: the line number of the source-code location where the error
4469 Routine: the name of the source-code routine reporting the error.
4477 The client is responsible for formatting displayed information to meet its
4478 needs; in particular it should break long lines as needed. Newline characters
4479 appearing in the error message fields should be treated as paragraph breaks,
4485 <sect1 id="protocol-changes">
4486 <title>Summary of Changes since Protocol 2.0</title>
4489 This section provides a quick checklist of changes, for the benefit of
4490 developers trying to update existing client libraries to protocol 3.0.
4494 The initial startup packet uses a flexible list-of-strings format
4495 instead of a fixed format. Notice that session default values for run-time
4496 parameters can now be specified directly in the startup packet. (Actually,
4497 you could do that before using the <literal>options</> field, but given the
4498 limited width of <literal>options</> and the lack of any way to quote
4499 whitespace in the values, it wasn't a very safe technique.)
4503 All messages now have a length count immediately following the message type
4504 byte (except for startup packets, which have no type byte). Also note that
4505 PasswordMessage now has a type byte.
4509 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
4510 messages now contain multiple fields, from which the client code can
4511 assemble an error message of the desired level of verbosity. Note that
4512 individual fields will typically not end with a newline, whereas the single
4513 string sent in the older protocol always did.
4517 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
4522 The distinction between BinaryRow and DataRow message types is gone; the
4523 single DataRow message type serves for returning data in all formats.
4524 Note that the layout of DataRow has changed to make it easier to parse.
4525 Also, the representation of binary values has changed: it is no longer
4526 directly tied to the server's internal representation.
4530 There is a new <quote>extended query</> sub-protocol, which adds the frontend
4531 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
4532 backend message types ParseComplete, BindComplete, PortalSuspended,
4533 ParameterDescription, NoData, and CloseComplete. Existing clients do not
4534 have to concern themselves with this sub-protocol, but making use of it
4535 might allow improvements in performance or functionality.
4539 <command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
4540 is a well-defined way to recover from errors during <command>COPY</command>. The special
4541 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4542 during <command>COPY OUT</command>.
4543 (It is still recognized as a terminator during <command>COPY IN</command>, but its use is
4544 deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
4545 The CopyInResponse and CopyOutResponse messages include fields indicating
4546 the number of columns and the format of each column.
4550 The layout of FunctionCall and FunctionCallResponse messages has changed.
4551 FunctionCall can now support passing NULL arguments to functions. It also
4552 can handle passing parameters and retrieving results in either text or
4553 binary format. There is no longer any reason to consider FunctionCall a
4554 potential security hole, since it does not offer direct access to internal
4555 server data representations.
4559 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4560 startup for all parameters it considers interesting to the client library.
4561 Subsequently, a ParameterStatus message is sent whenever the active value
4562 changes for any of these parameters.
4566 The RowDescription ('<literal>T</>') message carries new table OID and column
4567 number fields for each column of the described row. It also shows the format
4568 code for each column.
4572 The CursorResponse ('<literal>P</>') message is no longer generated by
4577 The NotificationResponse ('<literal>A</>') message has an additional string
4578 field, which can carry a <quote>payload</> string passed
4579 from the <command>NOTIFY</command> event sender.
4583 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4584 string parameter; this has been removed.