1 <!-- doc/src/sgml/protocol.sgml -->
3 <chapter id="protocol">
4 <title>Frontend/Backend Protocol</title>
6 <indexterm zone="protocol">
7 <primary>protocol</primary>
8 <secondary>frontend-backend</secondary>
12 <productname>PostgreSQL</productname> uses a message-based protocol
13 for communication between frontends and backends (clients and servers).
14 The protocol is supported over <acronym>TCP/IP</acronym> and also over
15 Unix-domain sockets. Port number 5432 has been registered with IANA as
16 the customary TCP port number for servers supporting this protocol, but
17 in practice any non-privileged port number can be used.
21 This document describes version 3.0 of the protocol, implemented in
22 <productname>PostgreSQL</productname> 7.4 and later. For descriptions
23 of the earlier protocol versions, see previous releases of the
24 <productname>PostgreSQL</productname> documentation. A single server
25 can support multiple protocol versions. The initial
26 startup-request message tells the server which protocol version the
27 client is attempting to use, and then the server follows that protocol
32 In order to serve multiple clients efficiently, the server launches
33 a new <quote>backend</> process for each client.
34 In the current implementation, a new child
35 process is created immediately after an incoming connection is detected.
36 This is transparent to the protocol, however. For purposes of the
37 protocol, the terms <quote>backend</> and <quote>server</> are
38 interchangeable; likewise <quote>frontend</> and <quote>client</>
42 <sect1 id="protocol-overview">
43 <title>Overview</title>
46 The protocol has separate phases for startup and normal operation.
47 In the startup phase, the frontend opens a connection to the server
48 and authenticates itself to the satisfaction of the server. (This might
49 involve a single message, or multiple messages depending on the
50 authentication method being used.) If all goes well, the server then sends
51 status information to the frontend, and finally enters normal operation.
52 Except for the initial startup-request message, this part of the
53 protocol is driven by the server.
57 During normal operation, the frontend sends queries and
58 other commands to the backend, and the backend sends back query results
59 and other responses. There are a few cases (such as <command>NOTIFY</>)
61 backend will send unsolicited messages, but for the most part this portion
62 of a session is driven by frontend requests.
66 Termination of the session is normally by frontend choice, but can be
67 forced by the backend in certain cases. In any case, when the backend
68 closes the connection, it will roll back any open (incomplete) transaction
73 Within normal operation, SQL commands can be executed through either of
74 two sub-protocols. In the <quote>simple query</> protocol, the frontend
75 just sends a textual query string, which is parsed and immediately
76 executed by the backend. In the <quote>extended query</> protocol,
77 processing of queries is separated into multiple steps: parsing,
78 binding of parameter values, and execution. This offers flexibility
79 and performance benefits, at the cost of extra complexity.
83 Normal operation has additional sub-protocols for special operations
84 such as <command>COPY</>.
87 <sect2 id="protocol-message-concepts">
88 <title>Messaging Overview</title>
91 All communication is through a stream of messages. The first byte of a
92 message identifies the message type, and the next four bytes specify the
93 length of the rest of the message (this length count includes itself, but
94 not the message-type byte). The remaining contents of the message are
95 determined by the message type. For historical reasons, the very first
96 message sent by the client (the startup message) has no initial
101 To avoid losing synchronization with the message stream, both servers and
102 clients typically read an entire message into a buffer (using the byte
103 count) before attempting to process its contents. This allows easy
104 recovery if an error is detected while processing the contents. In
105 extreme situations (such as not having enough memory to buffer the
106 message), the receiver can use the byte count to determine how much
107 input to skip before it resumes reading messages.
111 Conversely, both servers and clients must take care never to send an
112 incomplete message. This is commonly done by marshaling the entire message
113 in a buffer before beginning to send it. If a communications failure
114 occurs partway through sending or receiving a message, the only sensible
115 response is to abandon the connection, since there is little hope of
116 recovering message-boundary synchronization.
120 <sect2 id="protocol-query-concepts">
121 <title>Extended Query Overview</title>
124 In the extended-query protocol, execution of SQL commands is divided
125 into multiple steps. The state retained between steps is represented
126 by two types of objects: <firstterm>prepared statements</> and
127 <firstterm>portals</>. A prepared statement represents the result of
128 parsing and semantic analysis of a textual query string.
129 A prepared statement is not in itself ready to execute, because it might
130 lack specific values for <firstterm>parameters</>. A portal represents
131 a ready-to-execute or already-partially-executed statement, with any
132 missing parameter values filled in. (For <command>SELECT</> statements,
133 a portal is equivalent to an open cursor, but we choose to use a different
134 term since cursors don't handle non-<command>SELECT</> statements.)
138 The overall execution cycle consists of a <firstterm>parse</> step,
139 which creates a prepared statement from a textual query string; a
140 <firstterm>bind</> step, which creates a portal given a prepared
141 statement and values for any needed parameters; and an
142 <firstterm>execute</> step that runs a portal's query. In the case of
143 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
144 the execute step can be told to fetch only
145 a limited number of rows, so that multiple execute steps might be needed
146 to complete the operation.
150 The backend can keep track of multiple prepared statements and portals
151 (but note that these exist only within a session, and are never shared
152 across sessions). Existing prepared statements and portals are
153 referenced by names assigned when they were created. In addition,
154 an <quote>unnamed</> prepared statement and portal exist. Although these
155 behave largely the same as named objects, operations on them are optimized
156 for the case of executing a query only once and then discarding it,
157 whereas operations on named objects are optimized on the expectation
162 <sect2 id="protocol-format-codes">
163 <title>Formats and Format Codes</title>
166 Data of a particular data type might be transmitted in any of several
167 different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
168 the only supported formats are <quote>text</> and <quote>binary</>,
169 but the protocol makes provision for future extensions. The desired
170 format for any value is specified by a <firstterm>format code</>.
171 Clients can specify a format code for each transmitted parameter value
172 and for each column of a query result. Text has format code zero,
173 binary has format code one, and all other format codes are reserved
174 for future definition.
178 The text representation of values is whatever strings are produced
179 and accepted by the input/output conversion functions for the
180 particular data type. In the transmitted representation, there is
181 no trailing null character; the frontend must add one to received
182 values if it wants to process them as C strings.
183 (The text format does not allow embedded nulls, by the way.)
187 Binary representations for integers use network byte order (most
188 significant byte first). For other data types consult the documentation
189 or source code to learn about the binary representation. Keep in mind
190 that binary representations for complex data types might change across
191 server versions; the text format is usually the more portable choice.
196 <sect1 id="protocol-flow">
197 <title>Message Flow</title>
200 This section describes the message flow and the semantics of each
201 message type. (Details of the exact representation of each message
202 appear in <xref linkend="protocol-message-formats">.) There are
203 several different sub-protocols depending on the state of the
204 connection: start-up, query, function call,
205 <command>COPY</command>, and termination. There are also special
206 provisions for asynchronous operations (including notification
207 responses and command cancellation), which can occur at any time
208 after the start-up phase.
212 <title>Start-up</title>
215 To begin a session, a frontend opens a connection to the server and sends
216 a startup message. This message includes the names of the user and of the
217 database the user wants to connect to; it also identifies the particular
218 protocol version to be used. (Optionally, the startup message can include
219 additional settings for run-time parameters.)
220 The server then uses this information and
221 the contents of its configuration files (such as
222 <filename>pg_hba.conf</filename>) to determine
223 whether the connection is provisionally acceptable, and what additional
224 authentication is required (if any).
228 The server then sends an appropriate authentication request message,
229 to which the frontend must reply with an appropriate authentication
230 response message (such as a password).
231 For all authentication methods except GSSAPI and SSPI, there is at most
232 one request and one response. In some methods, no response
233 at all is needed from the frontend, and so no authentication request
234 occurs. For GSSAPI and SSPI, multiple exchanges of packets may be needed
235 to complete the authentication.
239 The authentication cycle ends with the server either rejecting the
240 connection attempt (ErrorResponse), or sending AuthenticationOk.
244 The possible messages from the server in this phase are:
248 <term>ErrorResponse</term>
251 The connection attempt has been rejected.
252 The server then immediately closes the connection.
258 <term>AuthenticationOk</term>
261 The authentication exchange is successfully completed.
267 <term>AuthenticationKerberosV5</term>
270 The frontend must now take part in a Kerberos V5
271 authentication dialog (not described here, part of the
272 Kerberos specification) with the server. If this is
273 successful, the server responds with an AuthenticationOk,
274 otherwise it responds with an ErrorResponse. This is no
281 <term>AuthenticationCleartextPassword</term>
284 The frontend must now send a PasswordMessage containing the
285 password in clear-text form. If
286 this is the correct password, the server responds with an
287 AuthenticationOk, otherwise it responds with an ErrorResponse.
293 <term>AuthenticationMD5Password</term>
296 The frontend must now send a PasswordMessage containing the
297 password (with username) encrypted via MD5, then encrypted
298 again using the 4-byte random salt specified in the
299 AuthenticationMD5Password message. If this is the correct
300 password, the server responds with an AuthenticationOk,
301 otherwise it responds with an ErrorResponse. The actual
302 PasswordMessage can be computed in SQL as <literal>concat('md5',
303 md5(concat(md5(concat(password, username)), random-salt)))</>.
304 (Keep in mind the <function>md5()</> function returns its
305 result as a hex string.)
311 <term>AuthenticationSCMCredential</term>
314 This response is only possible for local Unix-domain connections
315 on platforms that support SCM credential messages. The frontend
316 must issue an SCM credential message and then send a single data
317 byte. (The contents of the data byte are uninteresting; it's
318 only used to ensure that the server waits long enough to receive
319 the credential message.) If the credential is acceptable,
320 the server responds with an
321 AuthenticationOk, otherwise it responds with an ErrorResponse.
322 (This message type is only issued by pre-9.1 servers. It may
323 eventually be removed from the protocol specification.)
329 <term>AuthenticationGSS</term>
332 The frontend must now initiate a GSSAPI negotiation. The frontend
333 will send a PasswordMessage with the first part of the GSSAPI
334 data stream in response to this. If further messages are needed,
335 the server will respond with AuthenticationGSSContinue.
341 <term>AuthenticationSSPI</term>
344 The frontend must now initiate a SSPI negotiation. The frontend
345 will send a PasswordMessage with the first part of the SSPI
346 data stream in response to this. If further messages are needed,
347 the server will respond with AuthenticationGSSContinue.
353 <term>AuthenticationGSSContinue</term>
356 This message contains the response data from the previous step
357 of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI
358 or a previous AuthenticationGSSContinue). If the GSSAPI
359 or SSPI data in this message
360 indicates more data is needed to complete the authentication,
361 the frontend must send that data as another PasswordMessage. If
362 GSSAPI or SSPI authentication is completed by this message, the server
363 will next send AuthenticationOk to indicate successful authentication
364 or ErrorResponse to indicate failure.
373 If the frontend does not support the authentication method
374 requested by the server, then it should immediately close the
379 After having received AuthenticationOk, the frontend must wait
380 for further messages from the server. In this phase a backend process
381 is being started, and the frontend is just an interested bystander.
382 It is still possible for the startup attempt
383 to fail (ErrorResponse), but in the normal case the backend will send
384 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
388 During this phase the backend will attempt to apply any additional
389 run-time parameter settings that were given in the startup message.
390 If successful, these values become session defaults. An error causes
391 ErrorResponse and exit.
395 The possible messages from the backend in this phase are:
399 <term>BackendKeyData</term>
402 This message provides secret-key data that the frontend must
403 save if it wants to be able to issue cancel requests later.
404 The frontend should not respond to this message, but should
405 continue listening for a ReadyForQuery message.
411 <term>ParameterStatus</term>
414 This message informs the frontend about the current (initial)
415 setting of backend parameters, such as <xref
416 linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
417 The frontend can ignore this message, or record the settings
418 for its future use; see <xref linkend="protocol-async"> for
419 more details. The frontend should not respond to this
420 message, but should continue listening for a ReadyForQuery
427 <term>ReadyForQuery</term>
430 Start-up is completed. The frontend can now issue commands.
436 <term>ErrorResponse</term>
439 Start-up failed. The connection is closed after sending this
446 <term>NoticeResponse</term>
449 A warning message has been issued. The frontend should
450 display the message but continue listening for ReadyForQuery
459 The ReadyForQuery message is the same one that the backend will
460 issue after each command cycle. Depending on the coding needs of
461 the frontend, it is reasonable to consider ReadyForQuery as
462 starting a command cycle, or to consider ReadyForQuery as ending the
463 start-up phase and each subsequent command cycle.
468 <title>Simple Query</title>
471 A simple query cycle is initiated by the frontend sending a Query message
472 to the backend. The message includes an SQL command (or commands)
473 expressed as a text string.
474 The backend then sends one or more response
475 messages depending on the contents of the query command string,
476 and finally a ReadyForQuery response message. ReadyForQuery
477 informs the frontend that it can safely send a new command.
478 (It is not actually necessary for the frontend to wait for
479 ReadyForQuery before issuing another command, but the frontend must
480 then take responsibility for figuring out what happens if the earlier
481 command fails and already-issued later commands succeed.)
485 The possible response messages from the backend are:
489 <term>CommandComplete</term>
492 An SQL command completed normally.
498 <term>CopyInResponse</term>
501 The backend is ready to copy data from the frontend to a
502 table; see <xref linkend="protocol-copy">.
508 <term>CopyOutResponse</term>
511 The backend is ready to copy data from a table to the
512 frontend; see <xref linkend="protocol-copy">.
518 <term>RowDescription</term>
521 Indicates that rows are about to be returned in response to
522 a <command>SELECT</command>, <command>FETCH</command>, etc query.
523 The contents of this message describe the column layout of the rows.
524 This will be followed by a DataRow message for each row being returned
534 One of the set of rows returned by
535 a <command>SELECT</command>, <command>FETCH</command>, etc query.
541 <term>EmptyQueryResponse</term>
544 An empty query string was recognized.
550 <term>ErrorResponse</term>
553 An error has occurred.
559 <term>ReadyForQuery</term>
562 Processing of the query string is complete. A separate
563 message is sent to indicate this because the query string might
564 contain multiple SQL commands. (CommandComplete marks the
565 end of processing one SQL command, not the whole string.)
566 ReadyForQuery will always be sent, whether processing
567 terminates successfully or with an error.
573 <term>NoticeResponse</term>
576 A warning message has been issued in relation to the query.
577 Notices are in addition to other responses, i.e., the backend
578 will continue processing the command.
587 The response to a <command>SELECT</> query (or other queries that
588 return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
589 normally consists of RowDescription, zero or more
590 DataRow messages, and then CommandComplete.
591 <command>COPY</> to or from the frontend invokes special protocol
592 as described in <xref linkend="protocol-copy">.
593 All other query types normally produce only
594 a CommandComplete message.
598 Since a query string could contain several queries (separated by
599 semicolons), there might be several such response sequences before the
600 backend finishes processing the query string. ReadyForQuery is issued
601 when the entire string has been processed and the backend is ready to
602 accept a new query string.
606 If a completely empty (no contents other than whitespace) query string
607 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
611 In the event of an error, ErrorResponse is issued followed by
612 ReadyForQuery. All further processing of the query string is aborted by
613 ErrorResponse (even if more queries remained in it). Note that this
614 might occur partway through the sequence of messages generated by an
619 In simple Query mode, the format of retrieved values is always text,
620 except when the given command is a <command>FETCH</> from a cursor
621 declared with the <literal>BINARY</> option. In that case, the
622 retrieved values are in binary format. The format codes given in
623 the RowDescription message tell which format is being used.
627 A frontend must be prepared to accept ErrorResponse and
628 NoticeResponse messages whenever it is expecting any other type of
629 message. See also <xref linkend="protocol-async"> concerning messages
630 that the backend might generate due to outside events.
634 Recommended practice is to code frontends in a state-machine style
635 that will accept any message type at any time that it could make sense,
636 rather than wiring in assumptions about the exact sequence of messages.
640 <sect2 id="protocol-flow-ext-query">
641 <title>Extended Query</title>
644 The extended query protocol breaks down the above-described simple
645 query protocol into multiple steps. The results of preparatory
646 steps can be re-used multiple times for improved efficiency.
647 Furthermore, additional features are available, such as the possibility
648 of supplying data values as separate parameters instead of having to
649 insert them directly into a query string.
653 In the extended protocol, the frontend first sends a Parse message,
654 which contains a textual query string, optionally some information
655 about data types of parameter placeholders, and the
656 name of a destination prepared-statement object (an empty string
657 selects the unnamed prepared statement). The response is
658 either ParseComplete or ErrorResponse. Parameter data types can be
659 specified by OID; if not given, the parser attempts to infer the
660 data types in the same way as it would do for untyped literal string
666 A parameter data type can be left unspecified by setting it to zero,
667 or by making the array of parameter type OIDs shorter than the
668 number of parameter symbols (<literal>$</><replaceable>n</>)
669 used in the query string. Another special case is that a parameter's
670 type can be specified as <type>void</> (that is, the OID of the
671 <type>void</> pseudotype). This is meant to allow parameter symbols
672 to be used for function parameters that are actually OUT parameters.
673 Ordinarily there is no context in which a <type>void</> parameter
674 could be used, but if such a parameter symbol appears in a function's
675 parameter list, it is effectively ignored. For example, a function
676 call such as <literal>foo($1,$2,$3,$4)</> could match a function with
677 two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
678 are specified as having type <type>void</>.
684 The query string contained in a Parse message cannot include more
685 than one SQL statement; else a syntax error is reported. This
686 restriction does not exist in the simple-query protocol, but it
687 does exist in the extended protocol, because allowing prepared
688 statements or portals to contain multiple commands would complicate
694 If successfully created, a named prepared-statement object lasts till
695 the end of the current session, unless explicitly destroyed. An unnamed
696 prepared statement lasts only until the next Parse statement specifying
697 the unnamed statement as destination is issued. (Note that a simple
698 Query message also destroys the unnamed statement.) Named prepared
699 statements must be explicitly closed before they can be redefined by
700 another Parse message, but this is not required for the unnamed statement.
701 Named prepared statements can also be created and accessed at the SQL
702 command level, using <command>PREPARE</> and <command>EXECUTE</>.
706 Once a prepared statement exists, it can be readied for execution using a
707 Bind message. The Bind message gives the name of the source prepared
708 statement (empty string denotes the unnamed prepared statement), the name
709 of the destination portal (empty string denotes the unnamed portal), and
710 the values to use for any parameter placeholders present in the prepared
712 supplied parameter set must match those needed by the prepared statement.
713 (If you declared any <type>void</> parameters in the Parse message,
714 pass NULL values for them in the Bind message.)
715 Bind also specifies the format to use for any data returned
716 by the query; the format can be specified overall, or per-column.
717 The response is either BindComplete or ErrorResponse.
722 The choice between text and binary output is determined by the format
723 codes given in Bind, regardless of the SQL command involved. The
724 <literal>BINARY</> attribute in cursor declarations is irrelevant when
725 using extended query protocol.
730 Query planning typically occurs when the Bind message is processed.
731 If the prepared statement has no parameters, or is executed repeatedly,
732 the server might save the created plan and re-use it during subsequent
733 Bind messages for the same prepared statement. However, it will do so
734 only if it finds that a generic plan can be created that is not much
735 less efficient than a plan that depends on the specific parameter values
736 supplied. This happens transparently so far as the protocol is concerned.
740 If successfully created, a named portal object lasts till the end of the
741 current transaction, unless explicitly destroyed. An unnamed portal is
742 destroyed at the end of the transaction, or as soon as the next Bind
743 statement specifying the unnamed portal as destination is issued. (Note
744 that a simple Query message also destroys the unnamed portal.) Named
745 portals must be explicitly closed before they can be redefined by another
746 Bind message, but this is not required for the unnamed portal.
747 Named portals can also be created and accessed at the SQL
748 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
752 Once a portal exists, it can be executed using an Execute message.
753 The Execute message specifies the portal name (empty string denotes the
755 a maximum result-row count (zero meaning <quote>fetch all rows</>).
756 The result-row count is only meaningful for portals
757 containing commands that return row sets; in other cases the command is
758 always executed to completion, and the row count is ignored.
760 responses to Execute are the same as those described above for queries
761 issued via simple query protocol, except that Execute doesn't cause
762 ReadyForQuery or RowDescription to be issued.
766 If Execute terminates before completing the execution of a portal
767 (due to reaching a nonzero result-row count), it will send a
768 PortalSuspended message; the appearance of this message tells the frontend
769 that another Execute should be issued against the same portal to
770 complete the operation. The CommandComplete message indicating
771 completion of the source SQL command is not sent until
772 the portal's execution is completed. Therefore, an Execute phase is
773 always terminated by the appearance of exactly one of these messages:
774 CommandComplete, EmptyQueryResponse (if the portal was created from
775 an empty query string), ErrorResponse, or PortalSuspended.
779 At completion of each series of extended-query messages, the frontend
780 should issue a Sync message. This parameterless message causes the
781 backend to close the current transaction if it's not inside a
782 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
783 meaning to commit if no error, or roll back if error). Then a
784 ReadyForQuery response is issued. The purpose of Sync is to provide
785 a resynchronization point for error recovery. When an error is detected
786 while processing any extended-query message, the backend issues
787 ErrorResponse, then reads and discards messages until a Sync is reached,
788 then issues ReadyForQuery and returns to normal message processing.
789 (But note that no skipping occurs if an error is detected
790 <emphasis>while</> processing Sync — this ensures that there is one
791 and only one ReadyForQuery sent for each Sync.)
796 Sync does not cause a transaction block opened with <command>BEGIN</>
797 to be closed. It is possible to detect this situation since the
798 ReadyForQuery message includes transaction status information.
803 In addition to these fundamental, required operations, there are several
804 optional operations that can be used with extended-query protocol.
808 The Describe message (portal variant) specifies the name of an existing
809 portal (or an empty string for the unnamed portal). The response is a
810 RowDescription message describing the rows that will be returned by
811 executing the portal; or a NoData message if the portal does not contain a
812 query that will return rows; or ErrorResponse if there is no such portal.
816 The Describe message (statement variant) specifies the name of an existing
817 prepared statement (or an empty string for the unnamed prepared
818 statement). The response is a ParameterDescription message describing the
819 parameters needed by the statement, followed by a RowDescription message
820 describing the rows that will be returned when the statement is eventually
821 executed (or a NoData message if the statement will not return rows).
822 ErrorResponse is issued if there is no such prepared statement. Note that
823 since Bind has not yet been issued, the formats to be used for returned
824 columns are not yet known to the backend; the format code fields in the
825 RowDescription message will be zeroes in this case.
830 In most scenarios the frontend should issue one or the other variant
831 of Describe before issuing Execute, to ensure that it knows how to
832 interpret the results it will get back.
837 The Close message closes an existing prepared statement or portal
838 and releases resources. It is not an error to issue Close against
839 a nonexistent statement or portal name. The response is normally
840 CloseComplete, but could be ErrorResponse if some difficulty is
841 encountered while releasing resources. Note that closing a prepared
842 statement implicitly closes any open portals that were constructed
847 The Flush message does not cause any specific output to be generated,
848 but forces the backend to deliver any data pending in its output
849 buffers. A Flush must be sent after any extended-query command except
850 Sync, if the frontend wishes to examine the results of that command before
851 issuing more commands. Without Flush, messages returned by the backend
852 will be combined into the minimum possible number of packets to minimize
858 The simple Query message is approximately equivalent to the series Parse,
859 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
860 statement and portal objects and no parameters. One difference is that
861 it will accept multiple SQL statements in the query string, automatically
862 performing the bind/describe/execute sequence for each one in succession.
863 Another difference is that it will not return ParseComplete, BindComplete,
864 CloseComplete, or NoData messages.
870 <title>Function Call</title>
873 The Function Call sub-protocol allows the client to request a direct
874 call of any function that exists in the database's
875 <structname>pg_proc</structname> system catalog. The client must have
876 execute permission for the function.
881 The Function Call sub-protocol is a legacy feature that is probably best
882 avoided in new code. Similar results can be accomplished by setting up
883 a prepared statement that does <literal>SELECT function($1, ...)</>.
884 The Function Call cycle can then be replaced with Bind/Execute.
889 A Function Call cycle is initiated by the frontend sending a
890 FunctionCall message to the backend. The backend then sends one
891 or more response messages depending on the results of the function
892 call, and finally a ReadyForQuery response message. ReadyForQuery
893 informs the frontend that it can safely send a new query or
898 The possible response messages from the backend are:
902 <term>ErrorResponse</term>
905 An error has occurred.
911 <term>FunctionCallResponse</term>
914 The function call was completed and returned the result given
916 (Note that the Function Call protocol can only handle a single
917 scalar result, not a row type or set of results.)
923 <term>ReadyForQuery</term>
926 Processing of the function call is complete. ReadyForQuery
927 will always be sent, whether processing terminates
928 successfully or with an error.
934 <term>NoticeResponse</term>
937 A warning message has been issued in relation to the function
938 call. Notices are in addition to other responses, i.e., the
939 backend will continue processing the command.
947 <sect2 id="protocol-copy">
948 <title>COPY Operations</title>
951 The <command>COPY</> command allows high-speed bulk data transfer
952 to or from the server. Copy-in and copy-out operations each switch
953 the connection into a distinct sub-protocol, which lasts until the
954 operation is completed.
958 Copy-in mode (data transfer to the server) is initiated when the
959 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
960 sends a CopyInResponse message to the frontend. The frontend should
961 then send zero or more CopyData messages, forming a stream of input
962 data. (The message boundaries are not required to have anything to do
963 with row boundaries, although that is often a reasonable choice.)
964 The frontend can terminate the copy-in mode by sending either a CopyDone
965 message (allowing successful termination) or a CopyFail message (which
966 will cause the <command>COPY</> SQL statement to fail with an
967 error). The backend then reverts to the command-processing mode it was
968 in before the <command>COPY</> started, which will be either simple or
969 extended query protocol. It will next send either CommandComplete
970 (if successful) or ErrorResponse (if not).
974 In the event of a backend-detected error during copy-in mode (including
975 receipt of a CopyFail message), the backend will issue an ErrorResponse
976 message. If the <command>COPY</> command was issued via an extended-query
977 message, the backend will now discard frontend messages until a Sync
978 message is received, then it will issue ReadyForQuery and return to normal
979 processing. If the <command>COPY</> command was issued in a simple
980 Query message, the rest of that message is discarded and ReadyForQuery
981 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
982 messages issued by the frontend will simply be dropped.
986 The backend will ignore Flush and Sync messages received during copy-in
987 mode. Receipt of any other non-copy message type constitutes an error
988 that will abort the copy-in state as described above. (The exception for
989 Flush and Sync is for the convenience of client libraries that always
990 send Flush or Sync after an Execute message, without checking whether
991 the command to be executed is a <command>COPY FROM STDIN</>.)
995 Copy-out mode (data transfer from the server) is initiated when the
996 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
997 sends a CopyOutResponse message to the frontend, followed by
998 zero or more CopyData messages (always one per row), followed by CopyDone.
999 The backend then reverts to the command-processing mode it was
1000 in before the <command>COPY</> started, and sends CommandComplete.
1001 The frontend cannot abort the transfer (except by closing the connection
1002 or issuing a Cancel request),
1003 but it can discard unwanted CopyData and CopyDone messages.
1007 In the event of a backend-detected error during copy-out mode,
1008 the backend will issue an ErrorResponse message and revert to normal
1009 processing. The frontend should treat receipt of ErrorResponse as
1010 terminating the copy-out mode.
1014 It is possible for NoticeResponse and ParameterStatus messages to be
1015 interspersed between CopyData messages; frontends must handle these cases,
1016 and should be prepared for other asynchronous message types as well (see
1017 <xref linkend="protocol-async">). Otherwise, any message type other than
1018 CopyData or CopyDone may be treated as terminating copy-out mode.
1022 There is another Copy-related mode called copy-both, which allows
1023 high-speed bulk data transfer to <emphasis>and</> from the server.
1024 Copy-both mode is initiated when a backend in walsender mode
1025 executes a <command>START_REPLICATION</command> statement. The
1026 backend sends a CopyBothResponse message to the frontend. Both
1027 the backend and the frontend may then send CopyData messages
1028 until either end sends a CopyDone message. After the client
1029 sends a CopyDone message, the connection goes from copy-both mode to
1030 copy-out mode, and the client may not send any more CopyData messages.
1031 Similarly, when the server sends a CopyDone message, the connection
1032 goes into copy-in mode, and the server may not send any more CopyData
1033 messages. After both sides have sent a CopyDone message, the copy mode
1034 is terminated, and the backend reverts to the command-processing mode.
1035 In the event of a backend-detected error during copy-both mode,
1036 the backend will issue an ErrorResponse message, discard frontend messages
1037 until a Sync message is received, and then issue ReadyForQuery and return
1038 to normal processing. The frontend should treat receipt of ErrorResponse
1039 as terminating the copy in both directions; no CopyDone should be sent
1040 in this case. See <xref linkend="protocol-replication"> for more
1041 information on the subprotocol transmitted over copy-both mode.
1045 The CopyInResponse, CopyOutResponse and CopyBothResponse messages
1046 include fields that inform the frontend of the number of columns
1047 per row and the format codes being used for each column. (As of
1048 the present implementation, all columns in a given <command>COPY</>
1049 operation will use the same format, but the message design does not
1055 <sect2 id="protocol-async">
1056 <title>Asynchronous Operations</title>
1059 There are several cases in which the backend will send messages that
1060 are not specifically prompted by the frontend's command stream.
1061 Frontends must be prepared to deal with these messages at any time,
1062 even when not engaged in a query.
1063 At minimum, one should check for these cases before beginning to
1064 read a query response.
1068 It is possible for NoticeResponse messages to be generated due to
1069 outside activity; for example, if the database administrator commands
1070 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1071 indicating this fact before closing the connection. Accordingly,
1072 frontends should always be prepared to accept and display NoticeResponse
1073 messages, even when the connection is nominally idle.
1077 ParameterStatus messages will be generated whenever the active
1078 value changes for any of the parameters the backend believes the
1079 frontend should know about. Most commonly this occurs in response
1080 to a <command>SET</> SQL command executed by the frontend, and
1081 this case is effectively synchronous — but it is also possible
1082 for parameter status changes to occur because the administrator
1083 changed a configuration file and then sent the
1084 <systemitem>SIGHUP</systemitem> signal to the server. Also,
1085 if a <command>SET</command> command is rolled back, an appropriate
1086 ParameterStatus message will be generated to report the current
1091 At present there is a hard-wired set of parameters for which
1092 ParameterStatus will be generated: they are
1093 <varname>server_version</>,
1094 <varname>server_encoding</>,
1095 <varname>client_encoding</>,
1096 <varname>application_name</>,
1097 <varname>is_superuser</>,
1098 <varname>session_authorization</>,
1099 <varname>DateStyle</>,
1100 <varname>IntervalStyle</>,
1101 <varname>TimeZone</>,
1102 <varname>integer_datetimes</>, and
1103 <varname>standard_conforming_strings</>.
1104 (<varname>server_encoding</>, <varname>TimeZone</>, and
1105 <varname>integer_datetimes</> were not reported by releases before 8.0;
1106 <varname>standard_conforming_strings</> was not reported by releases
1108 <varname>IntervalStyle</> was not reported by releases before 8.4;
1109 <varname>application_name</> was not reported by releases before 9.0.)
1111 <varname>server_version</>,
1112 <varname>server_encoding</> and
1113 <varname>integer_datetimes</>
1114 are pseudo-parameters that cannot change after startup.
1115 This set might change in the future, or even become configurable.
1116 Accordingly, a frontend should simply ignore ParameterStatus for
1117 parameters that it does not understand or care about.
1121 If a frontend issues a <command>LISTEN</command> command, then the
1122 backend will send a NotificationResponse message (not to be
1123 confused with NoticeResponse!) whenever a
1124 <command>NOTIFY</command> command is executed for the same
1130 At present, NotificationResponse can only be sent outside a
1131 transaction, and thus it will not occur in the middle of a
1132 command-response series, though it might occur just before ReadyForQuery.
1133 It is unwise to design frontend logic that assumes that, however.
1134 Good practice is to be able to accept NotificationResponse at any
1135 point in the protocol.
1141 <title>Canceling Requests in Progress</title>
1144 During the processing of a query, the frontend might request
1145 cancellation of the query. The cancel request is not sent
1146 directly on the open connection to the backend for reasons of
1147 implementation efficiency: we don't want to have the backend
1148 constantly checking for new input from the frontend during query
1149 processing. Cancel requests should be relatively infrequent, so
1150 we make them slightly cumbersome in order to avoid a penalty in
1155 To issue a cancel request, the frontend opens a new connection to
1156 the server and sends a CancelRequest message, rather than the
1157 StartupMessage message that would ordinarily be sent across a new
1158 connection. The server will process this request and then close
1159 the connection. For security reasons, no direct reply is made to
1160 the cancel request message.
1164 A CancelRequest message will be ignored unless it contains the
1165 same key data (PID and secret key) passed to the frontend during
1166 connection start-up. If the request matches the PID and secret
1167 key for a currently executing backend, the processing of the
1168 current query is aborted. (In the existing implementation, this is
1169 done by sending a special signal to the backend process that is
1170 processing the query.)
1174 The cancellation signal might or might not have any effect — for
1175 example, if it arrives after the backend has finished processing
1176 the query, then it will have no effect. If the cancellation is
1177 effective, it results in the current command being terminated
1178 early with an error message.
1182 The upshot of all this is that for reasons of both security and
1183 efficiency, the frontend has no direct way to tell whether a
1184 cancel request has succeeded. It must continue to wait for the
1185 backend to respond to the query. Issuing a cancel simply improves
1186 the odds that the current query will finish soon, and improves the
1187 odds that it will fail with an error message instead of
1192 Since the cancel request is sent across a new connection to the
1193 server and not across the regular frontend/backend communication
1194 link, it is possible for the cancel request to be issued by any
1195 process, not just the frontend whose query is to be canceled.
1196 This might provide additional flexibility when building
1197 multiple-process applications. It also introduces a security
1198 risk, in that unauthorized persons might try to cancel queries.
1199 The security risk is addressed by requiring a dynamically
1200 generated secret key to be supplied in cancel requests.
1205 <title>Termination</title>
1208 The normal, graceful termination procedure is that the frontend
1209 sends a Terminate message and immediately closes the connection.
1210 On receipt of this message, the backend closes the connection and
1215 In rare cases (such as an administrator-commanded database shutdown)
1216 the backend might disconnect without any frontend request to do so.
1217 In such cases the backend will attempt to send an error or notice message
1218 giving the reason for the disconnection before it closes the connection.
1222 Other termination scenarios arise from various failure cases, such as core
1223 dump at one end or the other, loss of the communications link, loss of
1224 message-boundary synchronization, etc. If either frontend or backend sees
1225 an unexpected closure of the connection, it should clean
1226 up and terminate. The frontend has the option of launching a new backend
1227 by recontacting the server if it doesn't want to terminate itself.
1228 Closing the connection is also advisable if an unrecognizable message type
1229 is received, since this probably indicates loss of message-boundary sync.
1233 For either normal or abnormal termination, any open transaction is
1234 rolled back, not committed. One should note however that if a
1235 frontend disconnects while a non-<command>SELECT</command> query
1236 is being processed, the backend will probably finish the query
1237 before noticing the disconnection. If the query is outside any
1238 transaction block (<command>BEGIN</> ... <command>COMMIT</>
1239 sequence) then its results might be committed before the
1240 disconnection is recognized.
1245 <title><acronym>SSL</acronym> Session Encryption</title>
1248 If <productname>PostgreSQL</> was built with
1249 <acronym>SSL</acronym> support, frontend/backend communications
1250 can be encrypted using <acronym>SSL</acronym>. This provides
1251 communication security in environments where attackers might be
1252 able to capture the session traffic. For more information on
1253 encrypting <productname>PostgreSQL</productname> sessions with
1254 <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1258 To initiate an <acronym>SSL</acronym>-encrypted connection, the
1259 frontend initially sends an SSLRequest message rather than a
1260 StartupMessage. The server then responds with a single byte
1261 containing <literal>S</> or <literal>N</>, indicating that it is
1262 willing or unwilling to perform <acronym>SSL</acronym>,
1263 respectively. The frontend might close the connection at this point
1264 if it is dissatisfied with the response. To continue after
1265 <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1266 (not described here, part of the <acronym>SSL</acronym>
1267 specification) with the server. If this is successful, continue
1268 with sending the usual StartupMessage. In this case the
1269 StartupMessage and all subsequent data will be
1270 <acronym>SSL</acronym>-encrypted. To continue after
1271 <literal>N</>, send the usual StartupMessage and proceed without
1276 The frontend should also be prepared to handle an ErrorMessage
1277 response to SSLRequest from the server. This would only occur if
1278 the server predates the addition of <acronym>SSL</acronym> support
1279 to <productname>PostgreSQL</>. (Such servers are now very ancient,
1280 and likely do not exist in the wild anymore.)
1281 In this case the connection must
1282 be closed, but the frontend might choose to open a fresh connection
1283 and proceed without requesting <acronym>SSL</acronym>.
1287 An initial SSLRequest can also be used in a connection that is being
1288 opened to send a CancelRequest message.
1292 While the protocol itself does not provide a way for the server to
1293 force <acronym>SSL</acronym> encryption, the administrator can
1294 configure the server to reject unencrypted sessions as a byproduct
1295 of authentication checking.
1300 <sect1 id="protocol-replication">
1301 <title>Streaming Replication Protocol</title>
1304 To initiate streaming replication, the frontend sends the
1305 <literal>replication</> parameter in the startup message. A Boolean value
1306 of <literal>true</> tells the backend to go into walsender mode, wherein a
1307 small set of replication commands can be issued instead of SQL statements. Only
1308 the simple query protocol can be used in walsender mode.
1309 Replication commands are logged in the server log when
1310 <xref linkend="guc-log-replication-commands"> is enabled.
1311 Passing <literal>database</> as the value instructs walsender to connect to
1312 the database specified in the <literal>dbname</> parameter, which will allow
1313 the connection to be used for logical replication from that database.
1316 For the purpose of testing replication commands, you can make a replication
1317 connection via <application>psql</application> or any other <literal>libpq</literal>-using
1318 tool with a connection string including the <literal>replication</literal> option,
1321 psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
1323 However it is often more useful to use
1324 <application>pg_receivexlog</application> (for physical replication) or
1325 <application>pg_recvlogical</application> (for logical replication).
1329 The commands accepted in walsender mode are:
1332 <term>IDENTIFY_SYSTEM
1333 <indexterm><primary>IDENTIFY_SYSTEM</primary></indexterm>
1337 Requests the server to identify itself. Server replies with a result
1338 set of a single row, containing four fields:
1349 The unique system identifier identifying the cluster. This
1350 can be used to check that the base backup used to initialize the
1351 standby came from the same cluster.
1362 Current TimelineID. Also useful to check that the standby is
1363 consistent with the master.
1374 Current xlog flush location. Useful to get a known location in the
1375 transaction log where streaming can start.
1386 Database connected to or NULL.
1397 <term>TIMELINE_HISTORY <replaceable class="parameter">tli</replaceable>
1398 <indexterm><primary>TIMELINE_HISTORY</primary></indexterm>
1402 Requests the server to send over the timeline history file for timeline
1403 <replaceable class="parameter">tli</replaceable>. Server replies with a
1404 result set of a single row, containing two fields:
1415 Filename of the timeline history file, e.g <filename>00000002.history</>.
1426 Contents of the timeline history file.
1437 <term>CREATE_REPLICATION_SLOT <replaceable class="parameter">slot_name</> { <literal>PHYSICAL</> | <literal>LOGICAL</> <replaceable class="parameter">output_plugin</> }
1438 <indexterm><primary>CREATE_REPLICATION_SLOT</primary></indexterm>
1442 Create a physical or logical replication
1443 slot. See <xref linkend="streaming-replication-slots"> for more about
1448 <term><replaceable class="parameter">slot_name</></term>
1451 The name of the slot to create. Must be a valid replication slot
1452 name (see <xref linkend="streaming-replication-slots-manipulation">).
1458 <term><replaceable class="parameter">output_plugin</></term>
1461 The name of the output plugin used for logical decoding
1462 (see <xref linkend="logicaldecoding-output-plugin">).
1471 <term>START_REPLICATION [<literal>SLOT</literal> <replaceable class="parameter">slot_name</>] [<literal>PHYSICAL</literal>] <replaceable class="parameter">XXX/XXX</> [<literal>TIMELINE</literal> <replaceable class="parameter">tli</>]
1472 <indexterm><primary>START_REPLICATION</primary></indexterm>
1476 Instructs server to start streaming WAL, starting at
1477 WAL position <replaceable class="parameter">XXX/XXX</>.
1478 If <literal>TIMELINE</literal> option is specified,
1479 streaming starts on timeline <replaceable class="parameter">tli</>;
1480 otherwise, the server's current timeline is selected. The server can
1481 reply with an error, e.g. if the requested section of WAL has already
1482 been recycled. On success, server responds with a CopyBothResponse
1483 message, and then starts to stream WAL to the frontend.
1487 If a slot's name is provided
1488 via <replaceable class="parameter">slot_name</>, it will be updated
1489 as replication progresses so that the server knows which WAL segments,
1490 and if <varname>hot_standby_feedback</> is on which transactions,
1491 are still needed by the standby.
1495 If the client requests a timeline that's not the latest, but is part of
1496 the history of the server, the server will stream all the WAL on that
1497 timeline starting from the requested startpoint, up to the point where
1498 the server switched to another timeline. If the client requests
1499 streaming at exactly the end of an old timeline, the server responds
1500 immediately with CommandComplete without entering COPY mode.
1504 After streaming all the WAL on a timeline that is not the latest one,
1505 the server will end streaming by exiting the COPY mode. When the client
1506 acknowledges this by also exiting COPY mode, the server sends a result
1507 set with one row and two columns, indicating the next timeline in this
1508 server's history. The first column is the next timeline's ID, and the
1509 second column is the XLOG position where the switch happened. Usually,
1510 the switch position is the end of the WAL that was streamed, but there
1511 are corner cases where the server can send some WAL from the old
1512 timeline that it has not itself replayed before promoting. Finally, the
1513 server sends CommandComplete message, and is ready to accept a new
1518 WAL data is sent as a series of CopyData messages. (This allows
1519 other information to be intermixed; in particular the server can send
1520 an ErrorResponse message if it encounters a failure after beginning
1521 to stream.) The payload of each CopyData message from server to the
1522 client contains a message of one of the following formats:
1540 Identifies the message as WAL data.
1550 The starting point of the WAL data in this message.
1560 The current end of WAL on the server.
1570 The server's system clock at the time of transmission, as
1571 microseconds since midnight on 2000-01-01.
1577 Byte<replaceable>n</replaceable>
1581 A section of the WAL data stream.
1584 A single WAL record is never split across two XLogData messages.
1585 When a WAL record crosses a WAL page boundary, and is therefore
1586 already split using continuation records, it can be split at the page
1587 boundary. In other words, the first main WAL record and its
1588 continuation records can be sent in different XLogData messages.
1598 Primary keepalive message (B)
1609 Identifies the message as a sender keepalive.
1619 The current end of WAL on the server.
1629 The server's system clock at the time of transmission, as
1630 microseconds since midnight on 2000-01-01.
1640 1 means that the client should reply to this message as soon as
1641 possible, to avoid a timeout disconnect. 0 otherwise.
1653 The receiving process can send replies back to the sender at any time,
1654 using one of the following message formats (also in the payload of a
1662 Standby status update (F)
1673 Identifies the message as a receiver status update.
1683 The location of the last WAL byte + 1 received and written to disk
1694 The location of the last WAL byte + 1 flushed to disk in
1705 The location of the last WAL byte + 1 applied in the standby.
1715 The client's system clock at the time of transmission, as
1716 microseconds since midnight on 2000-01-01.
1726 If 1, the client requests the server to reply to this message
1727 immediately. This can be used to ping the server, to test if
1728 the connection is still healthy.
1743 Hot Standby feedback message (F)
1754 Identifies the message as a Hot Standby feedback message.
1764 The client's system clock at the time of transmission, as
1765 microseconds since midnight on 2000-01-01.
1775 The standby's current xmin. This may be 0, if the standby is
1776 sending notification that Hot Standby feedback will no longer
1777 be sent on this connection. Later non-zero messages may
1778 reinitiate the feedback mechanism.
1788 The standby's current epoch.
1801 <term>START_REPLICATION <literal>SLOT</literal> <replaceable class="parameter">slot_name</> <literal>LOGICAL</literal> <replaceable class="parameter">XXX/XXX</> [ ( <replaceable>option_name</replaceable> [<replaceable>option_value</replaceable>] [, ... ] ) ]</term>
1804 Instructs server to start streaming WAL for logical replication, starting
1805 at WAL position <replaceable class="parameter">XXX/XXX</>. The server can
1806 reply with an error, e.g. if the requested section of WAL has already
1807 been recycled. On success, server responds with a CopyBothResponse
1808 message, and then starts to stream WAL to the frontend.
1812 The messages inside the CopyBothResponse messages are of the same format
1813 documented for <literal>START_REPLICATION ... PHYSICAL</literal>.
1817 The output plugin associated with the selected slot is used
1818 to process the output for streaming.
1823 <term><literal>SLOT</literal> <replaceable class="parameter">slot_name</></term>
1826 The name of the slot to stream changes from. This parameter is required,
1827 and must correspond to an existing logical replication slot created
1828 with <literal>CREATE_REPLICATION_SLOT</literal> in
1829 <literal>LOGICAL</literal> mode.
1834 <term><replaceable class="parameter">XXX/XXX</></term>
1837 The WAL position to begin streaming at.
1842 <term><replaceable class="parameter">option_name</></term>
1845 The name of an option passed to the slot's logical decoding plugin.
1850 <term><replaceable class="parameter">option_value</></term>
1853 Optional value, in the form of a string constant, associated with the
1863 <term>DROP_REPLICATION_SLOT <replaceable class="parameter">slot_name</>
1864 <indexterm><primary>DROP_REPLICATION_SLOT</primary></indexterm>
1868 Drops a replication slot, freeing any reserved server-side resources. If
1869 the slot is currently in use by an active connection, this command fails.
1873 <term><replaceable class="parameter">slot_name</></term>
1876 The name of the slot to drop.
1885 <term>BASE_BACKUP [<literal>LABEL</literal> <replaceable>'label'</replaceable>] [<literal>PROGRESS</literal>] [<literal>FAST</literal>] [<literal>WAL</literal>] [<literal>NOWAIT</literal>] [<literal>MAX_RATE</literal> <replaceable>rate</replaceable>] [<literal>TABLESPACE_MAP</literal>]
1886 <indexterm><primary>BASE_BACKUP</primary></indexterm>
1890 Instructs the server to start streaming a base backup.
1891 The system will automatically be put in backup mode before the backup
1892 is started, and taken out of it when the backup is complete. The
1893 following options are accepted:
1896 <term><literal>LABEL</literal> <replaceable>'label'</replaceable></term>
1899 Sets the label of the backup. If none is specified, a backup label
1900 of <literal>base backup</literal> will be used. The quoting rules
1901 for the label are the same as a standard SQL string with
1902 <xref linkend="guc-standard-conforming-strings"> turned on.
1908 <term><literal>PROGRESS</></term>
1911 Request information required to generate a progress report. This will
1912 send back an approximate size in the header of each tablespace, which
1913 can be used to calculate how far along the stream is done. This is
1914 calculated by enumerating all the file sizes once before the transfer
1915 is even started, and may as such have a negative impact on the
1916 performance - in particular it may take longer before the first data
1917 is streamed. Since the database files can change during the backup,
1918 the size is only approximate and may both grow and shrink between
1919 the time of approximation and the sending of the actual files.
1925 <term><literal>FAST</></term>
1928 Request a fast checkpoint.
1934 <term><literal>WAL</literal></term>
1937 Include the necessary WAL segments in the backup. This will include
1938 all the files between start and stop backup in the
1939 <filename>pg_xlog</filename> directory of the base directory tar
1946 <term><literal>NOWAIT</literal></term>
1949 By default, the backup will wait until the last required xlog
1950 segment has been archived, or emit a warning if log archiving is
1951 not enabled. Specifying <literal>NOWAIT</literal> disables both
1952 the waiting and the warning, leaving the client responsible for
1953 ensuring the required log is available.
1959 <term><literal>MAX_RATE</literal> <replaceable>rate</></term>
1962 Limit (throttle) the maximum amount of data transferred from server
1963 to client per unit of time. The expected unit is kilobytes per second.
1964 If this option is specified, the value must either be equal to zero
1965 or it must fall within the range from 32 kB through 1 GB (inclusive).
1966 If zero is passed or the option is not specified, no restriction is
1967 imposed on the transfer.
1973 <term><literal>TABLESPACE_MAP</literal></term>
1976 Include information about symbolic links present in the directory
1977 <filename>pg_tblspc</filename> in a file named
1978 <filename>tablespace_map</filename>. The tablespace map file includes
1979 each symbolic link name as it exists in the directory
1980 <filename>pg_tblspc/</> and the full path of that symbolic link.
1987 When the backup is started, the server will first send two
1988 ordinary result sets, followed by one or more CopyResponse
1992 The first ordinary result set contains the starting position of the
1993 backup, in a single row with two columns. The first column contains
1994 the start position given in XLogRecPtr format, and the second column
1995 contains the corresponding timeline ID.
1998 The second ordinary result set has one row for each tablespace.
1999 The fields in this row are:
2005 The oid of the tablespace, or <literal>NULL</> if it's the base
2011 <term>spclocation</term>
2014 The full path of the tablespace directory, or <literal>NULL</>
2015 if it's the base directory.
2023 The approximate size of the tablespace, if progress report has
2024 been requested; otherwise it's <literal>NULL</>.
2031 After the second regular result set, one or more CopyResponse results
2032 will be sent, one for PGDATA and one for each additional tablespace other
2033 than <literal>pg_default</> and <literal>pg_global</>. The data in
2034 the CopyResponse results will be a tar format (following the
2035 <quote>ustar interchange format</> specified in the POSIX 1003.1-2008
2036 standard) dump of the tablespace contents, except that the two trailing
2037 blocks of zeroes specified in the standard are omitted.
2038 After the tar data is complete, a final ordinary result set will be sent,
2039 containing the WAL end position of the backup, in the same format as
2044 The tar archive for the data directory and each tablespace will contain
2045 all files in the directories, regardless of whether they are
2046 <productname>PostgreSQL</> files or other files added to the same
2047 directory. The only excluded files are:
2048 <itemizedlist spacing="compact" mark="bullet">
2051 <filename>postmaster.pid</>
2056 <filename>postmaster.opts</>
2061 various temporary files created during the operation of the PostgreSQL server
2066 <filename>pg_xlog</>, including subdirectories. If the backup is run
2067 with WAL files included, a synthesized version of <filename>pg_xlog</filename> will be
2068 included, but it will only contain the files necessary for the
2069 backup to work, not the rest of the contents.
2074 <filename>pg_replslot</> is copied as an empty directory.
2079 Files other than regular files and directories, such as symbolic
2080 links and special device files, are skipped. (Symbolic links
2081 in <filename>pg_tblspc</filename> are maintained.)
2085 Owner, group and file mode are set if the underlying file system on
2086 the server supports it.
2089 Once all tablespaces have been sent, a final regular result set will
2090 be sent. This result set contains the end position of the
2091 backup, given in XLogRecPtr format as a single column in a single row.
2101 <sect1 id="protocol-message-types">
2102 <title>Message Data Types</title>
2105 This section describes the base data types used in messages.
2111 Int<replaceable>n</replaceable>(<replaceable>i</replaceable>)
2115 An <replaceable>n</replaceable>-bit integer in network byte
2116 order (most significant byte first).
2117 If <replaceable>i</replaceable> is specified it
2118 is the exact value that will appear, otherwise the value
2119 is variable. Eg. Int16, Int32(42).
2126 Int<replaceable>n</replaceable>[<replaceable>k</replaceable>]
2130 An array of <replaceable>k</replaceable>
2131 <replaceable>n</replaceable>-bit integers, each in network
2132 byte order. The array length <replaceable>k</replaceable>
2133 is always determined by an earlier field in the message.
2141 String(<replaceable>s</replaceable>)
2145 A null-terminated string (C-style string). There is no
2146 specific length limitation on strings.
2147 If <replaceable>s</replaceable> is specified it is the exact
2148 value that will appear, otherwise the value is variable.
2149 Eg. String, String("user").
2154 <emphasis>There is no predefined limit</emphasis> on the length of a string
2155 that can be returned by the backend. Good coding strategy for a frontend
2156 is to use an expandable buffer so that anything that fits in memory can be
2157 accepted. If that's not feasible, read the full string and discard trailing
2158 characters that don't fit into your fixed-size buffer.
2166 Byte<replaceable>n</replaceable>(<replaceable>c</replaceable>)
2170 Exactly <replaceable>n</replaceable> bytes. If the field
2171 width <replaceable>n</replaceable> is not a constant, it is
2172 always determinable from an earlier field in the message.
2173 If <replaceable>c</replaceable> is specified it is the exact
2174 value. Eg. Byte2, Byte1('\n').
2183 <sect1 id="protocol-message-formats">
2184 <title>Message Formats</title>
2187 This section describes the detailed format of each message. Each is marked to
2188 indicate that it can be sent by a frontend (F), a backend (B), or both
2190 Notice that although each message includes a byte count at the beginning,
2191 the message format is defined so that the message end can be found without
2192 reference to the byte count. This aids validity checking. (The CopyData
2193 message is an exception, because it forms part of a data stream; the contents
2194 of any individual CopyData message cannot be interpretable on their own.)
2202 AuthenticationOk (B)
2214 Identifies the message as an authentication request.
2224 Length of message contents in bytes, including self.
2234 Specifies that the authentication was successful.
2247 AuthenticationKerberosV5 (B)
2259 Identifies the message as an authentication request.
2269 Length of message contents in bytes, including self.
2279 Specifies that Kerberos V5 authentication is required.
2291 AuthenticationCleartextPassword (B)
2303 Identifies the message as an authentication request.
2313 Length of message contents in bytes, including self.
2323 Specifies that a clear-text password is required.
2335 AuthenticationMD5Password (B)
2347 Identifies the message as an authentication request.
2357 Length of message contents in bytes, including self.
2367 Specifies that an MD5-encrypted password is required.
2377 The salt to use when encrypting the password.
2390 AuthenticationSCMCredential (B)
2402 Identifies the message as an authentication request.
2412 Length of message contents in bytes, including self.
2422 Specifies that an SCM credentials message is required.
2435 AuthenticationGSS (B)
2447 Identifies the message as an authentication request.
2457 Length of message contents in bytes, including self.
2467 Specifies that GSSAPI authentication is required.
2480 AuthenticationSSPI (B)
2492 Identifies the message as an authentication request.
2502 Length of message contents in bytes, including self.
2512 Specifies that SSPI authentication is required.
2523 AuthenticationGSSContinue (B)
2535 Identifies the message as an authentication request.
2545 Length of message contents in bytes, including self.
2555 Specifies that this message contains GSSAPI or SSPI data.
2561 Byte<replaceable>n</replaceable>
2565 GSSAPI or SSPI authentication data.
2590 Identifies the message as cancellation key data.
2591 The frontend must save these values if it wishes to be
2592 able to issue CancelRequest messages later.
2602 Length of message contents in bytes, including self.
2612 The process ID of this backend.
2622 The secret key of this backend.
2647 Identifies the message as a Bind command.
2657 Length of message contents in bytes, including self.
2667 The name of the destination portal
2668 (an empty string selects the unnamed portal).
2678 The name of the source prepared statement
2679 (an empty string selects the unnamed prepared statement).
2689 The number of parameter format codes that follow
2690 (denoted <replaceable>C</> below).
2691 This can be zero to indicate that there are no parameters
2692 or that the parameters all use the default format (text);
2693 or one, in which case the specified format code is applied
2694 to all parameters; or it can equal the actual number of
2701 Int16[<replaceable>C</>]
2705 The parameter format codes. Each must presently be
2706 zero (text) or one (binary).
2716 The number of parameter values that follow (possibly zero).
2717 This must match the number of parameters needed by the query.
2722 Next, the following pair of fields appear for each parameter:
2730 The length of the parameter value, in bytes (this count
2731 does not include itself). Can be zero.
2732 As a special case, -1 indicates a NULL parameter value.
2733 No value bytes follow in the NULL case.
2739 Byte<replaceable>n</replaceable>
2743 The value of the parameter, in the format indicated by the
2744 associated format code.
2745 <replaceable>n</replaceable> is the above length.
2750 After the last parameter, the following fields appear:
2758 The number of result-column format codes that follow
2759 (denoted <replaceable>R</> below).
2760 This can be zero to indicate that there are no result columns
2761 or that the result columns should all use the default format
2763 or one, in which case the specified format code is applied
2764 to all result columns (if any); or it can equal the actual
2765 number of result columns of the query.
2771 Int16[<replaceable>R</>]
2775 The result-column format codes. Each must presently be
2776 zero (text) or one (binary).
2800 Identifies the message as a Bind-complete indicator.
2810 Length of message contents in bytes, including self.
2835 Length of message contents in bytes, including self.
2845 The cancel request code. The value is chosen to contain
2846 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
2847 least 16 significant bits. (To avoid confusion, this code
2848 must not be the same as any protocol version number.)
2858 The process ID of the target backend.
2868 The secret key for the target backend.
2893 Identifies the message as a Close command.
2903 Length of message contents in bytes, including self.
2913 '<literal>S</>' to close a prepared statement; or
2914 '<literal>P</>' to close a portal.
2924 The name of the prepared statement or portal to close
2925 (an empty string selects the unnamed prepared statement
2950 Identifies the message as a Close-complete indicator.
2960 Length of message contents in bytes, including self.
2985 Identifies the message as a command-completed response.
2995 Length of message contents in bytes, including self.
3005 The command tag. This is usually a single
3006 word that identifies which SQL command was completed.
3010 For an <command>INSERT</command> command, the tag is
3011 <literal>INSERT <replaceable>oid</replaceable>
3012 <replaceable>rows</replaceable></literal>, where
3013 <replaceable>rows</replaceable> is the number of rows
3014 inserted. However, if and only if <literal>ON CONFLICT
3015 UPDATE</> is specified, then the tag is <literal>UPSERT
3016 <replaceable>oid</replaceable>
3017 <replaceable>rows</replaceable></literal>, where
3018 <replaceable>rows</replaceable> is the number of rows inserted
3019 <emphasis>or updated</emphasis>.
3020 <replaceable>oid</replaceable> is the object ID of the
3021 inserted row if <replaceable>rows</replaceable> is 1 and the
3022 target table has OIDs, and (for the <literal>UPSERT</literal>
3023 tag), the row was actually inserted rather than updated;
3024 otherwise <replaceable>oid</replaceable> is 0.
3028 For a <command>DELETE</command> command, the tag is
3029 <literal>DELETE <replaceable>rows</replaceable></literal> where
3030 <replaceable>rows</replaceable> is the number of rows deleted.
3034 For an <command>UPDATE</command> command, the tag is
3035 <literal>UPDATE <replaceable>rows</replaceable></literal> where
3036 <replaceable>rows</replaceable> is the number of rows updated.
3040 For a <command>SELECT</command> or <command>CREATE TABLE AS</command>
3041 command, the tag is <literal>SELECT <replaceable>rows</replaceable></literal>
3042 where <replaceable>rows</replaceable> is the number of rows retrieved.
3046 For a <command>MOVE</command> command, the tag is
3047 <literal>MOVE <replaceable>rows</replaceable></literal> where
3048 <replaceable>rows</replaceable> is the number of rows the
3049 cursor's position has been changed by.
3053 For a <command>FETCH</command> command, the tag is
3054 <literal>FETCH <replaceable>rows</replaceable></literal> where
3055 <replaceable>rows</replaceable> is the number of rows that
3056 have been retrieved from the cursor.
3060 For a <command>COPY</command> command, the tag is
3061 <literal>COPY <replaceable>rows</replaceable></literal> where
3062 <replaceable>rows</replaceable> is the number of rows copied.
3063 (Note: the row count appears only in
3064 <productname>PostgreSQL</productname> 8.2 and later.)
3078 CopyData (F & B)
3089 Identifies the message as <command>COPY</command> data.
3099 Length of message contents in bytes, including self.
3105 Byte<replaceable>n</replaceable>
3109 Data that forms part of a <command>COPY</command> data stream. Messages sent
3110 from the backend will always correspond to single data rows,
3111 but messages sent by frontends might divide the data stream
3124 CopyDone (F & B)
3136 Identifies the message as a <command>COPY</command>-complete indicator.
3146 Length of message contents in bytes, including self.
3171 Identifies the message as a <command>COPY</command>-failure indicator.
3181 Length of message contents in bytes, including self.
3191 An error message to report as the cause of failure.
3216 Identifies the message as a Start Copy In response.
3217 The frontend must now send copy-in data (if not
3218 prepared to do so, send a CopyFail message).
3228 Length of message contents in bytes, including self.
3238 0 indicates the overall <command>COPY</command> format is textual (rows
3239 separated by newlines, columns separated by separator
3241 1 indicates the overall copy format is binary (similar
3243 See <xref linkend="sql-copy">
3244 for more information.
3254 The number of columns in the data to be copied
3255 (denoted <replaceable>N</> below).
3261 Int16[<replaceable>N</>]
3265 The format codes to be used for each column.
3266 Each must presently be zero (text) or one (binary).
3267 All must be zero if the overall copy format is textual.
3292 Identifies the message as a Start Copy Out response.
3293 This message will be followed by copy-out data.
3303 Length of message contents in bytes, including self.
3313 0 indicates the overall <command>COPY</command> format
3314 is textual (rows separated by newlines, columns
3315 separated by separator characters, etc). 1 indicates
3316 the overall copy format is binary (similar to DataRow
3317 format). See <xref linkend="sql-copy"> for more information.
3327 The number of columns in the data to be copied
3328 (denoted <replaceable>N</> below).
3334 Int16[<replaceable>N</>]
3338 The format codes to be used for each column.
3339 Each must presently be zero (text) or one (binary).
3340 All must be zero if the overall copy format is textual.
3353 CopyBothResponse (B)
3365 Identifies the message as a Start Copy Both response.
3366 This message is used only for Streaming Replication.
3376 Length of message contents in bytes, including self.
3386 0 indicates the overall <command>COPY</command> format
3387 is textual (rows separated by newlines, columns
3388 separated by separator characters, etc). 1 indicates
3389 the overall copy format is binary (similar to DataRow
3390 format). See <xref linkend="sql-copy"> for more information.
3400 The number of columns in the data to be copied
3401 (denoted <replaceable>N</> below).
3407 Int16[<replaceable>N</>]
3411 The format codes to be used for each column.
3412 Each must presently be zero (text) or one (binary).
3413 All must be zero if the overall copy format is textual.
3437 Identifies the message as a data row.
3447 Length of message contents in bytes, including self.
3457 The number of column values that follow (possibly zero).
3462 Next, the following pair of fields appear for each column:
3470 The length of the column value, in bytes (this count
3471 does not include itself). Can be zero.
3472 As a special case, -1 indicates a NULL column value.
3473 No value bytes follow in the NULL case.
3479 Byte<replaceable>n</replaceable>
3483 The value of the column, in the format indicated by the
3484 associated format code.
3485 <replaceable>n</replaceable> is the above length.
3510 Identifies the message as a Describe command.
3520 Length of message contents in bytes, including self.
3530 '<literal>S</>' to describe a prepared statement; or
3531 '<literal>P</>' to describe a portal.
3541 The name of the prepared statement or portal to describe
3542 (an empty string selects the unnamed prepared statement
3555 EmptyQueryResponse (B)
3567 Identifies the message as a response to an empty query string.
3568 (This substitutes for CommandComplete.)
3578 Length of message contents in bytes, including self.
3603 Identifies the message as an error.
3613 Length of message contents in bytes, including self.
3618 The message body consists of one or more identified fields,
3619 followed by a zero byte as a terminator. Fields can appear in
3620 any order. For each field there is the following:
3628 A code identifying the field type; if zero, this is
3629 the message terminator and no string follows.
3630 The presently defined field types are listed in
3631 <xref linkend="protocol-error-fields">.
3632 Since more field types might be added in future,
3633 frontends should silently ignore fields of unrecognized
3669 Identifies the message as an Execute command.
3679 Length of message contents in bytes, including self.
3689 The name of the portal to execute
3690 (an empty string selects the unnamed portal).
3700 Maximum number of rows to return, if portal contains
3701 a query that returns rows (ignored otherwise). Zero
3702 denotes <quote>no limit</>.
3726 Identifies the message as a Flush command.
3736 Length of message contents in bytes, including self.
3761 Identifies the message as a function call.
3771 Length of message contents in bytes, including self.
3781 Specifies the object ID of the function to call.
3791 The number of argument format codes that follow
3792 (denoted <replaceable>C</> below).
3793 This can be zero to indicate that there are no arguments
3794 or that the arguments all use the default format (text);
3795 or one, in which case the specified format code is applied
3796 to all arguments; or it can equal the actual number of
3803 Int16[<replaceable>C</>]
3807 The argument format codes. Each must presently be
3808 zero (text) or one (binary).
3818 Specifies the number of arguments being supplied to the
3824 Next, the following pair of fields appear for each argument:
3832 The length of the argument value, in bytes (this count
3833 does not include itself). Can be zero.
3834 As a special case, -1 indicates a NULL argument value.
3835 No value bytes follow in the NULL case.
3841 Byte<replaceable>n</replaceable>
3845 The value of the argument, in the format indicated by the
3846 associated format code.
3847 <replaceable>n</replaceable> is the above length.
3852 After the last argument, the following field appears:
3860 The format code for the function result. Must presently be
3861 zero (text) or one (binary).
3874 FunctionCallResponse (B)
3886 Identifies the message as a function call result.
3896 Length of message contents in bytes, including self.
3906 The length of the function result value, in bytes (this count
3907 does not include itself). Can be zero.
3908 As a special case, -1 indicates a NULL function result.
3909 No value bytes follow in the NULL case.
3915 Byte<replaceable>n</replaceable>
3919 The value of the function result, in the format indicated by
3920 the associated format code.
3921 <replaceable>n</replaceable> is the above length.
3946 Identifies the message as a no-data indicator.
3956 Length of message contents in bytes, including self.
3981 Identifies the message as a notice.
3991 Length of message contents in bytes, including self.
3996 The message body consists of one or more identified fields,
3997 followed by a zero byte as a terminator. Fields can appear in
3998 any order. For each field there is the following:
4006 A code identifying the field type; if zero, this is
4007 the message terminator and no string follows.
4008 The presently defined field types are listed in
4009 <xref linkend="protocol-error-fields">.
4010 Since more field types might be added in future,
4011 frontends should silently ignore fields of unrecognized
4035 NotificationResponse (B)
4047 Identifies the message as a notification response.
4057 Length of message contents in bytes, including self.
4067 The process ID of the notifying backend process.
4077 The name of the channel that the notify has been raised on.
4087 The <quote>payload</> string passed from the notifying process.
4100 ParameterDescription (B)
4112 Identifies the message as a parameter description.
4122 Length of message contents in bytes, including self.
4132 The number of parameters used by the statement
4138 Then, for each parameter, there is the following:
4146 Specifies the object ID of the parameter data type.
4170 Identifies the message as a run-time parameter status report.
4180 Length of message contents in bytes, including self.
4190 The name of the run-time parameter being reported.
4200 The current value of the parameter.
4224 Identifies the message as a Parse command.
4234 Length of message contents in bytes, including self.
4244 The name of the destination prepared statement
4245 (an empty string selects the unnamed prepared statement).
4255 The query string to be parsed.
4265 The number of parameter data types specified
4266 (can be zero). Note that this is not an indication of
4267 the number of parameters that might appear in the
4268 query string, only the number that the frontend wants to
4269 prespecify types for.
4274 Then, for each parameter, there is the following:
4282 Specifies the object ID of the parameter data type.
4283 Placing a zero here is equivalent to leaving the type
4308 Identifies the message as a Parse-complete indicator.
4318 Length of message contents in bytes, including self.
4343 Identifies the message as a password response. Note that
4344 this is also used for GSSAPI and SSPI response messages
4345 (which is really a design error, since the contained data
4346 is not a null-terminated string in that case, but can be
4347 arbitrary binary data).
4357 Length of message contents in bytes, including self.
4367 The password (encrypted, if requested).
4391 Identifies the message as a portal-suspended indicator.
4392 Note this only appears if an Execute message's row-count limit
4403 Length of message contents in bytes, including self.
4428 Identifies the message as a simple query.
4438 Length of message contents in bytes, including self.
4448 The query string itself.
4473 Identifies the message type. ReadyForQuery is sent
4474 whenever the backend is ready for a new query cycle.
4484 Length of message contents in bytes, including self.
4494 Current backend transaction status indicator.
4495 Possible values are '<literal>I</>' if idle (not in
4496 a transaction block); '<literal>T</>' if in a transaction
4497 block; or '<literal>E</>' if in a failed transaction
4498 block (queries will be rejected until block is ended).
4523 Identifies the message as a row description.
4533 Length of message contents in bytes, including self.
4543 Specifies the number of fields in a row (can be zero).
4548 Then, for each field, there is the following:
4566 If the field can be identified as a column of a specific
4567 table, the object ID of the table; otherwise zero.
4577 If the field can be identified as a column of a specific
4578 table, the attribute number of the column; otherwise zero.
4588 The object ID of the field's data type.
4598 The data type size (see <varname>pg_type.typlen</>).
4599 Note that negative values denote variable-width types.
4609 The type modifier (see <varname>pg_attribute.atttypmod</>).
4610 The meaning of the modifier is type-specific.
4620 The format code being used for the field. Currently will
4621 be zero (text) or one (binary). In a RowDescription
4622 returned from the statement variant of Describe, the
4623 format code is not yet known and will always be zero.
4648 Length of message contents in bytes, including self.
4658 The <acronym>SSL</acronym> request code. The value is chosen to contain
4659 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
4660 least 16 significant bits. (To avoid confusion, this code
4661 must not be the same as any protocol version number.)
4686 Length of message contents in bytes, including self.
4696 The protocol version number. The most significant 16 bits are
4697 the major version number (3 for the protocol described here).
4698 The least significant 16 bits are the minor version number
4699 (0 for the protocol described here).
4704 The protocol version number is followed by one or more pairs of
4705 parameter name and value strings. A zero byte is required as a
4706 terminator after the last name/value pair.
4707 Parameters can appear in any
4708 order. <literal>user</> is required, others are optional.
4709 Each parameter is specified as:
4717 The parameter name. Currently recognized names are:
4726 The database user name to connect as. Required;
4727 there is no default.
4733 <literal>database</>
4737 The database to connect to. Defaults to the user name.
4747 Command-line arguments for the backend. (This is
4748 deprecated in favor of setting individual run-time
4755 In addition to the above, any run-time parameter that can be
4756 set at backend start time might be listed. Such settings
4757 will be applied during backend start (after parsing the
4758 command-line options if any). The values will act as
4759 session defaults. Spaces in option values need to be escaped
4760 with a backslash (<literal>\</>). A literal backslash can be
4761 passed by escaping it with another backslash
4762 (i.e <literal>\\</>).
4772 The parameter value.
4797 Identifies the message as a Sync command.
4807 Length of message contents in bytes, including self.
4832 Identifies the message as a termination.
4842 Length of message contents in bytes, including self.
4858 <sect1 id="protocol-error-fields">
4859 <title>Error and Notice Message Fields</title>
4862 This section describes the fields that can appear in ErrorResponse and
4863 NoticeResponse messages. Each field type has a single-byte identification
4864 token. Note that any given field type should appear at most once per
4876 Severity: the field contents are
4877 <literal>ERROR</>, <literal>FATAL</>, or
4878 <literal>PANIC</> (in an error message), or
4879 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
4880 <literal>INFO</>, or <literal>LOG</> (in a notice message),
4881 or a localized translation of one of these. Always present.
4892 Code: the SQLSTATE code for the error (see <xref
4893 linkend="errcodes-appendix">). Not localizable. Always present.
4904 Message: the primary human-readable error message.
4905 This should be accurate but terse (typically one line).
4917 Detail: an optional secondary error message carrying more
4918 detail about the problem. Might run to multiple lines.
4929 Hint: an optional suggestion what to do about the problem.
4930 This is intended to differ from Detail in that it offers advice
4931 (potentially inappropriate) rather than hard facts.
4932 Might run to multiple lines.
4943 Position: the field value is a decimal ASCII integer, indicating
4944 an error cursor position as an index into the original query string.
4945 The first character has index 1, and positions are measured in
4946 characters not bytes.
4957 Internal position: this is defined the same as the <literal>P</>
4958 field, but it is used when the cursor position refers to an internally
4959 generated command rather than the one submitted by the client.
4960 The <literal>q</> field will always appear when this field appears.
4971 Internal query: the text of a failed internally-generated command.
4972 This could be, for example, a SQL query issued by a PL/pgSQL function.
4983 Where: an indication of the context in which the error occurred.
4984 Presently this includes a call stack traceback of active
4985 procedural language functions and internally-generated queries.
4986 The trace is one entry per line, most recent first.
4997 Schema name: if the error was associated with a specific database
4998 object, the name of the schema containing that object, if any.
5009 Table name: if the error was associated with a specific table, the
5010 name of the table. (Refer to the schema name field for the name of
5011 the table's schema.)
5022 Column name: if the error was associated with a specific table column,
5023 the name of the column. (Refer to the schema and table name fields to
5024 identify the table.)
5035 Data type name: if the error was associated with a specific data type,
5036 the name of the data type. (Refer to the schema name field for the
5037 name of the data type's schema.)
5048 Constraint name: if the error was associated with a specific
5049 constraint, the name of the constraint. Refer to fields listed above
5050 for the associated table or domain. (For this purpose, indexes are
5051 treated as constraints, even if they weren't created with constraint
5063 File: the file name of the source-code location where the error
5075 Line: the line number of the source-code location where the error
5087 Routine: the name of the source-code routine reporting the error.
5096 The fields for schema name, table name, column name, data type name, and
5097 constraint name are supplied only for a limited number of error types;
5098 see <xref linkend="errcodes-appendix">. Frontends should not assume that
5099 the presence of any of these fields guarantees the presence of another
5100 field. Core error sources observe the interrelationships noted above, but
5101 user-defined functions may use these fields in other ways. In the same
5102 vein, clients should not assume that these fields denote contemporary
5103 objects in the current database.
5108 The client is responsible for formatting displayed information to meet its
5109 needs; in particular it should break long lines as needed. Newline characters
5110 appearing in the error message fields should be treated as paragraph breaks,
5116 <sect1 id="protocol-changes">
5117 <title>Summary of Changes since Protocol 2.0</title>
5120 This section provides a quick checklist of changes, for the benefit of
5121 developers trying to update existing client libraries to protocol 3.0.
5125 The initial startup packet uses a flexible list-of-strings format
5126 instead of a fixed format. Notice that session default values for run-time
5127 parameters can now be specified directly in the startup packet. (Actually,
5128 you could do that before using the <literal>options</> field, but given the
5129 limited width of <literal>options</> and the lack of any way to quote
5130 whitespace in the values, it wasn't a very safe technique.)
5134 All messages now have a length count immediately following the message type
5135 byte (except for startup packets, which have no type byte). Also note that
5136 PasswordMessage now has a type byte.
5140 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
5141 messages now contain multiple fields, from which the client code can
5142 assemble an error message of the desired level of verbosity. Note that
5143 individual fields will typically not end with a newline, whereas the single
5144 string sent in the older protocol always did.
5148 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
5153 The distinction between BinaryRow and DataRow message types is gone; the
5154 single DataRow message type serves for returning data in all formats.
5155 Note that the layout of DataRow has changed to make it easier to parse.
5156 Also, the representation of binary values has changed: it is no longer
5157 directly tied to the server's internal representation.
5161 There is a new <quote>extended query</> sub-protocol, which adds the frontend
5162 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
5163 backend message types ParseComplete, BindComplete, PortalSuspended,
5164 ParameterDescription, NoData, and CloseComplete. Existing clients do not
5165 have to concern themselves with this sub-protocol, but making use of it
5166 might allow improvements in performance or functionality.
5170 <command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
5171 is a well-defined way to recover from errors during <command>COPY</command>. The special
5172 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
5173 during <command>COPY OUT</command>.
5174 (It is still recognized as a terminator during <command>COPY IN</command>, but its use is
5175 deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
5176 The CopyInResponse and CopyOutResponse messages include fields indicating
5177 the number of columns and the format of each column.
5181 The layout of FunctionCall and FunctionCallResponse messages has changed.
5182 FunctionCall can now support passing NULL arguments to functions. It also
5183 can handle passing parameters and retrieving results in either text or
5184 binary format. There is no longer any reason to consider FunctionCall a
5185 potential security hole, since it does not offer direct access to internal
5186 server data representations.
5190 The backend sends ParameterStatus ('<literal>S</>') messages during connection
5191 startup for all parameters it considers interesting to the client library.
5192 Subsequently, a ParameterStatus message is sent whenever the active value
5193 changes for any of these parameters.
5197 The RowDescription ('<literal>T</>') message carries new table OID and column
5198 number fields for each column of the described row. It also shows the format
5199 code for each column.
5203 The CursorResponse ('<literal>P</>') message is no longer generated by
5208 The NotificationResponse ('<literal>A</>') message has an additional string
5209 field, which can carry a <quote>payload</> string passed
5210 from the <command>NOTIFY</command> event sender.
5214 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
5215 string parameter; this has been removed.