1 <!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.71 2008/01/14 18:46:17 tgl Exp $ -->
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 Higher level features built on this protocol (for example, how
33 <application>libpq</application> passes certain environment
34 variables when the connection is established) are covered elsewhere.
38 In order to serve multiple clients efficiently, the server launches
39 a new <quote>backend</> process for each client.
40 In the current implementation, a new child
41 process is created immediately after an incoming connection is detected.
42 This is transparent to the protocol, however. For purposes of the
43 protocol, the terms <quote>backend</> and <quote>server</> are
44 interchangeable; likewise <quote>frontend</> and <quote>client</>
48 <sect1 id="protocol-overview">
49 <title>Overview</title>
52 The protocol has separate phases for startup and normal operation.
53 In the startup phase, the frontend opens a connection to the server
54 and authenticates itself to the satisfaction of the server. (This might
55 involve a single message, or multiple messages depending on the
56 authentication method being used.) If all goes well, the server then sends
57 status information to the frontend, and finally enters normal operation.
58 Except for the initial startup-request message, this part of the
59 protocol is driven by the server.
63 During normal operation, the frontend sends queries and
64 other commands to the backend, and the backend sends back query results
65 and other responses. There are a few cases (such as <command>NOTIFY</>)
67 backend will send unsolicited messages, but for the most part this portion
68 of a session is driven by frontend requests.
72 Termination of the session is normally by frontend choice, but can be
73 forced by the backend in certain cases. In any case, when the backend
74 closes the connection, it will roll back any open (incomplete) transaction
79 Within normal operation, SQL commands can be executed through either of
80 two sub-protocols. In the <quote>simple query</> protocol, the frontend
81 just sends a textual query string, which is parsed and immediately
82 executed by the backend. In the <quote>extended query</> protocol,
83 processing of queries is separated into multiple steps: parsing,
84 binding of parameter values, and execution. This offers flexibility
85 and performance benefits, at the cost of extra complexity.
89 Normal operation has additional sub-protocols for special operations
90 such as <command>COPY</>.
93 <sect2 id="protocol-message-concepts">
94 <title>Messaging Overview</title>
97 All communication is through a stream of messages. The first byte of a
98 message identifies the message type, and the next four bytes specify the
99 length of the rest of the message (this length count includes itself, but
100 not the message-type byte). The remaining contents of the message are
101 determined by the message type. For historical reasons, the very first
102 message sent by the client (the startup message) has no initial
107 To avoid losing synchronization with the message stream, both servers and
108 clients typically read an entire message into a buffer (using the byte
109 count) before attempting to process its contents. This allows easy
110 recovery if an error is detected while processing the contents. In
111 extreme situations (such as not having enough memory to buffer the
112 message), the receiver can use the byte count to determine how much
113 input to skip before it resumes reading messages.
117 Conversely, both servers and clients must take care never to send an
118 incomplete message. This is commonly done by marshaling the entire message
119 in a buffer before beginning to send it. If a communications failure
120 occurs partway through sending or receiving a message, the only sensible
121 response is to abandon the connection, since there is little hope of
122 recovering message-boundary synchronization.
126 <sect2 id="protocol-query-concepts">
127 <title>Extended Query Overview</title>
130 In the extended-query protocol, execution of SQL commands is divided
131 into multiple steps. The state retained between steps is represented
132 by two types of objects: <firstterm>prepared statements</> and
133 <firstterm>portals</>. A prepared statement represents the result of
134 parsing, semantic analysis, and (optionally) planning of a textual query
136 A prepared statement is not necessarily ready to execute, because it might
137 lack specific values for <firstterm>parameters</>. A portal represents
138 a ready-to-execute or already-partially-executed statement, with any
139 missing parameter values filled in. (For <command>SELECT</> statements,
140 a portal is equivalent to an open cursor, but we choose to use a different
141 term since cursors don't handle non-<command>SELECT</> statements.)
145 The overall execution cycle consists of a <firstterm>parse</> step,
146 which creates a prepared statement from a textual query string; a
147 <firstterm>bind</> step, which creates a portal given a prepared
148 statement and values for any needed parameters; and an
149 <firstterm>execute</> step that runs a portal's query. In the case of
150 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
151 the execute step can be told to fetch only
152 a limited number of rows, so that multiple execute steps might be needed
153 to complete the operation.
157 The backend can keep track of multiple prepared statements and portals
158 (but note that these exist only within a session, and are never shared
159 across sessions). Existing prepared statements and portals are
160 referenced by names assigned when they were created. In addition,
161 an <quote>unnamed</> prepared statement and portal exist. Although these
162 behave largely the same as named objects, operations on them are optimized
163 for the case of executing a query only once and then discarding it,
164 whereas operations on named objects are optimized on the expectation
169 <sect2 id="protocol-format-codes">
170 <title>Formats and Format Codes</title>
173 Data of a particular data type might be transmitted in any of several
174 different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
175 the only supported formats are <quote>text</> and <quote>binary</>,
176 but the protocol makes provision for future extensions. The desired
177 format for any value is specified by a <firstterm>format code</>.
178 Clients can specify a format code for each transmitted parameter value
179 and for each column of a query result. Text has format code zero,
180 binary has format code one, and all other format codes are reserved
181 for future definition.
185 The text representation of values is whatever strings are produced
186 and accepted by the input/output conversion functions for the
187 particular data type. In the transmitted representation, there is
188 no trailing null character; the frontend must add one to received
189 values if it wants to process them as C strings.
190 (The text format does not allow embedded nulls, by the way.)
194 Binary representations for integers use network byte order (most
195 significant byte first). For other data types consult the documentation
196 or source code to learn about the binary representation. Keep in mind
197 that binary representations for complex data types might change across
198 server versions; the text format is usually the more portable choice.
203 <sect1 id="protocol-flow">
204 <title>Message Flow</title>
207 This section describes the message flow and the semantics of each
208 message type. (Details of the exact representation of each message
209 appear in <xref linkend="protocol-message-formats">.) There are
210 several different sub-protocols depending on the state of the
211 connection: start-up, query, function call,
212 <command>COPY</command>, and termination. There are also special
213 provisions for asynchronous operations (including notification
214 responses and command cancellation), which can occur at any time
215 after the start-up phase.
219 <title>Start-Up</title>
222 To begin a session, a frontend opens a connection to the server and sends
223 a startup message. This message includes the names of the user and of the
224 database the user wants to connect to; it also identifies the particular
225 protocol version to be used. (Optionally, the startup message can include
226 additional settings for run-time parameters.)
227 The server then uses this information and
228 the contents of its configuration files (such as
229 <filename>pg_hba.conf</filename>) to determine
230 whether the connection is provisionally acceptable, and what additional
231 authentication is required (if any).
235 The server then sends an appropriate authentication request message,
236 to which the frontend must reply with an appropriate authentication
237 response message (such as a password).
238 For all authentication methods except GSSAPI and SSPI, there is at most
239 one request and one response. In some methods, no response
240 at all is needed from the frontend, and so no authentication request
241 occurs. For GSSAPI and SSPI, multiple iterations of packets may be needed to
242 complete the authentication.
246 The authentication cycle ends with the server either rejecting the
247 connection attempt (ErrorResponse), or sending AuthenticationOk.
251 The possible messages from the server in this phase are:
255 <term>ErrorResponse</term>
258 The connection attempt has been rejected.
259 The server then immediately closes the connection.
265 <term>AuthenticationOk</term>
268 The authentication exchange is successfully completed.
274 <term>AuthenticationKerberosV5</term>
277 The frontend must now take part in a Kerberos V5
278 authentication dialog (not described here, part of the
279 Kerberos specification) with the server. If this is
280 successful, the server responds with an AuthenticationOk,
281 otherwise it responds with an ErrorResponse.
287 <term>AuthenticationCleartextPassword</term>
290 The frontend must now send a PasswordMessage containing the
291 password in clear-text form. If
292 this is the correct password, the server responds with an
293 AuthenticationOk, otherwise it responds with an ErrorResponse.
299 <term>AuthenticationCryptPassword</term>
302 The frontend must now send a PasswordMessage containing the
303 password encrypted via crypt(3), using the 2-character salt
304 specified in the AuthenticationCryptPassword message. If
305 this is the correct password, the server responds with an
306 AuthenticationOk, otherwise it responds with an ErrorResponse.
312 <term>AuthenticationMD5Password</term>
315 The frontend must now send a PasswordMessage containing the
316 password encrypted via MD5, using the 4-character salt
317 specified in the AuthenticationMD5Password message. If
318 this is the correct password, the server responds with an
319 AuthenticationOk, otherwise it responds with an ErrorResponse.
325 <term>AuthenticationSCMCredential</term>
328 This response is only possible for local Unix-domain connections
329 on platforms that support SCM credential messages. The frontend
330 must issue an SCM credential message and then send a single data
331 byte. (The contents of the data byte are uninteresting; it's
332 only used to ensure that the server waits long enough to receive
333 the credential message.) If the credential is acceptable,
334 the server responds with an
335 AuthenticationOk, otherwise it responds with an ErrorResponse.
341 <term>AuthenticationGSS</term>
344 The frontend must now initiate a GSSAPI negotiation. The frontend
345 will send a PasswordMessage with the first part of the GSSAPI
346 data stream in response to this. If further messages are needed,
347 the server will respond with AuthenticationGSSContinue.
353 <term>AuthenticationSSPI</term>
356 The frontend must now initiate a SSPI negotiation. The frontend
357 will send a PasswordMessage with the first part of the SSPI
358 data stream in response to this. If further messages are needed,
359 the server will respond with AuthenticationGSSContinue.
365 <term>AuthenticationGSSContinue</term>
368 This message contains the response data from the previous step
369 of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI
370 or a previous AuthenticationGSSContinue). If the GSSAPI
371 or SSPI data in this message
372 indicates more data is needed to complete the authentication,
373 the frontend must send this data as another PasswordMessage. If
374 GSSAPI authentication is completed by this message, the server
375 will also send AuthenticationOk to indicate successful authentication
376 or ErrorResponse to indicate failure.
385 If the frontend does not support the authentication method
386 requested by the server, then it should immediately close the
391 After having received AuthenticationOk, the frontend must wait
392 for further messages from the server. In this phase a backend process
393 is being started, and the frontend is just an interested bystander.
394 It is still possible for the startup attempt
395 to fail (ErrorResponse), but in the normal case the backend will send
396 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
400 During this phase the backend will attempt to apply any additional
401 run-time parameter settings that were given in the startup message.
402 If successful, these values become session defaults. An error causes
403 ErrorResponse and exit.
407 The possible messages from the backend in this phase are:
411 <term>BackendKeyData</term>
414 This message provides secret-key data that the frontend must
415 save if it wants to be able to issue cancel requests later.
416 The frontend should not respond to this message, but should
417 continue listening for a ReadyForQuery message.
423 <term>ParameterStatus</term>
426 This message informs the frontend about the current (initial)
427 setting of backend parameters, such as <xref
428 linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
429 The frontend can ignore this message, or record the settings
430 for its future use; see <xref linkend="protocol-async"> for
431 more details. The frontend should not respond to this
432 message, but should continue listening for a ReadyForQuery
439 <term>ReadyForQuery</term>
442 Start-up is completed. The frontend can now issue commands.
448 <term>ErrorResponse</term>
451 Start-up failed. The connection is closed after sending this
458 <term>NoticeResponse</term>
461 A warning message has been issued. The frontend should
462 display the message but continue listening for ReadyForQuery
471 The ReadyForQuery message is the same one that the backend will
472 issue after each command cycle. Depending on the coding needs of
473 the frontend, it is reasonable to consider ReadyForQuery as
474 starting a command cycle, or to consider ReadyForQuery as ending the
475 start-up phase and each subsequent command cycle.
480 <title>Simple Query</title>
483 A simple query cycle is initiated by the frontend sending a Query message
484 to the backend. The message includes an SQL command (or commands)
485 expressed as a text string.
486 The backend then sends one or more response
487 messages depending on the contents of the query command string,
488 and finally a ReadyForQuery response message. ReadyForQuery
489 informs the frontend that it can safely send a new command.
490 (It is not actually necessary for the frontend to wait for
491 ReadyForQuery before issuing another command, but the frontend must
492 then take responsibility for figuring out what happens if the earlier
493 command fails and already-issued later commands succeed.)
497 The possible response messages from the backend are:
501 <term>CommandComplete</term>
504 An SQL command completed normally.
510 <term>CopyInResponse</term>
513 The backend is ready to copy data from the frontend to a
514 table; see <xref linkend="protocol-copy">.
520 <term>CopyOutResponse</term>
523 The backend is ready to copy data from a table to the
524 frontend; see <xref linkend="protocol-copy">.
530 <term>RowDescription</term>
533 Indicates that rows are about to be returned in response to
534 a <command>SELECT</command>, <command>FETCH</command>, etc query.
535 The contents of this message describe the column layout of the rows.
536 This will be followed by a DataRow message for each row being returned
546 One of the set of rows returned by
547 a <command>SELECT</command>, <command>FETCH</command>, etc query.
553 <term>EmptyQueryResponse</term>
556 An empty query string was recognized.
562 <term>ErrorResponse</term>
565 An error has occurred.
571 <term>ReadyForQuery</term>
574 Processing of the query string is complete. A separate
575 message is sent to indicate this because the query string might
576 contain multiple SQL commands. (CommandComplete marks the
577 end of processing one SQL command, not the whole string.)
578 ReadyForQuery will always be sent, whether processing
579 terminates successfully or with an error.
585 <term>NoticeResponse</term>
588 A warning message has been issued in relation to the query.
589 Notices are in addition to other responses, i.e., the backend
590 will continue processing the command.
599 The response to a <command>SELECT</> query (or other queries that
600 return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
601 normally consists of RowDescription, zero or more
602 DataRow messages, and then CommandComplete.
603 <command>COPY</> to or from the frontend invokes special protocol
604 as described in <xref linkend="protocol-copy">.
605 All other query types normally produce only
606 a CommandComplete message.
610 Since a query string could contain several queries (separated by
611 semicolons), there might be several such response sequences before the
612 backend finishes processing the query string. ReadyForQuery is issued
613 when the entire string has been processed and the backend is ready to
614 accept a new query string.
618 If a completely empty (no contents other than whitespace) query string
619 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
623 In the event of an error, ErrorResponse is issued followed by
624 ReadyForQuery. All further processing of the query string is aborted by
625 ErrorResponse (even if more queries remained in it). Note that this
626 might occur partway through the sequence of messages generated by an
631 In simple Query mode, the format of retrieved values is always text,
632 except when the given command is a <command>FETCH</> from a cursor
633 declared with the <literal>BINARY</> option. In that case, the
634 retrieved values are in binary format. The format codes given in
635 the RowDescription message tell which format is being used.
639 A frontend must be prepared to accept ErrorResponse and
640 NoticeResponse messages whenever it is expecting any other type of
641 message. See also <xref linkend="protocol-async"> concerning messages
642 that the backend might generate due to outside events.
646 Recommended practice is to code frontends in a state-machine style
647 that will accept any message type at any time that it could make sense,
648 rather than wiring in assumptions about the exact sequence of messages.
652 <sect2 id="protocol-flow-ext-query">
653 <title>Extended Query</title>
656 The extended query protocol breaks down the above-described simple
657 query protocol into multiple steps. The results of preparatory
658 steps can be re-used multiple times for improved efficiency.
659 Furthermore, additional features are available, such as the possibility
660 of supplying data values as separate parameters instead of having to
661 insert them directly into a query string.
665 In the extended protocol, the frontend first sends a Parse message,
666 which contains a textual query string, optionally some information
667 about data types of parameter placeholders, and the
668 name of a destination prepared-statement object (an empty string
669 selects the unnamed prepared statement). The response is
670 either ParseComplete or ErrorResponse. Parameter data types can be
671 specified by OID; if not given, the parser attempts to infer the
672 data types in the same way as it would do for untyped literal string
678 A parameter data type can be left unspecified by setting it to zero,
679 or by making the array of parameter type OIDs shorter than the
680 number of parameter symbols (<literal>$</><replaceable>n</>)
681 used in the query string. Another special case is that a parameter's
682 type can be specified as <type>void</> (that is, the OID of the
683 <type>void</> pseudotype). This is meant to allow parameter symbols
684 to be used for function parameters that are actually OUT parameters.
685 Ordinarily there is no context in which a <type>void</> parameter
686 could be used, but if such a parameter symbol appears in a function's
687 parameter list, it is effectively ignored. For example, a function
688 call such as <literal>foo($1,$2,$3,$4)</> could match a function with
689 two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
690 are specified as having type <type>void</>.
696 The query string contained in a Parse message cannot include more
697 than one SQL statement; else a syntax error is reported. This
698 restriction does not exist in the simple-query protocol, but it
699 does exist in the extended protocol, because allowing prepared
700 statements or portals to contain multiple commands would complicate
706 If successfully created, a named prepared-statement object lasts till
707 the end of the current session, unless explicitly destroyed. An unnamed
708 prepared statement lasts only until the next Parse statement specifying
709 the unnamed statement as destination is issued. (Note that a simple
710 Query message also destroys the unnamed statement.) Named prepared
711 statements must be explicitly closed before they can be redefined by
712 a Parse message, but this is not required for the unnamed statement.
713 Named prepared statements can also be created and accessed at the SQL
714 command level, using <command>PREPARE</> and <command>EXECUTE</>.
718 Once a prepared statement exists, it can be readied for execution using a
719 Bind message. The Bind message gives the name of the source prepared
720 statement (empty string denotes the unnamed prepared statement), the name
721 of the destination portal (empty string denotes the unnamed portal), and
722 the values to use for any parameter placeholders present in the prepared
724 supplied parameter set must match those needed by the prepared statement.
725 (If you declared any <type>void</> parameters in the Parse message,
726 pass NULL values for them in the Bind message.)
727 Bind also specifies the format to use for any data returned
728 by the query; the format can be specified overall, or per-column.
729 The response is either BindComplete or ErrorResponse.
734 The choice between text and binary output is determined by the format
735 codes given in Bind, regardless of the SQL command involved. The
736 <literal>BINARY</> attribute in cursor declarations is irrelevant when
737 using extended query protocol.
742 Query planning for named prepared-statement objects occurs when the Parse
743 message is processed. If a query will be repeatedly executed with
744 different parameters, it might be beneficial to send a single Parse message
745 containing a parameterized query, followed by multiple Bind
746 and Execute messages. This will avoid replanning the query on each
751 The unnamed prepared statement is likewise planned during Parse processing
752 if the Parse message defines no parameters. But if there are parameters,
753 query planning occurs during Bind processing instead. This allows the
754 planner to make use of the actual values of the parameters provided in
755 the Bind message when planning the query.
760 Query plans generated from a parameterized query might be less
761 efficient than query plans generated from an equivalent query with actual
762 parameter values substituted. The query planner cannot make decisions
763 based on actual parameter values (for example, index selectivity) when
764 planning a parameterized query assigned to a named prepared-statement
765 object. This possible penalty is avoided when using the unnamed
766 statement, since it is not planned until actual parameter values are
767 available. The cost is that planning must occur afresh for each Bind,
768 even if the query stays the same.
773 If successfully created, a named portal object lasts till the end of the
774 current transaction, unless explicitly destroyed. An unnamed portal is
775 destroyed at the end of the transaction, or as soon as the next Bind
776 statement specifying the unnamed portal as destination is issued. (Note
777 that a simple Query message also destroys the unnamed portal.) Named
778 portals must be explicitly closed before they can be redefined by a Bind
779 message, but this is not required for the unnamed portal.
780 Named portals can also be created and accessed at the SQL
781 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
785 Once a portal exists, it can be executed using an Execute message.
786 The Execute message specifies the portal name (empty string denotes the
788 a maximum result-row count (zero meaning <quote>fetch all rows</>).
789 The result-row count is only meaningful for portals
790 containing commands that return row sets; in other cases the command is
791 always executed to completion, and the row count is ignored.
793 responses to Execute are the same as those described above for queries
794 issued via simple query protocol, except that Execute doesn't cause
795 ReadyForQuery or RowDescription to be issued.
799 If Execute terminates before completing the execution of a portal
800 (due to reaching a nonzero result-row count), it will send a
801 PortalSuspended message; the appearance of this message tells the frontend
802 that another Execute should be issued against the same portal to
803 complete the operation. The CommandComplete message indicating
804 completion of the source SQL command is not sent until
805 the portal's execution is completed. Therefore, an Execute phase is
806 always terminated by the appearance of exactly one of these messages:
807 CommandComplete, EmptyQueryResponse (if the portal was created from
808 an empty query string), ErrorResponse, or PortalSuspended.
812 At completion of each series of extended-query messages, the frontend
813 should issue a Sync message. This parameterless message causes the
814 backend to close the current transaction if it's not inside a
815 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
816 meaning to commit if no error, or roll back if error). Then a
817 ReadyForQuery response is issued. The purpose of Sync is to provide
818 a resynchronization point for error recovery. When an error is detected
819 while processing any extended-query message, the backend issues
820 ErrorResponse, then reads and discards messages until a Sync is reached,
821 then issues ReadyForQuery and returns to normal message processing.
822 (But note that no skipping occurs if an error is detected
823 <emphasis>while</> processing Sync — this ensures that there is one
824 and only one ReadyForQuery sent for each Sync.)
829 Sync does not cause a transaction block opened with <command>BEGIN</>
830 to be closed. It is possible to detect this situation since the
831 ReadyForQuery message includes transaction status information.
836 In addition to these fundamental, required operations, there are several
837 optional operations that can be used with extended-query protocol.
841 The Describe message (portal variant) specifies the name of an existing
842 portal (or an empty string for the unnamed portal). The response is a
843 RowDescription message describing the rows that will be returned by
844 executing the portal; or a NoData message if the portal does not contain a
845 query that will return rows; or ErrorResponse if there is no such portal.
849 The Describe message (statement variant) specifies the name of an existing
850 prepared statement (or an empty string for the unnamed prepared
851 statement). The response is a ParameterDescription message describing the
852 parameters needed by the statement, followed by a RowDescription message
853 describing the rows that will be returned when the statement is eventually
854 executed (or a NoData message if the statement will not return rows).
855 ErrorResponse is issued if there is no such prepared statement. Note that
856 since Bind has not yet been issued, the formats to be used for returned
857 columns are not yet known to the backend; the format code fields in the
858 RowDescription message will be zeroes in this case.
863 In most scenarios the frontend should issue one or the other variant
864 of Describe before issuing Execute, to ensure that it knows how to
865 interpret the results it will get back.
870 The Close message closes an existing prepared statement or portal
871 and releases resources. It is not an error to issue Close against
872 a nonexistent statement or portal name. The response is normally
873 CloseComplete, but could be ErrorResponse if some difficulty is
874 encountered while releasing resources. Note that closing a prepared
875 statement implicitly closes any open portals that were constructed
880 The Flush message does not cause any specific output to be generated,
881 but forces the backend to deliver any data pending in its output
882 buffers. A Flush must be sent after any extended-query command except
883 Sync, if the frontend wishes to examine the results of that command before
884 issuing more commands. Without Flush, messages returned by the backend
885 will be combined into the minimum possible number of packets to minimize
891 The simple Query message is approximately equivalent to the series Parse,
892 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
893 statement and portal objects and no parameters. One difference is that
894 it will accept multiple SQL statements in the query string, automatically
895 performing the bind/describe/execute sequence for each one in succession.
896 Another difference is that it will not return ParseComplete, BindComplete,
897 CloseComplete, or NoData messages.
903 <title>Function Call</title>
906 The Function Call sub-protocol allows the client to request a direct
907 call of any function that exists in the database's
908 <structname>pg_proc</structname> system catalog. The client must have
909 execute permission for the function.
914 The Function Call sub-protocol is a legacy feature that is probably best
915 avoided in new code. Similar results can be accomplished by setting up
916 a prepared statement that does <literal>SELECT function($1, ...)</>.
917 The Function Call cycle can then be replaced with Bind/Execute.
922 A Function Call cycle is initiated by the frontend sending a
923 FunctionCall message to the backend. The backend then sends one
924 or more response messages depending on the results of the function
925 call, and finally a ReadyForQuery response message. ReadyForQuery
926 informs the frontend that it can safely send a new query or
931 The possible response messages from the backend are:
935 <term>ErrorResponse</term>
938 An error has occurred.
944 <term>FunctionCallResponse</term>
947 The function call was completed and returned the result given
949 (Note that the Function Call protocol can only handle a single
950 scalar result, not a row type or set of results.)
956 <term>ReadyForQuery</term>
959 Processing of the function call is complete. ReadyForQuery
960 will always be sent, whether processing terminates
961 successfully or with an error.
967 <term>NoticeResponse</term>
970 A warning message has been issued in relation to the function
971 call. Notices are in addition to other responses, i.e., the
972 backend will continue processing the command.
980 <sect2 id="protocol-copy">
981 <title>COPY Operations</title>
984 The <command>COPY</> command allows high-speed bulk data transfer
985 to or from the server. Copy-in and copy-out operations each switch
986 the connection into a distinct sub-protocol, which lasts until the
987 operation is completed.
991 Copy-in mode (data transfer to the server) is initiated when the
992 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
993 sends a CopyInResponse message to the frontend. The frontend should
994 then send zero or more CopyData messages, forming a stream of input
995 data. (The message boundaries are not required to have anything to do
996 with row boundaries, although that is often a reasonable choice.)
997 The frontend can terminate the copy-in mode by sending either a CopyDone
998 message (allowing successful termination) or a CopyFail message (which
999 will cause the <command>COPY</> SQL statement to fail with an
1000 error). The backend then reverts to the command-processing mode it was
1001 in before the <command>COPY</> started, which will be either simple or
1002 extended query protocol. It will next send either CommandComplete
1003 (if successful) or ErrorResponse (if not).
1007 In the event of a backend-detected error during copy-in mode (including
1008 receipt of a CopyFail message), the backend will issue an ErrorResponse
1009 message. If the <command>COPY</> command was issued via an extended-query
1010 message, the backend will now discard frontend messages until a Sync
1011 message is received, then it will issue ReadyForQuery and return to normal
1012 processing. If the <command>COPY</> command was issued in a simple
1013 Query message, the rest of that message is discarded and ReadyForQuery
1014 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
1015 messages issued by the frontend will simply be dropped.
1019 The backend will ignore Flush and Sync messages received during copy-in
1020 mode. Receipt of any other non-copy message type constitutes an error
1021 that will abort the copy-in state as described above. (The exception for
1022 Flush and Sync is for the convenience of client libraries that always
1023 send Flush or Sync after an Execute message, without checking whether
1024 the command to be executed is a <command>COPY FROM STDIN</>.)
1028 Copy-out mode (data transfer from the server) is initiated when the
1029 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
1030 sends a CopyOutResponse message to the frontend, followed by
1031 zero or more CopyData messages (always one per row), followed by CopyDone.
1032 The backend then reverts to the command-processing mode it was
1033 in before the <command>COPY</> started, and sends CommandComplete.
1034 The frontend cannot abort the transfer (except by closing the connection
1035 or issuing a Cancel request),
1036 but it can discard unwanted CopyData and CopyDone messages.
1040 In the event of a backend-detected error during copy-out mode,
1041 the backend will issue an ErrorResponse message and revert to normal
1042 processing. The frontend should treat receipt of ErrorResponse as
1043 terminating the copy-out mode.
1047 It is possible for NoticeResponse messages to be interspersed between
1048 CopyData messages; frontends must handle this case, and should be
1049 prepared for other asynchronous message types as well (see <xref
1050 linkend="protocol-async">). Otherwise, any message type other than
1051 CopyData or CopyDone may be treated as terminating copy-out mode.
1055 The CopyInResponse and CopyOutResponse messages include fields that
1056 inform the frontend of the number of columns per row and the format
1057 codes being used for each column. (As of the present implementation,
1058 all columns in a given <command>COPY</> operation will use the same
1059 format, but the message design does not assume this.)
1063 <sect2 id="protocol-async">
1064 <title>Asynchronous Operations</title>
1067 There are several cases in which the backend will send messages that
1068 are not specifically prompted by the frontend's command stream.
1069 Frontends must be prepared to deal with these messages at any time,
1070 even when not engaged in a query.
1071 At minimum, one should check for these cases before beginning to
1072 read a query response.
1076 It is possible for NoticeResponse messages to be generated due to
1077 outside activity; for example, if the database administrator commands
1078 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1079 indicating this fact before closing the connection. Accordingly,
1080 frontends should always be prepared to accept and display NoticeResponse
1081 messages, even when the connection is nominally idle.
1085 ParameterStatus messages will be generated whenever the active
1086 value changes for any of the parameters the backend believes the
1087 frontend should know about. Most commonly this occurs in response
1088 to a <command>SET</> SQL command executed by the frontend, and
1089 this case is effectively synchronous — but it is also possible
1090 for parameter status changes to occur because the administrator
1091 changed a configuration file and then sent the
1092 <systemitem>SIGHUP</systemitem> signal to the server. Also,
1093 if a <command>SET</command> command is rolled back, an appropriate
1094 ParameterStatus message will be generated to report the current
1099 At present there is a hard-wired set of parameters for which
1100 ParameterStatus will be generated: they are
1101 <literal>server_version</>,
1102 <literal>server_encoding</>,
1103 <literal>client_encoding</>,
1104 <literal>is_superuser</>,
1105 <literal>session_authorization</>,
1106 <literal>DateStyle</>,
1107 <literal>TimeZone</>,
1108 <literal>integer_datetimes</>, and
1109 <literal>standard_conforming_strings</>.
1110 (<literal>server_encoding</>, <literal>TimeZone</>, and
1111 <literal>integer_datetimes</> were not reported by releases before 8.0;
1112 <literal>standard_conforming_strings</> was not reported by releases
1115 <literal>server_version</>,
1116 <literal>server_encoding</> and
1117 <literal>integer_datetimes</>
1118 are pseudo-parameters that cannot change after startup.
1119 This set might change in the future, or even become configurable.
1120 Accordingly, a frontend should simply ignore ParameterStatus for
1121 parameters that it does not understand or care about.
1125 If a frontend issues a <command>LISTEN</command> command, then the
1126 backend will send a NotificationResponse message (not to be
1127 confused with NoticeResponse!) whenever a
1128 <command>NOTIFY</command> command is executed for the same
1134 At present, NotificationResponse can only be sent outside a
1135 transaction, and thus it will not occur in the middle of a
1136 command-response series, though it might occur just before ReadyForQuery.
1137 It is unwise to design frontend logic that assumes that, however.
1138 Good practice is to be able to accept NotificationResponse at any
1139 point in the protocol.
1145 <title>Cancelling Requests in Progress</title>
1148 During the processing of a query, the frontend might request
1149 cancellation of the query. The cancel request is not sent
1150 directly on the open connection to the backend for reasons of
1151 implementation efficiency: we don't want to have the backend
1152 constantly checking for new input from the frontend during query
1153 processing. Cancel requests should be relatively infrequent, so
1154 we make them slightly cumbersome in order to avoid a penalty in
1159 To issue a cancel request, the frontend opens a new connection to
1160 the server and sends a CancelRequest message, rather than the
1161 StartupMessage message that would ordinarily be sent across a new
1162 connection. The server will process this request and then close
1163 the connection. For security reasons, no direct reply is made to
1164 the cancel request message.
1168 A CancelRequest message will be ignored unless it contains the
1169 same key data (PID and secret key) passed to the frontend during
1170 connection start-up. If the request matches the PID and secret
1171 key for a currently executing backend, the processing of the
1172 current query is aborted. (In the existing implementation, this is
1173 done by sending a special signal to the backend process that is
1174 processing the query.)
1178 The cancellation signal might or might not have any effect — for
1179 example, if it arrives after the backend has finished processing
1180 the query, then it will have no effect. If the cancellation is
1181 effective, it results in the current command being terminated
1182 early with an error message.
1186 The upshot of all this is that for reasons of both security and
1187 efficiency, the frontend has no direct way to tell whether a
1188 cancel request has succeeded. It must continue to wait for the
1189 backend to respond to the query. Issuing a cancel simply improves
1190 the odds that the current query will finish soon, and improves the
1191 odds that it will fail with an error message instead of
1196 Since the cancel request is sent across a new connection to the
1197 server and not across the regular frontend/backend communication
1198 link, it is possible for the cancel request to be issued by any
1199 process, not just the frontend whose query is to be canceled.
1200 This might provide additional flexibility when building
1201 multiple-process applications. It also introduces a security
1202 risk, in that unauthorized persons might try to cancel queries.
1203 The security risk is addressed by requiring a dynamically
1204 generated secret key to be supplied in cancel requests.
1209 <title>Termination</title>
1212 The normal, graceful termination procedure is that the frontend
1213 sends a Terminate message and immediately closes the connection.
1214 On receipt of this message, the backend closes the connection and
1219 In rare cases (such as an administrator-commanded database shutdown)
1220 the backend might disconnect without any frontend request to do so.
1221 In such cases the backend will attempt to send an error or notice message
1222 giving the reason for the disconnection before it closes the connection.
1226 Other termination scenarios arise from various failure cases, such as core
1227 dump at one end or the other, loss of the communications link, loss of
1228 message-boundary synchronization, etc. If either frontend or backend sees
1229 an unexpected closure of the connection, it should clean
1230 up and terminate. The frontend has the option of launching a new backend
1231 by recontacting the server if it doesn't want to terminate itself.
1232 Closing the connection is also advisable if an unrecognizable message type
1233 is received, since this probably indicates loss of message-boundary sync.
1237 For either normal or abnormal termination, any open transaction is
1238 rolled back, not committed. One should note however that if a
1239 frontend disconnects while a non-<command>SELECT</command> query
1240 is being processed, the backend will probably finish the query
1241 before noticing the disconnection. If the query is outside any
1242 transaction block (<command>BEGIN</> ... <command>COMMIT</>
1243 sequence) then its results might be committed before the
1244 disconnection is recognized.
1249 <title><acronym>SSL</acronym> Session Encryption</title>
1252 If <productname>PostgreSQL</> was built with
1253 <acronym>SSL</acronym> support, frontend/backend communications
1254 can be encrypted using <acronym>SSL</acronym>. This provides
1255 communication security in environments where attackers might be
1256 able to capture the session traffic. For more information on
1257 encrypting <productname>PostgreSQL</productname> sessions with
1258 <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1262 To initiate an <acronym>SSL</acronym>-encrypted connection, the
1263 frontend initially sends an SSLRequest message rather than a
1264 StartupMessage. The server then responds with a single byte
1265 containing <literal>S</> or <literal>N</>, indicating that it is
1266 willing or unwilling to perform <acronym>SSL</acronym>,
1267 respectively. The frontend might close the connection at this point
1268 if it is dissatisfied with the response. To continue after
1269 <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1270 (not described here, part of the <acronym>SSL</acronym>
1271 specification) with the server. If this is successful, continue
1272 with sending the usual StartupMessage. In this case the
1273 StartupMessage and all subsequent data will be
1274 <acronym>SSL</acronym>-encrypted. To continue after
1275 <literal>N</>, send the usual StartupMessage and proceed without
1280 The frontend should also be prepared to handle an ErrorMessage
1281 response to SSLRequest from the server. This would only occur if
1282 the server predates the addition of <acronym>SSL</acronym> support
1283 to <productname>PostgreSQL</>. In this case the connection must
1284 be closed, but the frontend might choose to open a fresh connection
1285 and proceed without requesting <acronym>SSL</acronym>.
1289 An initial SSLRequest can also be used in a connection that is being
1290 opened to send a CancelRequest message.
1294 While the protocol itself does not provide a way for the server to
1295 force <acronym>SSL</acronym> encryption, the administrator can
1296 configure the server to reject unencrypted sessions as a byproduct
1297 of authentication checking.
1302 <sect1 id="protocol-message-types">
1303 <title>Message Data Types</title>
1306 This section describes the base data types used in messages.
1312 Int<replaceable>n</replaceable>(<replaceable>i</replaceable>)
1316 An <replaceable>n</replaceable>-bit integer in network byte
1317 order (most significant byte first).
1318 If <replaceable>i</replaceable> is specified it
1319 is the exact value that will appear, otherwise the value
1320 is variable. Eg. Int16, Int32(42).
1327 Int<replaceable>n</replaceable>[<replaceable>k</replaceable>]
1331 An array of <replaceable>k</replaceable>
1332 <replaceable>n</replaceable>-bit integers, each in network
1333 byte order. The array length <replaceable>k</replaceable>
1334 is always determined by an earlier field in the message.
1342 String(<replaceable>s</replaceable>)
1346 A null-terminated string (C-style string). There is no
1347 specific length limitation on strings.
1348 If <replaceable>s</replaceable> is specified it is the exact
1349 value that will appear, otherwise the value is variable.
1350 Eg. String, String("user").
1355 <emphasis>There is no predefined limit</emphasis> on the length of a string
1356 that can be returned by the backend. Good coding strategy for a frontend
1357 is to use an expandable buffer so that anything that fits in memory can be
1358 accepted. If that's not feasible, read the full string and discard trailing
1359 characters that don't fit into your fixed-size buffer.
1367 Byte<replaceable>n</replaceable>(<replaceable>c</replaceable>)
1371 Exactly <replaceable>n</replaceable> bytes. If the field
1372 width <replaceable>n</replaceable> is not a constant, it is
1373 always determinable from an earlier field in the message.
1374 If <replaceable>c</replaceable> is specified it is the exact
1375 value. Eg. Byte2, Byte1('\n').
1384 <sect1 id="protocol-message-formats">
1385 <title>Message Formats</title>
1388 This section describes the detailed format of each message. Each is marked to
1389 indicate that it can be sent by a frontend (F), a backend (B), or both
1391 Notice that although each message includes a byte count at the beginning,
1392 the message format is defined so that the message end can be found without
1393 reference to the byte count. This aids validity checking. (The CopyData
1394 message is an exception, because it forms part of a data stream; the contents
1395 of any individual CopyData message cannot be interpretable on their own.)
1403 AuthenticationOk (B)
1415 Identifies the message as an authentication request.
1425 Length of message contents in bytes, including self.
1435 Specifies that the authentication was successful.
1448 AuthenticationKerberosV5 (B)
1460 Identifies the message as an authentication request.
1470 Length of message contents in bytes, including self.
1480 Specifies that Kerberos V5 authentication is required.
1492 AuthenticationCleartextPassword (B)
1504 Identifies the message as an authentication request.
1514 Length of message contents in bytes, including self.
1524 Specifies that a clear-text password is required.
1536 AuthenticationCryptPassword (B)
1548 Identifies the message as an authentication request.
1558 Length of message contents in bytes, including self.
1568 Specifies that a crypt()-encrypted password is required.
1578 The salt to use when encrypting the password.
1591 AuthenticationMD5Password (B)
1603 Identifies the message as an authentication request.
1613 Length of message contents in bytes, including self.
1623 Specifies that an MD5-encrypted password is required.
1633 The salt to use when encrypting the password.
1646 AuthenticationSCMCredential (B)
1658 Identifies the message as an authentication request.
1668 Length of message contents in bytes, including self.
1678 Specifies that an SCM credentials message is required.
1691 AuthenticationGSS (B)
1703 Identifies the message as an authentication request.
1713 Length of message contents in bytes, including self.
1723 Specifies that GSSAPI authentication is required.
1736 AuthenticationSSPI (B)
1748 Identifies the message as an authentication request.
1758 Length of message contents in bytes, including self.
1768 Specifies that SSPI authentication is required.
1779 AuthenticationGSSContinue (B)
1791 Identifies the message as an authentication request.
1801 Length of message contents in bytes, including self.
1811 Specifies that this message contains GSSAPI data.
1817 Byte<replaceable>n</replaceable>
1821 GSSAPI or SSPI authentication data.
1846 Identifies the message as cancellation key data.
1847 The frontend must save these values if it wishes to be
1848 able to issue CancelRequest messages later.
1858 Length of message contents in bytes, including self.
1868 The process ID of this backend.
1878 The secret key of this backend.
1903 Identifies the message as a Bind command.
1913 Length of message contents in bytes, including self.
1923 The name of the destination portal
1924 (an empty string selects the unnamed portal).
1934 The name of the source prepared statement
1935 (an empty string selects the unnamed prepared statement).
1945 The number of parameter format codes that follow
1946 (denoted <replaceable>C</> below).
1947 This can be zero to indicate that there are no parameters
1948 or that the parameters all use the default format (text);
1949 or one, in which case the specified format code is applied
1950 to all parameters; or it can equal the actual number of
1957 Int16[<replaceable>C</>]
1961 The parameter format codes. Each must presently be
1962 zero (text) or one (binary).
1972 The number of parameter values that follow (possibly zero).
1973 This must match the number of parameters needed by the query.
1978 Next, the following pair of fields appear for each parameter:
1986 The length of the parameter value, in bytes (this count
1987 does not include itself). Can be zero.
1988 As a special case, -1 indicates a NULL parameter value.
1989 No value bytes follow in the NULL case.
1995 Byte<replaceable>n</replaceable>
1999 The value of the parameter, in the format indicated by the
2000 associated format code.
2001 <replaceable>n</replaceable> is the above length.
2006 After the last parameter, the following fields appear:
2014 The number of result-column format codes that follow
2015 (denoted <replaceable>R</> below).
2016 This can be zero to indicate that there are no result columns
2017 or that the result columns should all use the default format
2019 or one, in which case the specified format code is applied
2020 to all result columns (if any); or it can equal the actual
2021 number of result columns of the query.
2027 Int16[<replaceable>R</>]
2031 The result-column format codes. Each must presently be
2032 zero (text) or one (binary).
2056 Identifies the message as a Bind-complete indicator.
2066 Length of message contents in bytes, including self.
2091 Length of message contents in bytes, including self.
2101 The cancel request code. The value is chosen to contain
2102 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
2103 least 16 significant bits. (To avoid confusion, this code
2104 must not be the same as any protocol version number.)
2114 The process ID of the target backend.
2124 The secret key for the target backend.
2149 Identifies the message as a Close command.
2159 Length of message contents in bytes, including self.
2169 '<literal>S</>' to close a prepared statement; or
2170 '<literal>P</>' to close a portal.
2180 The name of the prepared statement or portal to close
2181 (an empty string selects the unnamed prepared statement
2206 Identifies the message as a Close-complete indicator.
2216 Length of message contents in bytes, including self.
2241 Identifies the message as a command-completed response.
2251 Length of message contents in bytes, including self.
2261 The command tag. This is usually a single
2262 word that identifies which SQL command was completed.
2266 For an <command>INSERT</command> command, the tag is
2267 <literal>INSERT <replaceable>oid</replaceable>
2268 <replaceable>rows</replaceable></literal>, where
2269 <replaceable>rows</replaceable> is the number of rows
2270 inserted. <replaceable>oid</replaceable> is the object ID
2271 of the inserted row if <replaceable>rows</replaceable> is 1
2272 and the target table has OIDs;
2273 otherwise <replaceable>oid</replaceable> is 0.
2277 For a <command>DELETE</command> command, the tag is
2278 <literal>DELETE <replaceable>rows</replaceable></literal> where
2279 <replaceable>rows</replaceable> is the number of rows deleted.
2283 For an <command>UPDATE</command> command, the tag is
2284 <literal>UPDATE <replaceable>rows</replaceable></literal> where
2285 <replaceable>rows</replaceable> is the number of rows updated.
2289 For a <command>MOVE</command> command, the tag is
2290 <literal>MOVE <replaceable>rows</replaceable></literal> where
2291 <replaceable>rows</replaceable> is the number of rows the
2292 cursor's position has been changed by.
2296 For a <command>FETCH</command> command, the tag is
2297 <literal>FETCH <replaceable>rows</replaceable></literal> where
2298 <replaceable>rows</replaceable> is the number of rows that
2299 have been retrieved from the cursor.
2303 For a <command>COPY</command> command, the tag is
2304 <literal>COPY <replaceable>rows</replaceable></literal> where
2305 <replaceable>rows</replaceable> is the number of rows copied.
2306 (Note: the row count appears only in
2307 <productname>PostgreSQL</productname> 8.2 and later.)
2321 CopyData (F & B)
2332 Identifies the message as <command>COPY</command> data.
2342 Length of message contents in bytes, including self.
2348 Byte<replaceable>n</replaceable>
2352 Data that forms part of a <command>COPY</command> data stream. Messages sent
2353 from the backend will always correspond to single data rows,
2354 but messages sent by frontends might divide the data stream
2367 CopyDone (F & B)
2379 Identifies the message as a <command>COPY</command>-complete indicator.
2389 Length of message contents in bytes, including self.
2414 Identifies the message as a <command>COPY</command>-failure indicator.
2424 Length of message contents in bytes, including self.
2434 An error message to report as the cause of failure.
2459 Identifies the message as a Start Copy In response.
2460 The frontend must now send copy-in data (if not
2461 prepared to do so, send a CopyFail message).
2471 Length of message contents in bytes, including self.
2481 0 indicates the overall <command>COPY</command> format is textual (rows
2482 separated by newlines, columns separated by separator
2484 1 indicates the overall copy format is binary (similar
2486 See <xref linkend="sql-copy" endterm="sql-copy-title">
2487 for more information.
2497 The number of columns in the data to be copied
2498 (denoted <replaceable>N</> below).
2504 Int16[<replaceable>N</>]
2508 The format codes to be used for each column.
2509 Each must presently be zero (text) or one (binary).
2510 All must be zero if the overall copy format is textual.
2535 Identifies the message as a Start Copy Out response.
2536 This message will be followed by copy-out data.
2546 Length of message contents in bytes, including self.
2556 0 indicates the overall <command>COPY</command> format
2557 is textual (rows separated by newlines, columns
2558 separated by separator characters, etc). 1 indicates
2559 the overall copy format is binary (similar to DataRow
2560 format). See <xref linkend="sql-copy"
2561 endterm="sql-copy-title"> for more information.
2571 The number of columns in the data to be copied
2572 (denoted <replaceable>N</> below).
2578 Int16[<replaceable>N</>]
2582 The format codes to be used for each column.
2583 Each must presently be zero (text) or one (binary).
2584 All must be zero if the overall copy format is textual.
2608 Identifies the message as a data row.
2618 Length of message contents in bytes, including self.
2628 The number of column values that follow (possibly zero).
2633 Next, the following pair of fields appear for each column:
2641 The length of the column value, in bytes (this count
2642 does not include itself). Can be zero.
2643 As a special case, -1 indicates a NULL column value.
2644 No value bytes follow in the NULL case.
2650 Byte<replaceable>n</replaceable>
2654 The value of the column, in the format indicated by the
2655 associated format code.
2656 <replaceable>n</replaceable> is the above length.
2681 Identifies the message as a Describe command.
2691 Length of message contents in bytes, including self.
2701 '<literal>S</>' to describe a prepared statement; or
2702 '<literal>P</>' to describe a portal.
2712 The name of the prepared statement or portal to describe
2713 (an empty string selects the unnamed prepared statement
2726 EmptyQueryResponse (B)
2738 Identifies the message as a response to an empty query string.
2739 (This substitutes for CommandComplete.)
2749 Length of message contents in bytes, including self.
2774 Identifies the message as an error.
2784 Length of message contents in bytes, including self.
2789 The message body consists of one or more identified fields,
2790 followed by a zero byte as a terminator. Fields can appear in
2791 any order. For each field there is the following:
2799 A code identifying the field type; if zero, this is
2800 the message terminator and no string follows.
2801 The presently defined field types are listed in
2802 <xref linkend="protocol-error-fields">.
2803 Since more field types might be added in future,
2804 frontends should silently ignore fields of unrecognized
2840 Identifies the message as an Execute command.
2850 Length of message contents in bytes, including self.
2860 The name of the portal to execute
2861 (an empty string selects the unnamed portal).
2871 Maximum number of rows to return, if portal contains
2872 a query that returns rows (ignored otherwise). Zero
2873 denotes <quote>no limit</>.
2897 Identifies the message as a Flush command.
2907 Length of message contents in bytes, including self.
2932 Identifies the message as a function call.
2942 Length of message contents in bytes, including self.
2952 Specifies the object ID of the function to call.
2962 The number of argument format codes that follow
2963 (denoted <replaceable>C</> below).
2964 This can be zero to indicate that there are no arguments
2965 or that the arguments all use the default format (text);
2966 or one, in which case the specified format code is applied
2967 to all arguments; or it can equal the actual number of
2974 Int16[<replaceable>C</>]
2978 The argument format codes. Each must presently be
2979 zero (text) or one (binary).
2989 Specifies the number of arguments being supplied to the
2995 Next, the following pair of fields appear for each argument:
3003 The length of the argument value, in bytes (this count
3004 does not include itself). Can be zero.
3005 As a special case, -1 indicates a NULL argument value.
3006 No value bytes follow in the NULL case.
3012 Byte<replaceable>n</replaceable>
3016 The value of the argument, in the format indicated by the
3017 associated format code.
3018 <replaceable>n</replaceable> is the above length.
3023 After the last argument, the following field appears:
3031 The format code for the function result. Must presently be
3032 zero (text) or one (binary).
3045 FunctionCallResponse (B)
3057 Identifies the message as a function call result.
3067 Length of message contents in bytes, including self.
3077 The length of the function result value, in bytes (this count
3078 does not include itself). Can be zero.
3079 As a special case, -1 indicates a NULL function result.
3080 No value bytes follow in the NULL case.
3086 Byte<replaceable>n</replaceable>
3090 The value of the function result, in the format indicated by
3091 the associated format code.
3092 <replaceable>n</replaceable> is the above length.
3117 Identifies the message as a no-data indicator.
3127 Length of message contents in bytes, including self.
3152 Identifies the message as a notice.
3162 Length of message contents in bytes, including self.
3167 The message body consists of one or more identified fields,
3168 followed by a zero byte as a terminator. Fields can appear in
3169 any order. For each field there is the following:
3177 A code identifying the field type; if zero, this is
3178 the message terminator and no string follows.
3179 The presently defined field types are listed in
3180 <xref linkend="protocol-error-fields">.
3181 Since more field types might be added in future,
3182 frontends should silently ignore fields of unrecognized
3206 NotificationResponse (B)
3218 Identifies the message as a notification response.
3228 Length of message contents in bytes, including self.
3238 The process ID of the notifying backend process.
3248 The name of the condition that the notify has been raised on.
3258 Additional information passed from the notifying process.
3259 (Currently, this feature is unimplemented so the field
3260 is always an empty string.)
3273 ParameterDescription (B)
3285 Identifies the message as a parameter description.
3295 Length of message contents in bytes, including self.
3305 The number of parameters used by the statement
3311 Then, for each parameter, there is the following:
3319 Specifies the object ID of the parameter data type.
3343 Identifies the message as a run-time parameter status report.
3353 Length of message contents in bytes, including self.
3363 The name of the run-time parameter being reported.
3373 The current value of the parameter.
3397 Identifies the message as a Parse command.
3407 Length of message contents in bytes, including self.
3417 The name of the destination prepared statement
3418 (an empty string selects the unnamed prepared statement).
3428 The query string to be parsed.
3438 The number of parameter data types specified
3439 (can be zero). Note that this is not an indication of
3440 the number of parameters that might appear in the
3441 query string, only the number that the frontend wants to
3442 prespecify types for.
3447 Then, for each parameter, there is the following:
3455 Specifies the object ID of the parameter data type.
3456 Placing a zero here is equivalent to leaving the type
3481 Identifies the message as a Parse-complete indicator.
3491 Length of message contents in bytes, including self.
3516 Identifies the message as a password response. Note that
3517 this is also used by GSSAPI response messages.
3527 Length of message contents in bytes, including self.
3537 The password (encrypted, if requested).
3561 Identifies the message as a portal-suspended indicator.
3562 Note this only appears if an Execute message's row-count limit
3573 Length of message contents in bytes, including self.
3598 Identifies the message as a simple query.
3608 Length of message contents in bytes, including self.
3618 The query string itself.
3643 Identifies the message type. ReadyForQuery is sent
3644 whenever the backend is ready for a new query cycle.
3654 Length of message contents in bytes, including self.
3664 Current backend transaction status indicator.
3665 Possible values are '<literal>I</>' if idle (not in
3666 a transaction block); '<literal>T</>' if in a transaction
3667 block; or '<literal>E</>' if in a failed transaction
3668 block (queries will be rejected until block is ended).
3693 Identifies the message as a row description.
3703 Length of message contents in bytes, including self.
3713 Specifies the number of fields in a row (can be zero).
3718 Then, for each field, there is the following:
3736 If the field can be identified as a column of a specific
3737 table, the object ID of the table; otherwise zero.
3747 If the field can be identified as a column of a specific
3748 table, the attribute number of the column; otherwise zero.
3758 The object ID of the field's data type.
3768 The data type size (see <varname>pg_type.typlen</>).
3769 Note that negative values denote variable-width types.
3779 The type modifier (see <varname>pg_attribute.atttypmod</>).
3780 The meaning of the modifier is type-specific.
3790 The format code being used for the field. Currently will
3791 be zero (text) or one (binary). In a RowDescription
3792 returned from the statement variant of Describe, the
3793 format code is not yet known and will always be zero.
3818 Length of message contents in bytes, including self.
3828 The <acronym>SSL</acronym> request code. The value is chosen to contain
3829 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
3830 least 16 significant bits. (To avoid confusion, this code
3831 must not be the same as any protocol version number.)
3856 Length of message contents in bytes, including self.
3866 The protocol version number. The most significant 16 bits are
3867 the major version number (3 for the protocol described here).
3868 The least significant 16 bits are the minor version number
3869 (0 for the protocol described here).
3874 The protocol version number is followed by one or more pairs of
3875 parameter name and value strings. A zero byte is required as a
3876 terminator after the last name/value pair.
3877 Parameters can appear in any
3878 order. <literal>user</> is required, others are optional.
3879 Each parameter is specified as:
3887 The parameter name. Currently recognized names are:
3896 The database user name to connect as. Required;
3897 there is no default.
3903 <literal>database</>
3907 The database to connect to. Defaults to the user name.
3917 Command-line arguments for the backend. (This is
3918 deprecated in favor of setting individual run-time
3925 In addition to the above, any run-time parameter that can be
3926 set at backend start time might be listed. Such settings
3927 will be applied during backend start (after parsing the
3928 command-line options if any). The values will act as
3939 The parameter value.
3964 Identifies the message as a Sync command.
3974 Length of message contents in bytes, including self.
3999 Identifies the message as a termination.
4009 Length of message contents in bytes, including self.
4025 <sect1 id="protocol-error-fields">
4026 <title>Error and Notice Message Fields</title>
4029 This section describes the fields that can appear in ErrorResponse and
4030 NoticeResponse messages. Each field type has a single-byte identification
4031 token. Note that any given field type should appear at most once per
4043 Severity: the field contents are
4044 <literal>ERROR</>, <literal>FATAL</>, or
4045 <literal>PANIC</> (in an error message), or
4046 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
4047 <literal>INFO</>, or <literal>LOG</> (in a notice message),
4048 or a localized translation of one of these. Always present.
4059 Code: the SQLSTATE code for the error (see <xref
4060 linkend="errcodes-appendix">). Not localizable. Always present.
4071 Message: the primary human-readable error message.
4072 This should be accurate but terse (typically one line).
4084 Detail: an optional secondary error message carrying more
4085 detail about the problem. Might run to multiple lines.
4096 Hint: an optional suggestion what to do about the problem.
4097 This is intended to differ from Detail in that it offers advice
4098 (potentially inappropriate) rather than hard facts.
4099 Might run to multiple lines.
4110 Position: the field value is a decimal ASCII integer, indicating
4111 an error cursor position as an index into the original query string.
4112 The first character has index 1, and positions are measured in
4113 characters not bytes.
4124 Internal position: this is defined the same as the <literal>P</>
4125 field, but it is used when the cursor position refers to an internally
4126 generated command rather than the one submitted by the client.
4127 The <literal>q</> field will always appear when this field appears.
4138 Internal query: the text of a failed internally-generated command.
4139 This could be, for example, a SQL query issued by a PL/pgSQL function.
4150 Where: an indication of the context in which the error occurred.
4151 Presently this includes a call stack traceback of active
4152 procedural language functions and internally-generated queries.
4153 The trace is one entry per line, most recent first.
4164 File: the file name of the source-code location where the error
4176 Line: the line number of the source-code location where the error
4188 Routine: the name of the source-code routine reporting the error.
4196 The client is responsible for formatting displayed information to meet its
4197 needs; in particular it should break long lines as needed. Newline characters
4198 appearing in the error message fields should be treated as paragraph breaks,
4205 <sect1 id="protocol-changes">
4206 <title>Summary of Changes since Protocol 2.0</title>
4209 This section provides a quick checklist of changes, for the benefit of
4210 developers trying to update existing client libraries to protocol 3.0.
4214 The initial startup packet uses a flexible list-of-strings format
4215 instead of a fixed format. Notice that session default values for run-time
4216 parameters can now be specified directly in the startup packet. (Actually,
4217 you could do that before using the <literal>options</> field, but given the
4218 limited width of <literal>options</> and the lack of any way to quote
4219 whitespace in the values, it wasn't a very safe technique.)
4223 All messages now have a length count immediately following the message type
4224 byte (except for startup packets, which have no type byte). Also note that
4225 PasswordMessage now has a type byte.
4229 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
4230 messages now contain multiple fields, from which the client code can
4231 assemble an error message of the desired level of verbosity. Note that
4232 individual fields will typically not end with a newline, whereas the single
4233 string sent in the older protocol always did.
4237 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
4242 The distinction between BinaryRow and DataRow message types is gone; the
4243 single DataRow message type serves for returning data in all formats.
4244 Note that the layout of DataRow has changed to make it easier to parse.
4245 Also, the representation of binary values has changed: it is no longer
4246 directly tied to the server's internal representation.
4250 There is a new <quote>extended query</> sub-protocol, which adds the frontend
4251 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
4252 backend message types ParseComplete, BindComplete, PortalSuspended,
4253 ParameterDescription, NoData, and CloseComplete. Existing clients do not
4254 have to concern themselves with this sub-protocol, but making use of it
4255 might allow improvements in performance or functionality.
4259 <command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
4260 is a well-defined way to recover from errors during <command>COPY</command>. The special
4261 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4262 during <command>COPY OUT</command>.
4263 (It is still recognized as a terminator during <command>COPY IN</command>, but its use is
4264 deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
4265 The CopyInResponse and CopyOutResponse messages include fields indicating
4266 the number of columns and the format of each column.
4270 The layout of FunctionCall and FunctionCallResponse messages has changed.
4271 FunctionCall can now support passing NULL arguments to functions. It also
4272 can handle passing parameters and retrieving results in either text or
4273 binary format. There is no longer any reason to consider FunctionCall a
4274 potential security hole, since it does not offer direct access to internal
4275 server data representations.
4279 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4280 startup for all parameters it considers interesting to the client library.
4281 Subsequently, a ParameterStatus message is sent whenever the active value
4282 changes for any of these parameters.
4286 The RowDescription ('<literal>T</>') message carries new table OID and column
4287 number fields for each column of the described row. It also shows the format
4288 code for each column.
4292 The CursorResponse ('<literal>P</>') message is no longer generated by
4297 The NotificationResponse ('<literal>A</>') message has an additional string
4298 field, which is presently empty but might someday carry additional data passed
4299 from the <command>NOTIFY</command> event sender.
4303 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4304 string parameter; this has been removed.