1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/protocol.sgml,v 1.38 2003/05/08 14:35:24 tgl Exp $ -->
3 <chapter id="protocol">
4 <title>Frontend/Backend Protocol</title>
7 <application>PostgreSQL</application> 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 <application>PostgreSQL</application> 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 datatype might be transmitted in any of several
168 different <firstterm>formats</>. As of <application>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 datatype. 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 datatypes consult the documentation
190 or source code to learn about the binary representation. Keep in mind
191 that binary representations for complex datatypes 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">.)
204 There are several different sub-protocols
205 depending on the state of the connection: start-up,
206 query, function call, COPY, and termination. There are also special
207 provisions for asynchronous operations (including
208 notification responses and command cancellation),
209 which can occur at any time 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 <varname>client_encoding</>
394 or <varname>DateStyle</>. The frontend may ignore this message,
395 or record the settings for its future use; see
396 <xref linkend="protocol-async"> for more detail.
397 The frontend should not respond to this message, but should
398 continue listening for a ReadyForQuery message.
404 <Term>ReadyForQuery</Term>
407 Start-up is completed. The frontend may now issue commands.
413 <Term>ErrorResponse</Term>
416 Start-up failed. The connection is closed after sending this
423 <Term>NoticeResponse</Term>
426 A warning message has been issued. The frontend should
427 display the message but continue listening for ReadyForQuery
436 The ReadyForQuery message is the same one that the backend will
437 issue after each command cycle. Depending on the coding needs of
438 the frontend, it is reasonable to consider ReadyForQuery as
439 starting a command cycle, or to consider ReadyForQuery as ending the
440 start-up phase and each subsequent command cycle.
445 <Title>Simple Query</Title>
448 A simple query cycle is initiated by the frontend sending a Query message
449 to the backend. The message includes an SQL command (or commands)
450 expressed as a text string.
451 The backend then sends one or more response
452 messages depending on the contents of the query command string,
453 and finally a ReadyForQuery response message. ReadyForQuery
454 informs the frontend that it may safely send a new command.
455 (It is not actually necessary for the frontend to wait for
456 ReadyForQuery before issuing another command, but the frontend must
457 then take responsibility for figuring out what happens if the earlier
458 command fails and already-issued later commands succeed.)
462 The possible response messages from the backend are:
466 <Term>CommandComplete</Term>
469 An SQL command completed normally.
475 <Term>CopyInResponse</Term>
478 The backend is ready to copy data from the frontend to a
479 table; see <xref linkend="protocol-copy">.
485 <Term>CopyOutResponse</Term>
488 The backend is ready to copy data from a table to the
489 frontend; see <xref linkend="protocol-copy">.
495 <Term>RowDescription</Term>
498 Indicates that rows are about to be returned in response to
499 a <command>SELECT</command>, <command>FETCH</command>, etc query.
500 The contents of this message describe the column layout of the rows.
501 This will be followed by a DataRow message for each row being returned
511 One of the set of rows returned by
512 a <command>SELECT</command>, <command>FETCH</command>, etc query.
518 <Term>EmptyQueryResponse</Term>
521 An empty query string was recognized.
527 <Term>ErrorResponse</Term>
530 An error has occurred.
536 <Term>ReadyForQuery</Term>
539 Processing of the query string is complete. A separate
540 message is sent to indicate this because the query string may
541 contain multiple SQL commands. (CommandComplete marks the
542 end of processing one SQL command, not the whole string.)
543 ReadyForQuery will always be sent, whether processing
544 terminates successfully or with an error.
550 <Term>NoticeResponse</Term>
553 A warning message has been issued in relation to the query.
554 Notices are in addition to other responses, i.e., the backend
555 will continue processing the command.
564 The response to a <command>SELECT</> query (or other queries that
565 return rowsets, such as <command>EXPLAIN</> or <command>SHOW</>)
566 normally consists of RowDescription, zero or more
567 DataRow messages, and then CommandComplete.
568 <command>COPY</> to or from the frontend invokes special protocol
569 as described in <xref linkend="protocol-copy">.
570 All other query types normally produce only
571 a CommandComplete message.
575 Since a query string could contain several queries (separated by
576 semicolons), there might be several such response sequences before the
577 backend finishes processing the query string. ReadyForQuery is issued
578 when the entire string has been processed and the backend is ready to
579 accept a new query string.
583 If a completely empty (no contents other than whitespace) query string
584 is received, the response is EmptyQueryResponse followed by ReadyForQuery.
588 In the event of an error, ErrorResponse is issued followed by
589 ReadyForQuery. All further processing of the query string is aborted by
590 ErrorResponse (even if more queries remained in it). Note that this
591 may occur partway through the sequence of messages generated by an
596 In simple Query mode, the format of retrieved values is always text,
597 except when the given command is a <command>FETCH</> from a cursor
598 declared with the <literal>BINARY</> option. In that case, the
599 retrieved values are in binary format. The format codes given in
600 the RowDescription message tell which format is being used.
604 A frontend must be prepared to accept ErrorResponse and
605 NoticeResponse messages whenever it is expecting any other type of
606 message. See also <xref linkend="protocol-async"> concerning messages
607 that the backend may generate due to outside events.
611 Recommended practice is to code frontends in a state-machine style
612 that will accept any message type at any time that it could make sense,
613 rather than wiring in assumptions about the exact sequence of messages.
618 <Title>Extended Query</Title>
621 The extended query protocol breaks down the above-described simple
622 query protocol into multiple steps. The results of preparatory
623 steps can be re-used multiple times for improved efficiency.
624 Furthermore, additional features are available, such as the possibility
625 of supplying data values as separate parameters instead of having to
626 insert them directly into a query string.
630 In the extended protocol, the frontend first sends a Parse message,
631 which contains a textual query string, optionally some information
632 about datatypes of parameter placeholders, and the
633 name of a destination prepared-statement object (an empty string
634 selects the unnamed prepared statement). The response is
635 either ParseComplete or ErrorResponse. Parameter datatypes may be
636 specified by OID; if not given, the parser attempts to infer the
637 datatypes in the same way as it would do for untyped literal string
643 The query string contained in a Parse message cannot include more
644 than one SQL statement; else a syntax error is reported. This
645 restriction does not exist in the simple-query protocol, but it
646 does exist in the extended protocol, because allowing prepared
647 statements or portals to contain multiple commands would complicate
653 If successfully created, a named prepared-statement object lasts till
654 the end of the current session, unless explicitly destroyed. An unnamed
655 prepared statement lasts only until the next Parse statement specifying
656 the unnamed statement as destination is issued. (Note that a simple
657 Query message also destroys the unnamed statement.) Named prepared
658 statements must be explicitly closed before they can be redefined by
659 a Parse message, but this is not required for the unnamed statement.
660 Named prepared statements can also be created and accessed at the SQL
661 command level, using <command>PREPARE</> and <command>EXECUTE</>.
665 Once a prepared statement exists, it can be readied for execution using a
666 Bind message. The Bind message gives the name of the source prepared
667 statement (empty string denotes the unnamed prepared statement), the name
668 of the destination portal (empty string denotes the unnamed portal), and
669 the values to use for any parameter placeholders present in the prepared
671 supplied parameter set must match those needed by the prepared statement.
672 Bind also specifies the format to use for any data returned
673 by the query; the format can be specified overall, or per-column.
674 The response is either BindComplete or ErrorResponse.
679 The choice between text and binary output is determined by the format
680 codes given in Bind, regardless of the SQL command involved. The
681 <literal>BINARY</> attribute in cursor declarations is irrelevant when
682 using extended query protocol.
687 If successfully created, a named portal object lasts till the end of the
688 current transaction, unless explicitly destroyed. An unnamed portal is
689 destroyed at the end of the transaction, or as soon as the next Bind
690 statement specifying the unnamed portal as destination is issued. (Note
691 that a simple Query message also destroys the unnamed portal.) Named
692 portals must be explicitly closed before they can be redefined by a Bind
693 message, but this is not required for the unnamed portal.
694 Named portals can also be created and accessed at the SQL
695 command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
699 Once a portal exists, it can be executed using an Execute message.
700 The Execute message specifies the portal name (empty string denotes the
702 a maximum result-row count (zero meaning <quote>fetch all rows</>).
703 The result-row count is only meaningful for portals
704 containing commands that return rowsets; in other cases the command is
705 always executed to completion, and the row count is ignored.
707 responses to Execute are the same as those described above for queries
708 issued via simple query protocol, except that Execute doesn't cause
709 ReadyForQuery to be issued.
713 If Execute terminates before completing the execution of a portal
714 (due to reaching a nonzero result-row count), it will send a
715 PortalSuspended message; the appearance of this message tells the frontend
716 that another Execute should be issued against the same portal to
717 complete the operation. The CommandComplete message indicating
718 completion of the source SQL command is not sent until
719 the portal's execution is completed. Therefore, an Execute phase is
720 always terminated by the appearance of exactly one of these messages:
721 CommandComplete, EmptyQueryResponse (if the portal was created from
722 an empty query string), ErrorResponse, or PortalSuspended.
726 At completion of each series of extended-query messages, the frontend
727 should issue a Sync message. This parameterless message causes the
728 backend to close the current transaction if it's not inside a
729 <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
730 meaning to commit if no error, or roll back if error). Then a
731 ReadyForQuery response is issued. The purpose of Sync is to provide
732 a resychronization point for error recovery. When an error is detected
733 while processing any extended-query message, the backend issues
734 ErrorResponse, then reads and discards messages until a Sync is reached,
735 then issues ReadyForQuery and returns to normal message processing.
736 (But note that no skipping occurs if an error is detected
737 <emphasis>while</> processing Sync --- this ensures that there is one
738 and only one ReadyForQuery sent for each Sync.)
743 Sync does not cause a transaction block opened with <command>BEGIN</>
744 to be closed. It is possible to detect this situation since the
745 ReadyForQuery message includes transaction status information.
750 In addition to these fundamental, required operations, there are several
751 optional operations that can be used with extended-query protocol.
755 The Describe message (portal variant) specifies the name of an existing
756 portal (or an empty string for the unnamed portal). The response is a
757 RowDescription message describing the rows that will be returned by
758 executing the portal; or a NoData message if the portal does not contain a
759 query that will return rows; or ErrorResponse if there is no such portal.
763 The Describe message (statement variant) specifies the name of an existing
764 prepared statement (or an empty string for the unnamed prepared
765 statement). The response is a ParameterDescription message describing the
766 parameters needed by the statement, followed by a RowDescription message
767 describing the rows that will be returned when the statement is eventually
768 executed (or a NoData message if the statement will not return rows).
769 ErrorResponse is issued if there is no such prepared statement. Note that
770 since Bind has not yet been issued, the formats to be used for returned
771 columns are not yet known to the backend; the format code fields in the
772 RowDescription message will be zeroes in this case.
777 In most scenarios the frontend should issue one or the other variant
778 of Describe before issuing Execute, to ensure that it knows how to
779 interpret the results it will get back.
784 The Close message closes an existing prepared statement or portal
785 and releases resources. It is not an error to issue Close against
786 a nonexistent statement or portal name. The response is normally
787 CloseComplete, but could be ErrorResponse if some difficulty is
788 encountered while releasing resources. Note that closing a prepared
789 statement implicitly closes any open portals that were constructed
794 The Flush message does not cause any specific output to be generated,
795 but forces the backend to deliver any data pending in its output
796 buffers. A Flush must be sent after any extended-query command except
797 Sync, if the frontend wishes to examine the results of that command before
798 issuing more commands. Without Flush, messages returned by the backend
799 will be combined into the minimum possible number of packets to minimize
805 The simple Query message is approximately equivalent to the series Parse,
806 Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
807 statement and portal objects and no parameters. One difference is that
808 it will accept multiple SQL statements in the query string, automatically
809 performing the bind/describe/execute sequence for each one in succession.
810 Another difference is that it will not return ParseComplete, BindComplete,
811 CloseComplete, or NoData messages.
817 <Title>Function Call</Title>
820 The Function Call sub-protocol allows the client to request a direct
821 call of any function that exists in the database's
822 <structname>pg_proc</structname> system catalog. The client must have
823 execute permission for the function.
828 The Function Call sub-protocol is a legacy feature that is probably best
829 avoided in new code. Similar results can be accomplished by setting up
830 a prepared statement that does <literal>SELECT function($1, ...)</>.
831 The Function Call cycle can then be replaced with Bind/Execute.
836 A Function Call cycle is initiated by the frontend sending a
837 FunctionCall message to the backend. The backend then sends one
838 or more response messages depending on the results of the function
839 call, and finally a ReadyForQuery response message. ReadyForQuery
840 informs the frontend that it may safely send a new query or
845 The possible response messages from the backend are:
849 <Term>ErrorResponse</Term>
852 An error has occurred.
858 <Term>FunctionCallResponse</Term>
861 The function call was completed and returned the result given
863 (Note that the Function Call protocol can only handle a single
864 scalar result, not a rowtype or set of results.)
870 <Term>ReadyForQuery</Term>
873 Processing of the function call is complete. ReadyForQuery
874 will always be sent, whether processing terminates
875 successfully or with an error.
881 <Term>NoticeResponse</Term>
884 A warning message has been issued in relation to the function
885 call. Notices are in addition to other responses, i.e., the
886 backend will continue processing the command.
894 <sect2 id="protocol-copy">
895 <title>COPY Operations</title>
898 The <command>COPY</> command allows high-speed bulk data transfer
899 to or from the server. Copy-in and copy-out operations each switch
900 the connection into a distinct sub-protocol, which lasts until the
901 operation is completed.
905 Copy-in mode (data transfer to the server) is initiated when the
906 backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
907 sends a CopyInResponse message to the frontend. The frontend should
908 then send zero or more CopyData messages, forming a stream of input
909 data. (The message boundaries are not required to have anything to do
910 with row boundaries, although that is often a reasonable choice.)
911 The frontend can terminate the copy-in mode by sending either a CopyDone
912 message (allowing successful termination) or a CopyFail message (which
913 will cause the <command>COPY</> SQL statement to fail with an
914 error). The backend then reverts to the command-processing mode it was
915 in before the <command>COPY</> started, which will be either simple or
916 extended query protocol. It will next send either CommandComplete
917 (if successful) or ErrorResponse (if not).
921 In the event of a backend-detected error during copy-in mode (including
922 receipt of a CopyFail message, or indeed any frontend message other than
923 CopyData or CopyDone), the backend will issue an ErrorResponse
924 message. If the <command>COPY</> command was issued via an extended-query
925 message, the backend will now discard frontend messages until a Sync
926 message is received, then it will issue ReadyForQuery and return to normal
927 processing. If the <command>COPY</> command was issued in a simple
928 Query message, the rest of that message is discarded and ReadyForQuery
929 is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
930 messages issued by the frontend will simply be dropped.
934 Copy-out mode (data transfer from the server) is initiated when the
935 backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
936 sends a CopyOutResponse message to the frontend, followed by
937 zero or more CopyData messages (always one per row), followed by CopyDone.
938 The backend then reverts to the command-processing mode it was
939 in before the <command>COPY</> started, and sends CommandComplete.
940 The frontend cannot abort the transfer (except by closing the connection
941 or issuing a Cancel request),
942 but it can discard unwanted CopyData and CopyDone messages.
946 In the event of a backend-detected error during copy-out mode,
947 the backend will issue an ErrorResponse message and revert to normal
948 processing. The frontend should treat receipt of ErrorResponse (or
949 indeed any message type other than CopyData or CopyDone) as terminating
954 The CopyInResponse and CopyOutResponse messages include fields that
955 inform the frontend of the number of columns per row and the format
956 codes being used for each column. (As of the present implementation,
957 all columns in a given <command>COPY</> operation will use the same
958 format, but the message design does not assume this.)
962 <sect2 id="protocol-async">
963 <title>Asynchronous Operations</title>
966 There are several cases in which the backend will send messages that
967 are not specifically prompted by the frontend's command stream.
968 Frontends must be prepared to deal with these messages at any time,
969 even when not engaged in a query.
970 At minimum, one should check for these cases before beginning to
971 read a query response.
975 It is possible for NoticeResponse messages to be generated due to
976 outside activity; for example, if the database administrator commands
977 a <quote>fast</> database shutdown, the backend will send a NoticeResponse
978 indicating this fact before closing the connection. Accordingly,
979 frontends should always be prepared to accept and display NoticeResponse
980 messages, even when the connection is nominally idle.
984 ParameterStatus messages will be generated whenever the active value
985 changes for any of the parameters the backend believes the frontend
986 should know about. Most commonly this occurs in response to a
987 <command>SET</> SQL command executed by the frontend, and this case
988 is effectively synchronous --- but it is also possible for parameter
989 status changes to occur because the administrator changed a configuration
990 file and then SIGHUP'd the postmaster. Also, if a SET command is
991 rolled back, an appropriate ParameterStatus message will be generated
992 to report the current effective value.
996 At present there is a hard-wired set of parameters for which
997 ParameterStatus will be generated: they are
998 <literal>server_version</> (a pseudo-parameter that cannot change after
1000 <literal>server_encoding</> (also not presently changeable after start);
1001 <literal>client_encoding</>, and
1002 <literal>DateStyle</>.
1003 This set might change in the future, or even become configurable.
1004 Accordingly, a frontend should simply ignore ParameterStatus for
1005 parameters that it does not understand or care about.
1009 If a frontend issues a <command>LISTEN</command> command, then the
1010 backend will send a NotificationResponse message (not to be
1011 confused with NoticeResponse!) whenever a
1012 <command>NOTIFY</command> command is executed for the same
1018 At present, NotificationResponse can only be sent outside a
1019 transaction, and thus it will not occur in the middle of a
1020 command-response series, though it may occur just before ReadyForQuery.
1021 It is unwise to design frontend logic that assumes that, however.
1022 Good practice is to be able to accept NotificationResponse at any
1023 point in the protocol.
1029 <Title>Cancelling Requests in Progress</Title>
1032 During the processing of a query, the frontend may request
1033 cancellation of the query. The cancel request is not sent
1034 directly on the open connection to the backend for reasons of
1035 implementation efficiency: we don't want to have the backend
1036 constantly checking for new input from the frontend during query
1037 processing. Cancel requests should be relatively infrequent, so
1038 we make them slightly cumbersome in order to avoid a penalty in
1043 To issue a cancel request, the frontend opens a new connection to
1044 the server and sends a CancelRequest message, rather than the
1045 StartupMessage message that would ordinarily be sent across a new
1046 connection. The server will process this request and then close
1047 the connection. For security reasons, no direct reply is made to
1048 the cancel request message.
1052 A CancelRequest message will be ignored unless it contains the
1053 same key data (PID and secret key) passed to the frontend during
1054 connection start-up. If the request matches the PID and secret
1055 key for a currently executing backend, the processing of the
1056 current query is aborted. (In the existing implementation, this is
1057 done by sending a special signal to the backend process that is
1058 processing the query.)
1062 The cancellation signal may or may not have any effect --- for
1063 example, if it arrives after the backend has finished processing
1064 the query, then it will have no effect. If the cancellation is
1065 effective, it results in the current command being terminated
1066 early with an error message.
1070 The upshot of all this is that for reasons of both security and
1071 efficiency, the frontend has no direct way to tell whether a
1072 cancel request has succeeded. It must continue to wait for the
1073 backend to respond to the query. Issuing a cancel simply improves
1074 the odds that the current query will finish soon, and improves the
1075 odds that it will fail with an error message instead of
1080 Since the cancel request is sent across a new connection to the
1081 server and not across the regular frontend/backend communication
1082 link, it is possible for the cancel request to be issued by any
1083 process, not just the frontend whose query is to be canceled.
1084 This may have some benefits of flexibility in building
1085 multiple-process applications. It also introduces a security
1086 risk, in that unauthorized persons might try to cancel queries.
1087 The security risk is addressed by requiring a dynamically
1088 generated secret key to be supplied in cancel requests.
1093 <Title>Termination</Title>
1096 The normal, graceful termination procedure is that the frontend
1097 sends a Terminate message and immediately closes the connection.
1098 On receipt of this message, the backend closes the connection and
1103 In rare cases (such as an administrator-commanded database shutdown)
1104 the backend may disconnect without any frontend request to do so.
1105 In such cases the backend will attempt to send an error or notice message
1106 giving the reason for the disconnection before it closes the connection.
1110 Other termination scenarios arise from various failure cases, such as core
1111 dump at one end or the other, loss of the communications link, loss of
1112 message-boundary synchronization, etc. If either frontend or backend sees
1113 an unexpected closure of the connection, it should clean
1114 up and terminate. The frontend has the option of launching a new backend
1115 by recontacting the server if it doesn't want to terminate itself.
1116 Closing the connection is also advisable if an unrecognizable message type
1117 is received, since this probably indicates loss of message-boundary sync.
1121 For either normal or abnormal termination, any open transaction is
1122 rolled back, not committed. One should note however that if a
1123 frontend disconnects while a non-SELECT query is being processed,
1124 the backend will probably finish the query before noticing the
1126 If the query is outside any transaction block (<command>BEGIN</>
1127 ... <command>COMMIT</> sequence) then its results may be committed
1128 before the disconnection is recognized.
1133 <Title>SSL Session Encryption</Title>
1136 If <productname>PostgreSQL</> was built with SSL support, frontend/backend
1137 communications can be encrypted using SSL. This provides communication
1138 security in environments where attackers might be able to capture the
1143 To initiate an SSL-encrypted connection, the frontend initially sends
1144 an SSLRequest message rather than a StartupMessage. The server then
1145 responds with a single byte containing <literal>Y</> or <literal>N</>,
1146 indicating that it is willing or unwilling to perform SSL, respectively.
1147 The frontend may close the connection at this point if it is dissatisfied
1148 with the response. To continue after <literal>Y</>, perform an SSL
1149 startup handshake (not described here, part of the SSL specification)
1150 with the server. If this is successful, continue with
1151 sending the usual StartupMessage. In this case the StartupMessage and
1152 all subsequent data will be SSL-encrypted. To continue after
1153 <literal>N</>, send the usual StartupMessage and proceed without
1158 The frontend should also be prepared to handle an ErrorMessage response
1159 to SSLRequest from the server. This would only occur if the server
1160 predates the addition of SSL support to <productname>PostgreSQL</>.
1161 In this case the connection must be closed, but the frontend may choose
1162 to open a fresh connection and proceed without requesting SSL.
1166 An initial SSLRequest may also be used in a connection that is being
1167 opened to send a CancelRequest message.
1171 While the protocol itself does not provide a way for the server to
1172 force SSL encryption, the administrator may configure the server to
1173 reject unencrypted sessions as a byproduct of authentication checking.
1178 <Sect1 id="protocol-message-types">
1179 <Title>Message Data Types</Title>
1182 This section describes the base data types used in messages.
1188 Int<Replaceable>n</Replaceable>(<Replaceable>i</Replaceable>)
1192 An <Replaceable>n</Replaceable>-bit integer in network byte
1193 order (most significant byte first).
1194 If <Replaceable>i</Replaceable> is specified it
1195 is the exact value that will appear, otherwise the value
1196 is variable. Eg. Int16, Int32(42).
1203 Int<Replaceable>n</Replaceable>[<Replaceable>k</Replaceable>]
1207 An array of <Replaceable>k</Replaceable>
1208 <Replaceable>n</Replaceable>-bit integers, each in network
1209 byte order. The array length <Replaceable>k</Replaceable>
1210 is always determined by an earlier field in the message.
1218 String(<Replaceable>s</Replaceable>)
1222 A null-terminated string (C-style string). There is no
1223 specific length limitation on strings.
1224 If <Replaceable>s</Replaceable> is specified it is the exact
1225 value that will appear, otherwise the value is variable.
1226 Eg. String, String("user").
1231 <Emphasis>There is no predefined limit</Emphasis> on the length of a string
1232 that can be returned by the backend. Good coding strategy for a frontend
1233 is to use an expandable buffer so that anything that fits in memory can be
1234 accepted. If that's not feasible, read the full string and discard trailing
1235 characters that don't fit into your fixed-size buffer.
1243 Byte<Replaceable>n</Replaceable>(<Replaceable>c</Replaceable>)
1247 Exactly <Replaceable>n</Replaceable> bytes. If the field
1248 width <Replaceable>n</Replaceable> is not a constant, it is
1249 always determinable from an earlier field in the message.
1250 If <Replaceable>c</Replaceable> is specified it is the exact
1251 value. Eg. Byte2, Byte1('\n').
1260 <Sect1 id="protocol-message-formats">
1261 <Title>Message Formats</Title>
1264 This section describes the detailed format of each message. Each is marked to
1265 indicate that it may be sent by a frontend (F), a backend (B), or both
1267 Notice that although each message includes a byte count at the beginning,
1268 the message format is defined so that the message end can be found without
1269 reference to the byte count. This aids validity checking. (The CopyData
1270 message is an exception, because it forms part of a data stream; the contents
1271 of any individual CopyData message may not be interpretable on their own.)
1279 AuthenticationOk (B)
1291 Identifies the message as an authentication request.
1301 Length of message contents in bytes, including self.
1311 Specifies that the authentication was successful.
1324 AuthenticationKerberosV4 (B)
1336 Identifies the message as an authentication request.
1346 Length of message contents in bytes, including self.
1356 Specifies that Kerberos V4 authentication is required.
1368 AuthenticationKerberosV5 (B)
1380 Identifies the message as an authentication request.
1390 Length of message contents in bytes, including self.
1400 Specifies that Kerberos V5 authentication is required.
1412 AuthenticationCleartextPassword (B)
1424 Identifies the message as an authentication request.
1434 Length of message contents in bytes, including self.
1444 Specifies that a cleartext password is required.
1456 AuthenticationCryptPassword (B)
1468 Identifies the message as an authentication request.
1478 Length of message contents in bytes, including self.
1488 Specifies that a crypt()-encrypted password is required.
1498 The salt to use when encrypting the password.
1511 AuthenticationMD5Password (B)
1523 Identifies the message as an authentication request.
1533 Length of message contents in bytes, including self.
1543 Specifies that an MD5-encrypted password is required.
1553 The salt to use when encrypting the password.
1566 AuthenticationSCMCredential (B)
1578 Identifies the message as an authentication request.
1588 Length of message contents in bytes, including self.
1598 Specifies that an SCM credentials message is required.
1623 Identifies the message as cancellation key data.
1624 The frontend must save these values if it wishes to be
1625 able to issue CancelRequest messages later.
1635 Length of message contents in bytes, including self.
1645 The process ID of this backend.
1655 The secret key of this backend.
1680 Identifies the message as a Bind command.
1690 Length of message contents in bytes, including self.
1700 The name of the destination portal
1701 (an empty string selects the unnamed portal).
1711 The name of the source prepared statement
1712 (an empty string selects the unnamed prepared statement).
1722 The number of parameter format codes that follow
1723 (denoted <replaceable>C</> below).
1724 This can be zero to indicate that there are no parameters
1725 or that the parameters all use the default format (text);
1726 or one, in which case the specified format code is applied
1727 to all parameters; or it can equal the actual number of
1734 Int16[<replaceable>C</>]
1738 The parameter format codes. Each must presently be
1739 zero (text) or one (binary).
1749 The number of parameter values that follow (possibly zero).
1750 This must match the number of parameters needed by the query.
1755 Next, the following pair of fields appear for each parameter:
1763 The length of the parameter value, in bytes (this count
1764 does not include itself). Can be zero.
1765 As a special case, -1 indicates a NULL parameter value.
1766 No value bytes follow in the NULL case.
1772 Byte<Replaceable>n</Replaceable>
1776 The value of the parameter, in the format indicated by the
1777 associated format code.
1778 <Replaceable>n</Replaceable> is the above length.
1783 After the last parameter, the following fields appear:
1791 The number of result-column format codes that follow
1792 (denoted <replaceable>R</> below).
1793 This can be zero to indicate that there are no result columns
1794 or that the result columns should all use the default format
1796 or one, in which case the specified format code is applied
1797 to all result columns (if any); or it can equal the actual
1798 number of result columns of the query.
1804 Int16[<replaceable>R</>]
1808 The result-column format codes. Each must presently be
1809 zero (text) or one (binary).
1833 Identifies the message as a Bind-complete indicator.
1843 Length of message contents in bytes, including self.
1868 Length of message contents in bytes, including self.
1878 The cancel request code. The value is chosen to contain
1879 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
1880 least 16 significant bits. (To avoid confusion, this code
1881 must not be the same as any protocol version number.)
1891 The process ID of the target backend.
1901 The secret key for the target backend.
1926 Identifies the message as a Close command.
1936 Length of message contents in bytes, including self.
1946 '<literal>S</>' to close a prepared statement; or
1947 '<literal>P</>' to close a portal.
1957 The name of the prepared statement or portal to close
1958 (an empty string selects the unnamed prepared statement
1983 Identifies the message as a Close-complete indicator.
1993 Length of message contents in bytes, including self.
2018 Identifies the message as a command-completed response.
2028 Length of message contents in bytes, including self.
2038 The command tag. This is usually a single
2039 word that identifies which SQL command was completed.
2043 For an <command>INSERT</command> command, the tag is
2044 <literal>INSERT <replaceable>oid</replaceable>
2045 <replaceable>rows</replaceable></literal>, where
2046 <replaceable>rows</replaceable> is the number of rows
2047 inserted. <replaceable>oid</replaceable> is the object ID
2048 of the inserted row if <Replaceable>rows</Replaceable> is 1
2049 and the target table has OIDs;
2050 otherwise <Replaceable>oid</Replaceable> is 0.
2054 For a <command>DELETE</command> command, the tag is
2055 <literal>DELETE <Replaceable>rows</Replaceable></literal> where
2056 <Replaceable>rows</Replaceable> is the number of rows deleted.
2060 For an <command>UPDATE</command> command, the tag is
2061 <literal>UPDATE <Replaceable>rows</Replaceable></literal> where
2062 <Replaceable>rows</Replaceable> is the number of rows updated.
2066 For a <command>MOVE</command> command, the tag is
2067 <literal>MOVE <replaceable>rows</replaceable></literal> where
2068 <replaceable>rows</replaceable> is the number of rows the
2069 cursor's position has been changed by.
2073 For a <command>FETCH</command> command, the tag is
2074 <literal>FETCH <replaceable>rows</replaceable></literal> where
2075 <replaceable>rows</replaceable> is the number of rows that
2076 have been retrieved from the cursor.
2089 CopyData (F & B)
2100 Identifies the message as COPY data.
2110 Length of message contents in bytes, including self.
2116 Byte<Replaceable>n</Replaceable>
2120 Data that forms part of a COPY datastream. Messages sent
2121 from the backend will always correspond to single data rows,
2122 but messages sent by frontends may divide the datastream
2135 CopyDone (F & B)
2147 Identifies the message as a COPY-complete indicator.
2157 Length of message contents in bytes, including self.
2182 Identifies the message as a COPY-failure indicator.
2192 Length of message contents in bytes, including self.
2202 An error message to report as the cause of failure.
2227 Identifies the message as a Start Copy In response.
2228 The frontend must now send copy-in data (if not
2229 prepared to do so, send a CopyFail message).
2239 Length of message contents in bytes, including self.
2249 0 indicates the overall copy format is textual (rows
2250 separated by newlines, columns separated by separator
2252 1 indicates the overall copy format is binary (similar
2254 See <xref linkend="sql-copy" endterm="sql-copy-title">
2255 for more information.
2265 The number of columns in the data to be copied
2266 (denoted <replaceable>N</> below).
2272 Int16[<replaceable>N</>]
2276 The format codes to be used for each column.
2277 Each must presently be zero (text) or one (binary).
2278 All must be zero if the overall copy format is textual.
2303 Identifies the message as a Start Copy Out response.
2304 This message will be followed by copy-out data.
2314 Length of message contents in bytes, including self.
2324 0 indicates the overall copy format is textual (rows
2325 separated by newlines, columns separated by separator
2327 1 indicates the overall copy format is binary (similar
2329 See <xref linkend="sql-copy" endterm="sql-copy-title">
2330 for more information.
2340 The number of columns in the data to be copied
2341 (denoted <replaceable>N</> below).
2347 Int16[<replaceable>N</>]
2351 The format codes to be used for each column.
2352 Each must presently be zero (text) or one (binary).
2353 All must be zero if the overall copy format is textual.
2377 Identifies the message as a data row.
2387 Length of message contents in bytes, including self.
2397 The number of column values that follow (possibly zero).
2402 Next, the following pair of fields appear for each column:
2410 The length of the column value, in bytes (this count
2411 does not include itself). Can be zero.
2412 As a special case, -1 indicates a NULL column value.
2413 No value bytes follow in the NULL case.
2419 Byte<Replaceable>n</Replaceable>
2423 The value of the column, in the format indicated by the
2424 associated format code.
2425 <Replaceable>n</Replaceable> is the above length.
2450 Identifies the message as a Describe command.
2460 Length of message contents in bytes, including self.
2470 '<literal>S</>' to describe a prepared statement; or
2471 '<literal>P</>' to describe a portal.
2481 The name of the prepared statement or portal to describe
2482 (an empty string selects the unnamed prepared statement
2495 EmptyQueryResponse (B)
2507 Identifies the message as a response to an empty query string.
2508 (This substitutes for CommandComplete.)
2518 Length of message contents in bytes, including self.
2543 Identifies the message as an error.
2553 Length of message contents in bytes, including self.
2558 The message body consists of one or more identified fields,
2559 followed by a zero byte as a terminator. Fields may appear in
2560 any order. For each field there is the following:
2568 A code identifying the field type; if zero, this is
2569 the message terminator and no string follows.
2570 The presently defined field types are listed in
2571 <xref linkend="protocol-error-fields">.
2572 Since more field types may be added in future,
2573 frontends should silently ignore fields of unrecognized
2609 Identifies the message as an Execute command.
2619 Length of message contents in bytes, including self.
2629 The name of the portal to execute
2630 (an empty string selects the unnamed portal).
2640 Maximum number of rows to return, if portal contains
2641 a query that returns rows (ignored otherwise). Zero
2642 denotes <quote>no limit</>.
2666 Identifies the message as a Flush command.
2676 Length of message contents in bytes, including self.
2701 Identifies the message as a function call.
2711 Length of message contents in bytes, including self.
2721 Specifies the object ID of the function to call.
2731 The number of argument format codes that follow
2732 (denoted <replaceable>C</> below).
2733 This can be zero to indicate that there are no arguments
2734 or that the arguments all use the default format (text);
2735 or one, in which case the specified format code is applied
2736 to all arguments; or it can equal the actual number of
2743 Int16[<replaceable>C</>]
2747 The argument format codes. Each must presently be
2748 zero (text) or one (binary).
2758 Specifies the number of arguments being supplied to the
2764 Next, the following pair of fields appear for each argument:
2772 The length of the argument value, in bytes (this count
2773 does not include itself). Can be zero.
2774 As a special case, -1 indicates a NULL argument value.
2775 No value bytes follow in the NULL case.
2781 Byte<Replaceable>n</Replaceable>
2785 The value of the argument, in the format indicated by the
2786 associated format code.
2787 <Replaceable>n</Replaceable> is the above length.
2792 After the last argument, the following field appears:
2800 The format code for the function result. Must presently be
2801 zero (text) or one (binary).
2814 FunctionCallResponse (B)
2826 Identifies the message as a function call result.
2836 Length of message contents in bytes, including self.
2846 The length of the function result value, in bytes (this count
2847 does not include itself). Can be zero.
2848 As a special case, -1 indicates a NULL function result.
2849 No value bytes follow in the NULL case.
2855 Byte<Replaceable>n</Replaceable>
2859 The value of the function result, in the format indicated by
2860 the associated format code.
2861 <Replaceable>n</Replaceable> is the above length.
2886 Identifies the message as a no-data indicator.
2896 Length of message contents in bytes, including self.
2921 Identifies the message as a notice.
2931 Length of message contents in bytes, including self.
2936 The message body consists of one or more identified fields,
2937 followed by a zero byte as a terminator. Fields may appear in
2938 any order. For each field there is the following:
2946 A code identifying the field type; if zero, this is
2947 the message terminator and no string follows.
2948 The presently defined field types are listed in
2949 <xref linkend="protocol-error-fields">.
2950 Since more field types may be added in future,
2951 frontends should silently ignore fields of unrecognized
2975 NotificationResponse (B)
2987 Identifies the message as a notification response.
2997 Length of message contents in bytes, including self.
3007 The process ID of the notifying backend process.
3017 The name of the condition that the notify has been raised on.
3027 Additional information passed from the notifying process.
3028 (Currently, this feature is unimplemented so the field
3029 is always an empty string.)
3042 ParameterDescription (B)
3054 Identifies the message as a parameter description.
3064 Length of message contents in bytes, including self.
3074 The number of parameters used by the statement
3080 Then, for each parameter, there is the following:
3088 Specifies the object ID of the parameter datatype.
3112 Identifies the message as a run-time parameter status report.
3122 Length of message contents in bytes, including self.
3132 The name of the run-time parameter being reported.
3142 The current value of the parameter.
3166 Identifies the message as a Parse command.
3176 Length of message contents in bytes, including self.
3186 The name of the destination prepared statement
3187 (an empty string selects the unnamed prepared statement).
3197 The query string to be parsed.
3207 The number of parameter datatypes specified
3208 (may be zero). Note that this is not an indication of
3209 the number of parameters that might appear in the
3210 query string, only the number that the frontend wants to
3211 prespecify types for.
3216 Then, for each parameter, there is the following:
3224 Specifies the object ID of the parameter datatype.
3225 Placing a zero here is equivalent to leaving the type
3250 Identifies the message as a Parse-complete indicator.
3260 Length of message contents in bytes, including self.
3285 Identifies the message as a password response.
3295 Length of message contents in bytes, including self.
3305 The password (encrypted, if requested).
3329 Identifies the message as a portal-suspended indicator.
3330 Note this only appears if an Execute message's row-count limit
3341 Length of message contents in bytes, including self.
3366 Identifies the message as a simple query.
3376 Length of message contents in bytes, including self.
3386 The query string itself.
3411 Identifies the message type. ReadyForQuery is sent
3412 whenever the backend is ready for a new query cycle.
3422 Length of message contents in bytes, including self.
3432 Current backend transaction status indicator.
3433 Possible values are '<literal>I</>' if idle (not in
3434 a transaction block); '<literal>T</>' if in a transaction
3435 block; or '<literal>E</>' if in a failed transaction
3436 block (queries will be rejected until block is ended).
3461 Identifies the message as a row description.
3471 Length of message contents in bytes, including self.
3481 Specifies the number of fields in a row (may be zero).
3486 Then, for each field, there is the following:
3504 If the field can be identified as a column of a specific
3505 table, the object ID of the table; otherwise zero.
3515 If the field can be identified as a column of a specific
3516 table, the attribute number of the column; otherwise zero.
3526 The object ID of the field's datatype.
3536 The datatype size (see <varname>pg_type.typlen</>).
3537 Note that negative values denote variable-width types.
3547 The type modifier (see <varname>pg_attribute.atttypmod</>).
3548 The meaning of the modifier is type-specific.
3558 The format code being used for the field. Currently will
3559 be zero (text) or one (binary). In a RowDescription
3560 returned from the statement variant of Describe, the
3561 format code is not yet known and will always be zero.
3586 Length of message contents in bytes, including self.
3596 The SSL request code. The value is chosen to contain
3597 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
3598 least 16 significant bits. (To avoid confusion, this code
3599 must not be the same as any protocol version number.)
3624 Length of message contents in bytes, including self.
3634 The protocol version number. The most significant 16 bits are
3635 the major version number (3 for the protocol described here).
3636 The least significant 16 bits are the minor version number
3637 (0 for the protocol described here).
3642 The protocol version number is followed by one or more pairs of
3643 parameter name and value strings. A zero byte is required as a
3644 terminator after the last name/value pair.
3645 Parameters can appear in any
3646 order. <literal>user</> is required, others are optional.
3647 Each parameter is specified as:
3655 The parameter name. Currently recognized names are:
3664 The database user name to connect as. Required;
3665 there is no default.
3671 <literal>database</>
3675 The database to connect to. Defaults to the user name.
3685 Command-line arguments for the backend. (This is
3686 deprecated in favor of setting individual run-time
3693 In addition to the above, any run-time parameter that can be
3694 set at backend start time may be listed. Such settings
3695 will be applied during backend start (after parsing the
3696 command-line options if any). The values will act as
3707 The parameter value.
3732 Identifies the message as a Sync command.
3742 Length of message contents in bytes, including self.
3767 Identifies the message as a termination.
3777 Length of message contents in bytes, including self.
3793 <Sect1 id="protocol-error-fields">
3794 <Title>Error and Notice Message Fields</Title>
3797 This section describes the fields that may appear in ErrorResponse and
3798 NoticeResponse messages. Each field type has a single-byte identification
3799 token. Note that any given field type should appear at most once per
3811 Severity: the field contents are
3812 <literal>ERROR</>, <literal>FATAL</>, or
3813 <literal>PANIC</> (in an error message), or
3814 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
3815 <literal>INFO</>, or <literal>LOG</> (in a notice message),
3816 or a localized translation of one of these. Always present.
3827 Code: the SQLSTATE code for the error (a 5-character
3828 string following SQL spec conventions). Not localizable.
3840 Message: the primary human-readable error message.
3841 This should be accurate but terse (typically one line).
3853 Detail: an optional secondary error message carrying more
3854 detail about the problem. May run to multiple lines.
3865 Hint: an optional suggestion what to do about the problem.
3866 This is intended to differ from Detail in that it offers advice
3867 (potentially inappropriate) rather than hard facts.
3868 May run to multiple lines.
3879 Position: the field value is a decimal ASCII integer, indicating
3880 an error cursor position as an index into the original query string.
3881 The first character has index 1, and positions are measured in
3882 characters not bytes.
3893 Where: an indication of the context in which the error occurred.
3894 Presently this includes a call stack traceback of active PL functions.
3895 The trace is one entry per line, most recent first.
3906 File: the file name of the source-code location where the error
3918 Line: the line number of the source-code location where the error
3930 Routine: the name of the source-code routine reporting the error.
3938 The client is responsible for formatting displayed information to meet its
3939 needs; in particular it should break long lines as needed. Newline characters
3940 appearing in the error message fields should be treated as paragraph breaks,
3947 <Sect1 id="protocol-changes">
3948 <Title>Summary of Changes since Protocol 2.0</Title>
3951 This section provides a quick checklist of changes, for the benefit of
3952 developers trying to update existing client libraries to protocol 3.0.
3956 The initial startup packet uses a flexible list-of-strings format
3957 instead of a fixed format. Notice that session default values for run-time
3958 parameters can now be specified directly in the startup packet. (Actually,
3959 you could do that before using the <literal>options</> field, but given the
3960 limited width of <literal>options</> and the lack of any way to quote
3961 whitespace in the values, it wasn't a very safe technique.)
3965 All messages now have a length count immediately following the message type
3966 byte (except for startup packets, which have no type byte). Also note that
3967 PasswordMessage now has a type byte.
3971 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
3972 messages now contain multiple fields, from which the client code may
3973 assemble an error message of the desired level of verbosity. Note that
3974 individual fields will typically not end with a newline, whereas the single
3975 string sent in the older protocol always did.
3979 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
3984 The distinction between BinaryRow and DataRow message types is gone; the
3985 single DataRow message type serves for returning data in all formats.
3986 Note that the layout of DataRow has changed to make it easier to parse.
3987 Also, the representation of binary values has changed: it is no longer
3988 directly tied to the server's internal representation.
3992 There is a new <quote>extended query</> sub-protocol, which adds the frontend
3993 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
3994 backend message types ParseComplete, BindComplete, PortalSuspended,
3995 ParameterDescription, NoData, and CloseComplete. Existing clients do not
3996 have to concern themselves with this sub-protocol, but making use of it
3997 may allow improvements in performance or functionality.
4001 COPY data is now encapsulated into CopyData and CopyDone messages. There
4002 is a well-defined way to recover from errors during COPY. The special
4003 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4005 (It is still recognized as a terminator during COPY IN, but its use is
4006 deprecated and will eventually be removed.) Binary COPY is supported.
4007 The CopyInResponse and CopyOutResponse messages include fields indicating
4008 the number of columns and the format of each column.
4012 The layout of FunctionCall and FunctionCallResponse messages has changed.
4013 FunctionCall can now support passing NULL arguments to functions. It also
4014 can handle passing parameters and retrieving results in either text or
4015 binary format. There is no longer any reason to consider FunctionCall a
4016 potential security hole, since it does not offer direct access to internal
4017 server data representations.
4021 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4022 startup for all parameters it considers interesting to the client library.
4023 Subsequently, a ParameterStatus message is sent whenever the active value
4024 changes for any of these parameters.
4028 The RowDescription ('<literal>T</>') message carries new table OID and column
4029 number fields for each column of the described row. It also shows the format
4030 code for each column.
4034 The CursorResponse ('<literal>P</>') message is no longer generated by
4039 The NotificationResponse ('<literal>A</>') message has an additional string
4040 field, which is presently empty but may someday carry additional data passed
4041 from the NOTIFY event sender.
4045 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4046 string parameter; this has been removed.