1
<!-- doc/src/sgml/protocol.sgml -->
3
<chapter id="protocol">
4
<title>Frontend/Backend Protocol</title>
6
<indexterm zone="protocol">
7
<primary>protocol</primary>
8
<secondary>frontend-backend</secondary>
12
<productname>PostgreSQL</productname> uses a message-based protocol
13
for communication between frontends and backends (clients and servers).
14
The protocol is supported over <acronym>TCP/IP</acronym> and also over
15
Unix-domain sockets. Port number 5432 has been registered with IANA as
16
the customary TCP port number for servers supporting this protocol, but
17
in practice any non-privileged port number can be used.
21
This document describes version 3.0 of the protocol, implemented in
22
<productname>PostgreSQL</productname> 7.4 and later. For descriptions
23
of the earlier protocol versions, see previous releases of the
24
<productname>PostgreSQL</productname> documentation. A single server
25
can support multiple protocol versions. The initial
26
startup-request message tells the server which protocol version the
27
client is attempting to use, and then the server follows that protocol
32
In order to serve multiple clients efficiently, the server launches
33
a new <quote>backend</> process for each client.
34
In the current implementation, a new child
35
process is created immediately after an incoming connection is detected.
36
This is transparent to the protocol, however. For purposes of the
37
protocol, the terms <quote>backend</> and <quote>server</> are
38
interchangeable; likewise <quote>frontend</> and <quote>client</>
42
<sect1 id="protocol-overview">
43
<title>Overview</title>
46
The protocol has separate phases for startup and normal operation.
47
In the startup phase, the frontend opens a connection to the server
48
and authenticates itself to the satisfaction of the server. (This might
49
involve a single message, or multiple messages depending on the
50
authentication method being used.) If all goes well, the server then sends
51
status information to the frontend, and finally enters normal operation.
52
Except for the initial startup-request message, this part of the
53
protocol is driven by the server.
57
During normal operation, the frontend sends queries and
58
other commands to the backend, and the backend sends back query results
59
and other responses. There are a few cases (such as <command>NOTIFY</>)
61
backend will send unsolicited messages, but for the most part this portion
62
of a session is driven by frontend requests.
66
Termination of the session is normally by frontend choice, but can be
67
forced by the backend in certain cases. In any case, when the backend
68
closes the connection, it will roll back any open (incomplete) transaction
73
Within normal operation, SQL commands can be executed through either of
74
two sub-protocols. In the <quote>simple query</> protocol, the frontend
75
just sends a textual query string, which is parsed and immediately
76
executed by the backend. In the <quote>extended query</> protocol,
77
processing of queries is separated into multiple steps: parsing,
78
binding of parameter values, and execution. This offers flexibility
79
and performance benefits, at the cost of extra complexity.
83
Normal operation has additional sub-protocols for special operations
84
such as <command>COPY</>.
87
<sect2 id="protocol-message-concepts">
88
<title>Messaging Overview</title>
91
All communication is through a stream of messages. The first byte of a
92
message identifies the message type, and the next four bytes specify the
93
length of the rest of the message (this length count includes itself, but
94
not the message-type byte). The remaining contents of the message are
95
determined by the message type. For historical reasons, the very first
96
message sent by the client (the startup message) has no initial
101
To avoid losing synchronization with the message stream, both servers and
102
clients typically read an entire message into a buffer (using the byte
103
count) before attempting to process its contents. This allows easy
104
recovery if an error is detected while processing the contents. In
105
extreme situations (such as not having enough memory to buffer the
106
message), the receiver can use the byte count to determine how much
107
input to skip before it resumes reading messages.
111
Conversely, both servers and clients must take care never to send an
112
incomplete message. This is commonly done by marshaling the entire message
113
in a buffer before beginning to send it. If a communications failure
114
occurs partway through sending or receiving a message, the only sensible
115
response is to abandon the connection, since there is little hope of
116
recovering message-boundary synchronization.
120
<sect2 id="protocol-query-concepts">
121
<title>Extended Query Overview</title>
124
In the extended-query protocol, execution of SQL commands is divided
125
into multiple steps. The state retained between steps is represented
126
by two types of objects: <firstterm>prepared statements</> and
127
<firstterm>portals</>. A prepared statement represents the result of
128
parsing, semantic analysis, and (optionally) planning of a textual query
130
A prepared statement is not necessarily ready to execute, because it might
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 might be needed
147
to complete the operation.
151
The backend can keep track of multiple prepared statements and portals
152
(but note that these exist only within a session, and are never shared
153
across sessions). Existing prepared statements and portals are
154
referenced by names assigned when they were created. In addition,
155
an <quote>unnamed</> prepared statement and portal exist. Although these
156
behave largely the same as named objects, operations on them are optimized
157
for the case of executing a query only once and then discarding it,
158
whereas operations on named objects are optimized on the expectation
163
<sect2 id="protocol-format-codes">
164
<title>Formats and Format Codes</title>
167
Data of a particular data type might be transmitted in any of several
168
different <firstterm>formats</>. As of <productname>PostgreSQL</> 7.4
169
the only supported formats are <quote>text</> and <quote>binary</>,
170
but the protocol makes provision for future extensions. The desired
171
format for any value is specified by a <firstterm>format code</>.
172
Clients can specify a format code for each transmitted parameter value
173
and for each column of a query result. Text has format code zero,
174
binary has format code one, and all other format codes are reserved
175
for future definition.
179
The text representation of values is whatever strings are produced
180
and accepted by the input/output conversion functions for the
181
particular data type. In the transmitted representation, there is
182
no trailing null character; the frontend must add one to received
183
values if it wants to process them as C strings.
184
(The text format does not allow embedded nulls, by the way.)
188
Binary representations for integers use network byte order (most
189
significant byte first). For other data types consult the documentation
190
or source code to learn about the binary representation. Keep in mind
191
that binary representations for complex data types might change across
192
server versions; the text format is usually the more portable choice.
197
<sect1 id="protocol-flow">
198
<title>Message Flow</title>
201
This section describes the message flow and the semantics of each
202
message type. (Details of the exact representation of each message
203
appear in <xref linkend="protocol-message-formats">.) There are
204
several different sub-protocols depending on the state of the
205
connection: start-up, query, function call,
206
<command>COPY</command>, and termination. There are also special
207
provisions for asynchronous operations (including notification
208
responses and command cancellation), which can occur at any time
209
after the start-up phase.
213
<title>Start-up</title>
216
To begin a session, a frontend opens a connection to the server and sends
217
a startup message. This message includes the names of the user and of the
218
database the user wants to connect to; it also identifies the particular
219
protocol version to be used. (Optionally, the startup message can include
220
additional settings for run-time parameters.)
221
The server then uses this information and
222
the contents of its configuration files (such as
223
<filename>pg_hba.conf</filename>) to determine
224
whether the connection is provisionally acceptable, and what additional
225
authentication is required (if any).
229
The server then sends an appropriate authentication request message,
230
to which the frontend must reply with an appropriate authentication
231
response message (such as a password).
232
For all authentication methods except GSSAPI and SSPI, there is at most
233
one request and one response. In some methods, no response
234
at all is needed from the frontend, and so no authentication request
235
occurs. For GSSAPI and SSPI, multiple exchanges of packets may be needed
236
to complete the authentication.
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>AuthenticationKerberosV5</term>
271
The frontend must now take part in a Kerberos V5
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>AuthenticationCleartextPassword</term>
284
The frontend must now send a PasswordMessage containing the
285
password in clear-text form. If
286
this is the correct password, the server responds with an
287
AuthenticationOk, otherwise it responds with an ErrorResponse.
293
<term>AuthenticationMD5Password</term>
296
The frontend must now send a PasswordMessage containing the
297
password encrypted via MD5, using the 4-character salt
298
specified in the AuthenticationMD5Password message. If
299
this is the correct password, the server responds with an
300
AuthenticationOk, otherwise it responds with an ErrorResponse.
306
<term>AuthenticationSCMCredential</term>
309
This response is only possible for local Unix-domain connections
310
on platforms that support SCM credential messages. The frontend
311
must issue an SCM credential message and then send a single data
312
byte. (The contents of the data byte are uninteresting; it's
313
only used to ensure that the server waits long enough to receive
314
the credential message.) If the credential is acceptable,
315
the server responds with an
316
AuthenticationOk, otherwise it responds with an ErrorResponse.
322
<term>AuthenticationGSS</term>
325
The frontend must now initiate a GSSAPI negotiation. The frontend
326
will send a PasswordMessage with the first part of the GSSAPI
327
data stream in response to this. If further messages are needed,
328
the server will respond with AuthenticationGSSContinue.
334
<term>AuthenticationSSPI</term>
337
The frontend must now initiate a SSPI negotiation. The frontend
338
will send a PasswordMessage with the first part of the SSPI
339
data stream in response to this. If further messages are needed,
340
the server will respond with AuthenticationGSSContinue.
346
<term>AuthenticationGSSContinue</term>
349
This message contains the response data from the previous step
350
of GSSAPI or SSPI negotiation (AuthenticationGSS, AuthenticationSSPI
351
or a previous AuthenticationGSSContinue). If the GSSAPI
352
or SSPI data in this message
353
indicates more data is needed to complete the authentication,
354
the frontend must send that data as another PasswordMessage. If
355
GSSAPI or SSPI authentication is completed by this message, the server
356
will next send AuthenticationOk to indicate successful authentication
357
or ErrorResponse to indicate failure.
366
If the frontend does not support the authentication method
367
requested by the server, then it should immediately close the
372
After having received AuthenticationOk, the frontend must wait
373
for further messages from the server. In this phase a backend process
374
is being started, and the frontend is just an interested bystander.
375
It is still possible for the startup attempt
376
to fail (ErrorResponse), but in the normal case the backend will send
377
some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.
381
During this phase the backend will attempt to apply any additional
382
run-time parameter settings that were given in the startup message.
383
If successful, these values become session defaults. An error causes
384
ErrorResponse and exit.
388
The possible messages from the backend in this phase are:
392
<term>BackendKeyData</term>
395
This message provides secret-key data that the frontend must
396
save if it wants to be able to issue cancel requests later.
397
The frontend should not respond to this message, but should
398
continue listening for a ReadyForQuery message.
404
<term>ParameterStatus</term>
407
This message informs the frontend about the current (initial)
408
setting of backend parameters, such as <xref
409
linkend="guc-client-encoding"> or <xref linkend="guc-datestyle">.
410
The frontend can ignore this message, or record the settings
411
for its future use; see <xref linkend="protocol-async"> for
412
more details. The frontend should not respond to this
413
message, but should continue listening for a ReadyForQuery
420
<term>ReadyForQuery</term>
423
Start-up is completed. The frontend can now issue commands.
429
<term>ErrorResponse</term>
432
Start-up failed. The connection is closed after sending this
439
<term>NoticeResponse</term>
442
A warning message has been issued. The frontend should
443
display the message but continue listening for ReadyForQuery
452
The ReadyForQuery message is the same one that the backend will
453
issue after each command cycle. Depending on the coding needs of
454
the frontend, it is reasonable to consider ReadyForQuery as
455
starting a command cycle, or to consider ReadyForQuery as ending the
456
start-up phase and each subsequent command cycle.
461
<title>Simple Query</title>
464
A simple query cycle is initiated by the frontend sending a Query message
465
to the backend. The message includes an SQL command (or commands)
466
expressed as a text string.
467
The backend then sends one or more response
468
messages depending on the contents of the query command string,
469
and finally a ReadyForQuery response message. ReadyForQuery
470
informs the frontend that it can safely send a new command.
471
(It is not actually necessary for the frontend to wait for
472
ReadyForQuery before issuing another command, but the frontend must
473
then take responsibility for figuring out what happens if the earlier
474
command fails and already-issued later commands succeed.)
478
The possible response messages from the backend are:
482
<term>CommandComplete</term>
485
An SQL command completed normally.
491
<term>CopyInResponse</term>
494
The backend is ready to copy data from the frontend to a
495
table; see <xref linkend="protocol-copy">.
501
<term>CopyOutResponse</term>
504
The backend is ready to copy data from a table to the
505
frontend; see <xref linkend="protocol-copy">.
511
<term>RowDescription</term>
514
Indicates that rows are about to be returned in response to
515
a <command>SELECT</command>, <command>FETCH</command>, etc query.
516
The contents of this message describe the column layout of the rows.
517
This will be followed by a DataRow message for each row being returned
527
One of the set of rows returned by
528
a <command>SELECT</command>, <command>FETCH</command>, etc query.
534
<term>EmptyQueryResponse</term>
537
An empty query string was recognized.
543
<term>ErrorResponse</term>
546
An error has occurred.
552
<term>ReadyForQuery</term>
555
Processing of the query string is complete. A separate
556
message is sent to indicate this because the query string might
557
contain multiple SQL commands. (CommandComplete marks the
558
end of processing one SQL command, not the whole string.)
559
ReadyForQuery will always be sent, whether processing
560
terminates successfully or with an error.
566
<term>NoticeResponse</term>
569
A warning message has been issued in relation to the query.
570
Notices are in addition to other responses, i.e., the backend
571
will continue processing the command.
580
The response to a <command>SELECT</> query (or other queries that
581
return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
582
normally consists of RowDescription, zero or more
583
DataRow messages, and then CommandComplete.
584
<command>COPY</> to or from the frontend invokes special protocol
585
as described in <xref linkend="protocol-copy">.
586
All other query types normally produce only
587
a CommandComplete message.
591
Since a query string could contain several queries (separated by
592
semicolons), there might be several such response sequences before the
593
backend finishes processing the query string. ReadyForQuery is issued
594
when the entire string has been processed and the backend is ready to
595
accept a new query string.
599
If a completely empty (no contents other than whitespace) query string
600
is received, the response is EmptyQueryResponse followed by ReadyForQuery.
604
In the event of an error, ErrorResponse is issued followed by
605
ReadyForQuery. All further processing of the query string is aborted by
606
ErrorResponse (even if more queries remained in it). Note that this
607
might occur partway through the sequence of messages generated by an
612
In simple Query mode, the format of retrieved values is always text,
613
except when the given command is a <command>FETCH</> from a cursor
614
declared with the <literal>BINARY</> option. In that case, the
615
retrieved values are in binary format. The format codes given in
616
the RowDescription message tell which format is being used.
620
A frontend must be prepared to accept ErrorResponse and
621
NoticeResponse messages whenever it is expecting any other type of
622
message. See also <xref linkend="protocol-async"> concerning messages
623
that the backend might generate due to outside events.
627
Recommended practice is to code frontends in a state-machine style
628
that will accept any message type at any time that it could make sense,
629
rather than wiring in assumptions about the exact sequence of messages.
633
<sect2 id="protocol-flow-ext-query">
634
<title>Extended Query</title>
637
The extended query protocol breaks down the above-described simple
638
query protocol into multiple steps. The results of preparatory
639
steps can be re-used multiple times for improved efficiency.
640
Furthermore, additional features are available, such as the possibility
641
of supplying data values as separate parameters instead of having to
642
insert them directly into a query string.
646
In the extended protocol, the frontend first sends a Parse message,
647
which contains a textual query string, optionally some information
648
about data types of parameter placeholders, and the
649
name of a destination prepared-statement object (an empty string
650
selects the unnamed prepared statement). The response is
651
either ParseComplete or ErrorResponse. Parameter data types can be
652
specified by OID; if not given, the parser attempts to infer the
653
data types in the same way as it would do for untyped literal string
659
A parameter data type can be left unspecified by setting it to zero,
660
or by making the array of parameter type OIDs shorter than the
661
number of parameter symbols (<literal>$</><replaceable>n</>)
662
used in the query string. Another special case is that a parameter's
663
type can be specified as <type>void</> (that is, the OID of the
664
<type>void</> pseudotype). This is meant to allow parameter symbols
665
to be used for function parameters that are actually OUT parameters.
666
Ordinarily there is no context in which a <type>void</> parameter
667
could be used, but if such a parameter symbol appears in a function's
668
parameter list, it is effectively ignored. For example, a function
669
call such as <literal>foo($1,$2,$3,$4)</> could match a function with
670
two IN and two OUT arguments, if <literal>$3</> and <literal>$4</>
671
are specified as having type <type>void</>.
677
The query string contained in a Parse message cannot include more
678
than one SQL statement; else a syntax error is reported. This
679
restriction does not exist in the simple-query protocol, but it
680
does exist in the extended protocol, because allowing prepared
681
statements or portals to contain multiple commands would complicate
687
If successfully created, a named prepared-statement object lasts till
688
the end of the current session, unless explicitly destroyed. An unnamed
689
prepared statement lasts only until the next Parse statement specifying
690
the unnamed statement as destination is issued. (Note that a simple
691
Query message also destroys the unnamed statement.) Named prepared
692
statements must be explicitly closed before they can be redefined by
693
a Parse message, but this is not required for the unnamed statement.
694
Named prepared statements can also be created and accessed at the SQL
695
command level, using <command>PREPARE</> and <command>EXECUTE</>.
699
Once a prepared statement exists, it can be readied for execution using a
700
Bind message. The Bind message gives the name of the source prepared
701
statement (empty string denotes the unnamed prepared statement), the name
702
of the destination portal (empty string denotes the unnamed portal), and
703
the values to use for any parameter placeholders present in the prepared
705
supplied parameter set must match those needed by the prepared statement.
706
(If you declared any <type>void</> parameters in the Parse message,
707
pass NULL values for them in the Bind message.)
708
Bind also specifies the format to use for any data returned
709
by the query; the format can be specified overall, or per-column.
710
The response is either BindComplete or ErrorResponse.
715
The choice between text and binary output is determined by the format
716
codes given in Bind, regardless of the SQL command involved. The
717
<literal>BINARY</> attribute in cursor declarations is irrelevant when
718
using extended query protocol.
723
Query planning for named prepared-statement objects occurs when the Parse
724
message is processed. If a query will be repeatedly executed with
725
different parameters, it might be beneficial to send a single Parse message
726
containing a parameterized query, followed by multiple Bind
727
and Execute messages. This will avoid replanning the query on each
732
The unnamed prepared statement is likewise planned during Parse processing
733
if the Parse message defines no parameters. But if there are parameters,
734
query planning occurs every time Bind parameters are supplied. This allows the
735
planner to make use of the actual values of the parameters provided by
736
each Bind message, rather than use generic estimates.
741
Query plans generated from a parameterized query might be less
742
efficient than query plans generated from an equivalent query with actual
743
parameter values substituted. The query planner cannot make decisions
744
based on actual parameter values (for example, index selectivity) when
745
planning a parameterized query assigned to a named prepared-statement
746
object. This possible penalty is avoided when using the unnamed
747
statement, since it is not planned until actual parameter values are
748
available. The cost is that planning must occur afresh for each Bind,
749
even if the query stays the same.
754
If successfully created, a named portal object lasts till the end of the
755
current transaction, unless explicitly destroyed. An unnamed portal is
756
destroyed at the end of the transaction, or as soon as the next Bind
757
statement specifying the unnamed portal as destination is issued. (Note
758
that a simple Query message also destroys the unnamed portal.) Named
759
portals must be explicitly closed before they can be redefined by a Bind
760
message, but this is not required for the unnamed portal.
761
Named portals can also be created and accessed at the SQL
762
command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
766
Once a portal exists, it can be executed using an Execute message.
767
The Execute message specifies the portal name (empty string denotes the
769
a maximum result-row count (zero meaning <quote>fetch all rows</>).
770
The result-row count is only meaningful for portals
771
containing commands that return row sets; in other cases the command is
772
always executed to completion, and the row count is ignored.
774
responses to Execute are the same as those described above for queries
775
issued via simple query protocol, except that Execute doesn't cause
776
ReadyForQuery or RowDescription to be issued.
780
If Execute terminates before completing the execution of a portal
781
(due to reaching a nonzero result-row count), it will send a
782
PortalSuspended message; the appearance of this message tells the frontend
783
that another Execute should be issued against the same portal to
784
complete the operation. The CommandComplete message indicating
785
completion of the source SQL command is not sent until
786
the portal's execution is completed. Therefore, an Execute phase is
787
always terminated by the appearance of exactly one of these messages:
788
CommandComplete, EmptyQueryResponse (if the portal was created from
789
an empty query string), ErrorResponse, or PortalSuspended.
793
At completion of each series of extended-query messages, the frontend
794
should issue a Sync message. This parameterless message causes the
795
backend to close the current transaction if it's not inside a
796
<command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
797
meaning to commit if no error, or roll back if error). Then a
798
ReadyForQuery response is issued. The purpose of Sync is to provide
799
a resynchronization point for error recovery. When an error is detected
800
while processing any extended-query message, the backend issues
801
ErrorResponse, then reads and discards messages until a Sync is reached,
802
then issues ReadyForQuery and returns to normal message processing.
803
(But note that no skipping occurs if an error is detected
804
<emphasis>while</> processing Sync — this ensures that there is one
805
and only one ReadyForQuery sent for each Sync.)
810
Sync does not cause a transaction block opened with <command>BEGIN</>
811
to be closed. It is possible to detect this situation since the
812
ReadyForQuery message includes transaction status information.
817
In addition to these fundamental, required operations, there are several
818
optional operations that can be used with extended-query protocol.
822
The Describe message (portal variant) specifies the name of an existing
823
portal (or an empty string for the unnamed portal). The response is a
824
RowDescription message describing the rows that will be returned by
825
executing the portal; or a NoData message if the portal does not contain a
826
query that will return rows; or ErrorResponse if there is no such portal.
830
The Describe message (statement variant) specifies the name of an existing
831
prepared statement (or an empty string for the unnamed prepared
832
statement). The response is a ParameterDescription message describing the
833
parameters needed by the statement, followed by a RowDescription message
834
describing the rows that will be returned when the statement is eventually
835
executed (or a NoData message if the statement will not return rows).
836
ErrorResponse is issued if there is no such prepared statement. Note that
837
since Bind has not yet been issued, the formats to be used for returned
838
columns are not yet known to the backend; the format code fields in the
839
RowDescription message will be zeroes in this case.
844
In most scenarios the frontend should issue one or the other variant
845
of Describe before issuing Execute, to ensure that it knows how to
846
interpret the results it will get back.
851
The Close message closes an existing prepared statement or portal
852
and releases resources. It is not an error to issue Close against
853
a nonexistent statement or portal name. The response is normally
854
CloseComplete, but could be ErrorResponse if some difficulty is
855
encountered while releasing resources. Note that closing a prepared
856
statement implicitly closes any open portals that were constructed
861
The Flush message does not cause any specific output to be generated,
862
but forces the backend to deliver any data pending in its output
863
buffers. A Flush must be sent after any extended-query command except
864
Sync, if the frontend wishes to examine the results of that command before
865
issuing more commands. Without Flush, messages returned by the backend
866
will be combined into the minimum possible number of packets to minimize
872
The simple Query message is approximately equivalent to the series Parse,
873
Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
874
statement and portal objects and no parameters. One difference is that
875
it will accept multiple SQL statements in the query string, automatically
876
performing the bind/describe/execute sequence for each one in succession.
877
Another difference is that it will not return ParseComplete, BindComplete,
878
CloseComplete, or NoData messages.
884
<title>Function Call</title>
887
The Function Call sub-protocol allows the client to request a direct
888
call of any function that exists in the database's
889
<structname>pg_proc</structname> system catalog. The client must have
890
execute permission for the function.
895
The Function Call sub-protocol is a legacy feature that is probably best
896
avoided in new code. Similar results can be accomplished by setting up
897
a prepared statement that does <literal>SELECT function($1, ...)</>.
898
The Function Call cycle can then be replaced with Bind/Execute.
903
A Function Call cycle is initiated by the frontend sending a
904
FunctionCall message to the backend. The backend then sends one
905
or more response messages depending on the results of the function
906
call, and finally a ReadyForQuery response message. ReadyForQuery
907
informs the frontend that it can safely send a new query or
912
The possible response messages from the backend are:
916
<term>ErrorResponse</term>
919
An error has occurred.
925
<term>FunctionCallResponse</term>
928
The function call was completed and returned the result given
930
(Note that the Function Call protocol can only handle a single
931
scalar result, not a row type or set of results.)
937
<term>ReadyForQuery</term>
940
Processing of the function call is complete. ReadyForQuery
941
will always be sent, whether processing terminates
942
successfully or with an error.
948
<term>NoticeResponse</term>
951
A warning message has been issued in relation to the function
952
call. Notices are in addition to other responses, i.e., the
953
backend will continue processing the command.
961
<sect2 id="protocol-copy">
962
<title>COPY Operations</title>
965
The <command>COPY</> command allows high-speed bulk data transfer
966
to or from the server. Copy-in and copy-out operations each switch
967
the connection into a distinct sub-protocol, which lasts until the
968
operation is completed.
972
Copy-in mode (data transfer to the server) is initiated when the
973
backend executes a <command>COPY FROM STDIN</> SQL statement. The backend
974
sends a CopyInResponse message to the frontend. The frontend should
975
then send zero or more CopyData messages, forming a stream of input
976
data. (The message boundaries are not required to have anything to do
977
with row boundaries, although that is often a reasonable choice.)
978
The frontend can terminate the copy-in mode by sending either a CopyDone
979
message (allowing successful termination) or a CopyFail message (which
980
will cause the <command>COPY</> SQL statement to fail with an
981
error). The backend then reverts to the command-processing mode it was
982
in before the <command>COPY</> started, which will be either simple or
983
extended query protocol. It will next send either CommandComplete
984
(if successful) or ErrorResponse (if not).
988
In the event of a backend-detected error during copy-in mode (including
989
receipt of a CopyFail message), the backend will issue an ErrorResponse
990
message. If the <command>COPY</> command was issued via an extended-query
991
message, the backend will now discard frontend messages until a Sync
992
message is received, then it will issue ReadyForQuery and return to normal
993
processing. If the <command>COPY</> command was issued in a simple
994
Query message, the rest of that message is discarded and ReadyForQuery
995
is issued. In either case, any subsequent CopyData, CopyDone, or CopyFail
996
messages issued by the frontend will simply be dropped.
1000
The backend will ignore Flush and Sync messages received during copy-in
1001
mode. Receipt of any other non-copy message type constitutes an error
1002
that will abort the copy-in state as described above. (The exception for
1003
Flush and Sync is for the convenience of client libraries that always
1004
send Flush or Sync after an Execute message, without checking whether
1005
the command to be executed is a <command>COPY FROM STDIN</>.)
1009
Copy-out mode (data transfer from the server) is initiated when the
1010
backend executes a <command>COPY TO STDOUT</> SQL statement. The backend
1011
sends a CopyOutResponse message to the frontend, followed by
1012
zero or more CopyData messages (always one per row), followed by CopyDone.
1013
The backend then reverts to the command-processing mode it was
1014
in before the <command>COPY</> started, and sends CommandComplete.
1015
The frontend cannot abort the transfer (except by closing the connection
1016
or issuing a Cancel request),
1017
but it can discard unwanted CopyData and CopyDone messages.
1021
In the event of a backend-detected error during copy-out mode,
1022
the backend will issue an ErrorResponse message and revert to normal
1023
processing. The frontend should treat receipt of ErrorResponse as
1024
terminating the copy-out mode.
1028
It is possible for NoticeResponse and ParameterStatus messages to be
1029
interspersed between CopyData messages; frontends must handle these cases,
1030
and should be prepared for other asynchronous message types as well (see
1031
<xref linkend="protocol-async">). Otherwise, any message type other than
1032
CopyData or CopyDone may be treated as terminating copy-out mode.
1036
There is another Copy-related mode called Copy-both, which allows
1037
high-speed bulk data transfer to <emphasis>and</> from the server.
1038
Copy-both mode is initiated when a backend in walsender mode
1039
executes a <command>START_REPLICATION</command> statement. The
1040
backend sends a CopyBothResponse message to the frontend. Both
1041
the backend and the frontend may then send CopyData messages
1042
until the connection is terminated. See <xref
1043
linkend="protocol-replication">.
1047
The CopyInResponse, CopyOutResponse and CopyBothResponse messages
1048
include fields that inform the frontend of the number of columns
1049
per row and the format codes being used for each column. (As of
1050
the present implementation, all columns in a given <command>COPY</>
1051
operation will use the same format, but the message design does not
1057
<sect2 id="protocol-async">
1058
<title>Asynchronous Operations</title>
1061
There are several cases in which the backend will send messages that
1062
are not specifically prompted by the frontend's command stream.
1063
Frontends must be prepared to deal with these messages at any time,
1064
even when not engaged in a query.
1065
At minimum, one should check for these cases before beginning to
1066
read a query response.
1070
It is possible for NoticeResponse messages to be generated due to
1071
outside activity; for example, if the database administrator commands
1072
a <quote>fast</> database shutdown, the backend will send a NoticeResponse
1073
indicating this fact before closing the connection. Accordingly,
1074
frontends should always be prepared to accept and display NoticeResponse
1075
messages, even when the connection is nominally idle.
1079
ParameterStatus messages will be generated whenever the active
1080
value changes for any of the parameters the backend believes the
1081
frontend should know about. Most commonly this occurs in response
1082
to a <command>SET</> SQL command executed by the frontend, and
1083
this case is effectively synchronous — but it is also possible
1084
for parameter status changes to occur because the administrator
1085
changed a configuration file and then sent the
1086
<systemitem>SIGHUP</systemitem> signal to the server. Also,
1087
if a <command>SET</command> command is rolled back, an appropriate
1088
ParameterStatus message will be generated to report the current
1093
At present there is a hard-wired set of parameters for which
1094
ParameterStatus will be generated: they are
1095
<varname>server_version</>,
1096
<varname>server_encoding</>,
1097
<varname>client_encoding</>,
1098
<varname>application_name</>,
1099
<varname>is_superuser</>,
1100
<varname>session_authorization</>,
1101
<varname>DateStyle</>,
1102
<varname>IntervalStyle</>,
1103
<varname>TimeZone</>,
1104
<varname>integer_datetimes</>, and
1105
<varname>standard_conforming_strings</>.
1106
(<varname>server_encoding</>, <varname>TimeZone</>, and
1107
<varname>integer_datetimes</> were not reported by releases before 8.0;
1108
<varname>standard_conforming_strings</> was not reported by releases
1110
<varname>IntervalStyle</> was not reported by releases before 8.4;
1111
<varname>application_name</> was not reported by releases before 9.0.)
1113
<varname>server_version</>,
1114
<varname>server_encoding</> and
1115
<varname>integer_datetimes</>
1116
are pseudo-parameters that cannot change after startup.
1117
This set might change in the future, or even become configurable.
1118
Accordingly, a frontend should simply ignore ParameterStatus for
1119
parameters that it does not understand or care about.
1123
If a frontend issues a <command>LISTEN</command> command, then the
1124
backend will send a NotificationResponse message (not to be
1125
confused with NoticeResponse!) whenever a
1126
<command>NOTIFY</command> command is executed for the same
1132
At present, NotificationResponse can only be sent outside a
1133
transaction, and thus it will not occur in the middle of a
1134
command-response series, though it might occur just before ReadyForQuery.
1135
It is unwise to design frontend logic that assumes that, however.
1136
Good practice is to be able to accept NotificationResponse at any
1137
point in the protocol.
1143
<title>Cancelling Requests in Progress</title>
1146
During the processing of a query, the frontend might request
1147
cancellation of the query. The cancel request is not sent
1148
directly on the open connection to the backend for reasons of
1149
implementation efficiency: we don't want to have the backend
1150
constantly checking for new input from the frontend during query
1151
processing. Cancel requests should be relatively infrequent, so
1152
we make them slightly cumbersome in order to avoid a penalty in
1157
To issue a cancel request, the frontend opens a new connection to
1158
the server and sends a CancelRequest message, rather than the
1159
StartupMessage message that would ordinarily be sent across a new
1160
connection. The server will process this request and then close
1161
the connection. For security reasons, no direct reply is made to
1162
the cancel request message.
1166
A CancelRequest message will be ignored unless it contains the
1167
same key data (PID and secret key) passed to the frontend during
1168
connection start-up. If the request matches the PID and secret
1169
key for a currently executing backend, the processing of the
1170
current query is aborted. (In the existing implementation, this is
1171
done by sending a special signal to the backend process that is
1172
processing the query.)
1176
The cancellation signal might or might not have any effect — for
1177
example, if it arrives after the backend has finished processing
1178
the query, then it will have no effect. If the cancellation is
1179
effective, it results in the current command being terminated
1180
early with an error message.
1184
The upshot of all this is that for reasons of both security and
1185
efficiency, the frontend has no direct way to tell whether a
1186
cancel request has succeeded. It must continue to wait for the
1187
backend to respond to the query. Issuing a cancel simply improves
1188
the odds that the current query will finish soon, and improves the
1189
odds that it will fail with an error message instead of
1194
Since the cancel request is sent across a new connection to the
1195
server and not across the regular frontend/backend communication
1196
link, it is possible for the cancel request to be issued by any
1197
process, not just the frontend whose query is to be canceled.
1198
This might provide additional flexibility when building
1199
multiple-process applications. It also introduces a security
1200
risk, in that unauthorized persons might try to cancel queries.
1201
The security risk is addressed by requiring a dynamically
1202
generated secret key to be supplied in cancel requests.
1207
<title>Termination</title>
1210
The normal, graceful termination procedure is that the frontend
1211
sends a Terminate message and immediately closes the connection.
1212
On receipt of this message, the backend closes the connection and
1217
In rare cases (such as an administrator-commanded database shutdown)
1218
the backend might disconnect without any frontend request to do so.
1219
In such cases the backend will attempt to send an error or notice message
1220
giving the reason for the disconnection before it closes the connection.
1224
Other termination scenarios arise from various failure cases, such as core
1225
dump at one end or the other, loss of the communications link, loss of
1226
message-boundary synchronization, etc. If either frontend or backend sees
1227
an unexpected closure of the connection, it should clean
1228
up and terminate. The frontend has the option of launching a new backend
1229
by recontacting the server if it doesn't want to terminate itself.
1230
Closing the connection is also advisable if an unrecognizable message type
1231
is received, since this probably indicates loss of message-boundary sync.
1235
For either normal or abnormal termination, any open transaction is
1236
rolled back, not committed. One should note however that if a
1237
frontend disconnects while a non-<command>SELECT</command> query
1238
is being processed, the backend will probably finish the query
1239
before noticing the disconnection. If the query is outside any
1240
transaction block (<command>BEGIN</> ... <command>COMMIT</>
1241
sequence) then its results might be committed before the
1242
disconnection is recognized.
1247
<title><acronym>SSL</acronym> Session Encryption</title>
1250
If <productname>PostgreSQL</> was built with
1251
<acronym>SSL</acronym> support, frontend/backend communications
1252
can be encrypted using <acronym>SSL</acronym>. This provides
1253
communication security in environments where attackers might be
1254
able to capture the session traffic. For more information on
1255
encrypting <productname>PostgreSQL</productname> sessions with
1256
<acronym>SSL</acronym>, see <xref linkend="ssl-tcp">.
1260
To initiate an <acronym>SSL</acronym>-encrypted connection, the
1261
frontend initially sends an SSLRequest message rather than a
1262
StartupMessage. The server then responds with a single byte
1263
containing <literal>S</> or <literal>N</>, indicating that it is
1264
willing or unwilling to perform <acronym>SSL</acronym>,
1265
respectively. The frontend might close the connection at this point
1266
if it is dissatisfied with the response. To continue after
1267
<literal>S</>, perform an <acronym>SSL</acronym> startup handshake
1268
(not described here, part of the <acronym>SSL</acronym>
1269
specification) with the server. If this is successful, continue
1270
with sending the usual StartupMessage. In this case the
1271
StartupMessage and all subsequent data will be
1272
<acronym>SSL</acronym>-encrypted. To continue after
1273
<literal>N</>, send the usual StartupMessage and proceed without
1278
The frontend should also be prepared to handle an ErrorMessage
1279
response to SSLRequest from the server. This would only occur if
1280
the server predates the addition of <acronym>SSL</acronym> support
1281
to <productname>PostgreSQL</>. In this case the connection must
1282
be closed, but the frontend might choose to open a fresh connection
1283
and proceed without requesting <acronym>SSL</acronym>.
1287
An initial SSLRequest can also be used in a connection that is being
1288
opened to send a CancelRequest message.
1292
While the protocol itself does not provide a way for the server to
1293
force <acronym>SSL</acronym> encryption, the administrator can
1294
configure the server to reject unencrypted sessions as a byproduct
1295
of authentication checking.
1300
<sect1 id="protocol-replication">
1301
<title>Streaming Replication Protocol</title>
1304
To initiate streaming replication, the frontend sends the
1305
<literal>replication</> parameter in the startup message. This tells the
1306
backend to go into walsender mode, wherein a small set of replication commands
1307
can be issued instead of SQL statements. Only the simple query protocol can be
1308
used in walsender mode.
1310
The commands accepted in walsender mode are:
1314
<term>IDENTIFY_SYSTEM</term>
1317
Requests the server to identify itself. Server replies with a result
1318
set of a single row, containing three fields:
1329
The unique system identifier identifying the cluster. This
1330
can be used to check that the base backup used to initialize the
1331
standby came from the same cluster.
1342
Current TimelineID. Also useful to check that the standby is
1343
consistent with the master.
1354
Current xlog write location. Useful to get a known location in the
1355
transaction log where streaming can start.
1366
<term>START_REPLICATION <replaceable>XXX</>/<replaceable>XXX</></term>
1369
Instructs server to start streaming WAL, starting at
1370
WAL position <replaceable>XXX</>/<replaceable>XXX</>.
1371
The server can reply with an error, e.g. if the requested section of WAL
1372
has already been recycled. On success, server responds with a
1373
CopyBothResponse message, and then starts to stream WAL to the frontend.
1374
WAL will continue to be streamed until the connection is broken;
1375
no further commands will be accepted.
1379
WAL data is sent as a series of CopyData messages. (This allows
1380
other information to be intermixed; in particular the server can send
1381
an ErrorResponse message if it encounters a failure after beginning
1382
to stream.) The payload in each CopyData message follows this format:
1400
Identifies the message as WAL data.
1410
The starting point of the WAL data in this message, given in
1421
The current end of WAL on the server, given in
1432
The server's system clock at the time of transmission,
1433
given in TimestampTz format.
1439
Byte<replaceable>n</replaceable>
1443
A section of the WAL data stream.
1454
A single WAL record is never split across two CopyData messages.
1455
When a WAL record crosses a WAL page boundary, and is therefore
1456
already split using continuation records, it can be split at the page
1457
boundary. In other words, the first main WAL record and its
1458
continuation records can be sent in different CopyData messages.
1461
Note that all fields within the WAL data and the above-described header
1462
will be in the sending server's native format. Endianness, and the
1463
format for the timestamp, are unpredictable unless the receiver has
1464
verified that the sender's system identifier matches its own
1465
<filename>pg_control</> contents.
1468
If the WAL sender process is terminated normally (during postmaster
1469
shutdown), it will send a CommandComplete message before exiting.
1470
This might not happen during an abnormal shutdown, of course.
1474
The receiving process can send replies back to the sender at any time,
1475
using one of the following message formats (also in the payload of a
1483
Standby status update (F)
1494
Identifies the message as a receiver status update.
1504
The location of the last WAL byte + 1 received and written to disk
1505
in the standby, in XLogRecPtr format.
1515
The location of the last WAL byte + 1 flushed to disk in
1516
the standby, in XLogRecPtr format.
1526
The location of the last WAL byte + 1 applied in the standby, in
1537
The server's system clock at the time of transmission,
1538
given in TimestampTz format.
1553
Hot Standby feedback message (F)
1564
Identifies the message as a Hot Standby feedback message.
1574
The server's system clock at the time of transmission,
1575
given in TimestampTz format.
1585
The standby's current xmin.
1595
The standby's current epoch.
1609
<term>BASE_BACKUP [<literal>LABEL</literal> <replaceable>'label'</replaceable>] [<literal>PROGRESS</literal>] [<literal>FAST</literal>] [<literal>WAL</literal>] [<literal>NOWAIT</literal>]</term>
1612
Instructs the server to start streaming a base backup.
1613
The system will automatically be put in backup mode before the backup
1614
is started, and taken out of it when the backup is complete. The
1615
following options are accepted:
1618
<term><literal>LABEL</literal> <replaceable>'label'</replaceable></term>
1621
Sets the label of the backup. If none is specified, a backup label
1622
of <literal>base backup</literal> will be used. The quoting rules
1623
for the label are the same as a standard SQL string with
1624
<xref linkend="guc-standard-conforming-strings"> turned on.
1630
<term><literal>PROGRESS</></term>
1633
Request information required to generate a progress report. This will
1634
send back an approximate size in the header of each tablespace, which
1635
can be used to calculate how far along the stream is done. This is
1636
calculated by enumerating all the file sizes once before the transfer
1637
is even started, and may as such have a negative impact on the
1638
performance - in particular it may take longer before the first data
1639
is streamed. Since the database files can change during the backup,
1640
the size is only approximate and may both grow and shrink between
1641
the time of approximation and the sending of the actual files.
1647
<term><literal>FAST</></term>
1650
Request a fast checkpoint.
1656
<term><literal>WAL</literal></term>
1659
Include the necessary WAL segments in the backup. This will include
1660
all the files between start and stop backup in the
1661
<filename>pg_xlog</filename> directory of the base directory tar
1668
<term><literal>NOWAIT</literal></term>
1671
By default, the backup will wait until the last required xlog
1672
segment has been archived, or emit a warning if log archiving is
1673
not enabled. Specifying <literal>NOWAIT</literal> disables both
1674
the waiting and the warning, leaving the client responsible for
1675
ensuring the required log is available.
1682
When the backup is started, the server will first send two
1683
ordinary result sets, followed by one or more CopyResponse
1687
The first ordinary result set contains the starting position of the
1688
backup, given in XLogRecPtr format as a single column in a single row.
1691
The second ordinary result set has one row for each tablespace.
1692
The fields in this row are:
1698
The oid of the tablespace, or <literal>NULL</> if it's the base
1704
<term>spclocation</term>
1707
The full path of the tablespace directory, or <literal>NULL</>
1708
if it's the base directory.
1716
The approximate size of the tablespace, if progress report has
1717
been requested; otherwise it's <literal>NULL</>.
1724
After the second regular result set, one or more CopyResponse results
1725
will be sent, one for PGDATA and one for each additional tablespace other
1726
than <literal>pg_default</> and <literal>pg_global</>. The data in
1727
the CopyResponse results will be a tar format (using ustar00
1728
extensions) dump of the tablespace contents. After the tar data is
1729
complete, a final ordinary result set will be sent.
1733
The tar archive for the data directory and each tablespace will contain
1734
all files in the directories, regardless of whether they are
1735
<productname>PostgreSQL</> files or other files added to the same
1736
directory. The only excluded files are:
1737
<itemizedlist spacing="compact" mark="bullet">
1740
<filename>postmaster.pid</>
1745
<filename>pg_xlog</>, including subdirectories. If the backup is run
1746
with wal files included, a synthesized version of pg_xlog will be
1747
included, but it will only contain the files necessary for the
1748
backup to work, not the rest of the contents.
1752
Owner, group and file mode are set if the underlying filesystem on
1753
the server supports it.
1756
Once all tablespaces have been sent, a final regular result set will
1757
be sent. This result set contains the end position of the
1758
backup, given in XLogRecPtr format as a single column in a single row.
1768
<sect1 id="protocol-message-types">
1769
<title>Message Data Types</title>
1772
This section describes the base data types used in messages.
1778
Int<replaceable>n</replaceable>(<replaceable>i</replaceable>)
1782
An <replaceable>n</replaceable>-bit integer in network byte
1783
order (most significant byte first).
1784
If <replaceable>i</replaceable> is specified it
1785
is the exact value that will appear, otherwise the value
1786
is variable. Eg. Int16, Int32(42).
1793
Int<replaceable>n</replaceable>[<replaceable>k</replaceable>]
1797
An array of <replaceable>k</replaceable>
1798
<replaceable>n</replaceable>-bit integers, each in network
1799
byte order. The array length <replaceable>k</replaceable>
1800
is always determined by an earlier field in the message.
1808
String(<replaceable>s</replaceable>)
1812
A null-terminated string (C-style string). There is no
1813
specific length limitation on strings.
1814
If <replaceable>s</replaceable> is specified it is the exact
1815
value that will appear, otherwise the value is variable.
1816
Eg. String, String("user").
1821
<emphasis>There is no predefined limit</emphasis> on the length of a string
1822
that can be returned by the backend. Good coding strategy for a frontend
1823
is to use an expandable buffer so that anything that fits in memory can be
1824
accepted. If that's not feasible, read the full string and discard trailing
1825
characters that don't fit into your fixed-size buffer.
1833
Byte<replaceable>n</replaceable>(<replaceable>c</replaceable>)
1837
Exactly <replaceable>n</replaceable> bytes. If the field
1838
width <replaceable>n</replaceable> is not a constant, it is
1839
always determinable from an earlier field in the message.
1840
If <replaceable>c</replaceable> is specified it is the exact
1841
value. Eg. Byte2, Byte1('\n').
1850
<sect1 id="protocol-message-formats">
1851
<title>Message Formats</title>
1854
This section describes the detailed format of each message. Each is marked to
1855
indicate that it can be sent by a frontend (F), a backend (B), or both
1857
Notice that although each message includes a byte count at the beginning,
1858
the message format is defined so that the message end can be found without
1859
reference to the byte count. This aids validity checking. (The CopyData
1860
message is an exception, because it forms part of a data stream; the contents
1861
of any individual CopyData message cannot be interpretable on their own.)
1869
AuthenticationOk (B)
1881
Identifies the message as an authentication request.
1891
Length of message contents in bytes, including self.
1901
Specifies that the authentication was successful.
1914
AuthenticationKerberosV5 (B)
1926
Identifies the message as an authentication request.
1936
Length of message contents in bytes, including self.
1946
Specifies that Kerberos V5 authentication is required.
1958
AuthenticationCleartextPassword (B)
1970
Identifies the message as an authentication request.
1980
Length of message contents in bytes, including self.
1990
Specifies that a clear-text password is required.
2002
AuthenticationMD5Password (B)
2014
Identifies the message as an authentication request.
2024
Length of message contents in bytes, including self.
2034
Specifies that an MD5-encrypted password is required.
2044
The salt to use when encrypting the password.
2057
AuthenticationSCMCredential (B)
2069
Identifies the message as an authentication request.
2079
Length of message contents in bytes, including self.
2089
Specifies that an SCM credentials message is required.
2102
AuthenticationGSS (B)
2114
Identifies the message as an authentication request.
2124
Length of message contents in bytes, including self.
2134
Specifies that GSSAPI authentication is required.
2147
AuthenticationSSPI (B)
2159
Identifies the message as an authentication request.
2169
Length of message contents in bytes, including self.
2179
Specifies that SSPI authentication is required.
2190
AuthenticationGSSContinue (B)
2202
Identifies the message as an authentication request.
2212
Length of message contents in bytes, including self.
2222
Specifies that this message contains GSSAPI or SSPI data.
2228
Byte<replaceable>n</replaceable>
2232
GSSAPI or SSPI authentication data.
2257
Identifies the message as cancellation key data.
2258
The frontend must save these values if it wishes to be
2259
able to issue CancelRequest messages later.
2269
Length of message contents in bytes, including self.
2279
The process ID of this backend.
2289
The secret key of this backend.
2314
Identifies the message as a Bind command.
2324
Length of message contents in bytes, including self.
2334
The name of the destination portal
2335
(an empty string selects the unnamed portal).
2345
The name of the source prepared statement
2346
(an empty string selects the unnamed prepared statement).
2356
The number of parameter format codes that follow
2357
(denoted <replaceable>C</> below).
2358
This can be zero to indicate that there are no parameters
2359
or that the parameters all use the default format (text);
2360
or one, in which case the specified format code is applied
2361
to all parameters; or it can equal the actual number of
2368
Int16[<replaceable>C</>]
2372
The parameter format codes. Each must presently be
2373
zero (text) or one (binary).
2383
The number of parameter values that follow (possibly zero).
2384
This must match the number of parameters needed by the query.
2389
Next, the following pair of fields appear for each parameter:
2397
The length of the parameter value, in bytes (this count
2398
does not include itself). Can be zero.
2399
As a special case, -1 indicates a NULL parameter value.
2400
No value bytes follow in the NULL case.
2406
Byte<replaceable>n</replaceable>
2410
The value of the parameter, in the format indicated by the
2411
associated format code.
2412
<replaceable>n</replaceable> is the above length.
2417
After the last parameter, the following fields appear:
2425
The number of result-column format codes that follow
2426
(denoted <replaceable>R</> below).
2427
This can be zero to indicate that there are no result columns
2428
or that the result columns should all use the default format
2430
or one, in which case the specified format code is applied
2431
to all result columns (if any); or it can equal the actual
2432
number of result columns of the query.
2438
Int16[<replaceable>R</>]
2442
The result-column format codes. Each must presently be
2443
zero (text) or one (binary).
2467
Identifies the message as a Bind-complete indicator.
2477
Length of message contents in bytes, including self.
2502
Length of message contents in bytes, including self.
2512
The cancel request code. The value is chosen to contain
2513
<literal>1234</> in the most significant 16 bits, and <literal>5678</> in the
2514
least 16 significant bits. (To avoid confusion, this code
2515
must not be the same as any protocol version number.)
2525
The process ID of the target backend.
2535
The secret key for the target backend.
2560
Identifies the message as a Close command.
2570
Length of message contents in bytes, including self.
2580
'<literal>S</>' to close a prepared statement; or
2581
'<literal>P</>' to close a portal.
2591
The name of the prepared statement or portal to close
2592
(an empty string selects the unnamed prepared statement
2617
Identifies the message as a Close-complete indicator.
2627
Length of message contents in bytes, including self.
2652
Identifies the message as a command-completed response.
2662
Length of message contents in bytes, including self.
2672
The command tag. This is usually a single
2673
word that identifies which SQL command was completed.
2677
For an <command>INSERT</command> command, the tag is
2678
<literal>INSERT <replaceable>oid</replaceable>
2679
<replaceable>rows</replaceable></literal>, where
2680
<replaceable>rows</replaceable> is the number of rows
2681
inserted. <replaceable>oid</replaceable> is the object ID
2682
of the inserted row if <replaceable>rows</replaceable> is 1
2683
and the target table has OIDs;
2684
otherwise <replaceable>oid</replaceable> is 0.
2688
For a <command>DELETE</command> command, the tag is
2689
<literal>DELETE <replaceable>rows</replaceable></literal> where
2690
<replaceable>rows</replaceable> is the number of rows deleted.
2694
For an <command>UPDATE</command> command, the tag is
2695
<literal>UPDATE <replaceable>rows</replaceable></literal> where
2696
<replaceable>rows</replaceable> is the number of rows updated.
2700
For a <command>SELECT</command> or <command>CREATE TABLE AS</command>
2701
command, the tag is <literal>SELECT <replaceable>rows</replaceable></literal>
2702
where <replaceable>rows</replaceable> is the number of rows retrieved.
2706
For a <command>MOVE</command> command, the tag is
2707
<literal>MOVE <replaceable>rows</replaceable></literal> where
2708
<replaceable>rows</replaceable> is the number of rows the
2709
cursor's position has been changed by.
2713
For a <command>FETCH</command> command, the tag is
2714
<literal>FETCH <replaceable>rows</replaceable></literal> where
2715
<replaceable>rows</replaceable> is the number of rows that
2716
have been retrieved from the cursor.
2720
For a <command>COPY</command> command, the tag is
2721
<literal>COPY <replaceable>rows</replaceable></literal> where
2722
<replaceable>rows</replaceable> is the number of rows copied.
2723
(Note: the row count appears only in
2724
<productname>PostgreSQL</productname> 8.2 and later.)
2738
CopyData (F & B)
2749
Identifies the message as <command>COPY</command> data.
2759
Length of message contents in bytes, including self.
2765
Byte<replaceable>n</replaceable>
2769
Data that forms part of a <command>COPY</command> data stream. Messages sent
2770
from the backend will always correspond to single data rows,
2771
but messages sent by frontends might divide the data stream
2784
CopyDone (F & B)
2796
Identifies the message as a <command>COPY</command>-complete indicator.
2806
Length of message contents in bytes, including self.
2831
Identifies the message as a <command>COPY</command>-failure indicator.
2841
Length of message contents in bytes, including self.
2851
An error message to report as the cause of failure.
2876
Identifies the message as a Start Copy In response.
2877
The frontend must now send copy-in data (if not
2878
prepared to do so, send a CopyFail message).
2888
Length of message contents in bytes, including self.
2898
0 indicates the overall <command>COPY</command> format is textual (rows
2899
separated by newlines, columns separated by separator
2901
1 indicates the overall copy format is binary (similar
2903
See <xref linkend="sql-copy">
2904
for more information.
2914
The number of columns in the data to be copied
2915
(denoted <replaceable>N</> below).
2921
Int16[<replaceable>N</>]
2925
The format codes to be used for each column.
2926
Each must presently be zero (text) or one (binary).
2927
All must be zero if the overall copy format is textual.
2952
Identifies the message as a Start Copy Out response.
2953
This message will be followed by copy-out data.
2963
Length of message contents in bytes, including self.
2973
0 indicates the overall <command>COPY</command> format
2974
is textual (rows separated by newlines, columns
2975
separated by separator characters, etc). 1 indicates
2976
the overall copy format is binary (similar to DataRow
2977
format). See <xref linkend="sql-copy"> for more information.
2987
The number of columns in the data to be copied
2988
(denoted <replaceable>N</> below).
2994
Int16[<replaceable>N</>]
2998
The format codes to be used for each column.
2999
Each must presently be zero (text) or one (binary).
3000
All must be zero if the overall copy format is textual.
3013
CopyBothResponse (B)
3025
Identifies the message as a Start Copy Both response.
3026
This message is used only for Streaming Replication.
3036
Length of message contents in bytes, including self.
3046
0 indicates the overall <command>COPY</command> format
3047
is textual (rows separated by newlines, columns
3048
separated by separator characters, etc). 1 indicates
3049
the overall copy format is binary (similar to DataRow
3050
format). See <xref linkend="sql-copy"> for more information.
3060
The number of columns in the data to be copied
3061
(denoted <replaceable>N</> below).
3067
Int16[<replaceable>N</>]
3071
The format codes to be used for each column.
3072
Each must presently be zero (text) or one (binary).
3073
All must be zero if the overall copy format is textual.
3097
Identifies the message as a data row.
3107
Length of message contents in bytes, including self.
3117
The number of column values that follow (possibly zero).
3122
Next, the following pair of fields appear for each column:
3130
The length of the column value, in bytes (this count
3131
does not include itself). Can be zero.
3132
As a special case, -1 indicates a NULL column value.
3133
No value bytes follow in the NULL case.
3139
Byte<replaceable>n</replaceable>
3143
The value of the column, in the format indicated by the
3144
associated format code.
3145
<replaceable>n</replaceable> is the above length.
3170
Identifies the message as a Describe command.
3180
Length of message contents in bytes, including self.
3190
'<literal>S</>' to describe a prepared statement; or
3191
'<literal>P</>' to describe a portal.
3201
The name of the prepared statement or portal to describe
3202
(an empty string selects the unnamed prepared statement
3215
EmptyQueryResponse (B)
3227
Identifies the message as a response to an empty query string.
3228
(This substitutes for CommandComplete.)
3238
Length of message contents in bytes, including self.
3263
Identifies the message as an error.
3273
Length of message contents in bytes, including self.
3278
The message body consists of one or more identified fields,
3279
followed by a zero byte as a terminator. Fields can appear in
3280
any order. For each field there is the following:
3288
A code identifying the field type; if zero, this is
3289
the message terminator and no string follows.
3290
The presently defined field types are listed in
3291
<xref linkend="protocol-error-fields">.
3292
Since more field types might be added in future,
3293
frontends should silently ignore fields of unrecognized
3329
Identifies the message as an Execute command.
3339
Length of message contents in bytes, including self.
3349
The name of the portal to execute
3350
(an empty string selects the unnamed portal).
3360
Maximum number of rows to return, if portal contains
3361
a query that returns rows (ignored otherwise). Zero
3362
denotes <quote>no limit</>.
3386
Identifies the message as a Flush command.
3396
Length of message contents in bytes, including self.
3421
Identifies the message as a function call.
3431
Length of message contents in bytes, including self.
3441
Specifies the object ID of the function to call.
3451
The number of argument format codes that follow
3452
(denoted <replaceable>C</> below).
3453
This can be zero to indicate that there are no arguments
3454
or that the arguments all use the default format (text);
3455
or one, in which case the specified format code is applied
3456
to all arguments; or it can equal the actual number of
3463
Int16[<replaceable>C</>]
3467
The argument format codes. Each must presently be
3468
zero (text) or one (binary).
3478
Specifies the number of arguments being supplied to the
3484
Next, the following pair of fields appear for each argument:
3492
The length of the argument value, in bytes (this count
3493
does not include itself). Can be zero.
3494
As a special case, -1 indicates a NULL argument value.
3495
No value bytes follow in the NULL case.
3501
Byte<replaceable>n</replaceable>
3505
The value of the argument, in the format indicated by the
3506
associated format code.
3507
<replaceable>n</replaceable> is the above length.
3512
After the last argument, the following field appears:
3520
The format code for the function result. Must presently be
3521
zero (text) or one (binary).
3534
FunctionCallResponse (B)
3546
Identifies the message as a function call result.
3556
Length of message contents in bytes, including self.
3566
The length of the function result value, in bytes (this count
3567
does not include itself). Can be zero.
3568
As a special case, -1 indicates a NULL function result.
3569
No value bytes follow in the NULL case.
3575
Byte<replaceable>n</replaceable>
3579
The value of the function result, in the format indicated by
3580
the associated format code.
3581
<replaceable>n</replaceable> is the above length.
3606
Identifies the message as a no-data indicator.
3616
Length of message contents in bytes, including self.
3641
Identifies the message as a notice.
3651
Length of message contents in bytes, including self.
3656
The message body consists of one or more identified fields,
3657
followed by a zero byte as a terminator. Fields can appear in
3658
any order. For each field there is the following:
3666
A code identifying the field type; if zero, this is
3667
the message terminator and no string follows.
3668
The presently defined field types are listed in
3669
<xref linkend="protocol-error-fields">.
3670
Since more field types might be added in future,
3671
frontends should silently ignore fields of unrecognized
3695
NotificationResponse (B)
3707
Identifies the message as a notification response.
3717
Length of message contents in bytes, including self.
3727
The process ID of the notifying backend process.
3737
The name of the channel that the notify has been raised on.
3747
The <quote>payload</> string passed from the notifying process.
3760
ParameterDescription (B)
3772
Identifies the message as a parameter description.
3782
Length of message contents in bytes, including self.
3792
The number of parameters used by the statement
3798
Then, for each parameter, there is the following:
3806
Specifies the object ID of the parameter data type.
3830
Identifies the message as a run-time parameter status report.
3840
Length of message contents in bytes, including self.
3850
The name of the run-time parameter being reported.
3860
The current value of the parameter.
3884
Identifies the message as a Parse command.
3894
Length of message contents in bytes, including self.
3904
The name of the destination prepared statement
3905
(an empty string selects the unnamed prepared statement).
3915
The query string to be parsed.
3925
The number of parameter data types specified
3926
(can be zero). Note that this is not an indication of
3927
the number of parameters that might appear in the
3928
query string, only the number that the frontend wants to
3929
prespecify types for.
3934
Then, for each parameter, there is the following:
3942
Specifies the object ID of the parameter data type.
3943
Placing a zero here is equivalent to leaving the type
3968
Identifies the message as a Parse-complete indicator.
3978
Length of message contents in bytes, including self.
4003
Identifies the message as a password response. Note that
4004
this is also used for GSSAPI and SSPI response messages
4005
(which is really a design error, since the contained data
4006
is not a null-terminated string in that case, but can be
4007
arbitrary binary data).
4017
Length of message contents in bytes, including self.
4027
The password (encrypted, if requested).
4051
Identifies the message as a portal-suspended indicator.
4052
Note this only appears if an Execute message's row-count limit
4063
Length of message contents in bytes, including self.
4088
Identifies the message as a simple query.
4098
Length of message contents in bytes, including self.
4108
The query string itself.
4133
Identifies the message type. ReadyForQuery is sent
4134
whenever the backend is ready for a new query cycle.
4144
Length of message contents in bytes, including self.
4154
Current backend transaction status indicator.
4155
Possible values are '<literal>I</>' if idle (not in
4156
a transaction block); '<literal>T</>' if in a transaction
4157
block; or '<literal>E</>' if in a failed transaction
4158
block (queries will be rejected until block is ended).
4183
Identifies the message as a row description.
4193
Length of message contents in bytes, including self.
4203
Specifies the number of fields in a row (can be zero).
4208
Then, for each field, there is the following:
4226
If the field can be identified as a column of a specific
4227
table, the object ID of the table; otherwise zero.
4237
If the field can be identified as a column of a specific
4238
table, the attribute number of the column; otherwise zero.
4248
The object ID of the field's data type.
4258
The data type size (see <varname>pg_type.typlen</>).
4259
Note that negative values denote variable-width types.
4269
The type modifier (see <varname>pg_attribute.atttypmod</>).
4270
The meaning of the modifier is type-specific.
4280
The format code being used for the field. Currently will
4281
be zero (text) or one (binary). In a RowDescription
4282
returned from the statement variant of Describe, the
4283
format code is not yet known and will always be zero.
4308
Length of message contents in bytes, including self.
4318
The <acronym>SSL</acronym> request code. The value is chosen to contain
4319
<literal>1234</> in the most significant 16 bits, and <literal>5679</> in the
4320
least 16 significant bits. (To avoid confusion, this code
4321
must not be the same as any protocol version number.)
4346
Length of message contents in bytes, including self.
4356
The protocol version number. The most significant 16 bits are
4357
the major version number (3 for the protocol described here).
4358
The least significant 16 bits are the minor version number
4359
(0 for the protocol described here).
4364
The protocol version number is followed by one or more pairs of
4365
parameter name and value strings. A zero byte is required as a
4366
terminator after the last name/value pair.
4367
Parameters can appear in any
4368
order. <literal>user</> is required, others are optional.
4369
Each parameter is specified as:
4377
The parameter name. Currently recognized names are:
4386
The database user name to connect as. Required;
4387
there is no default.
4393
<literal>database</>
4397
The database to connect to. Defaults to the user name.
4407
Command-line arguments for the backend. (This is
4408
deprecated in favor of setting individual run-time
4415
In addition to the above, any run-time parameter that can be
4416
set at backend start time might be listed. Such settings
4417
will be applied during backend start (after parsing the
4418
command-line options if any). The values will act as
4429
The parameter value.
4454
Identifies the message as a Sync command.
4464
Length of message contents in bytes, including self.
4489
Identifies the message as a termination.
4499
Length of message contents in bytes, including self.
4515
<sect1 id="protocol-error-fields">
4516
<title>Error and Notice Message Fields</title>
4519
This section describes the fields that can appear in ErrorResponse and
4520
NoticeResponse messages. Each field type has a single-byte identification
4521
token. Note that any given field type should appear at most once per
4533
Severity: the field contents are
4534
<literal>ERROR</>, <literal>FATAL</>, or
4535
<literal>PANIC</> (in an error message), or
4536
<literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
4537
<literal>INFO</>, or <literal>LOG</> (in a notice message),
4538
or a localized translation of one of these. Always present.
4549
Code: the SQLSTATE code for the error (see <xref
4550
linkend="errcodes-appendix">). Not localizable. Always present.
4561
Message: the primary human-readable error message.
4562
This should be accurate but terse (typically one line).
4574
Detail: an optional secondary error message carrying more
4575
detail about the problem. Might run to multiple lines.
4586
Hint: an optional suggestion what to do about the problem.
4587
This is intended to differ from Detail in that it offers advice
4588
(potentially inappropriate) rather than hard facts.
4589
Might run to multiple lines.
4600
Position: the field value is a decimal ASCII integer, indicating
4601
an error cursor position as an index into the original query string.
4602
The first character has index 1, and positions are measured in
4603
characters not bytes.
4614
Internal position: this is defined the same as the <literal>P</>
4615
field, but it is used when the cursor position refers to an internally
4616
generated command rather than the one submitted by the client.
4617
The <literal>q</> field will always appear when this field appears.
4628
Internal query: the text of a failed internally-generated command.
4629
This could be, for example, a SQL query issued by a PL/pgSQL function.
4640
Where: an indication of the context in which the error occurred.
4641
Presently this includes a call stack traceback of active
4642
procedural language functions and internally-generated queries.
4643
The trace is one entry per line, most recent first.
4654
File: the file name of the source-code location where the error
4666
Line: the line number of the source-code location where the error
4678
Routine: the name of the source-code routine reporting the error.
4686
The client is responsible for formatting displayed information to meet its
4687
needs; in particular it should break long lines as needed. Newline characters
4688
appearing in the error message fields should be treated as paragraph breaks,
4694
<sect1 id="protocol-changes">
4695
<title>Summary of Changes since Protocol 2.0</title>
4698
This section provides a quick checklist of changes, for the benefit of
4699
developers trying to update existing client libraries to protocol 3.0.
4703
The initial startup packet uses a flexible list-of-strings format
4704
instead of a fixed format. Notice that session default values for run-time
4705
parameters can now be specified directly in the startup packet. (Actually,
4706
you could do that before using the <literal>options</> field, but given the
4707
limited width of <literal>options</> and the lack of any way to quote
4708
whitespace in the values, it wasn't a very safe technique.)
4712
All messages now have a length count immediately following the message type
4713
byte (except for startup packets, which have no type byte). Also note that
4714
PasswordMessage now has a type byte.
4718
ErrorResponse and NoticeResponse ('<literal>E</>' and '<literal>N</>')
4719
messages now contain multiple fields, from which the client code can
4720
assemble an error message of the desired level of verbosity. Note that
4721
individual fields will typically not end with a newline, whereas the single
4722
string sent in the older protocol always did.
4726
The ReadyForQuery ('<literal>Z</>') message includes a transaction status
4731
The distinction between BinaryRow and DataRow message types is gone; the
4732
single DataRow message type serves for returning data in all formats.
4733
Note that the layout of DataRow has changed to make it easier to parse.
4734
Also, the representation of binary values has changed: it is no longer
4735
directly tied to the server's internal representation.
4739
There is a new <quote>extended query</> sub-protocol, which adds the frontend
4740
message types Parse, Bind, Execute, Describe, Close, Flush, and Sync, and the
4741
backend message types ParseComplete, BindComplete, PortalSuspended,
4742
ParameterDescription, NoData, and CloseComplete. Existing clients do not
4743
have to concern themselves with this sub-protocol, but making use of it
4744
might allow improvements in performance or functionality.
4748
<command>COPY</command> data is now encapsulated into CopyData and CopyDone messages. There
4749
is a well-defined way to recover from errors during <command>COPY</command>. The special
4750
<quote><literal>\.</></quote> last line is not needed anymore, and is not sent
4751
during <command>COPY OUT</command>.
4752
(It is still recognized as a terminator during <command>COPY IN</command>, but its use is
4753
deprecated and will eventually be removed.) Binary <command>COPY</command> is supported.
4754
The CopyInResponse and CopyOutResponse messages include fields indicating
4755
the number of columns and the format of each column.
4759
The layout of FunctionCall and FunctionCallResponse messages has changed.
4760
FunctionCall can now support passing NULL arguments to functions. It also
4761
can handle passing parameters and retrieving results in either text or
4762
binary format. There is no longer any reason to consider FunctionCall a
4763
potential security hole, since it does not offer direct access to internal
4764
server data representations.
4768
The backend sends ParameterStatus ('<literal>S</>') messages during connection
4769
startup for all parameters it considers interesting to the client library.
4770
Subsequently, a ParameterStatus message is sent whenever the active value
4771
changes for any of these parameters.
4775
The RowDescription ('<literal>T</>') message carries new table OID and column
4776
number fields for each column of the described row. It also shows the format
4777
code for each column.
4781
The CursorResponse ('<literal>P</>') message is no longer generated by
4786
The NotificationResponse ('<literal>A</>') message has an additional string
4787
field, which can carry a <quote>payload</> string passed
4788
from the <command>NOTIFY</command> event sender.
4792
The EmptyQueryResponse ('<literal>I</>') message used to include an empty
4793
string parameter; this has been removed.
added
removed
doc/src/sgml/protocol.sgml
