libpq.sgml

关系型数据库 Postgresql 6.5.2

<ListItem>
<Para>
PQexec can return only one PGresult structure.  If the submitted query
string contains multiple SQL commands, all but the last PGresult are
discarded by PQexec.
</Para>
</ListItem>
</ItemizedList>
</Para>

<Para>
Applications that do not like these limitations can instead use the
underlying functions that PQexec is built from: PQsendQuery and
PQgetResult.

<ItemizedList>
<ListItem>
<Para>
<Function>PQsendQuery</Function>
          Submit a query to <ProductName>Postgres</ProductName> without
	  waiting for the result(s).  TRUE is returned if the query was
	  successfully dispatched, FALSE if not (in which case, use
	  PQerrorMessage to get more information about the failure).
<synopsis>
int PQsendQuery(PGconn *conn,
                const char *query);
</synopsis>
	  After successfully calling PQsendQuery, call PQgetResult one or more
	  times to obtain the query results.  PQsendQuery may not be called
	  again (on the same connection) until PQgetResult has returned NULL,
	  indicating that the query is done.
</Para>
</ListItem>

<ListItem>
<Para>
<Function>PQgetResult</Function>
          Wait for the next result from a prior PQsendQuery,
	  and return it.  NULL is returned when the query is complete
	  and there will be no more results.
<synopsis>
PGresult *PQgetResult(PGconn *conn);
</synopsis>
	  PQgetResult must be called repeatedly until it returns NULL,
	  indicating that the query is done.  (If called when no query is
	  active, PQgetResult will just return NULL at once.)
	  Each non-null result from PQgetResult should be processed using
	  the same PGresult accessor functions previously described.
	  Don't forget to free each result object with PQclear when done with it.
	  Note that PQgetResult will block only if a query is active and the
	  necessary response data has not yet been read by PQconsumeInput.
</Para>
</ListItem>

</ItemizedList>
</Para>

<Para>
Using PQsendQuery and PQgetResult solves one of PQexec's problems:
if a query string contains multiple SQL commands, the results of those
commands can be obtained individually.  (This allows a simple form of
overlapped processing, by the way: the frontend can be handling the
results of one query while the backend is still working on later
queries in the same query string.)  However, calling PQgetResult will
still cause the frontend to block until the backend completes the
next SQL command.  This can be avoided by proper use of three more
functions:

<ItemizedList>
<ListItem>
<Para>
<Function>PQconsumeInput</Function>
	  If input is available from the backend, consume it.
<synopsis>
int PQconsumeInput(PGconn *conn);
</synopsis>
PQconsumeInput normally returns 1 indicating "no error", but returns
0 if there was some kind of trouble (in which case PQerrorMessage
is set).  Note that the result does not say whether any input data
was actually collected.   After calling PQconsumeInput,
the application may check PQisBusy and/or PQnotifies to see if their state
has changed.
	  PQconsumeInput may be called even if the application is not
	  prepared to deal with a result or notification just yet.  The
	  routine will read available data and save it in a buffer, thereby
	  causing a select(2) read-ready indication to go away.  The
	  application can thus use PQconsumeInput to clear the select
	  condition immediately, and then examine the results at leisure.
</Para>
</ListItem>

<ListItem>
<Para>
<Function>PQisBusy</Function>
	  Returns TRUE if a query is busy, that is, PQgetResult would block
	  waiting for input.  A FALSE return indicates that PQgetResult can
	  be called with assurance of not blocking.
<synopsis>
int PQisBusy(PGconn *conn);
</synopsis>
	  PQisBusy will not itself attempt to read data from the backend;
	  therefore PQconsumeInput must be invoked first, or the busy
	  state will never end.
</Para>
</ListItem>

<ListItem>
<Para>
<Function>PQsocket</Function>
	  Obtain the file descriptor number for the backend connection socket.
	  A valid descriptor will be >= 0; a result of -1 indicates that
	  no backend connection is currently open.
<synopsis>
int PQsocket(PGconn *conn);
</synopsis>
	  PQsocket should be used to obtain the backend socket descriptor
	  in preparation for executing select(2).  This allows an application
	  to wait for either backend responses or other conditions.
	  If the result of select(2) indicates that data can be read from
	  the backend socket, then PQconsumeInput should be called to read the
	  data; after which, PQisBusy, PQgetResult, and/or PQnotifies can be
	  used to process the response.
</Para>
</ListItem>

</ItemizedList>
</Para>

<Para>
A typical frontend using these functions will have a main loop that uses
select(2) to wait for all the conditions that it must respond to.  One of
the conditions will be input available from the backend, which in select's
terms is readable data on the file descriptor identified by PQsocket.
When the main loop detects input ready, it should call PQconsumeInput
to read the input.  It can then call PQisBusy, followed by PQgetResult
if PQisBusy returns FALSE.  It can also call PQnotifies to detect NOTIFY
messages (see "Asynchronous Notification", below).
</Para>

<Para>
A frontend that uses PQsendQuery/PQgetResult can also attempt to cancel
a query that is still being processed by the backend.
</Para>

<Para>
<ItemizedList>
<ListItem>
<Para>
<Function>PQrequestCancel</Function>
	  Request that <ProductName>Postgres</ProductName> abandon
	  processing of the current query.
<synopsis>
int PQrequestCancel(PGconn *conn);
</synopsis>
	  The return value is TRUE if the cancel request was successfully
	  dispatched, FALSE if not.  (If not, PQerrorMessage tells why not.)
	  Successful dispatch is no guarantee that the request will have any
	  effect, however.  Regardless of the return value of PQrequestCancel,
	  the application must continue with the normal result-reading
	  sequence using PQgetResult.  If the cancellation
	  is effective, the current query will terminate early and return
	  an error result.  If the cancellation fails (say because the
	  backend was already done processing the query), then there will
	  be no visible result at all.
</Para>
</ListItem>
</ItemizedList>
</Para>

<Para>
Note that if the current query is part of a transaction, cancellation
will abort the whole transaction.
</Para>

<Para>
PQrequestCancel can safely be invoked from a signal handler.  So, it is
also possible to use it in conjunction with plain PQexec, if the decision
to cancel can be made in a signal handler.  For example, psql invokes
PQrequestCancel from a SIGINT signal handler, thus allowing interactive
cancellation of queries that it issues through PQexec.  Note that
PQrequestCancel will have no effect if the connection is not currently open
or the backend is not currently processing a query.
</Para>

</Sect1>

<Sect1>
<Title>Fast Path</Title>

<Para>
<ProductName>Postgres</ProductName> provides a fast path interface to send
function calls to the backend.  This is a trapdoor into system internals and
can be a potential security hole.  Most users will not need this feature.

<ItemizedList>
<ListItem>
<Para>
<Function>PQfn</Function>
	Request execution of a backend function via the fast path interface.
<synopsis>
PGresult* PQfn(PGconn* conn,
               int fnid,
               int *result_buf,
               int *result_len,
               int result_is_int,
               PQArgBlock *args,
               int nargs);
</synopsis>
     The fnid argument is the object identifier of the function to be
     executed.
     result_buf is the buffer in which
     to place the return value.  The caller must  have  allocated
     sufficient space to store the return value (there is no check!).
     The actual result length will be returned in the integer pointed
     to  by  result_len.   If a 4-byte integer result is expected, set
     result_is_int to 1; otherwise set it to 0.  (Setting result_is_int to 1
     tells libpq to byte-swap the value if necessary, so that it is
     delivered as a proper int value for the client machine.  When
     result_is_int is 0, the byte string sent by the backend is returned
     unmodified.)
     args and nargs specify the arguments to be passed to the function.
<synopsis>
typedef struct {
             int len;
             int isint;
             union {
                 int *ptr;
                 int integer;
             } u;
         } PQArgBlock;
</synopsis>
     PQfn always returns a valid PGresult*.  The  resultStatus  should be checked before the result is used.   The
     caller is responsible for  freeing  the  PGresult  with
     PQclear when it is no longer needed.
</Para>
</ListItem>
</ItemizedList>
</Para>

</Sect1>

<Sect1>
<Title>Asynchronous Notification</Title>

<Para>
<ProductName>Postgres</ProductName> supports asynchronous notification via the
LISTEN and NOTIFY commands.  A backend registers its interest in a particular
notification condition with the LISTEN command (and can stop listening
with the UNLISTEN command).  All backends listening on a
particular condition will be notified asynchronously when a NOTIFY of that
condition name is executed by any backend.  No additional information is
passed from the notifier to the listener.  Thus, typically, any actual data
that needs to be communicated is transferred through a database relation.
Commonly the condition name is the same as the associated relation, but it is
not necessary for there to be any associated relation.
</Para>

<Para>
<FileName>libpq</FileName> applications submit LISTEN and UNLISTEN
commands as ordinary SQL queries.  Subsequently, arrival of NOTIFY
messages can be detected by calling PQnotifies().

<ItemizedList>
<ListItem>
<Para>
<Function>PQnotifies</Function>
          Returns  the next notification from a list of unhandled
          notification messages received from the backend.  Returns NULL if
          there are no pending notifications.  Once a notification is
	  returned from PQnotifies, it is considered handled and will be
	  removed from the list of notifications.
<synopsis>
PGnotify* PQnotifies(PGconn *conn);

typedef struct pgNotify
    {
        char        relname[NAMEDATALEN];       /* name of relation
                                                 * containing data */
        int         be_pid;                     /* process id of backend */
    } PGnotify;
</synopsis>
	  After processing a PGnotify object returned by PQnotifies,
	  be sure to free it with free() to avoid a memory leak.
	  NOTE: in <productname>Postgres</productname> 6.4 and later,
	  the be_pid is the notifying backend's, whereas in earlier versions
	  it was always your own backend's PID.
</Para>
</ListItem>
</ItemizedList>
</Para>

<Para>
The  second  sample program gives an example of the use
of asynchronous notification.
</Para>

<Para>
PQnotifies() does not actually read backend data; it just returns messages
previously absorbed by another <FileName>libpq</FileName> function.  In prior
releases of <FileName>libpq</FileName>, the only way to ensure timely receipt
of NOTIFY messages was to constantly submit queries, even empty ones, and then
check PQnotifies() after each PQexec().  While this still works, it is
deprecated as a waste of processing power.  A better way to check for NOTIFY
messages when you have no useful queries to make is to call PQconsumeInput(),
then check PQnotifies().  You can use select(2) to wait for backend data to
arrive, thereby using no CPU power unless there is something to do.  Note that
this will work OK whether you use PQsendQuery/PQgetResult or plain old PQexec
for queries.  You should, however, remember to check PQnotifies() after each
PQgetResult or PQexec to see if any notifications came in during the
processing of the query.
</Para>

</Sect1>

<Sect1>
<Title>Functions Associated with the COPY Command</Title>

<Para>
     The COPY command in <ProductName>Postgres</ProductName> has options to  read  from
     or  write  to  the  network  connection  used by <FileName>libpq</FileName>.
     Therefore, functions are necessary to access this  network
     connection directly so applications may take advantage of this capability.
</Para>

<Para>
     These functions should be executed only after obtaining a PGRES_COPY_OUT
     or PGRES_COPY_IN result object from PQexec or PQgetResult.
</Para>

<Para>
<ItemizedList>
<ListItem>
<Para>
<Function>PQgetline</Function>
          Reads  a  newline-terminated  line  of  characters
          (transmitted  by the backend server) into a buffer
          string of size length.
<synopsis>
int PQgetline(PGconn *conn,
              char *string,
              int length)
</synopsis>
  Like fgets(3),  this  routine copies up to length-1 characters into string.
          It is like gets(3), however, in that  it  converts
          the terminating newline into a null character.
          PQgetline returns EOF at EOF, 0 if the entire line
          has been read, and 1 if the buffer is full but the
          terminating newline has not yet been read.
          Notice that the application must check to see if a
          new line consists of  the  two characters  "\.",
          which  indicates  that the backend server has finished sending
	  the results  of  the  copy  command.
If  the  application might
receive lines that are more than length-1  characters  long,
care is needed to be sure one recognizes the "\." line correctly
(and does not, for example, mistake the end of a long data line
for a terminator line).
The code in
<FileName>
../src/bin/psql/psql.c
</FileName>
contains routines that correctly handle  the  copy protocol.
</Para>
</ListItem>

<ListItem>
<Para>
<Function>PQgetlineAsync</Function>
          Reads  a  newline-terminated  line  of  characters
          (transmitted  by the backend server) into a buffer
          without blocking.
<synopsis>
int PQgetlineAsync(PGconn *conn,
                   char *buffer,
                   int bufsize)
</synopsis>
This routine is similar to PQgetline, but it can be used by applications
that must read COPY data asynchronously, that is without blocking.
Having issued the COPY command and gotten a PGRES_COPY_OUT response, the
application should call PQconsumeInput and PQgetlineAsync until the
end-of-data signal is detected.  Unlike PQgetline, this routine takes
responsibility for detecting end-of-data.
On each call, PQgetlineAsync will return data if a complete newline-
terminated data line is available in libpq's input buffer, or if the
incoming data line is too long to fit in the buffer offered by the caller.
Otherwise, no data is returned until the rest of the line arrives.
The routine returns -1 if the end-of-copy-data marker has been recognized,
or 0 if no data is available, or a positive number giving the number of
bytes of data returned.  If -1 is returned, the caller must next call
PQendcopy, and then return to normal processing.
The data returned will not extend beyond a newline character.  If possible
a whole line will be returned at one time.  But if the buffer offered by
the caller is too small to hold a line sent by the backend, then a partial
data line will be returned.  This can be detected by testing whether the
last returned byte is '\n' or not.
The returned string is not null-terminated.  (If you want to add a
terminating null, be sure to pass a bufsize one smaller than the room