1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/protocol.sgml,v 1.39 2003/06/27 19:08:37 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</>,
1002 <literal>is_superuser</>, and
1003 <literal>DateStyle</>.
1004 This set might change in the future, or even become configurable.
1005 Accordingly, a frontend should simply ignore ParameterStatus for
1006 parameters that it does not understand or care about.
1010 If a frontend issues a <command>LISTEN</command> command, then the
1011 backend will send a NotificationResponse message (not to be
1012 confused with NoticeResponse!) whenever a
1013 <command>NOTIFY</command> command is executed for the same
1019 At present, NotificationResponse can only be sent outside a
1020 transaction, and thus it will not occur in the middle of a
1021 command-response series, though it may occur just before ReadyForQuery.
1022 It is unwise to design frontend logic that assumes that, however.
1023 Good practice is to be able to accept NotificationResponse at any
1024 point in the protocol.
1030 <Title>Cancelling Requests in Progress</Title>
1033 During the processing of a query, the frontend may request
1034 cancellation of the query. The cancel request is not sent
1035 directly on the open connection to the backend for reasons of
1036 implementation efficiency: we don't want to have the backend
1037 constantly checking for new input from the frontend during query
1038 processing. Cancel requests should be relatively infrequent, so
1039 we make them slightly cumbersome in order to avoid a penalty in
1044 To issue a cancel request, the frontend opens a new connection to
1045 the server and sends a CancelRequest message, rather than the
1046 StartupMessage message that would ordinarily be sent across a new
1047 connection. The server will process this request and then close
1048 the connection. For security reasons, no direct reply is made to
1049 the cancel request message.
1053 A CancelRequest message will be ignored unless it contains the
1054 same key data (PID and secret key) passed to the frontend during
1055 connection start-up. If the request matches the PID and secret
1056 key for a currently executing backend, the processing of the
1057 current query is aborted. (In the existing implementation, this is
1058 done by sending a special signal to the backend process that is
1059 processing the query.)
1063 The cancellation signal may or may not have any effect --- for
1064 example, if it arrives after the backend has finished processing
1065 the query, then it will have no effect. If the cancellation is
1066 effective, it results in the current command being terminated
1067 early with an error message.
1071 The upshot of all this is that for reasons of both security and
1072 efficiency, the frontend has no direct way to tell whether a
1073 cancel request has succeeded. It must continue to wait for the
1074 backend to respond to the query. Issuing a cancel simply improves
1075 the odds that the current query will finish soon, and improves the
1076 odds that it will fail with an error message instead of
1081 Since the cancel request is sent across a new connection to the
1082 server and not across the regular frontend/backend communication
1083 link, it is possible for the cancel request to be issued by any
1084 process, not just the frontend whose query is to be canceled.
1085 This may have some benefits of flexibility in building
1086 multiple-process applications. It also introduces a security
1087 risk, in that unauthorized persons might try to cancel queries.
1088 The security risk is addressed by requiring a dynamically
1089 generated secret key to be supplied in cancel requests.
1094 <Title>Termination</Title>
1097 The normal, graceful termination procedure is that the frontend
1098 sends a Terminate message and immediately closes the connection.
1099 On receipt of this message, the backend closes the connection and
1104 In rare cases (such as an administrator-commanded database shutdown)
1105 the backend may disconnect without any frontend request to do so.
1106 In such cases the backend will attempt to send an error or notice message
1107 giving the reason for the disconnection before it closes the connection.
1111 Other termination scenarios arise from various failure cases, such as core
1112 dump at one end or the other, loss of the communications link, loss of
1113 message-boundary synchronization, etc. If either frontend or backend sees
1114 an unexpected closure of the connection, it should clean
1115 up and terminate. The frontend has the option of launching a new backend
1116 by recontacting the server if it doesn't want to terminate itself.
1117 Closing the connection is also advisable if an unrecognizable message type
1118 is received, since this probably indicates loss of message-boundary sync.
1122 For either normal or abnormal termination, any open transaction is
1123 rolled back, not committed. One should note however that if a
1124 frontend disconnects while a non-SELECT query is being processed,
1125 the backend will probably finish the query before noticing the
1127 If the query is outside any transaction block (<command>BEGIN</>
1128 ... <command>COMMIT</> sequence) then its results may be committed
1129 before the disconnection is recognized.
1134 <Title>SSL Session Encryption</Title>
1137 If <productname>PostgreSQL</> was built with SSL support, frontend/backend
1138 communications can be encrypted using SSL. This provides communication
1139 security in environments where attackers might be able to capture the
1144 To initiate an SSL-encrypted connection, the frontend initially sends
1145 an SSLRequest message rather than a StartupMessage. The server then
1146 responds with a single byte containing <literal>Y</> or <literal>N</>,
1147 indicating that it is willing or unwilling to perform SSL, respectively.
1148 The frontend may close the connection at this point if it is dissatisfied
1149 with the response. To continue after <literal>Y</>, perform an SSL
1150 startup handshake (not described here, part of the SSL specification)
1151 with the server. If this is successful, continue with
1152 sending the usual StartupMessage. In this case the StartupMessage and
1153 all subsequent data will be SSL-encrypted. To continue after
1154 <literal>N</>, send the usual StartupMessage and proceed without
1159 The frontend should also be prepared to handle an ErrorMessage response
1160 to SSLRequest from the server. This would only occur if the server
1161 predates the addition of SSL support to <productname>PostgreSQL</>.
1162 In this case the connection must be closed, but the frontend may choose
1163 to open a fresh connection and proceed without requesting SSL.
1167 An initial SSLRequest may also be used in a connection that is being
1168 opened to send a CancelRequest message.
1172 While the protocol itself does not provide a way for the server to
1173 force SSL encryption, the administrator may configure the server to
1174 reject unencrypted sessions as a byproduct of authentication checking.
1179 <Sect1 id="protocol-message-types">
1180 <Title>Message Data Types</Title>
1183 This section describes the base data types used in messages.
1189 Int<Replaceable>n</Replaceable>(<Replaceable>i</Replaceable>)
1193 An <Replaceable>n</Replaceable>-bit integer in network byte
1194 order (most significant byte first).
1195 If <Replaceable>i</Replaceable> is specified it
1196 is the exact value that will appear, otherwise the value
1197 is variable. Eg. Int16, Int32(42).
1204 Int<Replaceable>n</Replaceable>[<Replaceable>k</Replaceable>]
1208 An array of <Replaceable>k</Replaceable>
1209 <Replaceable>n</Replaceable>-bit integers, each in network
1210 byte order. The array length <Replaceable>k</Replaceable>
1211 is always determined by an earlier field in the message.
1219 String(<Replaceable>s</Replaceable>)
1223 A null-terminated string (C-style string). There is no
1224 specific length limitation on strings.
1225 If <Replaceable>s</Replaceable> is specified it is the exact
1226 value that will appear, otherwise the value is variable.
1227 Eg. String, String("user").
1232 <Emphasis>There is no predefined limit</Emphasis> on the length of a string
1233 that can be returned by the backend. Good coding strategy for a frontend
1234 is to use an expandable buffer so that anything that fits in memory can be
1235 accepted. If that's not feasible, read the full string and discard trailing
1236 characters that don't fit into your fixed-size buffer.
1244 Byte<Replaceable>n</Replaceable>(<Replaceable>c</Replaceable>)
1248 Exactly <Replaceable>n</Replaceable> bytes. If the field
1249 width <Replaceable>n</Replaceable> is not a constant, it is
1250 always determinable from an earlier field in the message.
1251 If <Replaceable>c</Replaceable> is specified it is the exact
1252 value. Eg. Byte2, Byte1('\n').
1261 <Sect1 id="protocol-message-formats">
1262 <Title>Message Formats</Title>
1265 This section describes the detailed format of each message. Each is marked to
1266 indicate that it may be sent by a frontend (F), a backend (B), or both
1268 Notice that although each message includes a byte count at the beginning,
1269 the message format is defined so that the message end can be found without
1270 reference to the byte count. This aids validity checking. (The CopyData
1271 message is an exception, because it forms part of a data stream; the contents
1272 of any individual CopyData message may not be interpretable on their own.)
1280 AuthenticationOk (B)
1292 Identifies the message as an authentication request.
1302 Length of message contents in bytes, including self.
1312 Specifies that the authentication was successful.
1325 AuthenticationKerberosV4 (B)
1337 Identifies the message as an authentication request.
1347 Length of message contents in bytes, including self.
1357 Specifies that Kerberos V4 authentication is required.
1369 AuthenticationKerberosV5 (B)
1381 Identifies the message as an authentication request.
1391 Length of message contents in bytes, including self.
1401 Specifies that Kerberos V5 authentication is required.
1413 AuthenticationCleartextPassword (B)
1425 Identifies the message as an authentication request.
1435 Length of message contents in bytes, including self.
1445 Specifies that a cleartext password is required.
1457 AuthenticationCryptPassword (B)
1469 Identifies the message as an authentication request.
1479 Length of message contents in bytes, including self.
1489 Specifies that a crypt()-encrypted password is required.
1499 The salt to use when encrypting the password.
1512 AuthenticationMD5Password (B)
1524 Identifies the message as an authentication request.
1534 Length of message contents in bytes, including self.
1544 Specifies that an MD5-encrypted password is required.
1554 The salt to use when encrypting the password.
1567 AuthenticationSCMCredential (B)
1579 Identifies the message as an authentication request.
1589 Length of message contents in bytes, including self.
1599 Specifies that an SCM credentials message is required.
1624 Identifies the message as cancellation key data.
1625 The frontend must save these values if it wishes to be
1626 able to issue CancelRequest messages later.
1636 Length of message contents in bytes, including self.
1646 The process ID of this backend.
1656 The secret key of this backend.
1681 Identifies the message as a Bind command.
1691 Length of message contents in bytes, including self.
1701 The name of the destination portal
1702 (an empty string selects the unnamed portal).
1712 The name of the source prepared statement
1713 (an empty string selects the unnamed prepared statement).
1723 The number of parameter format codes that follow
1724 (denoted <replaceable>C</> below).
1725 This can be zero to indicate that there are no parameters
1726 or that the parameters all use the default format (text);
1727 or one, in which case the specified format code is applied
1728 to all parameters; or it can equal the actual number of
1735 Int16[<replaceable>C</>]
1739 The parameter format codes. Each must presently be
1740 zero (text) or one (binary).
1750 The number of parameter values that follow (possibly zero).
1751 This must match the number of parameters needed by the query.
1756 Next, the following pair of fields appear for each parameter:
1764 The length of the parameter value, in bytes (this count
1765 does not include itself). Can be zero.
1766 As a special case, -1 indicates a NULL parameter value.
1767 No value bytes follow in the NULL case.
1773 Byte<Replaceable>n</Replaceable>
1777 The value of the parameter, in the format indicated by the
1778 associated format code.
1779 <Replaceable>n</Replaceable> is the above length.
1784 After the last parameter, the following fields appear:
1792 The number of result-column format codes that follow
1793 (denoted <replaceable>R</> below).
1794 This can be zero to indicate that there are no result columns
1795 or that the result columns should all use the default format
1797 or one, in which case the specified format code is applied
1798 to all result columns (if any); or it can equal the actual
1799 number of result columns of the query.
1805 Int16[<replaceable>R</>]
1809 The result-column format codes. Each must presently be
1810 zero (text) or one (binary).
1834 Identifies the message as a Bind-complete indicator.
1844 Length of message contents in bytes, including self.
1869 Length of message contents in bytes, including self.
1879 The cancel request code. The value is chosen to contain
1880 <literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
1881 least 16 significant bits. (To avoid confusion, this code
1882 must not be the same as any protocol version number.)
1892 The process ID of the target backend.
1902 The secret key for the target backend.
1927 Identifies the message as a Close command.
1937 Length of message contents in bytes, including self.
1947 '<literal>S</>' to close a prepared statement; or
1948 '<literal>P</>' to close a portal.
1958 The name of the prepared statement or portal to close
1959 (an empty string selects the unnamed prepared statement
1984 Identifies the message as a Close-complete indicator.
1994 Length of message contents in bytes, including self.
2019 Identifies the message as a command-completed response.
2029 Length of message contents in bytes, including self.
2039 The command tag. This is usually a single
2040 word that identifies which SQL command was completed.
2044 For an <command>INSERT</command> command, the tag is
2045 <literal>INSERT <replaceable>oid</replaceable>
2046 <replaceable>rows</replaceable></literal>, where
2047 <replaceable>rows</replaceable> is the number of rows
2048 inserted. <replaceable>oid</replaceable> is the object ID
2049 of the inserted row if <Replaceable>rows</Replaceable> is 1
2050 and the target table has OIDs;
2051 otherwise <Replaceable>oid</Replaceable> is 0.
2055 For a <command>DELETE</command> command, the tag is
2056 <literal>DELETE <Replaceable>rows</Replaceable></literal> where
2057 <Replaceable>rows</Replaceable> is the number of rows deleted.
2061 For an <command>UPDATE</command> command, the tag is
2062 <literal>UPDATE <Replaceable>rows</Replaceable></literal> where
2063 <Replaceable>rows</Replaceable> is the number of rows updated.
2067 For a <command>MOVE</command> command, the tag is
2068 <literal>MOVE <replaceable>rows</replaceable></literal> where
2069 <replaceable>rows</replaceable> is the number of rows the
2070 cursor's position has been changed by.
2074 For a <command>FETCH</command> command, the tag is
2075 <literal>FETCH <replaceable>rows</replaceable></literal> where
2076 <replaceable>rows</replaceable> is the number of rows that
2077 have been retrieved from the cursor.
2090 CopyData (F & B)
2101 Identifies the message as COPY data.
2111 Length of message contents in bytes, including self.
2117 Byte<Replaceable>n</Replaceable>
2121 Data that forms part of a COPY datastream. Messages sent
2122 from the backend will always correspond to single data rows,
2123 but messages sent by frontends may divide the datastream
2136 CopyDone (F & B)
2148 Identifies the message as a COPY-complete indicator.
2158 Length of message contents in bytes, including self.
2183 Identifies the message as a COPY-failure indicator.
2193 Length of message contents in bytes, including self.
2203 An error message to report as the cause of failure.
2228 Identifies the message as a Start Copy In response.
2229 The frontend must now send copy-in data (if not
2230 prepared to do so, send a CopyFail message).
2240 Length of message contents in bytes, including self.
2250 0 indicates the overall copy format is textual (rows
2251 separated by newlines, columns separated by separator
2253 1 indicates the overall copy format is binary (similar
2255 See <xref linkend="sql-copy" endterm="sql-copy-title">
2256 for more information.
2266 The number of columns in the data to be copied
2267 (denoted <replaceable>N</> below).
2273 Int16[<replaceable>N</>]
2277 The format codes to be used for each column.
2278 Each must presently be zero (text) or one (binary).
2279 All must be zero if the overall copy format is textual.
2304 Identifies the message as a Start Copy Out response.
2305 This message will be followed by copy-out data.
2315 Length of message contents in bytes, including self.
2325 0 indicates the overall copy format is textual (rows
2326 separated by newlines, columns separated by separator
2328 1 indicates the overall copy format is binary (similar
2330 See <xref linkend="sql-copy" endterm="sql-copy-title">
2331 for more information.
2341 The number of columns in the data to be copied
2342 (denoted <replaceable>N</> below).
2348 Int16[<replaceable>N</>]
2352 The format codes to be used for each column.
2353 Each must presently be zero (text) or one (binary).
2354 All must be zero if the overall copy format is textual.
2378 Identifies the message as a data row.
2388 Length of message contents in bytes, including self.
2398 The number of column values that follow (possibly zero).
2403 Next, the following pair of fields appear for each column:
2411 The length of the column value, in bytes (this count
2412 does not include itself). Can be zero.
2413 As a special case, -1 indicates a NULL column value.
2414 No value bytes follow in the NULL case.
2420 Byte<Replaceable>n</Replaceable>
2424 The value of the column, in the format indicated by the
2425 associated format code.
2426 <Replaceable>n</Replaceable> is the above length.
2451 Identifies the message as a Describe command.
2461 Length of message contents in bytes, including self.
2471 '<literal>S</>' to describe a prepared statement; or
2472 '<literal>P</>' to describe a portal.
2482 The name of the prepared statement or portal to describe
2483 (an empty string selects the unnamed prepared statement
2496 EmptyQueryResponse (B)
2508 Identifies the message as a response to an empty query string.
2509 (This substitutes for CommandComplete.)
2519 Length of message contents in bytes, including self.
2544 Identifies the message as an error.
2554 Length of message contents in bytes, including self.
2559 The message body consists of one or more identified fields,
2560 followed by a zero byte as a terminator. Fields may appear in
2561 any order. For each field there is the following:
2569 A code identifying the field type; if zero, this is
2570 the message terminator and no string follows.
2571 The presently defined field types are listed in
2572 <xref linkend="protocol-error-fields">.
2573 Since more field types may be added in future,
2574 frontends should silently ignore fields of unrecognized
2610 Identifies the message as an Execute command.
2620 Length of message contents in bytes, including self.
2630 The name of the portal to execute
2631 (an empty string selects the unnamed portal).
2641 Maximum number of rows to return, if portal contains
2642 a query that returns rows (ignored otherwise). Zero
2643 denotes <quote>no limit</>.
2667 Identifies the message as a Flush command.
2677 Length of message contents in bytes, including self.
2702 Identifies the message as a function call.
2712 Length of message contents in bytes, including self.
2722 Specifies the object ID of the function to call.
2732 The number of argument format codes that follow
2733 (denoted <replaceable>C</> below).
2734 This can be zero to indicate that there are no arguments
2735 or that the arguments all use the default format (text);
2736 or one, in which case the specified format code is applied
2737 to all arguments; or it can equal the actual number of
2744 Int16[<replaceable>C</>]
2748 The argument format codes. Each must presently be
2749 zero (text) or one (binary).
2759 Specifies the number of arguments being supplied to the
2765 Next, the following pair of fields appear for each argument:
2773 The length of the argument value, in bytes (this count
2774 does not include itself). Can be zero.
2775 As a special case, -1 indicates a NULL argument value.
2776 No value bytes follow in the NULL case.
2782 Byte<Replaceable>n</Replaceable>
2786 The value of the argument, in the format indicated by the
2787 associated format code.
2788 <Replaceable>n</Replaceable> is the above length.
2793 After the last argument, the following field appears:
2801 The format code for the function result. Must presently be
2802 zero (text) or one (binary).
2815 FunctionCallResponse (B)
2827 Identifies the message as a function call result.
2837 Length of message contents in bytes, including self.
2847 The length of the function result value, in bytes (this count
2848 does not include itself). Can be zero.
2849 As a special case, -1 indicates a NULL function result.
2850 No value bytes follow in the NULL case.
2856 Byte<Replaceable>n</Replaceable>
2860 The value of the function result, in the format indicated by
2861 the associated format code.
2862 <Replaceable>n</Replaceable> is the above length.
2887 Identifies the message as a no-data indicator.
2897 Length of message contents in bytes, including self.
2922 Identifies the message as a notice.
2932 Length of message contents in bytes, including self.
2937 The message body consists of one or more identified fields,
2938 followed by a zero byte as a terminator. Fields may appear in
2939 any order. For each field there is the following:
2947 A code identifying the field type; if zero, this is
2948 the message terminator and no string follows.
2949 The presently defined field types are listed in
2950 <xref linkend="protocol-error-fields">.
2951 Since more field types may be added in future,
2952 frontends should silently ignore fields of unrecognized
2976 NotificationResponse (B)
2988 Identifies the message as a notification response.
2998 Length of message contents in bytes, including self.
3008 The process ID of the notifying backend process.
3018 The name of the condition that the notify has been raised on.
3028 Additional information passed from the notifying process.
3029 (Currently, this feature is unimplemented so the field
3030 is always an empty string.)
3043 ParameterDescription (B)
3055 Identifies the message as a parameter description.
3065 Length of message contents in bytes, including self.
3075 The number of parameters used by the statement
3081 Then, for each parameter, there is the following:
3089 Specifies the object ID of the parameter datatype.
3113 Identifies the message as a run-time parameter status report.
3123 Length of message contents in bytes, including self.
3133 The name of the run-time parameter being reported.
3143 The current value of the parameter.
3167 Identifies the message as a Parse command.
3177 Length of message contents in bytes, including self.
3187 The name of the destination prepared statement
3188 (an empty string selects the unnamed prepared statement).
3198 The query string to be parsed.
3208 The number of parameter datatypes specified
3209 (may be zero). Note that this is not an indication of
3210 the number of parameters that might appear in the
3211 query string, only the number that the frontend wants to
3212 prespecify types for.
3217 Then, for each parameter, there is the following:
3225 Specifies the object ID of the parameter datatype.
3226 Placing a zero here is equivalent to leaving the type
3251 Identifies the message as a Parse-complete indicator.
3261 Length of message contents in bytes, including self.
3286 Identifies the message as a password response.
3296 Length of message contents in bytes, including self.
3306 The password (encrypted, if requested).
3330 Identifies the message as a portal-suspended indicator.
3331 Note this only appears if an Execute message's row-count limit
3342 Length of message contents in bytes, including self.
3367 Identifies the message as a simple query.
3377 Length of message contents in bytes, including self.
3387 The query string itself.
3412 Identifies the message type. ReadyForQuery is sent
3413 whenever the backend is ready for a new query cycle.
3423 Length of message contents in bytes, including self.
3433 Current backend transaction status indicator.
3434 Possible values are '<literal>I</>' if idle (not in
3435 a transaction block); '<literal>T</>' if in a transaction
3436 block; or '<literal>E</>' if in a failed transaction
3437 block (queries will be rejected until block is ended).
3462 Identifies the message as a row description.
3472 Length of message contents in bytes, including self.
3482 Specifies the number of fields in a row (may be zero).
3487 Then, for each field, there is the following:
3505 If the field can be identified as a column of a specific
3506 table, the object ID of the table; otherwise zero.
3516 If the field can be identified as a column of a specific
3517 table, the attribute number of the column; otherwise zero.
3527 The object ID of the field's datatype.
3537 The datatype size (see <varname>pg_type.typlen</>).
3538 Note that negative values denote variable-width types.
3548 The type modifier (see <varname>pg_attribute.atttypmod</>).
3549 The meaning of the modifier is type-specific.
3559 The format code being used for the field. Currently will
3560 be zero (text) or one (binary). In a RowDescription
3561 returned from the statement variant of Describe, the
3562 format code is not yet known and will always be zero.
3587 Length of message contents in bytes, including self.
3597 The SSL request code. The value is chosen to contain
3598 <literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
3599 least 16 significant bits. (To avoid confusion, this code
3600 must not be the same as any protocol version number.)
3625 Length of message contents in bytes, including self.
3635 The protocol version number. The most significant 16 bits are
3636 the major version number (3 for the protocol described here).
3637 The least significant 16 bits are the minor version number
3638 (0 for the protocol described here).
3643 The protocol version number is followed by one or more pairs of
3644 parameter name and value strings. A zero byte is required as a
3645 terminator after the last name/value pair.
3646 Parameters can appear in any
3647 order. <literal>user</> is required, others are optional.
3648 Each parameter is specified as:
3656 The parameter name. Currently recognized names are:
3665 The database user name to connect as. Required;
3666 there is no default.
3672 <literal>database</>
3676 The database to connect to. Defaults to the user name.
3686 Command-line arguments for the backend. (This is
3687 deprecated in favor of setting individual run-time
3694 In addition to the above, any run-time parameter that can be
3695 set at backend start time may be listed. Such settings
3696 will be applied during backend start (after parsing the
3697 command-line options if any). The values will act as
3708 The parameter value.
3733 Identifies the message as a Sync command.
3743 Length of message contents in bytes, including self.
3768 Identifies the message as a termination.
3778 Length of message contents in bytes, including self.
3794 <Sect1 id="protocol-error-fields">
3795 <Title>Error and Notice Message Fields</Title>
3798 This section describes the fields that may appear in ErrorResponse and
3799 NoticeResponse messages. Each field type has a single-byte identification
3800 token. Note that any given field type should appear at most once per
3812 Severity: the field contents are
3813 <literal>ERROR</>, <literal>FATAL</>, or
3814 <literal>PANIC</> (in an error message), or
3815 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
3816 <literal>INFO</>, or <literal>LOG</> (in a notice message),
3817 or a localized translation of one of these. Always present.
3828 Code: the SQLSTATE code for the error (a 5-character
3829 string following SQL spec conventions). Not localizable.
3841 Message: the primary human-readable error message.
3842 This should be accurate but terse (typically one line).
3854 Detail: an optional secondary error message carrying more
3855 detail about the problem. May run to multiple lines.
3866 Hint: an optional suggestion what to do about the problem.
3867 This is intended to differ from Detail in that it offers advice
3868 (potentially inappropriate) rather than hard facts.
3869 May run to multiple lines.
3880 Position: the field value is a decimal ASCII integer, indicating
3881 an error cursor position as an index into the original query string.
3882 The first character has index 1, and positions are measured in
3883 characters not bytes.
3894 Where: an indication of the context in which the error occurred.
3895 Presently this includes a call stack traceback of active PL functions.
3896 The trace is one entry per line, most recent first.
3907 File: the file name of the source-code location where the error
3919 Line: the line number of the source-code location where the error
3931 Routine: the name of the source-code routine reporting the error.
3939 The client is responsible for formatting displayed information to meet its
3940 needs; in particular it should break long lines as needed. Newline characters
3941 appearing in the error message fields should be treated as paragraph breaks,
3948 <Sect1 id="protocol-changes">
3949 <Title>Summary of Changes since Protocol 2.0</Title>
3952 This section provides a quick checklist of changes, for the benefit of
3953 developers trying to update existing client libraries to protocol 3.0.
3957 The initial startup packet uses a flexible list-of-strings format
3958 instead of a fixed format. Notice that session default values for run-time
3959 parameters can now be specified directly in the startup packet. (Actually,
3960 you could do that before using the <literal>options</> field, but given the
3961 limited width of <literal>options</> and the lack of any way to quote
3962 whitespace in the values, it wasn't a very safe technique.)
3966 All messages now have a length count immediately following the message type
3967 byte (except for startup packets, which have no type byte). Also note that
3968 PasswordMessage now has a type byte.
3972 ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
3973 messages now contain multiple fields, from which the client code may
3974 assemble an error message of the desired level of verbosity. Note that
3975 individual fields will typically not end with a newline, whereas the single
3976 string sent in the older protocol always did.
3980 The ReadyForQuery ('<literal>Z</>') message includes a transaction status
3985 The distinction between BinaryRow and DataRow message types is gone; the
3986 single DataRow message type serves for returning data in all formats.
3987 Note that the layout of DataRow has changed to make it easier to parse.
3988 Also, the representation of binary values has changed: it is no longer
3989 directly tied to the server's internal representation.
3993 There is a new <quote>extended query</> sub-protocol, which adds the frontend
3994 message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
3995 backend message types ParseComplete, BindComplete, PortalSuspended,
3996 ParameterDescription, NoData, and CloseComplete. Existing clients do not
3997 have to concern themselves with this sub-protocol, but making use of it
3998 may allow improvements in performance or functionality.
4002 COPY data is now encapsulated into CopyData and CopyDone messages. There
4003 is a well-defined way to recover from errors during COPY. The special
4004 <quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4006 (It is still recognized as a terminator during COPY IN, but its use is
4007 deprecated and will eventually be removed.) Binary COPY is supported.
4008 The CopyInResponse and CopyOutResponse messages include fields indicating
4009 the number of columns and the format of each column.
4013 The layout of FunctionCall and FunctionCallResponse messages has changed.
4014 FunctionCall can now support passing NULL arguments to functions. It also
4015 can handle passing parameters and retrieving results in either text or
4016 binary format. There is no longer any reason to consider FunctionCall a
4017 potential security hole, since it does not offer direct access to internal
4018 server data representations.
4022 The backend sends ParameterStatus ('<literal>S</>') messages during connection
4023 startup for all parameters it considers interesting to the client library.
4024 Subsequently, a ParameterStatus message is sent whenever the active value
4025 changes for any of these parameters.
4029 The RowDescription ('<literal>T</>') message carries new table OID and column
4030 number fields for each column of the described row. It also shows the format
4031 code for each column.
4035 The CursorResponse ('<literal>P</>') message is no longer generated by
4040 The NotificationResponse ('<literal>A</>') message has an additional string
4041 field, which is presently empty but may someday carry additional data passed
4042 from the NOTIFY event sender.
4046 The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4047 string parameter; this has been removed.