protocol.sgml

PostgreSQL7.4.6 for Linux

       <Para>
        A warning message has been issued.  The frontend should
        display the message but continue listening for ReadyForQuery
        or ErrorResponse.
       </Para>
      </ListItem>
     </VarListEntry>
    </VariableList>
   </Para>

   <para>
    The ReadyForQuery message is the same one that the backend will
    issue after each command cycle.  Depending on the coding needs of
    the frontend, it is reasonable to consider ReadyForQuery as
    starting a command cycle, or to consider ReadyForQuery as ending the
    start-up phase and each subsequent command cycle.
   </para>
  </sect2>

  <Sect2>
   <Title>Simple Query</Title>

   <Para>
    A simple query cycle is initiated by the frontend sending a Query message
    to the backend.  The message includes an SQL command (or commands)
    expressed as a text string.
    The backend then sends one or more response
    messages depending on the contents of the query command string,
    and finally a ReadyForQuery response message.  ReadyForQuery
    informs the frontend that it may safely send a new command.
    (It is not actually necessary for the frontend to wait for
    ReadyForQuery before issuing another command, but the frontend must
    then take responsibility for figuring out what happens if the earlier
    command fails and already-issued later commands succeed.)
   </para>

   <Para>
    The possible response messages from the backend are:

    <VariableList>
     <VarListEntry>
      <Term>CommandComplete</Term>
      <ListItem>
       <Para>
        An SQL command completed normally.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>CopyInResponse</Term>
      <ListItem>
       <Para>
        The backend is ready to copy data from the frontend to a
        table; see <xref linkend="protocol-copy">.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>CopyOutResponse</Term>
      <ListItem>
       <Para>
        The backend is ready to copy data from a table to the
        frontend; see <xref linkend="protocol-copy">.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>RowDescription</Term>
      <ListItem>
       <Para>
        Indicates that rows are about to be returned in response to
	a <command>SELECT</command>, <command>FETCH</command>, etc query.
	The contents of this message describe the column layout of the rows.
	This will be followed by a DataRow message for each row being returned
        to the frontend.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>DataRow</Term>
      <ListItem>
       <Para>
        One of the set of rows returned by
	a <command>SELECT</command>, <command>FETCH</command>, etc query.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>EmptyQueryResponse</Term>
      <ListItem>
       <Para>
        An empty query string was recognized.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>ErrorResponse</Term>
      <ListItem>
       <Para>
        An error has occurred.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>ReadyForQuery</Term>
      <ListItem>
       <Para>
        Processing of the query string is complete.  A separate
        message is sent to indicate this because the query string may
        contain multiple SQL commands.  (CommandComplete marks the
        end of processing one SQL command, not the whole string.)
        ReadyForQuery will always be sent, whether processing
        terminates successfully or with an error.
       </Para>
      </ListItem>
     </VarListEntry>

     <VarListEntry>
      <Term>NoticeResponse</Term>
      <ListItem>
       <Para>
        A warning message has been issued in relation to the query.
        Notices are in addition to other responses, i.e., the backend
        will continue processing the command.
       </Para>
      </ListItem>
     </VarListEntry>

    </VariableList>
   </Para>

   <Para>
    The response to a <command>SELECT</> query (or other queries that
    return row sets, such as <command>EXPLAIN</> or <command>SHOW</>)
    normally consists of RowDescription, zero or more
    DataRow messages, and then CommandComplete.
    <command>COPY</> to or from the frontend invokes special protocol
    as described in <xref linkend="protocol-copy">.
    All other query types normally produce only
    a CommandComplete message.
   </Para>

   <Para>
    Since a query string could contain several queries (separated by
    semicolons), there might be several such response sequences before the
    backend finishes processing the query string.  ReadyForQuery is issued
    when the entire string has been processed and the backend is ready to
    accept a new query string.
   </Para>

   <Para>
    If a completely empty (no contents other than whitespace) query string
    is received, the response is EmptyQueryResponse followed by ReadyForQuery.
   </Para>

   <Para>
    In the event of an error, ErrorResponse is issued followed by
    ReadyForQuery.  All further processing of the query string is aborted by
    ErrorResponse (even if more queries remained in it).  Note that this
    may occur partway through the sequence of messages generated by an
    individual query.
   </Para>

   <para>
    In simple Query mode, the format of retrieved values is always text,
    except when the given command is a <command>FETCH</> from a cursor
    declared with the <literal>BINARY</> option.  In that case, the
    retrieved values are in binary format.  The format codes given in
    the RowDescription message tell which format is being used.
   </para>

   <para>
    A frontend must be prepared to accept ErrorResponse and
    NoticeResponse messages whenever it is expecting any other type of
    message.  See also <xref linkend="protocol-async"> concerning messages
    that the backend may generate due to outside events.
   </para>

   <para>
    Recommended practice is to code frontends in a state-machine style
    that will accept any message type at any time that it could make sense,
    rather than wiring in assumptions about the exact sequence of messages.
   </para>
  </sect2>

  <Sect2>
   <Title>Extended Query</Title>

   <para>
    The extended query protocol breaks down the above-described simple
    query protocol into multiple steps.  The results of preparatory
    steps can be re-used multiple times for improved efficiency.
    Furthermore, additional features are available, such as the possibility
    of supplying data values as separate parameters instead of having to
    insert them directly into a query string.
   </para>

   <para>
    In the extended protocol, the frontend first sends a Parse message,
    which contains a textual query string, optionally some information
    about data types of parameter placeholders, and the
    name of a destination prepared-statement object (an empty string
    selects the unnamed prepared statement).  The response is
    either ParseComplete or ErrorResponse.  Parameter data types may be
    specified by OID; if not given, the parser attempts to infer the
    data types in the same way as it would do for untyped literal string
    constants.
   </para>

   <note>
    <para>
     The query string contained in a Parse message cannot include more
     than one SQL statement; else a syntax error is reported.  This
     restriction does not exist in the simple-query protocol, but it
     does exist in the extended protocol, because allowing prepared
     statements or portals to contain multiple commands would complicate
     the protocol unduly.
    </para>
   </note>

   <para>
    If successfully created, a named prepared-statement object lasts till
    the end of the current session, unless explicitly destroyed.  An unnamed
    prepared statement lasts only until the next Parse statement specifying
    the unnamed statement as destination is issued.  (Note that a simple
    Query message also destroys the unnamed statement.)  Named prepared
    statements must be explicitly closed before they can be redefined by
    a Parse message, but this is not required for the unnamed statement.
    Named prepared statements can also be created and accessed at the SQL
    command level, using <command>PREPARE</> and <command>EXECUTE</>.
   </para>

   <para>
    Once a prepared statement exists, it can be readied for execution using a
    Bind message.  The Bind message gives the name of the source prepared
    statement (empty string denotes the unnamed prepared statement), the name
    of the destination portal (empty string denotes the unnamed portal), and
    the values to use for any parameter placeholders present in the prepared
    statement.  The
    supplied parameter set must match those needed by the prepared statement.
    Bind also specifies the format to use for any data returned
    by the query; the format can be specified overall, or per-column.
    The response is either BindComplete or ErrorResponse.
   </para>

   <note>
    <para>
     The choice between text and binary output is determined by the format
     codes given in Bind, regardless of the SQL command involved.  The
     <literal>BINARY</> attribute in cursor declarations is irrelevant when
     using extended query protocol.
    </para>
   </note>

   <para>
    If successfully created, a named portal object lasts till the end of the
    current transaction, unless explicitly destroyed.  An unnamed portal is
    destroyed at the end of the transaction, or as soon as the next Bind
    statement specifying the unnamed portal as destination is issued.  (Note
    that a simple Query message also destroys the unnamed portal.)  Named
    portals must be explicitly closed before they can be redefined by a Bind
    message, but this is not required for the unnamed portal.
    Named portals can also be created and accessed at the SQL
    command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
   </para>

   <para>
    Once a portal exists, it can be executed using an Execute message.
    The Execute message specifies the portal name (empty string denotes the
    unnamed portal) and
    a maximum result-row count (zero meaning <quote>fetch all rows</>).
    The result-row count is only meaningful for portals
    containing commands that return row sets; in other cases the command is
    always executed to completion, and the row count is ignored.
    The possible
    responses to Execute are the same as those described above for queries
    issued via simple query protocol, except that Execute doesn't cause
    ReadyForQuery to be issued.
   </para>

   <para>
    If Execute terminates before completing the execution of a portal
    (due to reaching a nonzero result-row count), it will send a
    PortalSuspended message; the appearance of this message tells the frontend
    that another Execute should be issued against the same portal to
    complete the operation.  The CommandComplete message indicating
    completion of the source SQL command is not sent until
    the portal's execution is completed.  Therefore, an Execute phase is
    always terminated by the appearance of exactly one of these messages:
    CommandComplete, EmptyQueryResponse (if the portal was created from
    an empty query string), ErrorResponse, or PortalSuspended.
   </para>

   <para>
    At completion of each series of extended-query messages, the frontend
    should issue a Sync message.  This parameterless message causes the
    backend to close the current transaction if it's not inside a
    <command>BEGIN</>/<command>COMMIT</> transaction block (<quote>close</>
    meaning to commit if no error, or roll back if error).  Then a
    ReadyForQuery response is issued.  The purpose of Sync is to provide
    a resynchronization point for error recovery.  When an error is detected
    while processing any extended-query message, the backend issues
    ErrorResponse, then reads and discards messages until a Sync is reached,
    then issues ReadyForQuery and returns to normal message processing.
    (But note that no skipping occurs if an error is detected
    <emphasis>while</> processing Sync --- this ensures that there is one
    and only one ReadyForQuery sent for each Sync.)
   </para>

   <note>
    <para>
     Sync does not cause a transaction block opened with <command>BEGIN</>
     to be closed.  It is possible to detect this situation since the
     ReadyForQuery message includes transaction status information.
    </para>
   </note>

   <para>
    In addition to these fundamental, required operations, there are several
    optional operations that can be used with extended-query protocol.
   </para>

   <para>
    The Describe message (portal variant) specifies the name of an existing
    portal (or an empty string for the unnamed portal).  The response is a
    RowDescription message describing the rows that will be returned by
    executing the portal; or a NoData message if the portal does not contain a
    query that will return rows; or ErrorResponse if there is no such portal.
   </para>

   <para>
    The Describe message (statement variant) specifies the name of an existing
    prepared statement (or an empty string for the unnamed prepared
    statement).  The response is a ParameterDescription message describing the
    parameters needed by the statement, followed by a RowDescription message
    describing the rows that will be returned when the statement is eventually
    executed (or a NoData message if the statement will not return rows).
    ErrorResponse is issued if there is no such prepared statement.  Note that
    since Bind has not yet been issued, the formats to be used for returned
    columns are not yet known to the backend; the format code fields in the
    RowDescription message will be zeroes in this case.
   </para>

   <tip>
    <para>
     In most scenarios the frontend should issue one or the other variant
     of Describe before issuing Execute, to ensure that it knows how to
     interpret the results it will get back.
    </para>
   </tip>

   <para>
    The Close message closes an existing prepared statement or portal
    and releases resources.  It is not an error to issue Close against
    a nonexistent statement or portal name.  The response is normally
    CloseComplete, but could be ErrorResponse if some difficulty is
    encountered while releasing resources.  Note that closing a prepared
    statement implicitly closes any open portals that were constructed
    from that statement.
   </para>

   <para>
    The Flush message does not cause any specific output to be generated,
    but forces the backend to deliver any data pending in its output
    buffers.  A Flush must be sent after any extended-query command except
    Sync, if the frontend wishes to examine the results of that command before
    issuing more commands.  Without Flush, messages returned by the backend
    will be combined into the minimum possible number of packets to minimize
    network overhead.
   </para>

   <note>
    <para>
     The simple Query message is approximately equivalent to the series Parse,
     Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared
     statement and portal objects and no parameters.  One difference is that
     it will accept multiple SQL statements in the query string, automatically
     performing the bind/describe/execute sequence for each one in succession.
     Another difference is that it will not return ParseComplete, BindComplete,
     CloseComplete, or NoData messages.
    </para>
   </note>
  </sect2>

  <Sect2>
   <Title>Function Call</Title>

   <para>
    The Function Call sub-protocol allows the client to request a direct
    call of any function that exists in the database's
    <structname>pg_proc</structname> system catalog.  The client must have
    execute permission for the function.
   </para>

   <note>
    <para>
     The Function Call sub-protocol is a legacy feature that is probably best
     avoided in new code.  Similar results can be accomplished by setting up
     a prepared statement that does <literal>SELECT function($1, ...)</>.
     The Function Call cycle can then be replaced with Bind/Execute.
    </para>
   </note>

   <para>
    A Function Call cycle is initiated by the frontend sending a
    FunctionCall message to the backend.  The backend then sends one
    or more response messages depending on the results of the function
    call, and finally a ReadyForQuery response message.  ReadyForQuery
    informs the frontend that it may safely send a new query or
    function call.
   </para>

   <para>
    The possible response messages from the backend are:

    <VariableList>
     <VarListEntry>