1 <!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.69 2007/12/03 13:40:11 mha Exp $ -->
3 <chapter id="protocol">
4 <title>Frontend/Backend Protocol</title>
7 <productname>PostgreSQL</productname> uses a message-based protocol
8 for communication between frontends and backends (clients and servers).
9 The protocol is supported over <acronym>TCP/IP</acronym> and also over
10 Unix-domain sockets. Port number 5432 has been registered with IANA as
11 the customary TCP port number for servers supporting this protocol, but
12 in practice any non-privileged port number can be used.
16 This document describes version 3.0 of the protocol, implemented in
17 <productname>PostgreSQL</productname> 7.4 and later. For descriptions
18 of the earlier protocol versions, see previous releases of the
19 <productname>PostgreSQL</productname> documentation. A single server
20 can support multiple protocol versions. The initial
21 startup-request message tells the server which protocol version the
22 client is attempting to use, and then the server follows that protocol
27 Higher level features built on this protocol (for example, how
28 <application>libpq</application> passes certain environment
29 variables when the connection is established) are covered elsewhere.
33 In order to serve multiple clients efficiently, the server launches
34 a new <quote>backend</> process for each client.
35 In the current implementation, a new child
36 process is created immediately after an incoming connection is detected.
37 This is transparent to the protocol, however. For purposes of the
38 protocol, the terms <quote>backend</> and <quote>server</> are
39 interchangeable; likewise <quote>frontend</> and <quote>client</>
43 <sect1 id="protocol-overview">
44 <title>Overview</title>
47 The protocol has separate phases for startup and normal operation.
48 In the startup phase, the frontend opens a connection to the server
49 and authenticates itself to the satisfaction of the server. (This might
50 involve a single message, or multiple messages depending on the
51 authentication method being used.) If all goes well, the server then sends
52 status information to the frontend, and finally enters normal operation.
53 Except for the initial startup-request message, this part of the
54 protocol is driven by the server.
58 During normal operation, the frontend sends queries and
59 other commands to the backend, and the backend sends back query results
60 and other responses. There are a few cases (such as <command>NOTIFY</>)
62 backend will send unsolicited messages, but for the most part this portion
63 of a session is driven by frontend requests.
67 Termination of the session is normally by frontend choice, but can be
68 forced by the backend in certain cases. In any case, when the backend
69 closes the connection, it will roll back any open (incomplete) transaction
74 Within normal operation, SQL commands can be executed through either of
75 two sub-protocols. In the <quote>simple query</> protocol, the frontend
76 just sends a textual query string, which is parsed and immediately
77 executed by the backend. In the <quote>extended query</> protocol,
78 processing of queries is separated into multiple steps: parsing,
79 binding of parameter values, and execution. This offers flexibility
80 and performance benefits, at the cost of extra complexity.
84 Normal operation has additional sub-protocols for special operations
85 such as <command>COPY</>.
88 <sect2 id="protocol-message-concepts">
89 <title>Messaging Overview</title>
92 All communication is through a stream of messages. The first byte of a
93 message identifies the message type, and the next four bytes specify the
94 length of the rest of the message (this length count includes itself, but
95 not the message-type byte). The remaining contents of the message are
96 determined by the message type. For historical reasons, the very first
97 message sent by the client (the startup message) has no initial
102 To avoid losing synchronization with the message stream, both servers and
103 clients typically read an entire message into a buffer (using the byte
104 count) before attempting to process its contents. This allows easy
105 recovery if an error is detected while processing the contents. In
106 extreme situations (such as not having enough memory to buffer the
107 message), the receiver can use the byte count to determine how much
108 input to skip before it resumes reading messages.
112 Conversely, both servers and clients must take care never to send an
113 incomplete message. This is commonly done by marshaling the entire message
114 in a buffer before beginning to send it. If a communications failure
115 occurs partway through sending or receiving a message, the only sensible
116 response is to abandon the connection, since there is little hope of
117 recovering message-boundary synchronization.
121 <sect2 id="protocol-query-concepts">
122 <title>Extended Query Overview</title>
125 In the extended-query protocol, execution of SQL commands is divided
126 into multiple steps. The state retained between steps is represented
127 by two types of objects: <firstterm>prepared statements</> and
128 <firstterm>portals</>. A prepared statement represents the result of
129 parsing, semantic analysis, and (optionally) planning of a textual query
131 A prepared statement is not necessarily ready to execute, because it might
132 lack specific values for <firstterm>parameters</>. A portal represents
133 a ready-to-execute or already-partially-executed statement, with any
134 missing parameter values filled in. (For <command>SELECT</> statements,
135 a portal is equivalent to an open cursor, but we choose to use a different
136 term since cursors don't handle non-<command>SELECT</> statements.)
140 The overall execution cycle consists of a <firstterm>parse</> step,
141 which creates a prepared statement from a textual query string; a
142 <firstterm>bind</> step, which creates a portal given a prepared
143 statement and values for any needed parameters; and an
144 <firstterm>execute</> step that runs a portal's query. In the case of
145 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
146 the execute step can be told to fetch only
147 a limited number of rows, so that multiple execute steps might be needed
148 to complete the operation.
152 The backend can keep track of multiple prepared statements and portals
153 (but note that these exist only within a session, and are never shared
154 across sessions). Existing prepared statements and portals are
155 referenced by names assigned when they were created. In addition,
156 an <quote>unnamed</> prepared statement and portal exist. Although these
157 behave largely the same as named objects, operations on them are optimized
158 for the case of executing a query only once and then discarding it,
159 whereas operations on named objects are optimized on the expectation
164 <sect2 id="protocol-format-codes">
165 <title>Formats and Format Codes</title>
168 Data of a particular data type might be transmitted in any of several
169 different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
170 the only supported formats are <quote>text</> and <quote>binary</>,
171 but the protocol makes provision for future extensions. The desired
172 format for any value is specified by a <firstterm>format code</>.
173 Clients can specify a format code for each transmitted parameter value
174 and for each column of a query result. Text has format code zero,
175 binary has format code one, and all other format codes are reserved
176 for future definition.
180 The text representation of values is whatever strings are produced
181 and accepted by the input/output conversion functions for the
182 particular data type. In the transmitted representation, there is
183 no trailing null character; the frontend must add one to received
184 values if it wants to process them as C strings.
185 (The text format does not allow embedded nulls, by the way.)
189 Binary representations for integers use network byte order (most
190 significant byte first). For other data types consult the documentation
191 or source code to learn about the binary representation. Keep in mind
192 that binary representations for complex data types might change across
193 server versions; the text format is usually the more portable choice.
198 <sect1 id="protocol-flow">
199 <title>Message Flow</title>
202 This section describes the message flow and the semantics of each
203 message type. (Details of the exact representation of each message
204 appear in <xref linkend="protocol-message-formats">.) There are
205 several different sub-protocols depending on the state of the
206 connection: start-up, query, function call,
207 <command>COPY</command>, and termination. There are also special
208 provisions for asynchronous operations (including notification
209 responses and command cancellation), which can occur at any time
210 after the start-up phase.
214 <title>Start-Up</title>
217 To begin a session, a frontend opens a connection to the server and sends
218 a startup message. This message includes the names of the user and of the
219 database the user wants to connect to; it also identifies the particular
220 protocol version to be used. (Optionally, the startup message can include
221 additional settings for run-time parameters.)
222 The server then uses this information and
223 the contents of its configuration files (such as
224 <filename>pg_hba.conf</filename>) to determine
225 whether the connection is provisionally acceptable, and what additional
226 authentication is required (if any).
230 The server then sends an appropriate authentication request message,
231 to which the frontend must reply with an appropriate authentication
232 response message (such as a password).
233 For all authentication methods except GSSAPI and SSPI, there is at most
234 one request and one response. In some methods, no response
235 at all is needed from the frontend, and so no authentication request
236 occurs. For GSSAPI and SSPI, multiple iterations of packets may be needed to
237 complete the authentication.
241 The authentication cycle ends with the server either rejecting the
242 connection attempt (ErrorResponse), or sending AuthenticationOk.
246 The possible messages from the server in this phase are:
250 <term>ErrorResponse</term>
253 The connection attempt has been rejected.
254 The server then immediately closes the connection.
260 <term>AuthenticationOk</term>
263 The authentication exchange is successfully completed.
269 <term>AuthenticationKerberosV5</term>
272 The frontend must now take part in a Kerberos V5
273 authentication dialog (not described here, part of the
274 Kerberos specification) with the server. If this is
275 successful, the server responds with an AuthenticationOk,
276 otherwise it responds with an ErrorResponse.
282 <term>AuthenticationCleartextPassword</term>
285 The frontend must now send a PasswordMessage containing the
286 password in clear-text form. If
287 this is the correct password, the server responds with an
288 AuthenticationOk, otherwise it responds with an ErrorResponse.
294 <term>AuthenticationCryptPassword</term>
297 The frontend must now send a PasswordMessage containing the
298 password encrypted via crypt(3), using the 2-character salt
299 specified in the AuthenticationCryptPassword message. If
300 this is the correct password, the server responds with an
301 AuthenticationOk, otherwise it responds with an ErrorResponse.
307 <term>AuthenticationMD5Password</term>
310 The frontend must now send a PasswordMessage containing the
311 password encrypted via MD5, using the 4-character salt
312 specified in the AuthenticationMD5Password message. If
313 this is the correct password, the server responds with an
314 AuthenticationOk, otherwise it responds with an ErrorResponse.
320 <term>AuthenticationSCMCredential</term>
323 This response is only possible for local Unix-domain connections
324 on platforms that support SCM credential messages. The frontend
325 must issue an SCM credential message and then send a single data
326 byte. (The contents of the data byte are uninteresting; it's
327 only used to ensure that the server waits long enough to receive
328 the credential message.) If the credential is acceptable,
329 the server responds with an
330 AuthenticationOk, otherwise it responds with an ErrorResponse.
336 <term>AuthenticationGSS</term>
339 The frontend must now initiate a GSSAPI negotiation. The frontend
340 will send a PasswordMessage with the first part of the GSSAPI
341 data stream in response to this. If further messages are needed,
342 the server will respond with AuthenticationGSSContinue.
348 <term>AuthenticationSSPI</term>
351 The frontend must now initiate a SSPI negotiation. The frontend
352 will send a PasswordMessage with the first part of the SSPI
353 data stream in response to this. If further messages are needed,
354 the server will respond with AuthenticationGSSContinue.
360 <term>AuthenticationGSSContinue</term>
363 This message contains the response data from the previous step
364 of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI
365 or a previous AuthenticationGSSContinue). If the GSSAPI
366 or SSPI data in this message
367 indicates more data is needed to complete the authentication,
368 the frontend must send this data as another PasswordMessage. If
369 GSSAPI authentication is completed by this message, the server
370 will also send AuthenticationOk to indicate successful authentication
371 or ErrorResponse to indicate failure.
380 If the frontend does not support the authentication method
381 requested by the server, then it should immediately close the
386 After having received AuthenticationOk, the frontend must wait
387 for further messages from the server. In this phase a backend process
388 is being started, and the frontend is just an interested bystander.
389 It is still possible for the startup attempt
390 to fail (ErrorResponse), but in the normal case the backend will send
391 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
395 During this phase the backend will attempt to apply any additional
396 run-time parameter settings that were given in the startup message.
397 If successful, these values become session defaults. An error causes
398 ErrorResponse and exit.
402 The possible messages from the backend in this phase are:
406 <term>BackendKeyData</term>
409 This message provides secret-key data that the frontend must
410 save if it wants to be able to issue cancel requests later.
411 The frontend should not respond to this message, but should
412 continue listening for a ReadyForQuery message.
418 <term>ParameterStatus</term>
421 This message informs the frontend about the current (initial)
422 setting of backend parameters, such as <xref
423 linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
424 The frontend can ignore this message, or record the settings
425 for its future use; see <xref linkend="protocol-async"> for
426 more details. The frontend should not respond to this
427 message, but should continue listening for a ReadyForQuery
434 <term>ReadyForQuery</term>
437 Start-up is completed. The frontend can now issue commands.
443 <term>ErrorResponse</term>
446 Start-up failed. The connection is closed after sending this
453 <term>NoticeResponse</term>
456 A warning message has been issued. The frontend should
457 display the message but continue listening for ReadyForQuery
466 The ReadyForQuery message is the same one that the backend will
467 issue after each command cycle. Depending on the coding needs of
468 the frontend, it is reasonable to consider ReadyForQuery as
469 starting a command cycle, or to consider ReadyForQuery as ending the
470 start-up phase and each subsequent command cycle.
475 <title>Simple Query</title>
478 A simple query cycle is initiated by the frontend sending a Query message
479 to the backend. The message includes an SQL command (or commands)
480 expressed as a text string.
481 The backend then sends one or more response
482 messages depending on the contents of the query command string,
483 and finally a ReadyForQuery response message. ReadyForQuery
484 informs the frontend that it can safely send a new command.
485 (It is not actually necessary for the frontend to wait for
486 ReadyForQuery before issuing another command, but the frontend must
487 then take responsibility for figuring out what happens if the earlier
488 command fails and already-issued later commands succeed.)
492 The possible response messages from the backend are:
496 <term>CommandComplete</term>
499 An SQL command completed normally.
505 <term>CopyInResponse</term>
508 The backend is ready to copy data from the frontend to a
509 table; see <xref linkend="protocol-copy">.
515 <term>CopyOutResponse</term>
518 The backend is ready to copy data from a table to the
519 frontend; see <xref linkend="protocol-copy">.
525 <term>RowDescription</term>
528 Indicates that rows are about to be returned in response to
529 a <command>SELECT</command>, <command>FETCH</command>, etc query.
530 The contents of this message describe the column layout of the rows.
531 This will be followed by a DataRow message for each row being returned
541 One of the set of rows returned by
542 a <command>SELECT</command>, <command>FETCH</command>, etc query.
548 <term>EmptyQueryResponse</term>
551 An empty query string was recognized.
557 <term>ErrorResponse</term>
560 An error has occurred.
566 <term>ReadyForQuery</term>
569 Processing of the query string is complete. A separate
570 message is sent to indicate this because the query string might
571 contain multiple SQL commands. (CommandComplete marks the
572 end of processing one SQL command, not the whole string.)
573 ReadyForQuery will always be sent, whether processing
574 terminates successfully or with an error.
580 <term>NoticeResponse</term>
583 A warning message has been issued in relation to the query.
584 Notices are in addition to other responses, i.e., the backend
585 will continue processing the command.
594 The response to a <command>SELECT</> query (or other queries that
595 return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
596 normally consists of RowDescription, zero or more
597 DataRow messages, and then CommandComplete.
598 <command>COPY</> to or from the frontend invokes special protocol
599 as described in <xref linkend="protocol-copy">.
600 All other query types normally produce only
601 a CommandComplete message.
605 Since a query string could contain several queries (separated by
606 semicolons), there might be several such response sequences before the
607 backend finishes processing the query string. ReadyForQuery is issued
608 when the entire string has been processed and the backend is ready to
609 accept a new query string.
613 If a completely empty (no contents other than whitespace) query string
614 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
618 In the event of an error, ErrorResponse is issued followed by
619 ReadyForQuery. All further processing of the query string is aborted by
620 ErrorResponse (even if more queries remained in it). Note that this
621 might occur partway through the sequence of messages generated by an
626 In simple Query mode, the format of retrieved values is always text,
627 except when the given command is a <command>FETCH</> from a cursor
628 declared with the <literal>BINARY</> option. In that case, the
629 retrieved values are in binary format. The format codes given in
630 the RowDescription message tell which format is being used.
634 A frontend must be prepared to accept ErrorResponse and
635 NoticeResponse messages whenever it is expecting any other type of
636 message. See also <xref linkend="protocol-async"> concerning messages
637 that the backend might generate due to outside events.
641 Recommended practice is to code frontends in a state-machine style
642 that will accept any message type at any time that it could make sense,
643 rather than wiring in assumptions about the exact sequence of messages.
647 <sect2 id="protocol-flow-ext-query">
648 <title>Extended Query</title>
651 The extended query protocol breaks down the above-described simple
652 query protocol into multiple steps. The results of preparatory
653 steps can be re-used multiple times for improved efficiency.
654 Furthermore, additional features are available, such as the possibility
655 of supplying data values as separate parameters instead of having to
656 insert them directly into a query string.
660 In the extended protocol, the frontend first sends a Parse message,
661 which contains a textual query string, optionally some information
662 about data types of parameter placeholders, and the
663 name of a destination prepared-statement object (an empty string
664 selects the unnamed prepared statement). The response is
665 either ParseComplete or ErrorResponse. Parameter data types can be
666 specified by OID; if not given, the parser attempts to infer the
667 data types in the same way as it would do for untyped literal string
673 A parameter data type can be left unspecified by setting it to zero,
674 or by making the array of parameter type OIDs shorter than the
675 number of parameter symbols (<literal>$</><replaceable>n</>)
676 used in the query string. Another special case is that a parameter's
677 type can be specified as <type>void</> (that is, the OID of the
678 <type>void</> pseudotype). This is meant to allow parameter symbols
679 to be used for function parameters that are actually OUT parameters.
680 Ordinarily there is no context in which a <type>void</> parameter
681 could be used, but if such a parameter symbol appears in a function's
682 parameter list, it is effectively ignored. For example, a function
683 call such as <literal>foo($1,$2,$3,$4)</> could match a function with
684 two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
685 are specified as having type <type>void</>.
691 The query string contained in a Parse message cannot include more
692 than one SQL statement; else a syntax error is reported. This
693 restriction does not exist in the simple-query protocol, but it
694 does exist in the extended protocol, because allowing prepared
695 statements or portals to contain multiple commands would complicate
701 If successfully created, a named prepared-statement object lasts till
702 the end of the current session, unless explicitly destroyed. An unnamed
703 prepared statement lasts only until the next Parse statement specifying
704 the unnamed statement as destination is issued. (Note that a simple
705 Query message also destroys the unnamed statement.) Named prepared
706 statements must be explicitly closed before they can be redefined by
707 a Parse message, but this is not required for the unnamed statement.
708 Named prepared statements can also be created and accessed at the SQL
709 command level, using <command>PREPARE</> and <command>EXECUTE</>.
713 Once a prepared statement exists, it can be readied for execution using a
714 Bind message. The Bind message gives the name of the source prepared
715 statement (empty string denotes the unnamed prepared statement), the name
716 of the destination portal (empty string denotes the unnamed portal), and
717 the values to use for any parameter placeholders present in the prepared
719 supplied parameter set must match those needed by the prepared statement.
720 (If you declared any <type>void</> parameters in the Parse message,
721 pass NULL values for them in the Bind message.)
722 Bind also specifies the format to use for any data returned
723 by the query; the format can be specified overall, or per-column.
724 The response is either BindComplete or ErrorResponse.
729 The choice between text and binary output is determined by the format
730 codes given in Bind, regardless of the SQL command involved. The
731 <literal>BINARY</> attribute in cursor declarations is irrelevant when
732 using extended query protocol.
737 Query planning for named prepared-statement objects occurs when the Parse
738 message is processed. If a query will be repeatedly executed with
739 different parameters, it might be beneficial to send a single Parse message
740 containing a parameterized query, followed by multiple Bind
741 and Execute messages. This will avoid replanning the query on each
746 The unnamed prepared statement is likewise planned during Parse processing
747 if the Parse message defines no parameters. But if there are parameters,
748 query planning occurs during Bind processing instead. This allows the
749 planner to make use of the actual values of the parameters provided in
750 the Bind message when planning the query.
755 Query plans generated from a parameterized query might be less
756 efficient than query plans generated from an equivalent query with actual
757 parameter values substituted. The query planner cannot make decisions
758 based on actual parameter values (for example, index selectivity) when
759 planning a parameterized query assigned to a named prepared-statement
760 object. This possible penalty is avoided when using the unnamed
761 statement, since it is not planned until actual parameter values are
762 available. The cost is that planning must occur afresh for each Bind,
763 even if the query stays the same.
768 If successfully created, a named portal object lasts till the end of the
769 current transaction, unless explicitly destroyed. An unnamed portal is
770 destroyed at the end of the transaction, or as soon as the next Bind
771 statement specifying the unnamed portal as destination is issued. (Note
772 that a simple Query message also destroys the unnamed portal.) Named
773 portals must be explicitly closed before they can be redefined by a Bind
774 message, but this is not required for the unnamed portal.
775 Named portals can also be created and accessed at the SQL
776 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
780 Once a portal exists, it can be executed using an Execute message.
781 The Execute message specifies the portal name (empty string denotes the
783 a maximum result-row count (zero meaning <quote>fetch all rows</>).
784 The result-row count is only meaningful for portals
785 containing commands that return row sets; in other cases the command is
786 always executed to completion, and the row count is ignored.
788 responses to Execute are the same as those described above for queries
789 issued via simple query protocol, except that Execute doesn't cause
790 ReadyForQuery or RowDescription to be issued.
794 If Execute terminates before completing the execution of a portal
795 (due to reaching a nonzero result-row count), it will send a
796 PortalSuspended message; the appearance of this message tells the frontend
797 that another Execute should be issued against the same portal to
798 complete the operation. The CommandComplete message indicating
799 completion of the source SQL command is not sent until
800 the portal's execution is completed. Therefore, an Execute phase is
801 always terminated by the appearance of exactly one of these messages:
802 CommandComplete, EmptyQueryResponse (if the portal was created from
803 an empty query string), ErrorResponse, or PortalSuspended.
807 At completion of each series of extended-query messages, the frontend
808 should issue a Sync message. This parameterless message causes the
809 backend to close the current transaction if it's not inside a
810 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
811 meaning to commit if no error, or roll back if error). Then a
812 ReadyForQuery response is issued. The purpose of Sync is to provide
813 a resynchronization point for error recovery. When an error is detected
814 while processing any extended-query message, the backend issues
815 ErrorResponse, then reads and discards messages until a Sync is reached,
816 then issues ReadyForQuery and returns to normal message processing.
817 (But note that no skipping occurs if an error is detected
818 <emphasis>while</> processing Sync — this ensures that there is one
819 and only one ReadyForQuery sent for each Sync.)
824 Sync does not cause a transaction block opened with <command>BEGIN</>
825 to be closed. It is possible to detect this situation since the
826 ReadyForQuery message includes transaction status information.
831 In addition to these fundamental, required operations, there are several
832 optional operations that can be used with extended-query protocol.
836 The Describe message (portal variant) specifies the name of an existing
837 portal (or an empty string for the unnamed portal). The response is a
838 RowDescription message describing the rows that will be returned by
839 executing the portal; or a NoData message if the portal does not contain a
840 query that will return rows; or ErrorResponse if there is no such portal.
844 The Describe message (statement variant) specifies the name of an existing
845 prepared statement (or an empty string for the unnamed prepared
846 statement). The response is a ParameterDescription message describing the
847 parameters needed by the statement, followed by a RowDescription message
848 describing the rows that will be returned when the statement is eventually
849 executed (or a NoData message if the statement will not return rows).
850 ErrorResponse is issued if there is no such prepared statement. Note that
851 since Bind has not yet been issued, the formats to be used for returned
852 columns are not yet known to the backend; the format code fields in the
853 RowDescription message will be zeroes in this case.
858 In most scenarios the frontend should issue one or the other variant
859 of Describe before issuing Execute, to ensure that it knows how to
860 interpret the results it will get back.
865 The Close message closes an existing prepared statement or portal
866 and releases resources. It is not an error to issue Close against
867 a nonexistent statement or portal name. The response is normally
868 CloseComplete, but could be ErrorResponse if some difficulty is
869 encountered while releasing resources. Note that closing a prepared
870 statement implicitly closes any open portals that were constructed
875 The Flush message does not cause any specific output to be generated,
876 but forces the backend to deliver any data pending in its output
877 buffers. A Flush must be sent after any extended-query command except
878 Sync, if the frontend wishes to examine the results of that command before
879 issuing more commands. Without Flush, messages returned by the backend
880 will be combined into the minimum possible number of packets to minimize
886 The simple Query message is approximately equivalent to the series Parse,
887 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
888 statement and portal objects and no parameters. One difference is that
889 it will accept multiple SQL statements in the query string, automatically
890 performing the bind/describe/execute sequence for each one in succession.
891 Another difference is that it will not return ParseComplete, BindComplete,
892 CloseComplete, or NoData messages.
898 <title>Function Call</title>
901 The Function Call sub-protocol allows the client to request a direct
902 call of any function that exists in the database's
903 <structname>pg_proc</structname> system catalog. The client must have
904 execute permission for the function.
909 The Function Call sub-protocol is a legacy feature that is probably best
910 avoided in new code. Similar results can be accomplished by setting up
911 a prepared statement that does <literal>SELECT function($1, ...)</>.
912 The Function Call cycle can then be replaced with Bind/Execute.
917 A Function Call cycle is initiated by the frontend sending a
918 FunctionCall message to the backend. The backend then sends one
919 or more response messages depending on the results of the function
920 call, and finally a ReadyForQuery response message. ReadyForQuery
921 informs the frontend that it can safely send a new query or
926 The possible response messages from the backend are:
930 <term>ErrorResponse</term>
933 An error has occurred.
939 <term>FunctionCallResponse</term>
942 The function call was completed and returned the result given
944 (Note that the Function Call protocol can only handle a single
945 scalar result, not a row type or set of results.)
951 <term>ReadyForQuery</term>
954 Processing of the function call is complete. ReadyForQuery
955 will always be sent, whether processing terminates
956 successfully or with an error.
962 <term>NoticeResponse</term>
965 A warning message has been issued in relation to the function
966 call. Notices are in addition to other responses, i.e., the
967 backend will continue processing the command.
975 <sect2 id="protocol-copy">
976 <title>COPY Operations</title>
979 The <command>COPY</> command allows high-speed bulk data transfer
980 to or from the server. Copy-in and copy-out operations each switch
981 the connection into a distinct sub-protocol, which lasts until the
982 operation is completed.
986 Copy-in mode (data transfer to the server) is initiated when the
987 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
988 sends a CopyInResponse message to the frontend. The frontend should
989 then send zero or more CopyData messages, forming a stream of input
990 data. (The message boundaries are not required to have anything to do
991 with row boundaries, although that is often a reasonable choice.)
992 The frontend can terminate the copy-in mode by sending either a CopyDone
993 message (allowing successful termination) or a CopyFail message (which
994 will cause the <command>COPY</> SQL statement to fail with an
995 error). The backend then reverts to the command-processing mode it was
996 in before the <command>COPY</> started, which will be either simple or
997 extended query protocol. It will next send either CommandComplete
998 (if successful) or ErrorResponse (if not).
1002 In the event of a backend-detected error during copy-in mode (including
1003 receipt of a CopyFail message), the backend will issue an ErrorResponse
1004 message. If the <command>COPY</> command was issued via an extended-query
1005 message, the backend will now discard frontend messages until a Sync
1006 message is received, then it will issue ReadyForQuery and return to normal
1007 processing. If the <command>COPY</> command was issued in a simple
1008 Query message, the rest of that message is discarded and ReadyForQuery
1009 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
1010 messages issued by the frontend will simply be dropped.
1014 The backend will ignore Flush and Sync messages received during copy-in
1015 mode. Receipt of any other non-copy message type constitutes an error
1016 that will abort the copy-in state as described above. (The exception for
1017 Flush and Sync is for the convenience of client libraries that always
1018 send Flush or Sync after an Execute message, without checking whether
1019 the command to be executed is a <command>COPY FROM STDIN</>.)
1023 Copy-out mode (data transfer from the server) is initiated when the
1024 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
1025 sends a CopyOutResponse message to the frontend, followed by
1026 zero or more CopyData messages (always one per row), followed by CopyDone.
1027 The backend then reverts to the command-processing mode it was
1028 in before the <command>COPY</> started, and sends CommandComplete.
1029 The frontend cannot abort the transfer (except by closing the connection
1030 or issuing a Cancel request),
1031 but it can discard unwanted CopyData and CopyDone messages.
1035 In the event of a backend-detected error during copy-out mode,
1036 the backend will issue an ErrorResponse message and revert to normal
1037 processing. The frontend should treat receipt of ErrorResponse (or
1038 indeed any message type other than CopyData or CopyDone) as terminating
1043 The CopyInResponse and CopyOutResponse messages include fields that
1044 inform the frontend of the number of columns per row and the format
1045 codes being used for each column. (As of the present implementation,
1046 all columns in a given <command>COPY</> operation will use the same
1047 format, but the message design does not assume this.)
1051 <sect2 id="protocol-async">
1052 <title>Asynchronous Operations</title>
1055 There are several cases in which the backend will send messages that
1056 are not specifically prompted by the frontend's command stream.
1057 Frontends must be prepared to deal with these messages at any time,
1058 even when not engaged in a query.
1059 At minimum, one should check for these cases before beginning to
1060 read a query response.
1064 It is possible for NoticeResponse messages to be generated due to
1065 outside activity; for example, if the database administrator commands
1066 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1067 indicating this fact before closing the connection. Accordingly,
1068 frontends should always be prepared to accept and display NoticeResponse
1069 messages, even when the connection is nominally idle.
1073 ParameterStatus messages will be generated whenever the active
1074 value changes for any of the parameters the backend believes the
1075 frontend should know about. Most commonly this occurs in response
1076 to a <command>SET</> SQL command executed by the frontend, and
1077 this case is effectively synchronous — but it is also possible
1078 for parameter status changes to occur because the administrator
1079 changed a configuration file and then sent the
1080 <systemitem>SIGHUP</systemitem> signal to the server. Also,
1081 if a <command>SET</command> command is rolled back, an appropriate
1082 ParameterStatus message will be generated to report the current
1087 At present there is a hard-wired set of parameters for which
1088 ParameterStatus will be generated: they are
1089 <literal>server_version</>,
1090 <literal>server_encoding</>,
1091 <literal>client_encoding</>,
1092 <literal>is_superuser</>,
1093 <literal>session_authorization</>,
1094 <literal>DateStyle</>,
1095 <literal>TimeZone</>,
1096 <literal>integer_datetimes</>, and
1097 <literal>standard_conforming_strings</>.
1098 (<literal>server_encoding</>, <literal>TimeZone</>, and
1099 <literal>integer_datetimes</> were not reported by releases before 8.0;
1100 <literal>standard_conforming_strings</> was not reported by releases
1103 <literal>server_version</>,
1104 <literal>server_encoding</> and
1105 <literal>integer_datetimes</>
1106 are pseudo-parameters that cannot change after startup.
1107 This set might change in the future, or even become configurable.
1108 Accordingly, a frontend should simply ignore ParameterStatus for
1109 parameters that it does not understand or care about.
1113 If a frontend issues a <command>LISTEN</command> command, then the
1114 backend will send a NotificationResponse message (not to be
1115 confused with NoticeResponse!) whenever a
1116 <command>NOTIFY</command> command is executed for the same
1122 At present, NotificationResponse can only be sent outside a
1123 transaction, and thus it will not occur in the middle of a
1124 command-response series, though it might occur just before ReadyForQuery.
1125 It is unwise to design frontend logic that assumes that, however.
1126 Good practice is to be able to accept NotificationResponse at any
1127 point in the protocol.
1133 <title>Cancelling Requests in Progress</title>
1136 During the processing of a query, the frontend might request
1137 cancellation of the query. The cancel request is not sent
1138 directly on the open connection to the backend for reasons of
1139 implementation efficiency: we don't want to have the backend
1140 constantly checking for new input from the frontend during query
1141 processing. Cancel requests should be relatively infrequent, so
1142 we make them slightly cumbersome in order to avoid a penalty in
1147 To issue a cancel request, the frontend opens a new connection to
1148 the server and sends a CancelRequest message, rather than the
1149 StartupMessage message that would ordinarily be sent across a new
1150 connection. The server will process this request and then close
1151 the connection. For security reasons, no direct reply is made to
1152 the cancel request message.
1156 A CancelRequest message will be ignored unless it contains the
1157 same key data (PID and secret key) passed to the frontend during
1158 connection start-up. If the request matches the PID and secret
1159 key for a currently executing backend, the processing of the
1160 current query is aborted. (In the existing implementation, this is
1161 done by sending a special signal to the backend process that is
1162 processing the query.)
1166 The cancellation signal might or might not have any effect — for
1167 example, if it arrives after the backend has finished processing
1168 the query, then it will have no effect. If the cancellation is
1169 effective, it results in the current command being terminated
1170 early with an error message.
1174 The upshot of all this is that for reasons of both security and
1175 efficiency, the frontend has no direct way to tell whether a
1176 cancel request has succeeded. It must continue to wait for the
1177 backend to respond to the query. Issuing a cancel simply improves
1178 the odds that the current query will finish soon, and improves the
1179 odds that it will fail with an error message instead of
1184 Since the cancel request is sent across a new connection to the
1185 server and not across the regular frontend/backend communication
1186 link, it is possible for the cancel request to be issued by any
1187 process, not just the frontend whose query is to be canceled.
1188 This might provide additional flexibility when building
1189 multiple-process applications. It also introduces a security
1190 risk, in that unauthorized persons might try to cancel queries.
1191 The security risk is addressed by requiring a dynamically
1192 generated secret key to be supplied in cancel requests.
1197 <title>Termination</title>
1200 The normal, graceful termination procedure is that the frontend
1201 sends a Terminate message and immediately closes the connection.
1202 On receipt of this message, the backend closes the connection and
1207 In rare cases (such as an administrator-commanded database shutdown)
1208 the backend might disconnect without any frontend request to do so.
1209 In such cases the backend will attempt to send an error or notice message
1210 giving the reason for the disconnection before it closes the connection.
1214 Other termination scenarios arise from various failure cases, such as core
1215 dump at one end or the other, loss of the communications link, loss of
1216 message-boundary synchronization, etc. If either frontend or backend sees
1217 an unexpected closure of the connection, it should clean
1218 up and terminate. The frontend has the option of launching a new backend
1219 by recontacting the server if it doesn't want to terminate itself.
1220 Closing the connection is also advisable if an unrecognizable message type
1221 is received, since this probably indicates loss of message-boundary sync.
1225 For either normal or abnormal termination, any open transaction is
1226 rolled back, not committed. One should note however that if a
1227 frontend disconnects while a non-<command>SELECT</command> query
1228 is being processed, the backend will probably finish the query
1229 before noticing the disconnection. If the query is outside any
1230 transaction block (<command>BEGIN</> ... <command>COMMIT</>
1231 sequence) then its results might be committed before the
1232 disconnection is recognized.
1237 <title><acronym>SSL</acronym> Session Encryption</title>
1240 If <productname>PostgreSQL</> was built with
1241 <acronym>SSL</acronym> support, frontend/backend communications
1242 can be encrypted using <acronym>SSL</acronym>. This provides
1243 communication security in environments where attackers might be
1244 able to capture the session traffic. For more information on
1245 encrypting <productname>PostgreSQL</productname> sessions with
1246 <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1250 To initiate an <acronym>SSL</acronym>-encrypted connection, the
1251 frontend initially sends an SSLRequest message rather than a
1252 StartupMessage. The server then responds with a single byte
1253 containing <literal>S</> or <literal>N</>, indicating that it is
1254 willing or unwilling to perform <acronym>SSL</acronym>,
1255 respectively. The frontend might close the connection at this point
1256 if it is dissatisfied with the response. To continue after
1257 <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1258 (not described here, part of the <acronym>SSL</acronym>
1259 specification) with the server. If this is successful, continue
1260 with sending the usual StartupMessage. In this case the
1261 StartupMessage and all subsequent data will be
1262 <acronym>SSL</acronym>-encrypted. To continue after
1263 <literal>N</>, send the usual StartupMessage and proceed without
1268 The frontend should also be prepared to handle an ErrorMessage
1269 response to SSLRequest from the server. This would only occur if
1270 the server predates the addition of <acronym>SSL</acronym> support
1271 to <productname>PostgreSQL</>. In this case the connection must
1272 be closed, but the frontend might choose to open a fresh connection
1273 and proceed without requesting <acronym>SSL</acronym>.
1277 An initial SSLRequest can also be used in a connection that is being
1278 opened to send a CancelRequest message.
1282 While the protocol itself does not provide a way for the server to
1283 force <acronym>SSL</acronym> encryption, the administrator can
1284 configure the server to reject unencrypted sessions as a byproduct
1285 of authentication checking.
1290 <sect1 id="protocol-message-types">
1291 <title>Message Data Types</title>
1294 This section describes the base data types used in messages.
1300 Int<replaceable>n</replaceable>(<replaceable>i</replaceable>)
1304 An <replaceable>n</replaceable>-bit integer in network byte
1305 order (most significant byte first).
1306 If <replaceable>i</replaceable> is specified it
1307 is the exact value that will appear, otherwise the value
1308 is variable. Eg. Int16, Int32(42).
1315 Int<replaceable>n</replaceable>[<replaceable>k</replaceable>]
1319 An array of <replaceable>k</replaceable>
1320 <replaceable>n</replaceable>-bit integers, each in network
1321 byte order. The array length <replaceable>k</replaceable>
1322 is always determined by an earlier field in the message.
1330 String(<replaceable>s</replaceable>)
1334 A null-terminated string (C-style string). There is no
1335 specific length limitation on strings.
1336 If <replaceable>s</replaceable> is specified it is the exact
1337 value that will appear, otherwise the value is variable.
1338 Eg. String, String("user").
1343 <emphasis>There is no predefined limit</emphasis> on the length of a string
1344 that can be returned by the backend. Good coding strategy for a frontend
1345 is to use an expandable buffer so that anything that fits in memory can be
1346 accepted. If that's not feasible, read the full string and discard trailing
1347 characters that don't fit into your fixed-size buffer.
1355 Byte<replaceable>n</replaceable>(<replaceable>c</replaceable>)
1359 Exactly <replaceable>n</replaceable> bytes. If the field
1360 width <replaceable>n</replaceable> is not a constant, it is
1361 always determinable from an earlier field in the message.
1362 If <replaceable>c</replaceable> is specified it is the exact
1363 value. Eg. Byte2, Byte1('\n').
1372 <sect1 id="protocol-message-formats">
1373 <title>Message Formats</title>
1376 This section describes the detailed format of each message. Each is marked to
1377 indicate that it can be sent by a frontend (F), a backend (B), or both
1379 Notice that although each message includes a byte count at the beginning,
1380 the message format is defined so that the message end can be found without
1381 reference to the byte count. This aids validity checking. (The CopyData
1382 message is an exception, because it forms part of a data stream; the contents
1383 of any individual CopyData message cannot be interpretable on their own.)
1391 AuthenticationOk (B)
1403 Identifies the message as an authentication request.
1413 Length of message contents in bytes, including self.
1423 Specifies that the authentication was successful.
1436 AuthenticationKerberosV5 (B)
1448 Identifies the message as an authentication request.
1458 Length of message contents in bytes, including self.
1468 Specifies that Kerberos V5 authentication is required.
1480 AuthenticationCleartextPassword (B)
1492 Identifies the message as an authentication request.
1502 Length of message contents in bytes, including self.
1512 Specifies that a clear-text password is required.
1524 AuthenticationCryptPassword (B)
1536 Identifies the message as an authentication request.
1546 Length of message contents in bytes, including self.
1556 Specifies that a crypt()-encrypted password is required.
1566 The salt to use when encrypting the password.
1579 AuthenticationMD5Password (B)
1591 Identifies the message as an authentication request.
1601 Length of message contents in bytes, including self.
1611 Specifies that an MD5-encrypted password is required.
1621 The salt to use when encrypting the password.
1634 AuthenticationSCMCredential (B)
1646 Identifies the message as an authentication request.
1656 Length of message contents in bytes, including self.
1666 Specifies that an SCM credentials message is required.
1679 AuthenticationGSS (B)
1691 Identifies the message as an authentication request.
1701 Length of message contents in bytes, including self.
1711 Specifies that GSSAPI authentication is required.
1724 AuthenticationSSPI (B)
1736 Identifies the message as an authentication request.
1746 Length of message contents in bytes, including self.
1756 Specifies that SSPI authentication is required.
1767 AuthenticationGSSContinue (B)
1779 Identifies the message as an authentication request.
1789 Length of message contents in bytes, including self.
1799 Specifies that this message contains GSSAPI data.
1805 Byte<replaceable>n</replaceable>
1809 GSSAPI or SSPI authentication data.
1834 Identifies the message as cancellation key data.
1835 The frontend must save these values if it wishes to be
1836 able to issue CancelRequest messages later.
1846 Length of message contents in bytes, including self.
1856 The process ID of this backend.
1866 The secret key of this backend.
1891 Identifies the message as a Bind command.
1901 Length of message contents in bytes, including self.
1911 The name of the destination portal
1912 (an empty string selects the unnamed portal).
1922 The name of the source prepared statement
1923 (an empty string selects the unnamed prepared statement).
1933 The number of parameter format codes that follow
1934 (denoted <replaceable>C</> below).
1935 This can be zero to indicate that there are no parameters
1936 or that the parameters all use the default format (text);
1937 or one, in which case the specified format code is applied
1938 to all parameters; or it can equal the actual number of
1945 Int16[<replaceable>C</>]
1949 The parameter format codes. Each must presently be
1950 zero (text) or one (binary).
1960 The number of parameter values that follow (possibly zero).
1961 This must match the number of parameters needed by the query.
1966 Next, the following pair of fields appear for each parameter:
1974 The length of the parameter value, in bytes (this count
1975 does not include itself). Can be zero.
1976 As a special case, -1 indicates a NULL parameter value.
1977 No value bytes follow in the NULL case.
1983 Byte<replaceable>n</replaceable>
1987 The value of the parameter, in the format indicated by the
1988 associated format code.
1989 <replaceable>n</replaceable> is the above length.
1994 After the last parameter, the following fields appear:
2002 The number of result-column format codes that follow
2003 (denoted <replaceable>R</> below).
2004 This can be zero to indicate that there are no result columns
2005 or that the result columns should all use the default format
2007 or one, in which case the specified format code is applied
2008 to all result columns (if any); or it can equal the actual
2009 number of result columns of the query.
2015 Int16[<replaceable>R</>]
2019 The result-column format codes. Each must presently be
2020 zero (text) or one (binary).
2044 Identifies the message as a Bind-complete indicator.
2054 Length of message contents in bytes, including self.
2079 Length of message contents in bytes, including self.
2089 The cancel request code. The value is chosen to contain
2090 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
2091 least 16 significant bits. (To avoid confusion, this code
2092 must not be the same as any protocol version number.)
2102 The process ID of the target backend.
2112 The secret key for the target backend.
2137 Identifies the message as a Close command.
2147 Length of message contents in bytes, including self.
2157 '<literal>S</>' to close a prepared statement; or
2158 '<literal>P</>' to close a portal.
2168 The name of the prepared statement or portal to close
2169 (an empty string selects the unnamed prepared statement
2194 Identifies the message as a Close-complete indicator.
2204 Length of message contents in bytes, including self.
2229 Identifies the message as a command-completed response.
2239 Length of message contents in bytes, including self.
2249 The command tag. This is usually a single
2250 word that identifies which SQL command was completed.
2254 For an <command>INSERT</command> command, the tag is
2255 <literal>INSERT <replaceable>oid</replaceable>
2256 <replaceable>rows</replaceable></literal>, where
2257 <replaceable>rows</replaceable> is the number of rows
2258 inserted. <replaceable>oid</replaceable> is the object ID
2259 of the inserted row if <replaceable>rows</replaceable> is 1
2260 and the target table has OIDs;
2261 otherwise <replaceable>oid</replaceable> is 0.
2265 For a <command>DELETE</command> command, the tag is
2266 <literal>DELETE <replaceable>rows</replaceable></literal> where
2267 <replaceable>rows</replaceable> is the number of rows deleted.
2271 For an <command>UPDATE</command> command, the tag is
2272 <literal>UPDATE <replaceable>rows</replaceable></literal> where
2273 <replaceable>rows</replaceable> is the number of rows updated.
2277 For a <command>MOVE</command> command, the tag is
2278 <literal>MOVE <replaceable>rows</replaceable></literal> where
2279 <replaceable>rows</replaceable> is the number of rows the
2280 cursor's position has been changed by.
2284 For a <command>FETCH</command> command, the tag is
2285 <literal>FETCH <replaceable>rows</replaceable></literal> where
2286 <replaceable>rows</replaceable> is the number of rows that
2287 have been retrieved from the cursor.
2291 For a <command>COPY</command> command, the tag is
2292 <literal>COPY <replaceable>rows</replaceable></literal> where
2293 <replaceable>rows</replaceable> is the number of rows copied.
2294 (Note: the row count appears only in
2295 <productname>PostgreSQL</productname> 8.2 and later.)
2309 CopyData (F & B)
2320 Identifies the message as <command>COPY</command> data.
2330 Length of message contents in bytes, including self.
2336 Byte<replaceable>n</replaceable>
2340 Data that forms part of a <command>COPY</command> data stream. Messages sent
2341 from the backend will always correspond to single data rows,
2342 but messages sent by frontends might divide the data stream
2355 CopyDone (F & B)
2367 Identifies the message as a <command>COPY</command>-complete indicator.
2377 Length of message contents in bytes, including self.
2402 Identifies the message as a <command>COPY</command>-failure indicator.
2412 Length of message contents in bytes, including self.
2422 An error message to report as the cause of failure.
2447 Identifies the message as a Start Copy In response.
2448 The frontend must now send copy-in data (if not
2449 prepared to do so, send a CopyFail message).
2459 Length of message contents in bytes, including self.
2469 0 indicates the overall <command>COPY</command> format is textual (rows
2470 separated by newlines, columns separated by separator
2472 1 indicates the overall copy format is binary (similar
2474 See <xref linkend="sql-copy" endterm="sql-copy-title">
2475 for more information.
2485 The number of columns in the data to be copied
2486 (denoted <replaceable>N</> below).
2492 Int16[<replaceable>N</>]
2496 The format codes to be used for each column.
2497 Each must presently be zero (text) or one (binary).
2498 All must be zero if the overall copy format is textual.
2523 Identifies the message as a Start Copy Out response.
2524 This message will be followed by copy-out data.
2534 Length of message contents in bytes, including self.
2544 0 indicates the overall <command>COPY</command> format
2545 is textual (rows separated by newlines, columns
2546 separated by separator characters, etc). 1 indicates
2547 the overall copy format is binary (similar to DataRow
2548 format). See <xref linkend="sql-copy"
2549 endterm="sql-copy-title"> for more information.
2559 The number of columns in the data to be copied
2560 (denoted <replaceable>N</> below).
2566 Int16[<replaceable>N</>]
2570 The format codes to be used for each column.
2571 Each must presently be zero (text) or one (binary).
2572 All must be zero if the overall copy format is textual.
2596 Identifies the message as a data row.
2606 Length of message contents in bytes, including self.
2616 The number of column values that follow (possibly zero).
2621 Next, the following pair of fields appear for each column:
2629 The length of the column value, in bytes (this count
2630 does not include itself). Can be zero.
2631 As a special case, -1 indicates a NULL column value.
2632 No value bytes follow in the NULL case.
2638 Byte<replaceable>n</replaceable>
2642 The value of the column, in the format indicated by the
2643 associated format code.
2644 <replaceable>n</replaceable> is the above length.
2669 Identifies the message as a Describe command.
2679 Length of message contents in bytes, including self.
2689 '<literal>S</>' to describe a prepared statement; or
2690 '<literal>P</>' to describe a portal.
2700 The name of the prepared statement or portal to describe
2701 (an empty string selects the unnamed prepared statement
2714 EmptyQueryResponse (B)
2726 Identifies the message as a response to an empty query string.
2727 (This substitutes for CommandComplete.)
2737 Length of message contents in bytes, including self.
2762 Identifies the message as an error.
2772 Length of message contents in bytes, including self.
2777 The message body consists of one or more identified fields,
2778 followed by a zero byte as a terminator. Fields can appear in
2779 any order. For each field there is the following:
2787 A code identifying the field type; if zero, this is
2788 the message terminator and no string follows.
2789 The presently defined field types are listed in
2790 <xref linkend="protocol-error-fields">.
2791 Since more field types might be added in future,
2792 frontends should silently ignore fields of unrecognized
2828 Identifies the message as an Execute command.
2838 Length of message contents in bytes, including self.
2848 The name of the portal to execute
2849 (an empty string selects the unnamed portal).
2859 Maximum number of rows to return, if portal contains
2860 a query that returns rows (ignored otherwise). Zero
2861 denotes <quote>no limit</>.
2885 Identifies the message as a Flush command.
2895 Length of message contents in bytes, including self.
2920 Identifies the message as a function call.
2930 Length of message contents in bytes, including self.
2940 Specifies the object ID of the function to call.
2950 The number of argument format codes that follow
2951 (denoted <replaceable>C</> below).
2952 This can be zero to indicate that there are no arguments
2953 or that the arguments all use the default format (text);
2954 or one, in which case the specified format code is applied
2955 to all arguments; or it can equal the actual number of
2962 Int16[<replaceable>C</>]
2966 The argument format codes. Each must presently be
2967 zero (text) or one (binary).
2977 Specifies the number of arguments being supplied to the
2983 Next, the following pair of fields appear for each argument:
2991 The length of the argument value, in bytes (this count
2992 does not include itself). Can be zero.
2993 As a special case, -1 indicates a NULL argument value.
2994 No value bytes follow in the NULL case.
3000 Byte<replaceable>n</replaceable>
3004 The value of the argument, in the format indicated by the
3005 associated format code.
3006 <replaceable>n</replaceable> is the above length.
3011 After the last argument, the following field appears:
3019 The format code for the function result. Must presently be
3020 zero (text) or one (binary).
3033 FunctionCallResponse (B)
3045 Identifies the message as a function call result.
3055 Length of message contents in bytes, including self.
3065 The length of the function result value, in bytes (this count
3066 does not include itself). Can be zero.
3067 As a special case, -1 indicates a NULL function result.
3068 No value bytes follow in the NULL case.
3074 Byte<replaceable>n</replaceable>
3078 The value of the function result, in the format indicated by
3079 the associated format code.
3080 <replaceable>n</replaceable> is the above length.
3105 Identifies the message as a no-data indicator.
3115 Length of message contents in bytes, including self.
3140 Identifies the message as a notice.
3150 Length of message contents in bytes, including self.
3155 The message body consists of one or more identified fields,
3156 followed by a zero byte as a terminator. Fields can appear in
3157 any order. For each field there is the following:
3165 A code identifying the field type; if zero, this is
3166 the message terminator and no string follows.
3167 The presently defined field types are listed in
3168 <xref linkend="protocol-error-fields">.
3169 Since more field types might be added in future,
3170 frontends should silently ignore fields of unrecognized
3194 NotificationResponse (B)
3206 Identifies the message as a notification response.
3216 Length of message contents in bytes, including self.
3226 The process ID of the notifying backend process.
3236 The name of the condition that the notify has been raised on.
3246 Additional information passed from the notifying process.
3247 (Currently, this feature is unimplemented so the field
3248 is always an empty string.)
3261 ParameterDescription (B)
3273 Identifies the message as a parameter description.
3283 Length of message contents in bytes, including self.
3293 The number of parameters used by the statement
3299 Then, for each parameter, there is the following:
3307 Specifies the object ID of the parameter data type.
3331 Identifies the message as a run-time parameter status report.
3341 Length of message contents in bytes, including self.
3351 The name of the run-time parameter being reported.
3361 The current value of the parameter.
3385 Identifies the message as a Parse command.
3395 Length of message contents in bytes, including self.
3405 The name of the destination prepared statement
3406 (an empty string selects the unnamed prepared statement).
3416 The query string to be parsed.
3426 The number of parameter data types specified
3427 (can be zero). Note that this is not an indication of
3428 the number of parameters that might appear in the
3429 query string, only the number that the frontend wants to
3430 prespecify types for.
3435 Then, for each parameter, there is the following:
3443 Specifies the object ID of the parameter data type.
3444 Placing a zero here is equivalent to leaving the type
3469 Identifies the message as a Parse-complete indicator.
3479 Length of message contents in bytes, including self.
3504 Identifies the message as a password response. Note that
3505 this is also used by GSSAPI response messages.
3515 Length of message contents in bytes, including self.
3525 The password (encrypted, if requested).
3549 Identifies the message as a portal-suspended indicator.
3550 Note this only appears if an Execute message's row-count limit
3561 Length of message contents in bytes, including self.
3586 Identifies the message as a simple query.
3596 Length of message contents in bytes, including self.
3606 The query string itself.
3631 Identifies the message type. ReadyForQuery is sent
3632 whenever the backend is ready for a new query cycle.
3642 Length of message contents in bytes, including self.
3652 Current backend transaction status indicator.
3653 Possible values are '<literal>I</>' if idle (not in
3654 a transaction block); '<literal>T</>' if in a transaction
3655 block; or '<literal>E</>' if in a failed transaction
3656 block (queries will be rejected until block is ended).
3681 Identifies the message as a row description.
3691 Length of message contents in bytes, including self.
3701 Specifies the number of fields in a row (can be zero).
3706 Then, for each field, there is the following:
3724 If the field can be identified as a column of a specific
3725 table, the object ID of the table; otherwise zero.
3735 If the field can be identified as a column of a specific
3736 table, the attribute number of the column; otherwise zero.
3746 The object ID of the field's data type.
3756 The data type size (see <varname>pg_type.typlen</>).
3757 Note that negative values denote variable-width types.
3767 The type modifier (see <varname>pg_attribute.atttypmod</>).
3768 The meaning of the modifier is type-specific.
3778 The format code being used for the field. Currently will
3779 be zero (text) or one (binary). In a RowDescription
3780 returned from the statement variant of Describe, the
3781 format code is not yet known and will always be zero.
3806 Length of message contents in bytes, including self.
3816 The <acronym>SSL</acronym> request code. The value is chosen to contain
3817 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
3818 least 16 significant bits. (To avoid confusion, this code
3819 must not be the same as any protocol version number.)
3844 Length of message contents in bytes, including self.
3854 The protocol version number. The most significant 16 bits are
3855 the major version number (3 for the protocol described here).
3856 The least significant 16 bits are the minor version number
3857 (0 for the protocol described here).
3862 The protocol version number is followed by one or more pairs of
3863 parameter name and value strings. A zero byte is required as a
3864 terminator after the last name/value pair.
3865 Parameters can appear in any
3866 order. <literal>user</> is required, others are optional.
3867 Each parameter is specified as:
3875 The parameter name. Currently recognized names are:
3884 The database user name to connect as. Required;
3885 there is no default.
3891 <literal>database</>
3895 The database to connect to. Defaults to the user name.
3905 Command-line arguments for the backend. (This is
3906 deprecated in favor of setting individual run-time
3913 In addition to the above, any run-time parameter that can be
3914 set at backend start time might be listed. Such settings
3915 will be applied during backend start (after parsing the
3916 command-line options if any). The values will act as
3927 The parameter value.
3952 Identifies the message as a Sync command.
3962 Length of message contents in bytes, including self.
3987 Identifies the message as a termination.
3997 Length of message contents in bytes, including self.
4013 <sect1 id="protocol-error-fields">
4014 <title>Error and Notice Message Fields</title>
4017 This section describes the fields that can appear in ErrorResponse and
4018 NoticeResponse messages. Each field type has a single-byte identification
4019 token. Note that any given field type should appear at most once per
4031 Severity: the field contents are
4032 <literal>ERROR</>, <literal>FATAL</>, or
4033 <literal>PANIC</> (in an error message), or
4034 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
4035 <literal>INFO</>, or <literal>LOG</> (in a notice message),
4036 or a localized translation of one of these. Always present.
4047 Code: the SQLSTATE code for the error (see <xref
4048 linkend="errcodes-appendix">). Not localizable. Always present.
4059 Message: the primary human-readable error message.
4060 This should be accurate but terse (typically one line).
4072 Detail: an optional secondary error message carrying more
4073 detail about the problem. Might run to multiple lines.
4084 Hint: an optional suggestion what to do about the problem.
4085 This is intended to differ from Detail in that it offers advice
4086 (potentially inappropriate) rather than hard facts.
4087 Might run to multiple lines.
4098 Position: the field value is a decimal ASCII integer, indicating
4099 an error cursor position as an index into the original query string.
4100 The first character has index 1, and positions are measured in
4101 characters not bytes.
4112 Internal position: this is defined the same as the <literal>P</>
4113 field, but it is used when the cursor position refers to an internally
4114 generated command rather than the one submitted by the client.
4115 The <literal>q</> field will always appear when this field appears.
4126 Internal query: the text of a failed internally-generated command.
4127 This could be, for example, a SQL query issued by a PL/pgSQL function.
4138 Where: an indication of the context in which the error occurred.
4139 Presently this includes a call stack traceback of active
4140 procedural language functions and internally-generated queries.
4141 The trace is one entry per line, most recent first.
4152 File: the file name of the source-code location where the error
4164 Line: the line number of the source-code location where the error
4176 Routine: the name of the source-code routine reporting the error.
4184 The client is responsible for formatting displayed information to meet its
4185 needs; in particular it should break long lines as needed. Newline characters
4186 appearing in the error message fields should be treated as paragraph breaks,
4193 <sect1 id="protocol-changes">
4194 <title>Summary of Changes since Protocol 2.0</title>
4197 This section provides a quick checklist of changes, for the benefit of
4198 developers trying to update existing client libraries to protocol 3.0.
4202 The initial startup packet uses a flexible list-of-strings format
4203 instead of a fixed format. Notice that session default values for run-time
4204 parameters can now be specified directly in the startup packet. (Actually,
4205 you could do that before using the <literal>options</> field, but given the
4206 limited width of <literal>options</> and the lack of any way to quote
4207 whitespace in the values, it wasn't a very safe technique.)
4211 All messages now have a length count immediately following the message type
4212 byte (except for startup packets, which have no type byte). Also note that
4213 PasswordMessage now has a type byte.
4217 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
4218 messages now contain multiple fields, from which the client code can
4219 assemble an error message of the desired level of verbosity. Note that
4220 individual fields will typically not end with a newline, whereas the single
4221 string sent in the older protocol always did.
4225 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
4230 The distinction between BinaryRow and DataRow message types is gone; the
4231 single DataRow message type serves for returning data in all formats.
4232 Note that the layout of DataRow has changed to make it easier to parse.
4233 Also, the representation of binary values has changed: it is no longer
4234 directly tied to the server's internal representation.
4238 There is a new <quote>extended query</> sub-protocol, which adds the frontend
4239 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
4240 backend message types ParseComplete, BindComplete, PortalSuspended,
4241 ParameterDescription, NoData, and CloseComplete. Existing clients do not
4242 have to concern themselves with this sub-protocol, but making use of it
4243 might allow improvements in performance or functionality.
4247 <command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
4248 is a well-defined way to recover from errors during <command>COPY</command>. The special
4249 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4250 during <command>COPY OUT</command>.
4251 (It is still recognized as a terminator during <command>COPY IN</command>, but its use is
4252 deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
4253 The CopyInResponse and CopyOutResponse messages include fields indicating
4254 the number of columns and the format of each column.
4258 The layout of FunctionCall and FunctionCallResponse messages has changed.
4259 FunctionCall can now support passing NULL arguments to functions. It also
4260 can handle passing parameters and retrieving results in either text or
4261 binary format. There is no longer any reason to consider FunctionCall a
4262 potential security hole, since it does not offer direct access to internal
4263 server data representations.
4267 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4268 startup for all parameters it considers interesting to the client library.
4269 Subsequently, a ParameterStatus message is sent whenever the active value
4270 changes for any of these parameters.
4274 The RowDescription ('<literal>T</>') message carries new table OID and column
4275 number fields for each column of the described row. It also shows the format
4276 code for each column.
4280 The CursorResponse ('<literal>P</>') message is no longer generated by
4285 The NotificationResponse ('<literal>A</>') message has an additional string
4286 field, which is presently empty but might someday carry additional data passed
4287 from the <command>NOTIFY</command> event sender.
4291 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4292 string parameter; this has been removed.