70
Chapter 49. Frontend/Backend Protocol
49.2.5. COPY Operations
The
COPY
command allows high-speed bulk data transfer to or from the server. Copy-in and copy-out
operations each switch the connection into a distinct sub-protocol, which lasts until the operation is
completed.
Copy-in mode (data transfer to the server) is initiated when the backend executes a
COPY FROM
STDIN
SQL statement. The backend sends a CopyInResponse message to the frontend. The frontend
should then send zero or more CopyData messages, forming a stream of input data. (The message
boundaries are not required to have anything to do with row boundaries, although that is often a rea-
sonable choice.) Thefrontendcanterminatethecopy-in mode bysendingeither a CopyDone message
(allowing successful termination) or a CopyFail message (which will cause the
COPY
SQL statement
to fail with an error). The backend then reverts to the command-processing mode it was in before
the
COPY
started, which will be either simple or extended query protocol. It will next send either
CommandComplete (if successful) or ErrorResponse (if not).
In the event of a backend-detected error during copy-in mode (including receipt of a CopyFail mes-
sage), the backend will issue an ErrorResponse message. If the
COPY
command was issued via an
extended-query message, thebackend willnow discardfrontendmessages until a Syncmessageis re-
ceived, then it will issue ReadyForQuery and return to normal processing. If the
COPY
command was
issued in a simple Query message, the restof thatmessage is discarded andReadyForQuery is issued.
In either case, any subsequent CopyData, CopyDone, or CopyFail messages issued by the frontend
will simply be dropped.
The backend will ignore Flush and Sync messages received during copy-in mode. Receipt of any
other non-copy message type constitutes an error that will abort the copy-in state as described above.
(The exception for Flush and Sync is for the convenience of client libraries that always send Flush
or Sync after an Execute message, without checking whether the command to be executed is a
COPY
FROM STDIN
.)
Copy-out mode (data transfer from the server) is initiated when the backend executes a
COPY TO
STDOUT
SQL statement. The backend sends a CopyOutResponse message to the frontend, followed
by zero or more CopyData messages (always one per row), followed by CopyDone. The backend
then reverts to the command-processing mode it was in before the
COPY
started, and sends Com-
mandComplete. The frontendcannot abort the transfer (except by closing the connection or issuing a
Cancel request), but it can discard unwanted CopyData and CopyDone messages.
In the event of a backend-detected error during copy-out mode, the backend will issue an ErrorRe-
sponse message and revert to normal processing. The frontend should treat receipt of ErrorResponse
as terminating the copy-out mode.
Itis possible for NoticeResponseandParameterStatus messages tobeinterspersedbetweenCopyData
messages;frontends must handle thesecases, and should beprepared for other asynchronous message
types as well (see Section 49.2.6). Otherwise, any message type other than CopyData or CopyDone
may be treated as terminating copy-out mode.
There is another Copy-related mode called copy-both, which allows high-speed bulk data transfer
to and from the server. Copy-both mode is initiated when a backend in walsender mode executes
a
START_REPLICATION
statement. The backend sends a CopyBothResponse message to the fron-
tend. Both the backend and the frontend may then send CopyData messages until either end sends
aCopyDone message. After the client sends a CopyDone message, the connection goes from copy-
both mode to copy-out mode, and the client may not send any more CopyData messages. Similarly,
when the server sends a CopyDone message, the connection goes into copy-in mode, and the server
may not send any more CopyData messages. After both sides have sent a CopyDone message, the
copy mode is terminated, and the backend reverts to the command-processing mode. In the event of
abackend-detected error during copy-both mode, the backend will issue an ErrorResponse message,
1872
How to C#: Set Image Thumbnail in C#.NET VB.NET How-to, VB.NET PDF, VB.NET Word following steps below, you can create an image viewer WinForm Open or create a new WinForms application, add necessary dll
add photo to pdf in preview; how to add image to pdf in acrobat
91
Chapter 49. Frontend/Backend Protocol
discard frontendmessages untila Syncmessage is received, andthenissueReadyForQueryandreturn
to normal processing. The frontend should treat receipt of ErrorResponse as terminating the copy in
both directions; no CopyDone should be sent in this case. See Section 49.3 for more information on
the subprotocol transmitted over copy-both mode.
The CopyInResponse, CopyOutResponseandCopyBothResponse messagesincludefieldsthatinform
the frontend of the number of columns per row and the format codes being used for each column. (As
of the present implementation, all columns in a given
COPY
operation will use the same format, but
the message design does not assume this.)
49.2.6. Asynchronous Operations
There areseveral cases in whichthebackendwill sendmessages thatare notspecifically prompted by
the frontend’s commandstream. Frontends must be prepared todeal withthese messages at any time,
even when not engaged in a query. At minimum, one should check for these cases before beginning
to read a query response.
It is possible for NoticeResponse messages to be generated due to outside activity; for example, if
the database administrator commands a “fast” database shutdown, the backend will send a NoticeRe-
sponse indicating this fact before closing the connection. Accordingly, frontends should always be
prepared to accept and display NoticeResponse messages, even when the connection is nominally
idle.
ParameterStatus messages will be generated whenever the active value changes for any of the param-
eters the backend believes the frontend should know about. Most commonly this occurs in response
to a
SET
SQL command executed by the frontend, and this case is effectively synchronous — but it
is also possible for parameter status changes to occur because the administrator changed a configu-
ration file and then sent the SIGHUP signal to the server. Also, if a
SET
command is rolled back, an
appropriate ParameterStatus message will be generated to report the current effective value.
At present there is a hard-wired set of parameters for which ParameterStatus will be generated:
they are
server_version
,
server_encoding
,
client_encoding
,
application_name
,
is_superuser
,
session_authorization
,
DateStyle
,
IntervalStyle
,
TimeZone
,
integer_datetimes
,
and
standard_conforming_strings
.
(
server_encoding
,
TimeZone
, and
integer_datetimes
were not reported by releases before 8.0;
standard_conforming_strings
was not reported by releases before 8.1;
IntervalStyle
was not reported by releases before 8.4;
application_name
was not reported by releases
before 9.0.) Note that
server_version
,
server_encoding
and
integer_datetimes
are
pseudo-parameters that cannot change after startup. This set might change in the future, or even
become configurable. Accordingly, a frontend should simply ignore ParameterStatus for parameters
that it does not understand or care about.
If a frontend issues a
LISTEN
command, then the backend will send a NotificationResponse message
(not to be confused with NoticeResponse!) whenever a
NOTIFY
command is executed for the same
channel name.
Note: At present, NotificationResponse canonly be sent outside a transaction, andthus it willnot
occur in the middle of a command-response series, though it might occur just before ReadyFor-
Query. It is unwise to design frontend logic that assumes that, however. Good practice is to be
able to accept NotificationResponse at any point in the protocol.
1873
48
Chapter 49. Frontend/Backend Protocol
49.2.7. Canceling Requests in Progress
During the processing of a query, the frontend might request cancellation of the query. The cancel
request is not sent directly on the open connection to the backend for reasons of implementation
efficiency: we don’t want to have the backend constantly checking for new input from the frontend
during query processing. Cancel requests should be relatively infrequent, so we make them slightly
cumbersome in order to avoid a penalty in the normal case.
To issue a cancel request, the frontend opens a new connection to the server and sends a CancelRe-
quest message, rather than the StartupMessage message that would ordinarily be sent across a new
connection. The server will process this request and then close the connection. For security reasons,
no direct reply is made to the cancel request message.
ACancelRequest message will be ignored unless it contains the same key data (PID and secret key)
passed to the frontend during connection start-up. If the request matches the PID and secret key
for a currently executing backend, the processing of the current query is aborted. (In the existing
implementation, this is done by sending a special signal to the backend process that is processing the
query.)
The cancellation signal might or might not have any effect — for example, if it arrives after the
backend has finished processing the query, then it will have no effect. If the cancellation is effective,
it results in the current command being terminated early with an error message.
The upshot of all this is that for reasons of both security andefficiency, the frontend has no direct way
to tell whether a cancel request has succeeded. It must continue to wait for the backend to respond
to the query. Issuing a cancel simply improves the odds that the current query will finish soon, and
improves the odds that it will fail with an error message instead of succeeding.
Since the cancel request is sent across a new connection to the server and not across the regular
frontend/backendcommunicationlink, itis possible for thecancel requesttobeissuedbyanyprocess,
not just the frontend whose query is to be canceled. This might provide additional flexibility when
building multiple-process applications. It also introduces a security risk, in that unauthorized persons
mighttry tocancelqueries. Thesecurityriskis addressedbyrequiringa dynamically generatedsecret
key to be supplied in cancel requests.
49.2.8. Termination
The normal, graceful termination procedure is that the frontend sends a Terminate message and im-
mediately closes the connection. On receipt of this message, the backend closes the connection and
terminates.
Inrare cases (suchas an administrator-commandeddatabase shutdown) thebackendmightdisconnect
without any frontendrequest to do so. In such casesthe backendwill attempt to send anerror or notice
message giving the reason for the disconnection before it closes the connection.
Other terminationscenarios arisefrom various failurecases, suchas coredumpat one endor the other,
loss of the communications link, loss of message-boundary synchronization, etc. If either frontend or
backend sees an unexpected closure of the connection, it should clean upand terminate. The frontend
has the option of launching a new backend by recontacting the server if it doesn’t want to terminate
itself. Closing the connection is also advisable if an unrecognizable message type is received, since
this probably indicates loss of message-boundary sync.
For either normal or abnormal termination, any open transaction is rolled back, not committed. One
should note however that if a frontend disconnects while a non-
SELECT
query is being processed,
the backend will probably finish the query before noticing the disconnection. If the query is outside
1874
65
Chapter 49. Frontend/Backend Protocol
any transaction block (
BEGIN
...
COMMIT
sequence) then its results might be committed before the
disconnection is recognized.
49.2.9. SSL Session Encryption
If PostgreSQL was builtwith SSL support, frontend/backend communications can be encryptedusing
SSL. This provides communicationsecurityin environments where attackers might be able to capture
the session traffic. For more information on encrypting PostgreSQL sessions with SSL, see Section
17.9.
To initiate an SSL-encrypted connection, the frontend initially sends an SSLRequest message rather
than a StartupMessage. The server then responds with a single byte containing
S
or
N
,indicating that
it is willing or unwilling to perform SSL, respectively. The frontend might close the connection at
this point if it is dissatisfied with the response. To continue after
S
,perform an SSL startup handshake
(not described here, part of the SSL specification) with the server. If this is successful, continue with
sending the usual StartupMessage. In this case the StartupMessage and all subsequent data will be
SSL-encrypted. To continue after
N
,send the usual StartupMessage and proceed without encryption.
The frontend should also be prepared to handle an ErrorMessage response to SSLRequest from the
server. This would only occur if the server predates the addition of SSL support to PostgreSQL.
(Such servers are now very ancient, and likely do not exist in the wild anymore.) In this case the
connection must be closed, but the frontend might choose to open a fresh connection and proceed
without requesting SSL.
An initial SSLRequest can also be used in a connection that is being opened to send a CancelRequest
message.
While theprotocolitself does notprovidea wayfor the server toforceSSL encryption, the administra-
tor can configure the server toreject unencrypted sessions as a byproduct of authentication checking.
49.3. Streaming Replication Protocol
To initiate streaming replication, the frontend sends the
replication
parameter in the startup mes-
sage. A Boolean value of
true
tells the backend to go into walsender mode, wherein a small set of
replication commands can be issued instead of SQL statements. Only the simple query protocol can
be used in walsender mode. Passing
database
as the value instructs walsender to connect to the
database specified in the
dbname
parameter, which will allow the connection to be used for logical
replication from that database.
For the purpose of testing replication commands, you can make a replication connection via psql or
any other
libpq
-using tool with a connectionstring including the
replication
option, e.g.:
psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
However itis oftenmoreuseful to usepg_receivexlog (for physicalreplication) or pg_recvlogical(for
logical replication).
The commands accepted in walsender mode are:
IDENTIFY_SYSTEM
Requests the server to identify itself. Server replies with a result set of a single row, containing
four fields:
1875
68
Chapter 49. Frontend/Backend Protocol
systemid
The unique system identifier identifying the cluster. This can be used to check that the base
backup used to initialize the standby came from the same cluster.
timeline
Current TimelineID. Also useful to check that the standby is consistent with the master.
xlogpos
Current xlog flush location. Useful to get a known location in the transaction log where
streaming can start.
dbname
Database connected to or NULL.
TIMELINE_HISTORY
tli
Requests the server to send over the timeline history file for timeline
tli
.Server replies with a
result set of a single row, containing two fields:
filename
Filename of the timeline history file, e.g
00000002.history
.
content
Contents of the timeline history file.
CREATE_REPLICATION_SLOT
slot_name
{
PHYSICAL
|
LOGICAL output_plugin
}
Create a physical or logical replication slot. See Section 25.2.6 for more about replication slots.
slot_name
The name of the slot tocreate. Must be a valid replication slot name (see Section 25.2.6.1).
output_plugin
The name of the output plugin used for logical decoding (see Section 46.6).
START_REPLICATION [
SLOT slot_name
][
PHYSICAL
]
XXX/XXX
[
TIMELINE tli
]
Instructs server tostart streamingWAL, starting atWAL position
XXX/XXX
.If
TIMELINE
option
is specified, streaming starts on timeline
tli
;otherwise, the server’s current timeline is selected.
Theserver canreplywithanerror,e.g. if the requestedsectionof WALhas alreadybeenrecycled.
Onsuccess, server responds with a CopyBothResponse message, and then starts to stream WAL
to the frontend.
If a slot’sname is provided via
slot_name
,it willbeupdated as replicationprogresses sothatthe
server knows which WAL segments, and if
hot_standby_feedback
is on which transactions,
are still needed by the standby.
If the client requests a timeline that’s not the latest, but is part of the history of the server, the
server will stream all the WAL on that timeline starting from the requested startpoint, up to the
point where the server switched to another timeline. If the client requests streaming at exactly
the end of an old timeline, the server responds immediately with CommandComplete without
entering COPY mode.
1876
42
Chapter 49. Frontend/Backend Protocol
After streamingall the WAL on a timeline thatis notthe latest one, the server willend streaming
by exiting the COPY mode. When the client acknowledges this by also exiting COPY mode,
the server sends a result set with one row and two columns, indicating the next timeline in this
server’s history. The first column is the next timeline’s ID, and the second column is the XLOG
position where the switch happened. Usually, the switchpositionis the end of the WAL that was
streamed, but there are corner cases where the server can send some WAL from the old timeline
that it has not itself replayed before promoting. Finally, the server sends CommandComplete
message, and is ready to accept a new command.
WAL data is sent as a series of CopyData messages. (This allows other information to be inter-
mixed; in particular the server can sendan ErrorResponse message if it encounters a failure after
beginning to stream.) The payload of each CopyData message from server to the client contains
amessage of one of the following formats:
XLogData (B)
Byte1(’w’)
Identifies the message as WAL data.
Int64
The starting point of the WAL data in this message.
Int64
The current end of WAL onthe server.
Int64
The server’s system clock at the time of transmission, as microseconds since midnight
on 2000-01-01.
Byte
n
Asection of the WAL data stream.
Asingle WAL record is never split across two XLogData messages. When a WAL
record crosses a WAL page boundary, and is therefore already split using continuation
records, it canbe split at the page boundary. In other words, the first mainWAL record
and its continuation records can be sent in different XLogData messages.
Primary keepalive message (B)
Byte1(’k’)
Identifies the message as a sender keepalive.
Int64
The current end of WAL onthe server.
Int64
The server’s system clock at the time of transmission, as microseconds since midnight
on 2000-01-01.
Byte1
1means that the client should reply to this message as soon as possible, to avoid a
timeout disconnect. 0 otherwise.
1877
Documents you may be interested
Documents you may be interested
