1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/protocol.sgml,v 1.36 2003/05/06 23:10:04 tgl Exp $ -->
3 <chapter id="protocol">
4 <title>Frontend/Backend Protocol</title>
8 This is currently a <emphasis>DRAFT</> description of FE/BE protocol
9 version 3.0. Details are still subject to change.
10 In particular, the representation of binary data is still under debate.
15 <application>PostgreSQL</application> uses a message-based protocol
16 for communication between frontends and backends (clients and servers).
17 The protocol is supported over <acronym>TCP/IP</acronym> and also over
18 Unix-domain sockets. Port number 5432 has been registered with IANA as
19 the customary TCP port number for servers supporting this protocol, but
20 in practice any non-privileged port number may be used.
24 This document describes version 3.0 of the protocol, implemented in
25 <application>PostgreSQL</application> 7.4 and later. For descriptions
26 of the earlier protocol versions, see previous releases of the
27 <productname>PostgreSQL</productname> documentation. A single server
28 can support multiple protocol versions. The initial
29 startup-request message tells the server which protocol version the
30 client is attempting to use, and then the server follows that protocol
35 Higher level features built on this protocol (for example, how
36 <application>libpq</application> passes certain environment
37 variables when the connection is established) are covered elsewhere.
41 In order to serve multiple clients efficiently, the server launches
42 a new <quote>backend</> process for each client.
43 In the current implementation, a new child
44 process is created immediately after an incoming connection is detected.
45 This is transparent to the protocol, however. For purposes of the
46 protocol, the terms <quote>backend</> and <quote>server</> are
47 interchangeable; likewise <quote>frontend</> and <quote>client</>
51 <sect1 id="protocol-overview">
52 <title>Overview</title>
55 The protocol has separate phases for startup and normal operation.
56 In the startup phase, the frontend opens a connection to the server
57 and authenticates itself to the satisfaction of the server. (This might
58 involve a single message, or multiple messages depending on the
59 authentication method being used.) If all goes well, the server then sends
60 status information to the frontend, and finally enters normal operation.
61 Except for the initial startup-request message, this part of the
62 protocol is driven by the server.
66 During normal operation, the frontend sends queries and
67 other commands to the backend, and the backend sends back query results
68 and other responses. There are a few cases (such as <command>NOTIFY</>)
70 backend will send unsolicited messages, but for the most part this portion
71 of a session is driven by frontend requests.
75 Termination of the session is normally by frontend choice, but can be
76 forced by the backend in certain cases. In any case, when the backend
77 closes the connection, it will roll back any open (incomplete) transaction
82 Within normal operation, SQL commands can be executed through either of
83 two sub-protocols. In the <quote>simple query</> protocol, the frontend
84 just sends a textual query string, which is parsed and immediately
85 executed by the backend. In the <quote>extended query</> protocol,
86 processing of queries is separated into multiple steps: parsing,
87 binding of parameter values, and execution. This offers flexibility
88 and performance benefits, at the cost of extra complexity.
92 Normal operation has additional sub-protocols for special operations
93 such as <command>COPY</>.
96 <sect2 id="protocol-message-concepts">
97 <title>Messaging Overview</title>
100 All communication is through a stream of messages. The first byte of a
101 message identifies the message type, and the next four bytes specify the
102 length of the rest of the message (this length count includes itself, but
103 not the message-type byte). The remaining contents of the message are
104 determined by the message type. For historical reasons, the very first
105 message sent by the client (the startup message) has no initial
110 To avoid losing synchronization with the message stream, both servers and
111 clients typically read an entire message into a buffer (using the byte
112 count) before attempting to process its contents. This allows easy
113 recovery if an error is detected while processing the contents. In
114 extreme situations (such as not having enough memory to buffer the
115 message), the receiver may use the byte count to determine how much
116 input to skip before it resumes reading messages.
120 Conversely, both servers and clients must take care never to send an
121 incomplete message. This is commonly done by marshaling the entire message
122 in a buffer before beginning to send it. If a communications failure
123 occurs partway through sending or receiving a message, the only sensible
124 response is to abandon the connection, since there is little hope of
125 recovering message-boundary synchronization.
129 <sect2 id="protocol-query-concepts">
130 <title>Extended Query Overview</title>
133 In the extended-query protocol, execution of SQL commands is divided
134 into multiple steps. The state retained between steps is represented
135 by two types of objects: <firstterm>prepared statements</> and
136 <firstterm>portals</>. A prepared statement represents the result of
137 parsing, semantic analysis, and planning of a textual query string. A
138 prepared statement is not necessarily ready to execute, because it may
139 lack specific values for <firstterm>parameters</>. A portal represents
140 a ready-to-execute or already-partially-executed statement, with any
141 missing parameter values filled in. (For <command>SELECT</> statements,
142 a portal is equivalent to an open cursor, but we use a different term
143 since cursors don't handle non-<command>SELECT</> statements.)
147 The overall execution cycle consists of a <firstterm>parse</> step,
148 which creates a prepared statement from a textual query string; a
149 <firstterm>bind</> step, which creates a portal given a prepared
150 statement and values for any needed parameters; and an
151 <firstterm>execute</> step that runs a portal's query. In the case of
152 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
153 the execute step can be told to fetch only
154 a limited number of rows, so that multiple execute steps may be needed
155 to complete the operation.
159 The backend can keep track of multiple prepared statements and portals
160 (but note that these exist only within a session, and are never shared
161 across sessions). Existing prepared statements and portals are
162 referenced by names assigned when they were created. In addition,
163 an <quote>unnamed</> prepared statement and portal exist. Although these
164 behave largely the same as named objects, operations on them are optimized
165 for the case of executing a query only once and then discarding it,
166 whereas operations on named objects are optimized on the expectation
172 <sect1 id="protocol-flow">
173 <title>Message Flow</title>
176 This section describes the message flow and the semantics of each
177 message type. There are several different sub-protocols
178 depending on the state of the connection: start-up,
179 query, function call, COPY, and termination. There are also special
180 provisions for asynchronous operations (including
181 notification responses and command cancellation),
182 which can occur at any time after the start-up phase.
186 <title>Start-Up</Title>
189 To begin a session, a frontend opens a connection to the server and sends
190 a startup message. This message includes the names of the user and of the
191 database the user wants to connect to; it also identifies the particular
192 protocol version to be used. The server then uses this information and
193 the contents of its configuration files (such as
194 <filename>pg_hba.conf</filename>) to determine
195 whether the connection is provisionally acceptable, and what additional
196 authentication is required (if any).
200 The server then sends an appropriate authentication request message,
201 to which the frontend must reply with an appropriate authentication
202 response message (such as a password).
203 In principle the authentication request/response cycle could require
204 multiple iterations, but none of the present authentication methods
205 use more than one request and response. In some methods, no response
206 at all is needed from the frontend, and so no authentication request
211 The authentication cycle ends with the server either rejecting the
212 connection attempt (ErrorResponse), or sending AuthenticationOK.
216 The possible messages from the server in this phase are:
220 <term>ErrorResponse</term>
223 The connection attempt has been rejected.
224 The server then immediately closes the connection.
230 <term>AuthenticationOk</term>
233 The authentication exchange is successfully completed.
239 <term>AuthenticationKerberosV4</Term>
242 The frontend must now take part in a Kerberos V4
243 authentication dialog (not described here, part of the
244 Kerberos specification) with the server. If this is
245 successful, the server responds with an AuthenticationOk,
246 otherwise it responds with an ErrorResponse.
252 <Term>AuthenticationKerberosV5</Term>
255 The frontend must now take part in a Kerberos V5
256 authentication dialog (not described here, part of the
257 Kerberos specification) with the server. If this is
258 successful, the server responds with an AuthenticationOk,
259 otherwise it responds with an ErrorResponse.
265 <Term>AuthenticationCleartextPassword</Term>
268 The frontend must now send a PasswordMessage containing the
269 password in clear-text form. If
270 this is the correct password, the server responds with an
271 AuthenticationOk, otherwise it responds with an ErrorResponse.
277 <Term>AuthenticationCryptPassword</Term>
280 The frontend must now send a PasswordMessage containing the
281 password encrypted via crypt(3), using the 2-character salt
282 specified in the AuthenticationCryptPassword message. If
283 this is the correct password, the server responds with an
284 AuthenticationOk, otherwise it responds with an ErrorResponse.
290 <Term>AuthenticationMD5Password</Term>
293 The frontend must now send a PasswordMessage containing the
294 password encrypted via MD5, using the 4-character salt
295 specified in the AuthenticationMD5Password message. If
296 this is the correct password, the server responds with an
297 AuthenticationOk, otherwise it responds with an ErrorResponse.
303 <Term>AuthenticationSCMCredential</Term>
306 This response is only possible for local Unix-domain connections
307 on platforms that support SCM credential messages. The frontend
308 must issue an SCM credential message and then send a single data
309 byte. (The contents of the data byte are uninteresting; it's
310 only used to ensure that the server waits long enough to receive
311 the credential message.) If the credential is acceptable,
312 the server responds with an
313 AuthenticationOk, otherwise it responds with an ErrorResponse.
322 If the frontend does not support the authentication method
323 requested by the server, then it should immediately close the
328 After having received AuthenticationOk, the frontend must wait
329 for further messages from the server. In this phase a backend process
330 is being started, and the frontend is just an interested bystander.
331 It is still possible for the startup attempt
332 to fail (ErrorResponse), but in the normal case the backend will send
333 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
337 The possible messages from the backend in this phase are:
341 <Term>BackendKeyData</Term>
344 This message provides secret-key data that the frontend must
345 save if it wants to be able to issue cancel requests later.
346 The frontend should not respond to this message, but should
347 continue listening for a ReadyForQuery message.
353 <Term>ParameterStatus</Term>
356 This message informs the frontend about the current (initial)
357 setting of backend parameters, such as <varname>client_encoding</>
358 or <varname>DateStyle</>. The frontend may ignore this message,
359 or record the settings for its future use; see
360 <xref linkend="protocol-async"> for more detail.
361 The frontend should not respond to this message, but should
362 continue listening for a ReadyForQuery message.
368 <Term>ReadyForQuery</Term>
371 Start-up is completed. The frontend may now issue commands.
377 <Term>ErrorResponse</Term>
380 Start-up failed. The connection is closed after sending this
387 <Term>NoticeResponse</Term>
390 A warning message has been issued. The frontend should
391 display the message but continue listening for ReadyForQuery
400 The ReadyForQuery message is the same one that the backend will
401 issue after each command cycle. Depending on the coding needs of
402 the frontend, it is reasonable to consider ReadyForQuery as
403 starting a command cycle, or to consider ReadyForQuery as ending the
404 start-up phase and each subsequent command cycle.
409 <Title>Simple Query</Title>
412 A simple query cycle is initiated by the frontend sending a Query message
413 to the backend. The backend then sends one or more response
414 messages depending on the contents of the query command string,
415 and finally a ReadyForQuery response message. ReadyForQuery
416 informs the frontend that it may safely send a new command.
417 (It is not actually necessary for the frontend to wait for
418 ReadyForQuery before issuing another command, but the frontend must
419 then take responsibility for figuring out what happens if the earlier
420 command fails and already-issued later commands succeed.)
424 The possible response messages from the backend are:
428 <Term>CommandComplete</Term>
431 An SQL command completed normally.
437 <Term>CopyInResponse</Term>
440 The backend is ready to copy data from the frontend to a
441 table; see <xref linkend="protocol-copy">.
447 <Term>CopyOutResponse</Term>
450 The backend is ready to copy data from a table to the
451 frontend; see <xref linkend="protocol-copy">.
457 <Term>RowDescription</Term>
460 Indicates that rows are about to be returned in response to
461 a <command>SELECT</command>, <command>FETCH</command>, etc query.
462 The message contents describe the layout of the rows. This
463 will be followed by a DataRow or BinaryRow message (depending on
464 whether a binary cursor was specified) for each row being returned
471 <Term>EmptyQueryResponse</Term>
474 An empty query string was recognized.
480 <Term>ErrorResponse</Term>
483 An error has occurred.
489 <Term>ReadyForQuery</Term>
492 Processing of the query string is complete. A separate
493 message is sent to indicate this because the query string may
494 contain multiple SQL commands. (CommandComplete marks the
495 end of processing one SQL command, not the whole string.)
496 ReadyForQuery will always be sent, whether processing
497 terminates successfully or with an error.
503 <Term>NoticeResponse</Term>
506 A warning message has been issued in relation to the query.
507 Notices are in addition to other responses, i.e., the backend
508 will continue processing the command.
517 The response to a <command>SELECT</> query (or other queries that
518 return rowsets, such as <command>EXPLAIN</> or <command>SHOW</>)
519 normally consists of RowDescription, zero or more
520 DataRow or BinaryRow messages, and then CommandComplete.
521 <command>COPY</> to or from the frontend invokes special protocol
522 as described in <xref linkend="protocol-copy">.
523 All other query types normally produce only
524 a CommandComplete message.
528 Since a query string could contain several queries (separated by
529 semicolons), there might be several such response sequences before the
530 backend finishes processing the query string. ReadyForQuery is issued
531 when the entire string has been processed and the backend is ready to
532 accept a new query string.
536 If a completely empty (no contents other than whitespace) query string
537 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
541 In the event of an error, ErrorResponse is issued followed by
542 ReadyForQuery. All further processing of the query string is aborted by
543 ErrorResponse (even if more queries remained in it). Note that this
544 may occur partway through the sequence of messages generated by an
549 A frontend must be prepared to accept ErrorResponse and
550 NoticeResponse messages whenever it is expecting any other type of
551 message. See also <xref linkend="protocol-async"> concerning messages
552 that the backend may generate due to outside events.
556 Recommended practice is to code frontends in a state-machine style
557 that will accept any message type at any time that it could make sense,
558 rather than wiring in assumptions about the exact sequence of messages.
563 <Title>Extended Query</Title>
566 The extended query protocol breaks down the above-described simple
567 query protocol into multiple steps. The results of preparatory
568 steps can be re-used multiple times for improved efficiency.
569 Furthermore, additional features are available, such as the possibility
570 of supplying data values as separate parameters instead of having to
571 insert them directly into a query string.
575 In the extended protocol, the frontend first sends a Parse message,
576 which contains a textual query string, optionally some information
577 about datatypes of parameter placeholders, and the
578 name of a destination prepared-statement object (an empty string
579 selects the unnamed prepared statement). The response is
580 either ParseComplete or ErrorResponse. Parameter datatypes may be
581 specified by OID; if not given, the parser attempts to infer the
582 datatypes in the same way as it would do for untyped literal string
588 The query string contained in a Parse message cannot include more
589 than one SQL statement; else a syntax error is reported. This
590 restriction does not exist in the simple-query protocol, but it
591 does exist in the extended protocol, because allowing prepared
592 statements or portals to contain multiple commands would complicate
598 If successfully created, a named prepared-statement object lasts till
599 the end of the current session, unless explicitly destroyed. An unnamed
600 prepared statement lasts only until the next Parse statement specifying
601 the unnamed statement as destination is issued. (Note that a simple
602 Query message also destroys the unnamed statement.) Named prepared
603 statements must be explicitly closed before they can be redefined by
604 a Parse message, but this is not required for the unnamed statement.
605 Named prepared statements can also be created and accessed at the SQL
606 command level, using <command>PREPARE</> and <command>EXECUTE</>.
610 Once a prepared statement exists, it can be readied for execution using a
611 Bind message. The Bind message gives the name of the source prepared
612 statement (empty string denotes the unnamed prepared statement), the name
613 of the destination portal (empty string denotes the unnamed portal), and
614 the values to use for any parameter placeholders present in the prepared
615 statement. The response is either BindComplete or ErrorResponse. The
616 supplied parameter set must match those needed by the prepared statement.
620 If successfully created, a named portal object lasts till the end of the
621 current transaction, unless explicitly destroyed. An unnamed portal is
622 destroyed at the end of the transaction, or as soon as the next Bind
623 statement specifying the unnamed portal as destination is issued. (Note
624 that a simple Query message also destroys the unnamed portal.) Named
625 portals must be explicitly closed before they can be redefined by a Bind
626 message, but this is not required for the unnamed portal.
627 Named portals can also be created and accessed at the SQL
628 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
632 Once a portal exists, it can be executed using an Execute message.
633 The Execute message specifies the portal name (empty string denotes the
634 unnamed portal), the desired output format (text or binary), and
635 a maximum result-row count (zero meaning <quote>fetch all rows</>).
636 The output format and result-row count are only meaningful for portals
637 containing commands that return rowsets; they are ignored for other types
638 of commands. The possible
639 responses to Execute are the same as those described above for queries
640 issued via simple query protocol, except that Execute doesn't cause
641 ReadyForQuery to be issued. Also, the choice between text and binary
642 output (DataRow or BinaryRow messages) is determined by Execute's
643 format field, regardless of the command; the <literal>BINARY</> attribute
644 in cursor declarations is irrelevant when using this protocol.
648 If Execute terminates before completing the execution of a portal
649 (due to reaching a nonzero result-row count), it will send a
650 PortalSuspended message; the appearance of this message tells the frontend
651 that another Execute should be issued against the same portal to
652 complete the operation. The CommandComplete message indicating
653 completion of the source SQL command is not sent until
654 the portal's execution is completed. Therefore, an Execute phase is
655 always terminated by the appearance of exactly one of these messages:
656 CommandComplete, EmptyQueryResponse (if the portal was created from
657 an empty query string), ErrorResponse, or PortalSuspended.
661 At completion of each series of extended-query messages, the frontend
662 should issue a Sync message. This parameterless message causes the
663 backend to close the current transaction if it's not inside a
664 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
665 meaning to commit if no error, or roll back if error). Then a
666 ReadyForQuery response is issued. The purpose of Sync is to provide
667 a resychronization point for error recovery. When an error is detected
668 while processing any extended-query message, the backend issues
669 ErrorResponse, then reads and discards messages until a Sync is reached,
670 then issues ReadyForQuery and returns to normal message processing.
671 (But note that no skipping occurs if an error is detected
672 <emphasis>while</> processing Sync --- this ensures that there is one
673 and only one ReadyForQuery sent for each Sync.)
678 Sync does not cause a transaction block opened with <command>BEGIN</>
679 to be closed. It is possible to detect this situation since the
680 ReadyForQuery message includes transaction status information.
685 In addition to these fundamental, required operations, there are several
686 optional operations that can be used with extended-query protocol.
690 The Describe message (portal variant) specifies the name of an existing
691 portal (or an empty string for the unnamed portal). The response is a
692 RowDescription message describing the rows that will be returned by
693 executing the portal; or a NoData message if the portal does not contain a
694 query that will return rows; or ErrorResponse if there is no such portal.
698 The Describe message (statement variant) specifies the name of an existing
699 prepared statement (or an empty string for the unnamed prepared
700 statement). The response is a ParameterDescription message describing the
701 parameters needed by the statement (if any), followed by a RowDescription
702 message describing the rows that will be returned when the statement is
703 eventually executed (or NoData if the statement will not return rows).
704 ErrorResponse is issued if there is no such prepared statement.
709 In most scenarios the frontend should issue one or the other variant
710 of Describe before issuing Execute, to ensure that it knows how to
711 interpret the results it will get back.
716 The Close message closes an existing prepared statement or portal
717 and releases resources. It is not an error to issue Close against
718 a nonexistent statement or portal name. The response is normally
719 CloseComplete, but could be ErrorResponse if some difficulty is
720 encountered while releasing resources. Note that closing a prepared
721 statement implicitly closes any open portals that were constructed
726 The Flush message does not cause any specific output to be generated,
727 but forces the backend to deliver any data pending in its output
728 buffers. A Flush must be sent after any extended-query command except
729 Sync, if the frontend wishes to examine the results of that command before
730 issuing more commands. Without Flush, messages returned by the backend
731 will be combined into the minimum possible number of packets to minimize
737 The simple Query message is approximately equivalent to the series Parse,
738 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
739 statement and portal objects and no parameters. One difference is that
740 it will accept multiple SQL statements in the query string, automatically
741 performing the bind/describe/execute sequence for each one in succession.
742 Another difference is that it will not return ParseComplete, BindComplete,
743 CloseComplete, or NoData messages.
749 <Title>Function Call</Title>
753 The Function Call sub-protocol is a legacy feature that is probably best
754 avoided in new code. Similar results can be accomplished by setting up
755 a prepared statement that does <literal>SELECT function($1, ...)</>.
756 The Function Call cycle can then be replaced with Bind/Execute.
761 A Function Call cycle is initiated by the frontend sending a
762 FunctionCall message to the backend. The backend then sends one
763 or more response messages depending on the results of the function
764 call, and finally a ReadyForQuery response message. ReadyForQuery
765 informs the frontend that it may safely send a new query or
770 The possible response messages from the backend are:
774 <Term>ErrorResponse</Term>
777 An error has occurred.
783 <Term>FunctionResultResponse</Term>
786 The function call was executed and returned a non-null result.
787 (Note that the Function Call protocol can only handle a single
788 scalar result, not a rowtype or set of results.)
794 <Term>FunctionVoidResponse</Term>
797 The function call was executed and returned a NULL value.
803 <Term>ReadyForQuery</Term>
806 Processing of the function call is complete. ReadyForQuery
807 will always be sent, whether processing terminates
808 successfully or with an error.
814 <Term>NoticeResponse</Term>
817 A warning message has been issued in relation to the function
818 call. Notices are in addition to other responses, i.e., the
819 backend will continue processing the command.
827 <sect2 id="protocol-copy">
828 <title>COPY Operations</title>
831 The <command>COPY</> command allows high-speed bulk data transfer
832 to or from the server. Copy-in and copy-out operations each switch
833 the connection into a distinct sub-protocol, which lasts until the
834 operation is completed.
838 Copy-in mode (data transfer to the server) is initiated when the
839 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
840 sends a CopyInResponse message to the frontend. The frontend should
841 then send zero or more CopyData messages, forming a stream of input
842 data. (The message boundaries are not required to have anything to do
843 with row boundaries, although that is often a reasonable choice.)
844 The frontend can terminate the copy-in mode by sending either a CopyDone
845 message (allowing successful termination) or a CopyFail message (which
846 will cause the <command>COPY</> SQL statement to fail with an
847 error). The backend then reverts to the command-processing mode it was
848 in before the <command>COPY</> started, which will be either simple or
849 extended query protocol. It will next send either CommandComplete
850 (if successful) or ErrorResponse (if not).
854 In the event of a backend-detected error during copy-in mode (including
855 receipt of a CopyFail message, or indeed any frontend message other than
856 CopyData or CopyDone), the backend will issue an ErrorResponse
857 message. If the <command>COPY</> command was issued via an extended-query
858 message, the backend will now discard frontend messages until a Sync
859 message is received, then it will issue ReadyForQuery and return to normal
860 processing. If the <command>COPY</> command was issued in a simple
861 Query message, the rest of that message is discarded and ReadyForQuery
862 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
863 messages issued by the frontend will simply be dropped.
867 Copy-out mode (data transfer from the server) is initiated when the
868 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
869 sends a CopyOutResponse message to the frontend, followed by
870 zero or more CopyData messages (always one per row), followed by CopyDone.
871 The backend then reverts to the command-processing mode it was
872 in before the <command>COPY</> started, and sends CommandComplete.
873 The frontend cannot abort the transfer (except by closing the connection
874 or issuing a Cancel request),
875 but it can discard unwanted CopyData and CopyDone messages.
879 In the event of a backend-detected error during copy-out mode,
880 the backend will issue an ErrorResponse message and revert to normal
881 processing. The frontend should treat receipt of ErrorResponse (or
882 indeed any message type other than CopyData or CopyDone) as terminating
887 <sect2 id="protocol-async">
888 <title>Asynchronous Operations</title>
891 There are several cases in which the backend will send messages that
892 are not specifically prompted by the frontend's command stream.
893 Frontends must be prepared to deal with these messages at any time,
894 even when not engaged in a query.
895 At minimum, one should check for these cases before beginning to
896 read a query response.
900 It is possible for NoticeResponse messages to be generated due to
901 outside activity; for example, if the database administrator commands
902 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
903 indicating this fact before closing the connection. Accordingly,
904 frontends should always be prepared to accept and display NoticeResponse
905 messages, even when the connection is nominally idle.
909 ParameterStatus messages will be generated whenever the active value
910 changes for any of the parameters the backend believes the frontend
911 should know about. Most commonly this occurs in response to a
912 <command>SET</> SQL command executed by the frontend, and this case
913 is effectively synchronous --- but it is also possible for parameter
914 status changes to occur because the administrator changed a configuration
915 file and then SIGHUP'd the postmaster. Also, if a SET command is
916 rolled back, an appropriate ParameterStatus message will be generated
917 to report the current effective value.
921 At present there is a hard-wired set of parameters for which
922 ParameterStatus will be generated: they are
923 <literal>server_version</> (a pseudo-parameter that cannot change after
925 <literal>server_encoding</> (also not presently changeable after start);
926 <literal>client_encoding</>, and
927 <literal>DateStyle</>.
928 This set might change in the future, or even become configurable.
929 Accordingly, a frontend should simply ignore ParameterStatus for
930 parameters that it does not understand or care about.
934 If a frontend issues a <command>LISTEN</command> command, then the
935 backend will send a NotificationResponse message (not to be
936 confused with NoticeResponse!) whenever a
937 <command>NOTIFY</command> command is executed for the same
943 At present, NotificationResponse can only be sent outside a
944 transaction, and thus it will not occur in the middle of a
945 command-response series, though it may occur just before ReadyForQuery.
946 It is unwise to design frontend logic that assumes that, however.
947 Good practice is to be able to accept NotificationResponse at any
948 point in the protocol.
954 <Title>Cancelling Requests in Progress</Title>
957 During the processing of a query, the frontend may request
958 cancellation of the query. The cancel request is not sent
959 directly on the open connection to the backend for reasons of
960 implementation efficiency: we don't want to have the backend
961 constantly checking for new input from the frontend during query
962 processing. Cancel requests should be relatively infrequent, so
963 we make them slightly cumbersome in order to avoid a penalty in
968 To issue a cancel request, the frontend opens a new connection to
969 the server and sends a CancelRequest message, rather than the
970 StartupMessage message that would ordinarily be sent across a new
971 connection. The server will process this request and then close
972 the connection. For security reasons, no direct reply is made to
973 the cancel request message.
977 A CancelRequest message will be ignored unless it contains the
978 same key data (PID and secret key) passed to the frontend during
979 connection start-up. If the request matches the PID and secret
980 key for a currently executing backend, the processing of the
981 current query is aborted. (In the existing implementation, this is
982 done by sending a special signal to the backend process that is
983 processing the query.)
987 The cancellation signal may or may not have any effect --- for
988 example, if it arrives after the backend has finished processing
989 the query, then it will have no effect. If the cancellation is
990 effective, it results in the current command being terminated
991 early with an error message.
995 The upshot of all this is that for reasons of both security and
996 efficiency, the frontend has no direct way to tell whether a
997 cancel request has succeeded. It must continue to wait for the
998 backend to respond to the query. Issuing a cancel simply improves
999 the odds that the current query will finish soon, and improves the
1000 odds that it will fail with an error message instead of
1005 Since the cancel request is sent across a new connection to the
1006 server and not across the regular frontend/backend communication
1007 link, it is possible for the cancel request to be issued by any
1008 process, not just the frontend whose query is to be canceled.
1009 This may have some benefits of flexibility in building
1010 multiple-process applications. It also introduces a security
1011 risk, in that unauthorized persons might try to cancel queries.
1012 The security risk is addressed by requiring a dynamically
1013 generated secret key to be supplied in cancel requests.
1018 <Title>Termination</Title>
1021 The normal, graceful termination procedure is that the frontend
1022 sends a Terminate message and immediately closes the connection.
1023 On receipt of this message, the backend closes the connection and
1028 In rare cases (such as an administrator-commanded database shutdown)
1029 the backend may disconnect without any frontend request to do so.
1030 In such cases the backend will attempt to send an error or notice message
1031 giving the reason for the disconnection before it closes the connection.
1035 Other termination scenarios arise from various failure cases, such as core
1036 dump at one end or the other, loss of the communications link, loss of
1037 message-boundary synchronization, etc. If either frontend or backend sees
1038 an unexpected closure of the connection, it should clean
1039 up and terminate. The frontend has the option of launching a new backend
1040 by recontacting the server if it doesn't want to terminate itself.
1041 Closing the connection is also advisable if an unrecognizable message type
1042 is received, since this probably indicates loss of message-boundary sync.
1046 For either normal or abnormal termination, any open transaction is
1047 rolled back, not committed. One should note however that if a
1048 frontend disconnects while a non-SELECT query is being processed,
1049 the backend will probably finish the query before noticing the
1051 If the query is outside any transaction block (<command>BEGIN</>
1052 ... <command>COMMIT</> sequence) then its results may be committed
1053 before the disconnection is recognized.
1058 <Title>SSL Session Encryption</Title>
1061 If <productname>PostgreSQL</> was built with SSL support, frontend/backend
1062 communications can be encrypted using SSL. This provides communication
1063 security in environments where attackers might be able to capture the
1068 To initiate an SSL-encrypted connection, the frontend initially sends
1069 an SSLRequest message rather than a StartupMessage. The server then
1070 responds with a single byte containing <literal>Y</> or <literal>N</>,
1071 indicating that it is willing or unwilling to perform SSL, respectively.
1072 The frontend may close the connection at this point if it is dissatisfied
1073 with the response. To continue after <literal>Y</>, perform an SSL
1074 startup handshake (not described here, part of the SSL specification)
1075 with the server. If this is successful, continue with
1076 sending the usual StartupMessage. In this case the StartupMessage and
1077 all subsequent data will be SSL-encrypted. To continue after
1078 <literal>N</>, send the usual StartupMessage and proceed without
1083 The frontend should also be prepared to handle an ErrorMessage response
1084 to SSLRequest from the server. This would only occur if the server
1085 predates the addition of SSL support to <productname>PostgreSQL</>.
1086 In this case the connection must be closed, but the frontend may choose
1087 to open a fresh connection and proceed without requesting SSL.
1091 An initial SSLRequest may also be used in a connection that is being
1092 opened to send a CancelRequest message.
1096 While the protocol itself does not provide a way for the server to
1097 force SSL encryption, the administrator may configure the server to
1098 reject unencrypted sessions as a byproduct of authentication checking.
1103 <Sect1 id="protocol-message-types">
1104 <Title>Message Data Types</Title>
1107 This section describes the base data types used in messages.
1113 Int<Replaceable>n</Replaceable>(<Replaceable>i</Replaceable>)
1117 An <Replaceable>n</Replaceable> bit integer in network byte
1119 If <Replaceable>i</Replaceable> is specified it
1120 is the exact value that will appear, otherwise the value
1121 is variable. Eg. Int16, Int32(42).
1128 String(<Replaceable>s</Replaceable>)
1132 A null-terminated string (C-style string). There is no
1133 specific length limitation on strings.
1134 If <Replaceable>s</Replaceable> is specified it is the exact
1135 value that will appear, otherwise the value is variable.
1136 Eg. String, String("user").
1141 <Emphasis>There is no predefined limit</Emphasis> on the length of a string
1142 that can be returned by the backend. Good coding strategy for a frontend
1143 is to use an expandable buffer so that anything that fits in memory can be
1144 accepted. If that's not feasible, read the full string and discard trailing
1145 characters that don't fit into your fixed-size buffer.
1153 Byte<Replaceable>n</Replaceable>(<Replaceable>c</Replaceable>)
1157 Exactly <Replaceable>n</Replaceable> bytes. If
1158 <Replaceable>c</Replaceable> is specified it is the exact
1159 value. Eg. Byte2, Byte1('\n').
1168 <Sect1 id="protocol-message-formats">
1169 <Title>Message Formats</Title>
1172 This section describes the detailed format of each message. Each is marked to
1173 indicate that it may be sent by a frontend (F), a backend (B), or both
1175 Notice that although each message includes a byte count at the beginning,
1176 the message format is defined so that the message end can be found without
1177 reference to the byte count. This aids validity checking. (The CopyData
1178 message is an exception, because it forms part of a data stream; the contents
1179 of any individual CopyData message may not be interpretable on their own.)
1187 AuthenticationOk (B)
1199 Identifies the message as an authentication request.
1209 Length of message contents in bytes, including self.
1219 Specifies that the authentication was successful.
1232 AuthenticationKerberosV4 (B)
1244 Identifies the message as an authentication request.
1254 Length of message contents in bytes, including self.
1264 Specifies that Kerberos V4 authentication is required.
1276 AuthenticationKerberosV5 (B)
1288 Identifies the message as an authentication request.
1298 Length of message contents in bytes, including self.
1308 Specifies that Kerberos V5 authentication is required.
1320 AuthenticationCleartextPassword (B)
1332 Identifies the message as an authentication request.
1342 Length of message contents in bytes, including self.
1352 Specifies that a cleartext password is required.
1364 AuthenticationCryptPassword (B)
1376 Identifies the message as an authentication request.
1386 Length of message contents in bytes, including self.
1396 Specifies that a crypt()-encrypted password is required.
1406 The salt to use when encrypting the password.
1419 AuthenticationMD5Password (B)
1431 Identifies the message as an authentication request.
1441 Length of message contents in bytes, including self.
1451 Specifies that an MD5-encrypted password is required.
1461 The salt to use when encrypting the password.
1474 AuthenticationSCMCredential (B)
1486 Identifies the message as an authentication request.
1496 Length of message contents in bytes, including self.
1506 Specifies that an SCM credentials message is required.
1531 Identifies the message as cancellation key data.
1532 The frontend must save these values if it wishes to be
1533 able to issue CancelRequest messages later.
1543 Length of message contents in bytes, including self.
1553 The process ID of this backend.
1563 The secret key of this backend.
1587 Identifies the message as a binary data row.
1588 (Normally, a prior RowDescription message defines the number
1589 of fields in the row and their data types. Note that the
1590 receiver <emphasis>must</> know the number of fields to be
1591 able to decode the message contents.)
1601 Length of message contents in bytes, including self.
1607 Byte<Replaceable>n</Replaceable>
1611 A bit map with one bit for each field in the row. The 1st
1612 field corresponds to bit 7 (MSB) of the 1st byte, the 2nd
1613 field corresponds to bit 6 of the 1st byte, the 8th field
1614 corresponds to bit 0 (LSB) of the 1st byte, the 9th field
1615 corresponds to bit 7 of the 2nd byte, and so on. Each bit
1616 is set if the value of the corresponding field is not NULL.
1617 If the number of fields is not a multiple of 8, the remainder
1618 of the last byte in the bit map is wasted.
1623 Then, for each field with a non-NULL value, there is the following:
1631 Specifies the size of the value of the field, excluding
1638 Byte<Replaceable>n</Replaceable>
1642 Specifies the value of the field itself in binary
1643 format. <Replaceable>n</Replaceable> is the above size.
1668 Identifies the message as a Bind command.
1678 Length of message contents in bytes, including self.
1688 The name of the destination portal
1689 (an empty string selects the unnamed portal).
1699 The name of the source prepared statement
1700 (an empty string selects the unnamed prepared statement).
1710 0 if parameter values are specified in textual form.
1711 1 if parameter values are specified in binary form.
1721 The number of parameter values specified
1722 (may be zero). This must match the number of parameters
1723 needed by the query.
1728 If parameter values are specified in textual form, the following
1729 appears for each parameter:
1737 1 if the parameter is non-null. 0 if it is null.
1747 The parameter value in textual form (that is, suitable
1748 input for the parameter's datatype's input converter).
1749 If the preceding byte specified a null parameter, then
1750 the string is omitted.
1755 If parameter values are specified in binary form, the following
1756 appears for each parameter:
1764 Zero if the field is null, otherwise the <varname>typlen</>
1765 for the field datatype.
1771 Byte<Replaceable>n</Replaceable>
1775 The value of the field itself in binary format.
1776 Omitted if the field is null.
1777 <Replaceable>n</Replaceable> is the <varname>typlen</>
1778 value if <varname>typlen</> is positive. If
1779 <varname>typlen</> is -1 then the field value begins with
1780 its own length as an Int32 (the length includes itself).
1804 Identifies the message as a Bind-complete indicator.
1814 Length of message contents in bytes, including self.
1839 Length of message contents in bytes, including self.
1849 The cancel request code. The value is chosen to contain
1850 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
1851 least 16 significant bits. (To avoid confusion, this code
1852 must not be the same as any protocol version number.)
1862 The process ID of the target backend.
1872 The secret key for the target backend.
1897 Identifies the message as a Close command.
1907 Length of message contents in bytes, including self.
1917 '<literal>S</>' to close a prepared statement; or
1918 '<literal>P</>' to close a portal.
1928 The name of the prepared statement or portal to close
1929 (an empty string selects the unnamed prepared statement
1954 Identifies the message as a Close-complete indicator.
1964 Length of message contents in bytes, including self.
1989 Identifies the message as a command-completed response.
1999 Length of message contents in bytes, including self.
2009 The command tag. This is usually a single
2010 word that identifies which SQL command was completed.
2014 For an <command>INSERT</command> command, the tag is
2015 <literal>INSERT <replaceable>oid</replaceable>
2016 <replaceable>rows</replaceable></literal>, where
2017 <replaceable>rows</replaceable> is the number of rows
2018 inserted. <replaceable>oid</replaceable> is the object ID
2019 of the inserted row if <Replaceable>rows</Replaceable> is 1
2020 and the target table has OIDs;
2021 otherwise <Replaceable>oid</Replaceable> is 0.
2025 For a <command>DELETE</command> command, the tag is
2026 <literal>DELETE <Replaceable>rows</Replaceable></literal> where
2027 <Replaceable>rows</Replaceable> is the number of rows deleted.
2031 For an <command>UPDATE</command> command, the tag is
2032 <literal>UPDATE <Replaceable>rows</Replaceable></literal> where
2033 <Replaceable>rows</Replaceable> is the number of rows updated.
2037 For a <command>MOVE</command> command, the tag is
2038 <literal>MOVE <replaceable>rows</replaceable></literal> where
2039 <replaceable>rows</replaceable> is the number of rows the
2040 cursor's position has been changed by.
2044 For a <command>FETCH</command> command, the tag is
2045 <literal>FETCH <replaceable>rows</replaceable></literal> where
2046 <replaceable>rows</replaceable> is the number of rows that
2047 have been retrieved from the cursor.
2060 CopyData (F & B)
2071 Identifies the message as COPY data.
2081 Length of message contents in bytes, including self.
2087 Byte<Replaceable>n</Replaceable>
2091 Data that forms part of a COPY datastream. Messages sent
2092 from the backend will always correspond to single data rows,
2093 but messages sent by frontends may divide the datastream
2106 CopyDone (F & B)
2118 Identifies the message as a COPY-complete indicator.
2128 Length of message contents in bytes, including self.
2153 Identifies the message as a COPY-failure indicator.
2163 Length of message contents in bytes, including self.
2173 An error message to report as the cause of failure.
2198 Identifies the message as a Start Copy In response.
2199 The frontend must now send copy-in data (if not
2200 prepared to do so, send a CopyFail message).
2210 Length of message contents in bytes, including self.
2220 0 for textual copy, 1 for binary copy.
2245 Identifies the message as a Start Copy Out response.
2246 This message will be followed by copy-out data.
2256 Length of message contents in bytes, including self.
2266 0 for textual copy, 1 for binary copy.
2290 Identifies the message as a text-format data row.
2291 (Normally, a prior RowDescription message defines the number
2292 of fields in the row and their data types. Note that the
2293 receiver <emphasis>must</> know the number of fields to be
2294 able to decode the message contents.)
2304 Length of message contents in bytes, including self.
2310 Byte<Replaceable>n</Replaceable>
2314 A bit map with one bit for each field in the row. The 1st
2315 field corresponds to bit 7 (MSB) of the 1st byte, the 2nd
2316 field corresponds to bit 6 of the 1st byte, the 8th field
2317 corresponds to bit 0 (LSB) of the 1st byte, the 9th field
2318 corresponds to bit 7 of the 2nd byte, and so on. Each bit
2319 is set if the value of the corresponding field is not NULL.
2320 If the number of fields is not a multiple of 8, the remainder
2321 of the last byte in the bit map is wasted.
2326 Then, for each field with a non-NULL value, there is the following:
2334 Specifies the size of the value of the field, in
2335 bytes; the count includes itself.
2341 Byte<Replaceable>n</Replaceable>
2345 Specifies the value of the field itself in textual
2346 form (that is, the result of the output-conversion
2347 routine for the field's datatype).
2348 <Replaceable>n</Replaceable> is the above size minus 4.
2349 There is no trailing zero-byte in the field data; the
2350 frontend must add one if it wants one.
2375 Identifies the message as a Describe command.
2385 Length of message contents in bytes, including self.
2395 '<literal>S</>' to describe a prepared statement; or
2396 '<literal>P</>' to describe a portal.
2406 The name of the prepared statement or portal to describe
2407 (an empty string selects the unnamed prepared statement
2420 EmptyQueryResponse (B)
2432 Identifies the message as a response to an empty query string.
2433 (This substitutes for CommandComplete.)
2443 Length of message contents in bytes, including self.
2468 Identifies the message as an error.
2478 Length of message contents in bytes, including self.
2483 The message body consists of one or more identified fields,
2484 followed by a zero-byte terminator. Fields may appear in
2485 any order. For each field there is the following:
2493 A code identifying the field type; if zero, this is
2494 the message terminator and no string follows.
2495 The presently defined field types are listed in
2496 <xref linkend="protocol-error-fields">.
2497 Since more field types may be added in future,
2498 frontends should silently ignore fields of unrecognized
2534 Identifies the message as an Execute command.
2544 Length of message contents in bytes, including self.
2554 The name of the portal to execute
2555 (an empty string selects the unnamed portal).
2565 0 to return results in textual form (DataRow messages).
2566 1 to return results in binary form (BinaryRow messages).
2576 Maximum number of rows to return, if portal contains
2577 a query that returns rows (ignored otherwise). Zero
2578 denotes <quote>no limit</>.
2602 Identifies the message as a Flush command.
2612 Length of message contents in bytes, including self.
2637 Identifies the message as a function call.
2647 Length of message contents in bytes, including self.
2667 Specifies the object ID of the function to call.
2677 Specifies the number of arguments being supplied to the
2683 Then, for each argument, there is the following:
2691 Specifies the size of the value of the argument,
2692 excluding this size.
2698 Byte<Replaceable>n</Replaceable>
2702 Specifies the value of the field itself in binary
2703 format. <Replaceable>n</Replaceable> is the above size.
2716 FunctionResultResponse (B)
2728 Identifies the message as a function call result.
2738 Length of message contents in bytes, including self.
2748 Specifies that a non-null result was returned.
2758 Specifies the size of the value of the result, excluding this
2765 Byte<Replaceable>n</Replaceable>
2769 Specifies the value of the result itself in binary format.
2770 <Replaceable>n</Replaceable> is the above size.
2780 Unused. (Strictly speaking, FunctionResultResponse and
2781 FunctionVoidResponse are the same thing but with some optional
2782 parts to the message.)
2795 FunctionVoidResponse (B)
2807 Identifies the message as a function call result.
2817 Length of message contents in bytes, including self.
2827 Specifies that a null result was returned.
2852 Identifies the message as a no-data indicator.
2862 Length of message contents in bytes, including self.
2887 Identifies the message as a notice.
2897 Length of message contents in bytes, including self.
2902 The message body consists of one or more identified fields,
2903 followed by a zero-byte terminator. Fields may appear in
2904 any order. For each field there is the following:
2912 A code identifying the field type; if zero, this is
2913 the message terminator and no string follows.
2914 The presently defined field types are listed in
2915 <xref linkend="protocol-error-fields">.
2916 Since more field types may be added in future,
2917 frontends should silently ignore fields of unrecognized
2941 NotificationResponse (B)
2953 Identifies the message as a notification response.
2963 Length of message contents in bytes, including self.
2973 The process ID of the notifying backend process.
2983 The name of the condition that the notify has been raised on.
2993 Additional information passed from the notifying process.
2994 (Currently, this feature is unimplemented so the field
2995 is always an empty string.)
3008 ParameterDescription (B)
3020 Identifies the message as a parameter description.
3030 Length of message contents in bytes, including self.
3040 The number of parameters used by the statement
3046 Then, for each parameter, there is the following:
3054 Specifies the object ID of the parameter datatype.
3078 Identifies the message as a run-time parameter status report.
3088 Length of message contents in bytes, including self.
3098 The name of the run-time parameter being reported.
3108 The current value of the parameter.
3132 Identifies the message as a Parse command.
3142 Length of message contents in bytes, including self.
3152 The name of the destination prepared statement
3153 (an empty string selects the unnamed prepared statement).
3163 The query string to be parsed.
3173 The number of parameter datatypes specified
3174 (may be zero). Note that this is not an indication of
3175 the number of parameters that might appear in the
3176 query string, only the number that the frontend wants to
3177 prespecify types for.
3182 Then, for each parameter, there is the following:
3190 Specifies the object ID of the parameter datatype.
3191 Placing a zero here is equivalent to leaving the type
3216 Identifies the message as a Parse-complete indicator.
3226 Length of message contents in bytes, including self.
3251 Identifies the message as a password response.
3261 Length of message contents in bytes, including self.
3271 The password (encrypted, if requested).
3295 Identifies the message as a portal-suspended indicator.
3296 Note this only appears if an Execute row-count limit
3307 Length of message contents in bytes, including self.
3332 Identifies the message as a simple query.
3342 Length of message contents in bytes, including self.
3352 The query string itself.
3377 Identifies the message type. ReadyForQuery is sent
3378 whenever the backend is ready for a new query cycle.
3388 Length of message contents in bytes, including self.
3398 Current backend transaction status indicator.
3399 Possible values are '<literal>I</>' if idle (not in
3400 a transaction block); '<literal>T</>' if in a transaction
3401 block; or '<literal>E</>' if in a failed transaction
3402 block (queries will be rejected until block is ended).
3427 Identifies the message as a row description.
3437 Length of message contents in bytes, including self.
3447 Specifies the number of fields in a row (may be zero).
3452 Then, for each field, there is the following:
3470 If the field can be identified as a column of a specific
3471 table, the object ID of the table; otherwise zero.
3481 If the field can be identified as a column of a specific
3482 table, the attribute number of the column; otherwise zero.
3492 The object ID of the field's datatype.
3502 The datatype size (see <varname>pg_type.typlen</>).
3503 Note that negative values denote variable-width types.
3513 The type modifier (see <varname>pg_attribute.atttypmod</>).
3514 The meaning of the modifier is type-specific.
3539 Length of message contents in bytes, including self.
3549 The SSL request code. The value is chosen to contain
3550 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
3551 least 16 significant bits. (To avoid confusion, this code
3552 must not be the same as any protocol version number.)
3577 Length of message contents in bytes, including self.
3587 The protocol version number. The most significant 16 bits are
3588 the major version number (3 or more for the format described
3590 The least significant 16 bits are the minor version number.
3595 The protocol version number is followed by one or more pairs of
3596 parameter name and value strings. Parameters can appear in any
3597 order. <literal>user</> is required, others are optional.
3598 Each parameter is specified as:
3606 The parameter name. Currently recognized names are:
3615 The database user name to connect as. Required;
3616 there is no default.
3622 <literal>database</>
3626 The database to connect to. Defaults to the user name.
3636 Command-line arguments for the backend. (This is
3637 deprecated in favor of setting individual run-time
3644 In addition to the above, any run-time parameter that can be
3645 set at backend start time may be listed. Such settings
3646 will be applied during backend start (after parsing the
3647 command-line options if any). The values will act as
3658 The parameter value.
3683 Identifies the message as a Sync command.
3693 Length of message contents in bytes, including self.
3718 Identifies the message as a termination.
3728 Length of message contents in bytes, including self.
3744 <Sect1 id="protocol-error-fields">
3745 <Title>Error and Notice Message Fields</Title>
3748 This section describes the fields that may appear in ErrorResponse and
3749 NoticeResponse messages. Each field type has a single-byte identification
3750 token. Note that any given field type should appear at most once per
3762 Severity: the field contents are
3763 <literal>ERROR</>, <literal>FATAL</>, or
3764 <literal>PANIC</> (in an error message), or
3765 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
3766 <literal>INFO</>, or <literal>LOG</> (in a notice message),
3767 or a localized translation of one of these. Always present.
3778 Code: the SQLSTATE code for the error (a 5-character
3779 string following SQL spec conventions). Not localizable.
3791 Message: the primary human-readable error message.
3792 This should be accurate but terse (typically one line).
3804 Detail: an optional secondary error message carrying more
3805 detail about the problem. May run to multiple lines.
3816 Hint: an optional suggestion what to do about the problem.
3817 This is intended to differ from Detail in that it offers advice
3818 (potentially inappropriate) rather than hard facts.
3819 May run to multiple lines.
3830 Position: the field value is a decimal ASCII integer, indicating
3831 an error cursor position as an index into the original query string.
3832 The first character has index 1, and positions are measured in
3833 characters not bytes.
3844 Where: an indication of the context in which the error occurred.
3845 Presently this includes a call stack traceback of active PL functions.
3846 The trace is one entry per line, most recent first.
3857 File: the file name of the source-code location where the error
3869 Line: the line number of the source-code location where the error
3881 Routine: the name of the source-code routine reporting the error.
3889 The client is responsible for formatting displayed information to meet its
3890 needs; in particular it should break long lines as needed. Newline characters
3891 appearing in the error message fields should be treated as paragraph breaks,
3898 <Sect1 id="protocol-changes">
3899 <Title>Summary of Changes since Protocol 2.0</Title>
3902 This section provides a quick checklist of changes, for the benefit of
3903 developers trying to update existing client libraries to protocol 3.0.
3907 The initial startup packet uses a flexible list-of-strings format
3908 instead of a fixed format. Notice that session default values for run-time
3909 parameters can now be specified directly in the startup packet. (Actually,
3910 you could do that before using the <literal>options</> field, but given the
3911 limited width of <literal>options</> and the lack of any way to quote
3912 whitespace in the values, it wasn't a very safe technique.)
3916 All messages now have a length count immediately following the message type
3917 byte (except for startup packets, which have no type byte). Also note that
3918 PasswordMessage now has a type byte.
3922 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
3923 messages now contain multiple fields, from which the client code may
3924 assemble an error message of the desired level of verbosity. Note that
3925 individual fields will typically not end with a newline, whereas the single
3926 string sent in the older protocol always did.
3930 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
3935 There is a new <quote>extended query</> sub-protocol, which adds the frontend
3936 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
3937 backend message types ParseComplete, BindComplete, PortalSuspended,
3938 ParameterDescription, NoData, and CloseComplete. Existing clients do not
3939 have to concern themselves with this sub-protocol, but making use of it
3940 may allow improvements in performance or functionality.
3944 COPY data is now encapsulated into CopyData and CopyDone messages. There
3945 is a well-defined way to recover from errors during COPY. The special
3946 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
3948 (It is still recognized as a terminator during COPY IN, but its use is
3949 deprecated and will eventually be removed.) Binary COPY is supported.
3950 The CopyInResponse and CopyOutResponse messages include a field indicating
3951 whether the COPY operation is text or binary.
3955 The backend sends ParameterStatus ('<literal>S</>') messages during connection
3956 startup for all parameters it considers interesting to the client library.
3957 Subsequently, a ParameterStatus message is sent whenever the active value
3958 changes for any of these parameters.
3962 The RowDescription ('<literal>T</>') message carries new table OID and column
3963 number fields for each column of the described row.
3967 The CursorResponse ('<literal>P</>') message is no longer generated by
3972 The NotificationResponse ('<literal>A</>') message has an additional string
3973 field, which is presently empty but may someday carry additional data passed
3974 from the NOTIFY event sender.
3978 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
3979 string parameter; this has been removed.
3984 Additional changes will be documented as they are implemented.