1 <!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.56 2004/12/13 18:05:08 petere Exp $ -->
3 <chapter id="protocol">
4 <title>Frontend/Backend Protocol</title>
7 <productname>PostgreSQL</productname> uses a message-based protocol
8 for communication between frontends and backends (clients and servers).
9 The protocol is supported over <acronym>TCP/IP</acronym> and also over
10 Unix-domain sockets. Port number 5432 has been registered with IANA as
11 the customary TCP port number for servers supporting this protocol, but
12 in practice any non-privileged port number may be used.
16 This document describes version 3.0 of the protocol, implemented in
17 <productname>PostgreSQL</productname> 7.4 and later. For descriptions
18 of the earlier protocol versions, see previous releases of the
19 <productname>PostgreSQL</productname> documentation. A single server
20 can support multiple protocol versions. The initial
21 startup-request message tells the server which protocol version the
22 client is attempting to use, and then the server follows that protocol
27 Higher level features built on this protocol (for example, how
28 <application>libpq</application> passes certain environment
29 variables when the connection is established) are covered elsewhere.
33 In order to serve multiple clients efficiently, the server launches
34 a new <quote>backend</> process for each client.
35 In the current implementation, a new child
36 process is created immediately after an incoming connection is detected.
37 This is transparent to the protocol, however. For purposes of the
38 protocol, the terms <quote>backend</> and <quote>server</> are
39 interchangeable; likewise <quote>frontend</> and <quote>client</>
43 <sect1 id="protocol-overview">
44 <title>Overview</title>
47 The protocol has separate phases for startup and normal operation.
48 In the startup phase, the frontend opens a connection to the server
49 and authenticates itself to the satisfaction of the server. (This might
50 involve a single message, or multiple messages depending on the
51 authentication method being used.) If all goes well, the server then sends
52 status information to the frontend, and finally enters normal operation.
53 Except for the initial startup-request message, this part of the
54 protocol is driven by the server.
58 During normal operation, the frontend sends queries and
59 other commands to the backend, and the backend sends back query results
60 and other responses. There are a few cases (such as <command>NOTIFY</>)
62 backend will send unsolicited messages, but for the most part this portion
63 of a session is driven by frontend requests.
67 Termination of the session is normally by frontend choice, but can be
68 forced by the backend in certain cases. In any case, when the backend
69 closes the connection, it will roll back any open (incomplete) transaction
74 Within normal operation, SQL commands can be executed through either of
75 two sub-protocols. In the <quote>simple query</> protocol, the frontend
76 just sends a textual query string, which is parsed and immediately
77 executed by the backend. In the <quote>extended query</> protocol,
78 processing of queries is separated into multiple steps: parsing,
79 binding of parameter values, and execution. This offers flexibility
80 and performance benefits, at the cost of extra complexity.
84 Normal operation has additional sub-protocols for special operations
85 such as <command>COPY</>.
88 <sect2 id="protocol-message-concepts">
89 <title>Messaging Overview</title>
92 All communication is through a stream of messages. The first byte of a
93 message identifies the message type, and the next four bytes specify the
94 length of the rest of the message (this length count includes itself, but
95 not the message-type byte). The remaining contents of the message are
96 determined by the message type. For historical reasons, the very first
97 message sent by the client (the startup message) has no initial
102 To avoid losing synchronization with the message stream, both servers and
103 clients typically read an entire message into a buffer (using the byte
104 count) before attempting to process its contents. This allows easy
105 recovery if an error is detected while processing the contents. In
106 extreme situations (such as not having enough memory to buffer the
107 message), the receiver may use the byte count to determine how much
108 input to skip before it resumes reading messages.
112 Conversely, both servers and clients must take care never to send an
113 incomplete message. This is commonly done by marshaling the entire message
114 in a buffer before beginning to send it. If a communications failure
115 occurs partway through sending or receiving a message, the only sensible
116 response is to abandon the connection, since there is little hope of
117 recovering message-boundary synchronization.
121 <sect2 id="protocol-query-concepts">
122 <title>Extended Query Overview</title>
125 In the extended-query protocol, execution of SQL commands is divided
126 into multiple steps. The state retained between steps is represented
127 by two types of objects: <firstterm>prepared statements</> and
128 <firstterm>portals</>. A prepared statement represents the result of
129 parsing, semantic analysis, and planning of a textual query string. A
130 prepared statement is not necessarily ready to execute, because it may
131 lack specific values for <firstterm>parameters</>. A portal represents
132 a ready-to-execute or already-partially-executed statement, with any
133 missing parameter values filled in. (For <command>SELECT</> statements,
134 a portal is equivalent to an open cursor, but we choose to use a different
135 term since cursors don't handle non-<command>SELECT</> statements.)
139 The overall execution cycle consists of a <firstterm>parse</> step,
140 which creates a prepared statement from a textual query string; a
141 <firstterm>bind</> step, which creates a portal given a prepared
142 statement and values for any needed parameters; and an
143 <firstterm>execute</> step that runs a portal's query. In the case of
144 a query that returns rows (<command>SELECT</>, <command>SHOW</>, etc),
145 the execute step can be told to fetch only
146 a limited number of rows, so that multiple execute steps may be needed
147 to complete the operation.
151 The backend can keep track of multiple prepared statements and portals
152 (but note that these exist only within a session, and are never shared
153 across sessions). Existing prepared statements and portals are
154 referenced by names assigned when they were created. In addition,
155 an <quote>unnamed</> prepared statement and portal exist. Although these
156 behave largely the same as named objects, operations on them are optimized
157 for the case of executing a query only once and then discarding it,
158 whereas operations on named objects are optimized on the expectation
163 <sect2 id="protocol-format-codes">
164 <title>Formats and Format Codes</title>
167 Data of a particular data type might be transmitted in any of several
168 different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
169 the only supported formats are <quote>text</> and <quote>binary</>,
170 but the protocol makes provision for future extensions. The desired
171 format for any value is specified by a <firstterm>format code</>.
172 Clients may specify a format code for each transmitted parameter value
173 and for each column of a query result. Text has format code zero,
174 binary has format code one, and all other format codes are reserved
175 for future definition.
179 The text representation of values is whatever strings are produced
180 and accepted by the input/output conversion functions for the
181 particular data type. In the transmitted representation, there is
182 no trailing null character; the frontend must add one to received
183 values if it wants to process them as C strings.
184 (The text format does not allow embedded nulls, by the way.)
188 Binary representations for integers use network byte order (most
189 significant byte first). For other data types consult the documentation
190 or source code to learn about the binary representation. Keep in mind
191 that binary representations for complex data types may change across
192 server versions; the text format is usually the more portable choice.
197 <sect1 id="protocol-flow">
198 <title>Message Flow</title>
201 This section describes the message flow and the semantics of each
202 message type. (Details of the exact representation of each message
203 appear in <xref linkend="protocol-message-formats">.) There are
204 several different sub-protocols depending on the state of the
205 connection: start-up, query, function call,
206 <command>COPY</command>, and termination. There are also special
207 provisions for asynchronous operations (including notification
208 responses and command cancellation), which can occur at any time
209 after the start-up phase.
213 <title>Start-Up</Title>
216 To begin a session, a frontend opens a connection to the server and sends
217 a startup message. This message includes the names of the user and of the
218 database the user wants to connect to; it also identifies the particular
219 protocol version to be used. (Optionally, the startup message can include
220 additional settings for run-time parameters.)
221 The server then uses this information and
222 the contents of its configuration files (such as
223 <filename>pg_hba.conf</filename>) to determine
224 whether the connection is provisionally acceptable, and what additional
225 authentication is required (if any).
229 The server then sends an appropriate authentication request message,
230 to which the frontend must reply with an appropriate authentication
231 response message (such as a password).
232 In principle the authentication request/response cycle could require
233 multiple iterations, but none of the present authentication methods
234 use more than one request and response. In some methods, no response
235 at all is needed from the frontend, and so no authentication request
240 The authentication cycle ends with the server either rejecting the
241 connection attempt (ErrorResponse), or sending AuthenticationOk.
245 The possible messages from the server in this phase are:
249 <term>ErrorResponse</term>
252 The connection attempt has been rejected.
253 The server then immediately closes the connection.
259 <term>AuthenticationOk</term>
262 The authentication exchange is successfully completed.
268 <term>AuthenticationKerberosV4</Term>
271 The frontend must now take part in a Kerberos V4
272 authentication dialog (not described here, part of the
273 Kerberos specification) with the server. If this is
274 successful, the server responds with an AuthenticationOk,
275 otherwise it responds with an ErrorResponse.
281 <Term>AuthenticationKerberosV5</Term>
284 The frontend must now take part in a Kerberos V5
285 authentication dialog (not described here, part of the
286 Kerberos specification) with the server. If this is
287 successful, the server responds with an AuthenticationOk,
288 otherwise it responds with an ErrorResponse.
294 <Term>AuthenticationCleartextPassword</Term>
297 The frontend must now send a PasswordMessage containing the
298 password in clear-text form. If
299 this is the correct password, the server responds with an
300 AuthenticationOk, otherwise it responds with an ErrorResponse.
306 <Term>AuthenticationCryptPassword</Term>
309 The frontend must now send a PasswordMessage containing the
310 password encrypted via crypt(3), using the 2-character salt
311 specified in the AuthenticationCryptPassword message. If
312 this is the correct password, the server responds with an
313 AuthenticationOk, otherwise it responds with an ErrorResponse.
319 <Term>AuthenticationMD5Password</Term>
322 The frontend must now send a PasswordMessage containing the
323 password encrypted via MD5, using the 4-character salt
324 specified in the AuthenticationMD5Password message. If
325 this is the correct password, the server responds with an
326 AuthenticationOk, otherwise it responds with an ErrorResponse.
332 <Term>AuthenticationSCMCredential</Term>
335 This response is only possible for local Unix-domain connections
336 on platforms that support SCM credential messages. The frontend
337 must issue an SCM credential message and then send a single data
338 byte. (The contents of the data byte are uninteresting; it's
339 only used to ensure that the server waits long enough to receive
340 the credential message.) If the credential is acceptable,
341 the server responds with an
342 AuthenticationOk, otherwise it responds with an ErrorResponse.
351 If the frontend does not support the authentication method
352 requested by the server, then it should immediately close the
357 After having received AuthenticationOk, the frontend must wait
358 for further messages from the server. In this phase a backend process
359 is being started, and the frontend is just an interested bystander.
360 It is still possible for the startup attempt
361 to fail (ErrorResponse), but in the normal case the backend will send
362 some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
366 During this phase the backend will attempt to apply any additional
367 run-time parameter settings that were given in the startup message.
368 If successful, these values become session defaults. An error causes
369 ErrorResponse and exit.
373 The possible messages from the backend in this phase are:
377 <Term>BackendKeyData</Term>
380 This message provides secret-key data that the frontend must
381 save if it wants to be able to issue cancel requests later.
382 The frontend should not respond to this message, but should
383 continue listening for a ReadyForQuery message.
389 <Term>ParameterStatus</Term>
392 This message informs the frontend about the current (initial)
393 setting of backend parameters, such as <xref
394 linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
395 The frontend may ignore this message, or record the settings
396 for its future use; see <xref linkend="protocol-async"> for
397 more details. The frontend should not respond to this
398 message, but should continue listening for a ReadyForQuery
405 <Term>ReadyForQuery</Term>
408 Start-up is completed. The frontend may now issue commands.
414 <Term>ErrorResponse</Term>
417 Start-up failed. The connection is closed after sending this
424 <Term>NoticeResponse</Term>
427 A warning message has been issued. The frontend should
428 display the message but continue listening for ReadyForQuery
437 The ReadyForQuery message is the same one that the backend will
438 issue after each command cycle. Depending on the coding needs of
439 the frontend, it is reasonable to consider ReadyForQuery as
440 starting a command cycle, or to consider ReadyForQuery as ending the
441 start-up phase and each subsequent command cycle.
446 <Title>Simple Query</Title>
449 A simple query cycle is initiated by the frontend sending a Query message
450 to the backend. The message includes an SQL command (or commands)
451 expressed as a text string.
452 The backend then sends one or more response
453 messages depending on the contents of the query command string,
454 and finally a ReadyForQuery response message. ReadyForQuery
455 informs the frontend that it may safely send a new command.
456 (It is not actually necessary for the frontend to wait for
457 ReadyForQuery before issuing another command, but the frontend must
458 then take responsibility for figuring out what happens if the earlier
459 command fails and already-issued later commands succeed.)
463 The possible response messages from the backend are:
467 <Term>CommandComplete</Term>
470 An SQL command completed normally.
476 <Term>CopyInResponse</Term>
479 The backend is ready to copy data from the frontend to a
480 table; see <xref linkend="protocol-copy">.
486 <Term>CopyOutResponse</Term>
489 The backend is ready to copy data from a table to the
490 frontend; see <xref linkend="protocol-copy">.
496 <Term>RowDescription</Term>
499 Indicates that rows are about to be returned in response to
500 a <command>SELECT</command>, <command>FETCH</command>, etc query.
501 The contents of this message describe the column layout of the rows.
502 This will be followed by a DataRow message for each row being returned
512 One of the set of rows returned by
513 a <command>SELECT</command>, <command>FETCH</command>, etc query.
519 <Term>EmptyQueryResponse</Term>
522 An empty query string was recognized.
528 <Term>ErrorResponse</Term>
531 An error has occurred.
537 <Term>ReadyForQuery</Term>
540 Processing of the query string is complete. A separate
541 message is sent to indicate this because the query string may
542 contain multiple SQL commands. (CommandComplete marks the
543 end of processing one SQL command, not the whole string.)
544 ReadyForQuery will always be sent, whether processing
545 terminates successfully or with an error.
551 <Term>NoticeResponse</Term>
554 A warning message has been issued in relation to the query.
555 Notices are in addition to other responses, i.e., the backend
556 will continue processing the command.
565 The response to a <command>SELECT</> query (or other queries that
566 return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
567 normally consists of RowDescription, zero or more
568 DataRow messages, and then CommandComplete.
569 <command>COPY</> to or from the frontend invokes special protocol
570 as described in <xref linkend="protocol-copy">.
571 All other query types normally produce only
572 a CommandComplete message.
576 Since a query string could contain several queries (separated by
577 semicolons), there might be several such response sequences before the
578 backend finishes processing the query string. ReadyForQuery is issued
579 when the entire string has been processed and the backend is ready to
580 accept a new query string.
584 If a completely empty (no contents other than whitespace) query string
585 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
589 In the event of an error, ErrorResponse is issued followed by
590 ReadyForQuery. All further processing of the query string is aborted by
591 ErrorResponse (even if more queries remained in it). Note that this
592 may occur partway through the sequence of messages generated by an
597 In simple Query mode, the format of retrieved values is always text,
598 except when the given command is a <command>FETCH</> from a cursor
599 declared with the <literal>BINARY</> option. In that case, the
600 retrieved values are in binary format. The format codes given in
601 the RowDescription message tell which format is being used.
605 A frontend must be prepared to accept ErrorResponse and
606 NoticeResponse messages whenever it is expecting any other type of
607 message. See also <xref linkend="protocol-async"> concerning messages
608 that the backend may generate due to outside events.
612 Recommended practice is to code frontends in a state-machine style
613 that will accept any message type at any time that it could make sense,
614 rather than wiring in assumptions about the exact sequence of messages.
619 <Title>Extended Query</Title>
622 The extended query protocol breaks down the above-described simple
623 query protocol into multiple steps. The results of preparatory
624 steps can be re-used multiple times for improved efficiency.
625 Furthermore, additional features are available, such as the possibility
626 of supplying data values as separate parameters instead of having to
627 insert them directly into a query string.
631 In the extended protocol, the frontend first sends a Parse message,
632 which contains a textual query string, optionally some information
633 about data types of parameter placeholders, and the
634 name of a destination prepared-statement object (an empty string
635 selects the unnamed prepared statement). The response is
636 either ParseComplete or ErrorResponse. Parameter data types may be
637 specified by OID; if not given, the parser attempts to infer the
638 data types in the same way as it would do for untyped literal string
644 The query string contained in a Parse message cannot include more
645 than one SQL statement; else a syntax error is reported. This
646 restriction does not exist in the simple-query protocol, but it
647 does exist in the extended protocol, because allowing prepared
648 statements or portals to contain multiple commands would complicate
654 If successfully created, a named prepared-statement object lasts till
655 the end of the current session, unless explicitly destroyed. An unnamed
656 prepared statement lasts only until the next Parse statement specifying
657 the unnamed statement as destination is issued. (Note that a simple
658 Query message also destroys the unnamed statement.) Named prepared
659 statements must be explicitly closed before they can be redefined by
660 a Parse message, but this is not required for the unnamed statement.
661 Named prepared statements can also be created and accessed at the SQL
662 command level, using <command>PREPARE</> and <command>EXECUTE</>.
666 Once a prepared statement exists, it can be readied for execution using a
667 Bind message. The Bind message gives the name of the source prepared
668 statement (empty string denotes the unnamed prepared statement), the name
669 of the destination portal (empty string denotes the unnamed portal), and
670 the values to use for any parameter placeholders present in the prepared
672 supplied parameter set must match those needed by the prepared statement.
673 Bind also specifies the format to use for any data returned
674 by the query; the format can be specified overall, or per-column.
675 The response is either BindComplete or ErrorResponse.
680 The choice between text and binary output is determined by the format
681 codes given in Bind, regardless of the SQL command involved. The
682 <literal>BINARY</> attribute in cursor declarations is irrelevant when
683 using extended query protocol.
688 Query planning for named prepared-statement objects occurs when the Parse
689 message is received. If a query will be repeatedly executed with
690 different parameters, it may be beneficial to send a single Parse message
691 containing a parameterized query, followed by multiple Bind
692 and Execute messages. This will avoid replanning the query on each
697 The unnamed prepared statement is likewise planned during Parse processing
698 if the Parse message defines no parameters. But if there are parameters,
699 query planning is delayed until the first Bind message for the statement
700 is received. The planner will consider the actual values of the parameters
701 provided in the Bind message when planning the query.
706 Query plans generated from a parameterized query may be less
707 efficient than query plans generated from an equivalent query with actual
708 parameter values substituted. The query planner cannot make decisions
709 based on actual parameter values (for example, index selectivity) when
710 planning a parameterized query assigned to a named prepared-statement
711 object. This possible penalty is avoided when using the unnamed
712 statement, since it is not planned until actual parameter values are
717 If a second or subsequent Bind referencing the unnamed prepared-statement
718 object is received without an intervening Parse, the query is
719 not replanned. The parameter values used in the first Bind message may
720 produce a query plan that is only efficient for a subset of possible
721 parameter values. To force replanning of the query for a fresh set of
722 parameters, send another Parse message to replace the unnamed
723 prepared-statement object.
728 If successfully created, a named portal object lasts till the end of the
729 current transaction, unless explicitly destroyed. An unnamed portal is
730 destroyed at the end of the transaction, or as soon as the next Bind
731 statement specifying the unnamed portal as destination is issued. (Note
732 that a simple Query message also destroys the unnamed portal.) Named
733 portals must be explicitly closed before they can be redefined by a Bind
734 message, but this is not required for the unnamed portal.
735 Named portals can also be created and accessed at the SQL
736 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
740 Once a portal exists, it can be executed using an Execute message.
741 The Execute message specifies the portal name (empty string denotes the
743 a maximum result-row count (zero meaning <quote>fetch all rows</>).
744 The result-row count is only meaningful for portals
745 containing commands that return row sets; in other cases the command is
746 always executed to completion, and the row count is ignored.
748 responses to Execute are the same as those described above for queries
749 issued via simple query protocol, except that Execute doesn't cause
750 ReadyForQuery or RowDescription to be issued.
754 If Execute terminates before completing the execution of a portal
755 (due to reaching a nonzero result-row count), it will send a
756 PortalSuspended message; the appearance of this message tells the frontend
757 that another Execute should be issued against the same portal to
758 complete the operation. The CommandComplete message indicating
759 completion of the source SQL command is not sent until
760 the portal's execution is completed. Therefore, an Execute phase is
761 always terminated by the appearance of exactly one of these messages:
762 CommandComplete, EmptyQueryResponse (if the portal was created from
763 an empty query string), ErrorResponse, or PortalSuspended.
767 At completion of each series of extended-query messages, the frontend
768 should issue a Sync message. This parameterless message causes the
769 backend to close the current transaction if it's not inside a
770 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
771 meaning to commit if no error, or roll back if error). Then a
772 ReadyForQuery response is issued. The purpose of Sync is to provide
773 a resynchronization point for error recovery. When an error is detected
774 while processing any extended-query message, the backend issues
775 ErrorResponse, then reads and discards messages until a Sync is reached,
776 then issues ReadyForQuery and returns to normal message processing.
777 (But note that no skipping occurs if an error is detected
778 <emphasis>while</> processing Sync — this ensures that there is one
779 and only one ReadyForQuery sent for each Sync.)
784 Sync does not cause a transaction block opened with <command>BEGIN</>
785 to be closed. It is possible to detect this situation since the
786 ReadyForQuery message includes transaction status information.
791 In addition to these fundamental, required operations, there are several
792 optional operations that can be used with extended-query protocol.
796 The Describe message (portal variant) specifies the name of an existing
797 portal (or an empty string for the unnamed portal). The response is a
798 RowDescription message describing the rows that will be returned by
799 executing the portal; or a NoData message if the portal does not contain a
800 query that will return rows; or ErrorResponse if there is no such portal.
804 The Describe message (statement variant) specifies the name of an existing
805 prepared statement (or an empty string for the unnamed prepared
806 statement). The response is a ParameterDescription message describing the
807 parameters needed by the statement, followed by a RowDescription message
808 describing the rows that will be returned when the statement is eventually
809 executed (or a NoData message if the statement will not return rows).
810 ErrorResponse is issued if there is no such prepared statement. Note that
811 since Bind has not yet been issued, the formats to be used for returned
812 columns are not yet known to the backend; the format code fields in the
813 RowDescription message will be zeroes in this case.
818 In most scenarios the frontend should issue one or the other variant
819 of Describe before issuing Execute, to ensure that it knows how to
820 interpret the results it will get back.
825 The Close message closes an existing prepared statement or portal
826 and releases resources. It is not an error to issue Close against
827 a nonexistent statement or portal name. The response is normally
828 CloseComplete, but could be ErrorResponse if some difficulty is
829 encountered while releasing resources. Note that closing a prepared
830 statement implicitly closes any open portals that were constructed
835 The Flush message does not cause any specific output to be generated,
836 but forces the backend to deliver any data pending in its output
837 buffers. A Flush must be sent after any extended-query command except
838 Sync, if the frontend wishes to examine the results of that command before
839 issuing more commands. Without Flush, messages returned by the backend
840 will be combined into the minimum possible number of packets to minimize
846 The simple Query message is approximately equivalent to the series Parse,
847 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
848 statement and portal objects and no parameters. One difference is that
849 it will accept multiple SQL statements in the query string, automatically
850 performing the bind/describe/execute sequence for each one in succession.
851 Another difference is that it will not return ParseComplete, BindComplete,
852 CloseComplete, or NoData messages.
858 <Title>Function Call</Title>
861 The Function Call sub-protocol allows the client to request a direct
862 call of any function that exists in the database's
863 <structname>pg_proc</structname> system catalog. The client must have
864 execute permission for the function.
869 The Function Call sub-protocol is a legacy feature that is probably best
870 avoided in new code. Similar results can be accomplished by setting up
871 a prepared statement that does <literal>SELECT function($1, ...)</>.
872 The Function Call cycle can then be replaced with Bind/Execute.
877 A Function Call cycle is initiated by the frontend sending a
878 FunctionCall message to the backend. The backend then sends one
879 or more response messages depending on the results of the function
880 call, and finally a ReadyForQuery response message. ReadyForQuery
881 informs the frontend that it may safely send a new query or
886 The possible response messages from the backend are:
890 <Term>ErrorResponse</Term>
893 An error has occurred.
899 <Term>FunctionCallResponse</Term>
902 The function call was completed and returned the result given
904 (Note that the Function Call protocol can only handle a single
905 scalar result, not a row type or set of results.)
911 <Term>ReadyForQuery</Term>
914 Processing of the function call is complete. ReadyForQuery
915 will always be sent, whether processing terminates
916 successfully or with an error.
922 <Term>NoticeResponse</Term>
925 A warning message has been issued in relation to the function
926 call. Notices are in addition to other responses, i.e., the
927 backend will continue processing the command.
935 <sect2 id="protocol-copy">
936 <title>COPY Operations</title>
939 The <command>COPY</> command allows high-speed bulk data transfer
940 to or from the server. Copy-in and copy-out operations each switch
941 the connection into a distinct sub-protocol, which lasts until the
942 operation is completed.
946 Copy-in mode (data transfer to the server) is initiated when the
947 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
948 sends a CopyInResponse message to the frontend. The frontend should
949 then send zero or more CopyData messages, forming a stream of input
950 data. (The message boundaries are not required to have anything to do
951 with row boundaries, although that is often a reasonable choice.)
952 The frontend can terminate the copy-in mode by sending either a CopyDone
953 message (allowing successful termination) or a CopyFail message (which
954 will cause the <command>COPY</> SQL statement to fail with an
955 error). The backend then reverts to the command-processing mode it was
956 in before the <command>COPY</> started, which will be either simple or
957 extended query protocol. It will next send either CommandComplete
958 (if successful) or ErrorResponse (if not).
962 In the event of a backend-detected error during copy-in mode (including
963 receipt of a CopyFail message), the backend will issue an ErrorResponse
964 message. If the <command>COPY</> command was issued via an extended-query
965 message, the backend will now discard frontend messages until a Sync
966 message is received, then it will issue ReadyForQuery and return to normal
967 processing. If the <command>COPY</> command was issued in a simple
968 Query message, the rest of that message is discarded and ReadyForQuery
969 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
970 messages issued by the frontend will simply be dropped.
974 The backend will ignore Flush and Sync messages received during copy-in
975 mode. Receipt of any other non-copy message type constitutes an error
976 that will abort the copy-in state as described above. (The exception for
977 Flush and Sync is for the convenience of client libraries that always
978 send Flush or Sync after an Execute message, without checking whether
979 the command to be executed is a <command>COPY FROM STDIN</>.)
983 Copy-out mode (data transfer from the server) is initiated when the
984 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
985 sends a CopyOutResponse message to the frontend, followed by
986 zero or more CopyData messages (always one per row), followed by CopyDone.
987 The backend then reverts to the command-processing mode it was
988 in before the <command>COPY</> started, and sends CommandComplete.
989 The frontend cannot abort the transfer (except by closing the connection
990 or issuing a Cancel request),
991 but it can discard unwanted CopyData and CopyDone messages.
995 In the event of a backend-detected error during copy-out mode,
996 the backend will issue an ErrorResponse message and revert to normal
997 processing. The frontend should treat receipt of ErrorResponse (or
998 indeed any message type other than CopyData or CopyDone) as terminating
1003 The CopyInResponse and CopyOutResponse messages include fields that
1004 inform the frontend of the number of columns per row and the format
1005 codes being used for each column. (As of the present implementation,
1006 all columns in a given <command>COPY</> operation will use the same
1007 format, but the message design does not assume this.)
1011 <sect2 id="protocol-async">
1012 <title>Asynchronous Operations</title>
1015 There are several cases in which the backend will send messages that
1016 are not specifically prompted by the frontend's command stream.
1017 Frontends must be prepared to deal with these messages at any time,
1018 even when not engaged in a query.
1019 At minimum, one should check for these cases before beginning to
1020 read a query response.
1024 It is possible for NoticeResponse messages to be generated due to
1025 outside activity; for example, if the database administrator commands
1026 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1027 indicating this fact before closing the connection. Accordingly,
1028 frontends should always be prepared to accept and display NoticeResponse
1029 messages, even when the connection is nominally idle.
1033 ParameterStatus messages will be generated whenever the active
1034 value changes for any of the parameters the backend believes the
1035 frontend should know about. Most commonly this occurs in response
1036 to a <command>SET</> SQL command executed by the frontend, and
1037 this case is effectively synchronous — but it is also possible
1038 for parameter status changes to occur because the administrator
1039 changed a configuration file and then sent the
1040 <systemitem>SIGHUP</systemitem> signal to the postmaster. Also,
1041 if a <command>SET</command> command is rolled back, an appropriate
1042 ParameterStatus message will be generated to report the current
1047 At present there is a hard-wired set of parameters for which
1048 ParameterStatus will be generated: they are
1049 <literal>server_version</>,
1050 <literal>server_encoding</>,
1051 <literal>client_encoding</>,
1052 <literal>is_superuser</>,
1053 <literal>session_authorization</>,
1054 <literal>DateStyle</>, and
1055 <literal>integer_datetimes</>.
1056 (<literal>server_encoding</> and <literal>integer_datetimes</> were not
1057 reported by releases before 8.0.)
1059 <literal>server_version</>,
1060 <literal>server_encoding</> and
1061 <literal>integer_datetimes</>
1062 are pseudo-parameters that cannot change after startup.
1063 This set might change in the future, or even become configurable.
1064 Accordingly, a frontend should simply ignore ParameterStatus for
1065 parameters that it does not understand or care about.
1069 If a frontend issues a <command>LISTEN</command> command, then the
1070 backend will send a NotificationResponse message (not to be
1071 confused with NoticeResponse!) whenever a
1072 <command>NOTIFY</command> command is executed for the same
1078 At present, NotificationResponse can only be sent outside a
1079 transaction, and thus it will not occur in the middle of a
1080 command-response series, though it may occur just before ReadyForQuery.
1081 It is unwise to design frontend logic that assumes that, however.
1082 Good practice is to be able to accept NotificationResponse at any
1083 point in the protocol.
1089 <Title>Cancelling Requests in Progress</Title>
1092 During the processing of a query, the frontend may request
1093 cancellation of the query. The cancel request is not sent
1094 directly on the open connection to the backend for reasons of
1095 implementation efficiency: we don't want to have the backend
1096 constantly checking for new input from the frontend during query
1097 processing. Cancel requests should be relatively infrequent, so
1098 we make them slightly cumbersome in order to avoid a penalty in
1103 To issue a cancel request, the frontend opens a new connection to
1104 the server and sends a CancelRequest message, rather than the
1105 StartupMessage message that would ordinarily be sent across a new
1106 connection. The server will process this request and then close
1107 the connection. For security reasons, no direct reply is made to
1108 the cancel request message.
1112 A CancelRequest message will be ignored unless it contains the
1113 same key data (PID and secret key) passed to the frontend during
1114 connection start-up. If the request matches the PID and secret
1115 key for a currently executing backend, the processing of the
1116 current query is aborted. (In the existing implementation, this is
1117 done by sending a special signal to the backend process that is
1118 processing the query.)
1122 The cancellation signal may or may not have any effect — for
1123 example, if it arrives after the backend has finished processing
1124 the query, then it will have no effect. If the cancellation is
1125 effective, it results in the current command being terminated
1126 early with an error message.
1130 The upshot of all this is that for reasons of both security and
1131 efficiency, the frontend has no direct way to tell whether a
1132 cancel request has succeeded. It must continue to wait for the
1133 backend to respond to the query. Issuing a cancel simply improves
1134 the odds that the current query will finish soon, and improves the
1135 odds that it will fail with an error message instead of
1140 Since the cancel request is sent across a new connection to the
1141 server and not across the regular frontend/backend communication
1142 link, it is possible for the cancel request to be issued by any
1143 process, not just the frontend whose query is to be canceled.
1144 This may have some benefits of flexibility in building
1145 multiple-process applications. It also introduces a security
1146 risk, in that unauthorized persons might try to cancel queries.
1147 The security risk is addressed by requiring a dynamically
1148 generated secret key to be supplied in cancel requests.
1153 <Title>Termination</Title>
1156 The normal, graceful termination procedure is that the frontend
1157 sends a Terminate message and immediately closes the connection.
1158 On receipt of this message, the backend closes the connection and
1163 In rare cases (such as an administrator-commanded database shutdown)
1164 the backend may disconnect without any frontend request to do so.
1165 In such cases the backend will attempt to send an error or notice message
1166 giving the reason for the disconnection before it closes the connection.
1170 Other termination scenarios arise from various failure cases, such as core
1171 dump at one end or the other, loss of the communications link, loss of
1172 message-boundary synchronization, etc. If either frontend or backend sees
1173 an unexpected closure of the connection, it should clean
1174 up and terminate. The frontend has the option of launching a new backend
1175 by recontacting the server if it doesn't want to terminate itself.
1176 Closing the connection is also advisable if an unrecognizable message type
1177 is received, since this probably indicates loss of message-boundary sync.
1181 For either normal or abnormal termination, any open transaction is
1182 rolled back, not committed. One should note however that if a
1183 frontend disconnects while a non-<command>SELECT</command> query
1184 is being processed, the backend will probably finish the query
1185 before noticing the disconnection. If the query is outside any
1186 transaction block (<command>BEGIN</> ... <command>COMMIT</>
1187 sequence) then its results may be committed before the
1188 disconnection is recognized.
1193 <Title><acronym>SSL</acronym> Session Encryption</Title>
1196 If <productname>PostgreSQL</> was built with
1197 <acronym>SSL</acronym> support, frontend/backend communications
1198 can be encrypted using <acronym>SSL</acronym>. This provides
1199 communication security in environments where attackers might be
1200 able to capture the session traffic. For more information on
1201 encrypting <productname>PostgreSQL</productname> sessions with
1202 <acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1206 To initiate an <acronym>SSL</acronym>-encrypted connection, the
1207 frontend initially sends an SSLRequest message rather than a
1208 StartupMessage. The server then responds with a single byte
1209 containing <literal>S</> or <literal>N</>, indicating that it is
1210 willing or unwilling to perform <acronym>SSL</acronym>,
1211 respectively. The frontend may close the connection at this point
1212 if it is dissatisfied with the response. To continue after
1213 <literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1214 (not described here, part of the <acronym>SSL</acronym>
1215 specification) with the server. If this is successful, continue
1216 with sending the usual StartupMessage. In this case the
1217 StartupMessage and all subsequent data will be
1218 <acronym>SSL</acronym>-encrypted. To continue after
1219 <literal>N</>, send the usual StartupMessage and proceed without
1224 The frontend should also be prepared to handle an ErrorMessage
1225 response to SSLRequest from the server. This would only occur if
1226 the server predates the addition of <acronym>SSL</acronym> support
1227 to <productname>PostgreSQL</>. In this case the connection must
1228 be closed, but the frontend may choose to open a fresh connection
1229 and proceed without requesting <acronym>SSL</acronym>.
1233 An initial SSLRequest may also be used in a connection that is being
1234 opened to send a CancelRequest message.
1238 While the protocol itself does not provide a way for the server to
1239 force <acronym>SSL</acronym> encryption, the administrator may
1240 configure the server to reject unencrypted sessions as a byproduct
1241 of authentication checking.
1246 <Sect1 id="protocol-message-types">
1247 <Title>Message Data Types</Title>
1250 This section describes the base data types used in messages.
1256 Int<Replaceable>n</Replaceable>(<Replaceable>i</Replaceable>)
1260 An <Replaceable>n</Replaceable>-bit integer in network byte
1261 order (most significant byte first).
1262 If <Replaceable>i</Replaceable> is specified it
1263 is the exact value that will appear, otherwise the value
1264 is variable. Eg. Int16, Int32(42).
1271 Int<Replaceable>n</Replaceable>[<Replaceable>k</Replaceable>]
1275 An array of <Replaceable>k</Replaceable>
1276 <Replaceable>n</Replaceable>-bit integers, each in network
1277 byte order. The array length <Replaceable>k</Replaceable>
1278 is always determined by an earlier field in the message.
1286 String(<Replaceable>s</Replaceable>)
1290 A null-terminated string (C-style string). There is no
1291 specific length limitation on strings.
1292 If <Replaceable>s</Replaceable> is specified it is the exact
1293 value that will appear, otherwise the value is variable.
1294 Eg. String, String("user").
1299 <Emphasis>There is no predefined limit</Emphasis> on the length of a string
1300 that can be returned by the backend. Good coding strategy for a frontend
1301 is to use an expandable buffer so that anything that fits in memory can be
1302 accepted. If that's not feasible, read the full string and discard trailing
1303 characters that don't fit into your fixed-size buffer.
1311 Byte<Replaceable>n</Replaceable>(<Replaceable>c</Replaceable>)
1315 Exactly <Replaceable>n</Replaceable> bytes. If the field
1316 width <Replaceable>n</Replaceable> is not a constant, it is
1317 always determinable from an earlier field in the message.
1318 If <Replaceable>c</Replaceable> is specified it is the exact
1319 value. Eg. Byte2, Byte1('\n').
1328 <Sect1 id="protocol-message-formats">
1329 <Title>Message Formats</Title>
1332 This section describes the detailed format of each message. Each is marked to
1333 indicate that it may be sent by a frontend (F), a backend (B), or both
1335 Notice that although each message includes a byte count at the beginning,
1336 the message format is defined so that the message end can be found without
1337 reference to the byte count. This aids validity checking. (The CopyData
1338 message is an exception, because it forms part of a data stream; the contents
1339 of any individual CopyData message may not be interpretable on their own.)
1347 AuthenticationOk (B)
1359 Identifies the message as an authentication request.
1369 Length of message contents in bytes, including self.
1379 Specifies that the authentication was successful.
1392 AuthenticationKerberosV4 (B)
1404 Identifies the message as an authentication request.
1414 Length of message contents in bytes, including self.
1424 Specifies that Kerberos V4 authentication is required.
1436 AuthenticationKerberosV5 (B)
1448 Identifies the message as an authentication request.
1458 Length of message contents in bytes, including self.
1468 Specifies that Kerberos V5 authentication is required.
1480 AuthenticationCleartextPassword (B)
1492 Identifies the message as an authentication request.
1502 Length of message contents in bytes, including self.
1512 Specifies that a clear-text password is required.
1524 AuthenticationCryptPassword (B)
1536 Identifies the message as an authentication request.
1546 Length of message contents in bytes, including self.
1556 Specifies that a crypt()-encrypted password is required.
1566 The salt to use when encrypting the password.
1579 AuthenticationMD5Password (B)
1591 Identifies the message as an authentication request.
1601 Length of message contents in bytes, including self.
1611 Specifies that an MD5-encrypted password is required.
1621 The salt to use when encrypting the password.
1634 AuthenticationSCMCredential (B)
1646 Identifies the message as an authentication request.
1656 Length of message contents in bytes, including self.
1666 Specifies that an SCM credentials message is required.
1691 Identifies the message as cancellation key data.
1692 The frontend must save these values if it wishes to be
1693 able to issue CancelRequest messages later.
1703 Length of message contents in bytes, including self.
1713 The process ID of this backend.
1723 The secret key of this backend.
1748 Identifies the message as a Bind command.
1758 Length of message contents in bytes, including self.
1768 The name of the destination portal
1769 (an empty string selects the unnamed portal).
1779 The name of the source prepared statement
1780 (an empty string selects the unnamed prepared statement).
1790 The number of parameter format codes that follow
1791 (denoted <replaceable>C</> below).
1792 This can be zero to indicate that there are no parameters
1793 or that the parameters all use the default format (text);
1794 or one, in which case the specified format code is applied
1795 to all parameters; or it can equal the actual number of
1802 Int16[<replaceable>C</>]
1806 The parameter format codes. Each must presently be
1807 zero (text) or one (binary).
1817 The number of parameter values that follow (possibly zero).
1818 This must match the number of parameters needed by the query.
1823 Next, the following pair of fields appear for each parameter:
1831 The length of the parameter value, in bytes (this count
1832 does not include itself). Can be zero.
1833 As a special case, -1 indicates a NULL parameter value.
1834 No value bytes follow in the NULL case.
1840 Byte<Replaceable>n</Replaceable>
1844 The value of the parameter, in the format indicated by the
1845 associated format code.
1846 <Replaceable>n</Replaceable> is the above length.
1851 After the last parameter, the following fields appear:
1859 The number of result-column format codes that follow
1860 (denoted <replaceable>R</> below).
1861 This can be zero to indicate that there are no result columns
1862 or that the result columns should all use the default format
1864 or one, in which case the specified format code is applied
1865 to all result columns (if any); or it can equal the actual
1866 number of result columns of the query.
1872 Int16[<replaceable>R</>]
1876 The result-column format codes. Each must presently be
1877 zero (text) or one (binary).
1901 Identifies the message as a Bind-complete indicator.
1911 Length of message contents in bytes, including self.
1936 Length of message contents in bytes, including self.
1946 The cancel request code. The value is chosen to contain
1947 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
1948 least 16 significant bits. (To avoid confusion, this code
1949 must not be the same as any protocol version number.)
1959 The process ID of the target backend.
1969 The secret key for the target backend.
1994 Identifies the message as a Close command.
2004 Length of message contents in bytes, including self.
2014 '<literal>S</>' to close a prepared statement; or
2015 '<literal>P</>' to close a portal.
2025 The name of the prepared statement or portal to close
2026 (an empty string selects the unnamed prepared statement
2051 Identifies the message as a Close-complete indicator.
2061 Length of message contents in bytes, including self.
2086 Identifies the message as a command-completed response.
2096 Length of message contents in bytes, including self.
2106 The command tag. This is usually a single
2107 word that identifies which SQL command was completed.
2111 For an <command>INSERT</command> command, the tag is
2112 <literal>INSERT <replaceable>oid</replaceable>
2113 <replaceable>rows</replaceable></literal>, where
2114 <replaceable>rows</replaceable> is the number of rows
2115 inserted. <replaceable>oid</replaceable> is the object ID
2116 of the inserted row if <Replaceable>rows</Replaceable> is 1
2117 and the target table has OIDs;
2118 otherwise <Replaceable>oid</Replaceable> is 0.
2122 For a <command>DELETE</command> command, the tag is
2123 <literal>DELETE <Replaceable>rows</Replaceable></literal> where
2124 <Replaceable>rows</Replaceable> is the number of rows deleted.
2128 For an <command>UPDATE</command> command, the tag is
2129 <literal>UPDATE <Replaceable>rows</Replaceable></literal> where
2130 <Replaceable>rows</Replaceable> is the number of rows updated.
2134 For a <command>MOVE</command> command, the tag is
2135 <literal>MOVE <replaceable>rows</replaceable></literal> where
2136 <replaceable>rows</replaceable> is the number of rows the
2137 cursor's position has been changed by.
2141 For a <command>FETCH</command> command, the tag is
2142 <literal>FETCH <replaceable>rows</replaceable></literal> where
2143 <replaceable>rows</replaceable> is the number of rows that
2144 have been retrieved from the cursor.
2157 CopyData (F & B)
2168 Identifies the message as <command>COPY</command> data.
2178 Length of message contents in bytes, including self.
2184 Byte<Replaceable>n</Replaceable>
2188 Data that forms part of a <command>COPY</command> data stream. Messages sent
2189 from the backend will always correspond to single data rows,
2190 but messages sent by frontends may divide the data stream
2203 CopyDone (F & B)
2215 Identifies the message as a <command>COPY</command>-complete indicator.
2225 Length of message contents in bytes, including self.
2250 Identifies the message as a <command>COPY</command>-failure indicator.
2260 Length of message contents in bytes, including self.
2270 An error message to report as the cause of failure.
2295 Identifies the message as a Start Copy In response.
2296 The frontend must now send copy-in data (if not
2297 prepared to do so, send a CopyFail message).
2307 Length of message contents in bytes, including self.
2317 0 indicates the overall <command>COPY</command> format is textual (rows
2318 separated by newlines, columns separated by separator
2320 1 indicates the overall copy format is binary (similar
2322 See <xref linkend="sql-copy" endterm="sql-copy-title">
2323 for more information.
2333 The number of columns in the data to be copied
2334 (denoted <replaceable>N</> below).
2340 Int16[<replaceable>N</>]
2344 The format codes to be used for each column.
2345 Each must presently be zero (text) or one (binary).
2346 All must be zero if the overall copy format is textual.
2371 Identifies the message as a Start Copy Out response.
2372 This message will be followed by copy-out data.
2382 Length of message contents in bytes, including self.
2392 0 indicates the overall <command>COPY</command> format
2393 is textual (rows separated by newlines, columns
2394 separated by separator characters, etc). 1 indicates
2395 the overall copy format is binary (similar to DataRow
2396 format). See <xref linkend="sql-copy"
2397 endterm="sql-copy-title"> for more information.
2407 The number of columns in the data to be copied
2408 (denoted <replaceable>N</> below).
2414 Int16[<replaceable>N</>]
2418 The format codes to be used for each column.
2419 Each must presently be zero (text) or one (binary).
2420 All must be zero if the overall copy format is textual.
2444 Identifies the message as a data row.
2454 Length of message contents in bytes, including self.
2464 The number of column values that follow (possibly zero).
2469 Next, the following pair of fields appear for each column:
2477 The length of the column value, in bytes (this count
2478 does not include itself). Can be zero.
2479 As a special case, -1 indicates a NULL column value.
2480 No value bytes follow in the NULL case.
2486 Byte<Replaceable>n</Replaceable>
2490 The value of the column, in the format indicated by the
2491 associated format code.
2492 <Replaceable>n</Replaceable> is the above length.
2517 Identifies the message as a Describe command.
2527 Length of message contents in bytes, including self.
2537 '<literal>S</>' to describe a prepared statement; or
2538 '<literal>P</>' to describe a portal.
2548 The name of the prepared statement or portal to describe
2549 (an empty string selects the unnamed prepared statement
2562 EmptyQueryResponse (B)
2574 Identifies the message as a response to an empty query string.
2575 (This substitutes for CommandComplete.)
2585 Length of message contents in bytes, including self.
2610 Identifies the message as an error.
2620 Length of message contents in bytes, including self.
2625 The message body consists of one or more identified fields,
2626 followed by a zero byte as a terminator. Fields may appear in
2627 any order. For each field there is the following:
2635 A code identifying the field type; if zero, this is
2636 the message terminator and no string follows.
2637 The presently defined field types are listed in
2638 <xref linkend="protocol-error-fields">.
2639 Since more field types may be added in future,
2640 frontends should silently ignore fields of unrecognized
2676 Identifies the message as an Execute command.
2686 Length of message contents in bytes, including self.
2696 The name of the portal to execute
2697 (an empty string selects the unnamed portal).
2707 Maximum number of rows to return, if portal contains
2708 a query that returns rows (ignored otherwise). Zero
2709 denotes <quote>no limit</>.
2733 Identifies the message as a Flush command.
2743 Length of message contents in bytes, including self.
2768 Identifies the message as a function call.
2778 Length of message contents in bytes, including self.
2788 Specifies the object ID of the function to call.
2798 The number of argument format codes that follow
2799 (denoted <replaceable>C</> below).
2800 This can be zero to indicate that there are no arguments
2801 or that the arguments all use the default format (text);
2802 or one, in which case the specified format code is applied
2803 to all arguments; or it can equal the actual number of
2810 Int16[<replaceable>C</>]
2814 The argument format codes. Each must presently be
2815 zero (text) or one (binary).
2825 Specifies the number of arguments being supplied to the
2831 Next, the following pair of fields appear for each argument:
2839 The length of the argument value, in bytes (this count
2840 does not include itself). Can be zero.
2841 As a special case, -1 indicates a NULL argument value.
2842 No value bytes follow in the NULL case.
2848 Byte<Replaceable>n</Replaceable>
2852 The value of the argument, in the format indicated by the
2853 associated format code.
2854 <Replaceable>n</Replaceable> is the above length.
2859 After the last argument, the following field appears:
2867 The format code for the function result. Must presently be
2868 zero (text) or one (binary).
2881 FunctionCallResponse (B)
2893 Identifies the message as a function call result.
2903 Length of message contents in bytes, including self.
2913 The length of the function result value, in bytes (this count
2914 does not include itself). Can be zero.
2915 As a special case, -1 indicates a NULL function result.
2916 No value bytes follow in the NULL case.
2922 Byte<Replaceable>n</Replaceable>
2926 The value of the function result, in the format indicated by
2927 the associated format code.
2928 <Replaceable>n</Replaceable> is the above length.
2953 Identifies the message as a no-data indicator.
2963 Length of message contents in bytes, including self.
2988 Identifies the message as a notice.
2998 Length of message contents in bytes, including self.
3003 The message body consists of one or more identified fields,
3004 followed by a zero byte as a terminator. Fields may appear in
3005 any order. For each field there is the following:
3013 A code identifying the field type; if zero, this is
3014 the message terminator and no string follows.
3015 The presently defined field types are listed in
3016 <xref linkend="protocol-error-fields">.
3017 Since more field types may be added in future,
3018 frontends should silently ignore fields of unrecognized
3042 NotificationResponse (B)
3054 Identifies the message as a notification response.
3064 Length of message contents in bytes, including self.
3074 The process ID of the notifying backend process.
3084 The name of the condition that the notify has been raised on.
3094 Additional information passed from the notifying process.
3095 (Currently, this feature is unimplemented so the field
3096 is always an empty string.)
3109 ParameterDescription (B)
3121 Identifies the message as a parameter description.
3131 Length of message contents in bytes, including self.
3141 The number of parameters used by the statement
3147 Then, for each parameter, there is the following:
3155 Specifies the object ID of the parameter data type.
3179 Identifies the message as a run-time parameter status report.
3189 Length of message contents in bytes, including self.
3199 The name of the run-time parameter being reported.
3209 The current value of the parameter.
3233 Identifies the message as a Parse command.
3243 Length of message contents in bytes, including self.
3253 The name of the destination prepared statement
3254 (an empty string selects the unnamed prepared statement).
3264 The query string to be parsed.
3274 The number of parameter data types specified
3275 (may be zero). Note that this is not an indication of
3276 the number of parameters that might appear in the
3277 query string, only the number that the frontend wants to
3278 prespecify types for.
3283 Then, for each parameter, there is the following:
3291 Specifies the object ID of the parameter data type.
3292 Placing a zero here is equivalent to leaving the type
3317 Identifies the message as a Parse-complete indicator.
3327 Length of message contents in bytes, including self.
3352 Identifies the message as a password response.
3362 Length of message contents in bytes, including self.
3372 The password (encrypted, if requested).
3396 Identifies the message as a portal-suspended indicator.
3397 Note this only appears if an Execute message's row-count limit
3408 Length of message contents in bytes, including self.
3433 Identifies the message as a simple query.
3443 Length of message contents in bytes, including self.
3453 The query string itself.
3478 Identifies the message type. ReadyForQuery is sent
3479 whenever the backend is ready for a new query cycle.
3489 Length of message contents in bytes, including self.
3499 Current backend transaction status indicator.
3500 Possible values are '<literal>I</>' if idle (not in
3501 a transaction block); '<literal>T</>' if in a transaction
3502 block; or '<literal>E</>' if in a failed transaction
3503 block (queries will be rejected until block is ended).
3528 Identifies the message as a row description.
3538 Length of message contents in bytes, including self.
3548 Specifies the number of fields in a row (may be zero).
3553 Then, for each field, there is the following:
3571 If the field can be identified as a column of a specific
3572 table, the object ID of the table; otherwise zero.
3582 If the field can be identified as a column of a specific
3583 table, the attribute number of the column; otherwise zero.
3593 The object ID of the field's data type.
3603 The data type size (see <varname>pg_type.typlen</>).
3604 Note that negative values denote variable-width types.
3614 The type modifier (see <varname>pg_attribute.atttypmod</>).
3615 The meaning of the modifier is type-specific.
3625 The format code being used for the field. Currently will
3626 be zero (text) or one (binary). In a RowDescription
3627 returned from the statement variant of Describe, the
3628 format code is not yet known and will always be zero.
3653 Length of message contents in bytes, including self.
3663 The <acronym>SSL</acronym> request code. The value is chosen to contain
3664 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
3665 least 16 significant bits. (To avoid confusion, this code
3666 must not be the same as any protocol version number.)
3691 Length of message contents in bytes, including self.
3701 The protocol version number. The most significant 16 bits are
3702 the major version number (3 for the protocol described here).
3703 The least significant 16 bits are the minor version number
3704 (0 for the protocol described here).
3709 The protocol version number is followed by one or more pairs of
3710 parameter name and value strings. A zero byte is required as a
3711 terminator after the last name/value pair.
3712 Parameters can appear in any
3713 order. <literal>user</> is required, others are optional.
3714 Each parameter is specified as:
3722 The parameter name. Currently recognized names are:
3731 The database user name to connect as. Required;
3732 there is no default.
3738 <literal>database</>
3742 The database to connect to. Defaults to the user name.
3752 Command-line arguments for the backend. (This is
3753 deprecated in favor of setting individual run-time
3760 In addition to the above, any run-time parameter that can be
3761 set at backend start time may be listed. Such settings
3762 will be applied during backend start (after parsing the
3763 command-line options if any). The values will act as
3774 The parameter value.
3799 Identifies the message as a Sync command.
3809 Length of message contents in bytes, including self.
3834 Identifies the message as a termination.
3844 Length of message contents in bytes, including self.
3860 <Sect1 id="protocol-error-fields">
3861 <Title>Error and Notice Message Fields</Title>
3864 This section describes the fields that may appear in ErrorResponse and
3865 NoticeResponse messages. Each field type has a single-byte identification
3866 token. Note that any given field type should appear at most once per
3878 Severity: the field contents are
3879 <literal>ERROR</>, <literal>FATAL</>, or
3880 <literal>PANIC</> (in an error message), or
3881 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
3882 <literal>INFO</>, or <literal>LOG</> (in a notice message),
3883 or a localized translation of one of these. Always present.
3894 Code: the SQLSTATE code for the error (see <xref
3895 linkend="errcodes-appendix">). Not localizable. Always present.
3906 Message: the primary human-readable error message.
3907 This should be accurate but terse (typically one line).
3919 Detail: an optional secondary error message carrying more
3920 detail about the problem. May run to multiple lines.
3931 Hint: an optional suggestion what to do about the problem.
3932 This is intended to differ from Detail in that it offers advice
3933 (potentially inappropriate) rather than hard facts.
3934 May run to multiple lines.
3945 Position: the field value is a decimal ASCII integer, indicating
3946 an error cursor position as an index into the original query string.
3947 The first character has index 1, and positions are measured in
3948 characters not bytes.
3959 Internal position: this is defined the same as the <literal>P</>
3960 field, but it is used when the cursor position refers to an internally
3961 generated command rather than the one submitted by the client.
3962 The <literal>q</> field will always appear when this field appears.
3973 Internal query: the text of a failed internally-generated command.
3974 This could be, for example, a SQL query issued by a PL/pgSQL function.
3985 Where: an indication of the context in which the error occurred.
3986 Presently this includes a call stack traceback of active
3987 procedural language functions and internally-generated queries.
3988 The trace is one entry per line, most recent first.
3999 File: the file name of the source-code location where the error
4011 Line: the line number of the source-code location where the error
4023 Routine: the name of the source-code routine reporting the error.
4031 The client is responsible for formatting displayed information to meet its
4032 needs; in particular it should break long lines as needed. Newline characters
4033 appearing in the error message fields should be treated as paragraph breaks,
4040 <Sect1 id="protocol-changes">
4041 <Title>Summary of Changes since Protocol 2.0</Title>
4044 This section provides a quick checklist of changes, for the benefit of
4045 developers trying to update existing client libraries to protocol 3.0.
4049 The initial startup packet uses a flexible list-of-strings format
4050 instead of a fixed format. Notice that session default values for run-time
4051 parameters can now be specified directly in the startup packet. (Actually,
4052 you could do that before using the <literal>options</> field, but given the
4053 limited width of <literal>options</> and the lack of any way to quote
4054 whitespace in the values, it wasn't a very safe technique.)
4058 All messages now have a length count immediately following the message type
4059 byte (except for startup packets, which have no type byte). Also note that
4060 PasswordMessage now has a type byte.
4064 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
4065 messages now contain multiple fields, from which the client code may
4066 assemble an error message of the desired level of verbosity. Note that
4067 individual fields will typically not end with a newline, whereas the single
4068 string sent in the older protocol always did.
4072 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
4077 The distinction between BinaryRow and DataRow message types is gone; the
4078 single DataRow message type serves for returning data in all formats.
4079 Note that the layout of DataRow has changed to make it easier to parse.
4080 Also, the representation of binary values has changed: it is no longer
4081 directly tied to the server's internal representation.
4085 There is a new <quote>extended query</> sub-protocol, which adds the frontend
4086 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
4087 backend message types ParseComplete, BindComplete, PortalSuspended,
4088 ParameterDescription, NoData, and CloseComplete. Existing clients do not
4089 have to concern themselves with this sub-protocol, but making use of it
4090 may allow improvements in performance or functionality.
4094 <command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
4095 is a well-defined way to recover from errors during <command>COPY</command>. The special
4096 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4097 during <command>COPY OUT</command>.
4098 (It is still recognized as a terminator during <command>COPY IN</command>, but its use is
4099 deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
4100 The CopyInResponse and CopyOutResponse messages include fields indicating
4101 the number of columns and the format of each column.
4105 The layout of FunctionCall and FunctionCallResponse messages has changed.
4106 FunctionCall can now support passing NULL arguments to functions. It also
4107 can handle passing parameters and retrieving results in either text or
4108 binary format. There is no longer any reason to consider FunctionCall a
4109 potential security hole, since it does not offer direct access to internal
4110 server data representations.
4114 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4115 startup for all parameters it considers interesting to the client library.
4116 Subsequently, a ParameterStatus message is sent whenever the active value
4117 changes for any of these parameters.
4121 The RowDescription ('<literal>T</>') message carries new table OID and column
4122 number fields for each column of the described row. It also shows the format
4123 code for each column.
4127 The CursorResponse ('<literal>P</>') message is no longer generated by
4132 The NotificationResponse ('<literal>A</>') message has an additional string
4133 field, which is presently empty but may someday carry additional data passed
4134 from the <command>NOTIFY</command> event sender.
4138 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4139 string parameter; this has been removed.