Postgresql 中文操作指南

34.4. Asynchronous Command Processing #

PQexec 函数足以在正常的同步应用中提交命令。但是，它有几个缺点，对某些用户来说可能会很重要：

The PQexec function is adequate for submitting commands in normal, synchronous applications. It has a few deficiencies, however, that can be of importance to some users:

不喜欢这些限制的应用程序可以改为使用 PQexec 中内置的基本函数： PQsendQuery 和 PQgetResult 。还有 PQsendQueryParams 、 PQsendPrepare 、 PQsendQueryPrepared 、 PQsendDescribePrepared 和 PQsendDescribePortal ，它们可以与 PQgetResult 一起使用，分别复制 PQexecParams 、 PQprepare 、 PQexecPrepared 、 PQdescribePrepared 和 PQdescribePortal 的功能。

Applications that do not like these limitations can instead use the underlying functions that PQexec is built from: PQsendQuery and PQgetResult. There are also PQsendQueryParams, PQsendPrepare, PQsendQueryPrepared, PQsendDescribePrepared, and PQsendDescribePortal, which can be used with PQgetResult to duplicate the functionality of PQexecParams, PQprepare, PQexecPrepared, PQdescribePrepared, and PQdescribePortal respectively.

  • PQsendQuery #

    • Submits a command to the server without waiting for the result(s). 1 is returned if the command was successfully dispatched and 0 if not (in which case, use PQerrorMessage to get more information about the failure).

int PQsendQuery(PGconn *conn, const char *command);

  • After successfully calling PQsendQuery, call PQgetResult one or more times to obtain the results. PQsendQuery cannot be called again (on the same connection) until PQgetResult has returned a null pointer, indicating that the command is done.

  • In pipeline mode, this function is disallowed.

    • PQsendQueryParams #

  • Submits a command and separate parameters to the server without waiting for the result(s).

int PQsendQueryParams(PGconn *conn,
                      const char *command,
                      int nParams,
                      const Oid *paramTypes,
                      const char * const *paramValues,
                      const int *paramLengths,
                      const int *paramFormats,
                      int resultFormat);

  • This is equivalent to PQsendQuery except that query parameters can be specified separately from the query string. The function’s parameters are handled identically to PQexecParams. Like PQexecParams, it allows only one command in the query string.

    • PQsendPrepare #

  • Sends a request to create a prepared statement with the given parameters, without waiting for completion.

int PQsendPrepare(PGconn *conn,
                  const char *stmtName,
                  const char *query,
                  int nParams,
                  const Oid *paramTypes);

  • This is an asynchronous version of PQprepare: it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call PQgetResult to determine whether the server successfully created the prepared statement. The function’s parameters are handled identically to PQprepare.

    • PQsendQueryPrepared #

  • Sends a request to execute a prepared statement with given parameters, without waiting for the result(s).

int PQsendQueryPrepared(PGconn *conn,
                        const char *stmtName,
                        int nParams,
                        const char * const *paramValues,
                        const int *paramLengths,
                        const int *paramFormats,
                        int resultFormat);

  • This is similar to PQsendQueryParams, but the command to be executed is specified by naming a previously-prepared statement, instead of giving a query string. The function’s parameters are handled identically to PQexecPrepared.

    • PQsendDescribePrepared #

  • Submits a request to obtain information about the specified prepared statement, without waiting for completion.

int PQsendDescribePrepared(PGconn *conn, const char *stmtName);

  • This is an asynchronous version of PQdescribePrepared: it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call PQgetResult to obtain the results. The function’s parameters are handled identically to PQdescribePrepared.

    • PQsendDescribePortal #

  • Submits a request to obtain information about the specified portal, without waiting for completion.

int PQsendDescribePortal(PGconn *conn, const char *portalName);
PGresult *PQgetResult(PGconn *conn);

  • PQgetResult must be called repeatedly until it returns a null pointer, indicating that the command is done. (If called when no command is active, PQgetResult will just return a null pointer 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 command is active and the necessary response data has not yet been read by PQconsumeInput .

  • In pipeline mode, PQgetResult will return normally unless an error occurs; for any subsequent query sent after the one that caused the error until (and excluding) the next synchronization point, a special result of type PGRES_PIPELINE_ABORTED will be returned, and a null pointer will be returned after it. When the pipeline synchronization point is reached, a result of type PGRES_PIPELINE_SYNC will be returned. The result of the next query after the synchronization point follows immediately (that is, no null pointer is returned after the synchronization point.)

Note

即使 PQresultStatus 指示致命错误，也应调用 PQgetResult ，直到它返回空指针，以允许 libpq 完全处理错误信息。

Even when PQresultStatus indicates a fatal error, PQgetResult should be called until it returns a null pointer, to allow libpq to process the error information completely.

使用 PQsendQuery 和 PQgetResult 解决了一个 PQexec 问题：如果一个命令字符串包含多个 SQL 命令，可以分别获取这些命令的结果。（顺便说一下，这允许进行一种简单的重叠处理：客户端可以处理一个命令的结果，而服务器仍在处理同一个命令字符串中的后续查询。）

Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a command 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 client can be handling the results of one command while the server is still working on later queries in the same command string.)

PQsendQuery 和 PQgetResult 提供的另一个常用的优势是，可逐行检索大量的查询结果。 Section 34.6 中对这部分内容进行了讨论。

Another frequently-desired feature that can be obtained with PQsendQuery and PQgetResult is retrieving large query results a row at a time. This is discussed in Section 34.6.

单单调用 PQgetResult 仍然会使客户端在服务器完成下一个 SQL 命令之前始终处于阻塞状态。这可以通过正确使用另外两个函数来避免：

By itself, calling PQgetResult will still cause the client to block until the server completes the next SQL command. This can be avoided by proper use of two more functions:

  • PQconsumeInput #

    • If input is available from the server, consume it.

int PQconsumeInput(PGconn *conn);

  • PQconsumeInput normally returns 1 indicating “no error”, but returns 0 if there was some kind of trouble (in which case PQerrorMessage can be consulted). Note that the result does not say whether any input data was actually collected. After calling PQconsumeInput , the application can check PQisBusy and/or PQnotifies to see if their state has changed.

  • PQconsumeInput can be called even if the application is not prepared to deal with a result or notification just yet. The function will read available data and save it in a buffer, thereby causing a select() 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.

    • PQisBusy #

  • Returns 1 if a command is busy, that is, PQgetResult would block waiting for input. A 0 return indicates that PQgetResult can be called with assurance of not blocking.

int PQisBusy(PGconn *conn);

  • PQisBusy will not itself attempt to read data from the server; therefore PQconsumeInput must be invoked first, or the busy state will never end.

使用这些函数的典型应用程序将有一个主循环，它将使用 select() 或 poll() 等待必须响应的所有条件。其中一个条件是来自服务器的可用输入，就 select() 而言，这意味着 PQsocket 标识的文件描述符上的可读数据。当主循环检测到有输入就绪时，应调用 PQconsumeInput 读取输入。然后，可以调用 PQisBusy ，然后在 PQisBusy 返回 false (0) 的情况下调用 PQgetResult 。还可以调用 PQnotifies 来检测 NOTIFY 消息（请参阅 Section 34.9 ）。

A typical application using these functions will have a main loop that uses select() or poll() to wait for all the conditions that it must respond to. One of the conditions will be input available from the server, which in terms of select() means 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 (0). It can also call PQnotifies to detect NOTIFY messages (see Section 34.9).

使用 PQsendQuery / PQgetResult 的客户端也可以尝试取消仍在由服务器处理的命令；请参阅 Section 34.7 。但是，无论 PQcancel 的返回值如何，应用程序都必须使用 PQgetResult 继续进行常规结果读取序列。取消成功只会导致命令比预期更早终止。

A client that uses PQsendQuery/PQgetResult can also attempt to cancel a command that is still being processed by the server; see Section 34.7. But regardless of the return value of PQcancel, the application must continue with the normal result-reading sequence using PQgetResult. A successful cancellation will simply cause the command to terminate sooner than it would have otherwise.

通过使用上面描述的函数，可以在等待来自数据库服务器的输入时避免阻塞。然而，应用程序仍然可能会阻塞，等待向服务器发送输出。这种情况相对罕见，但如果发送非常长的 SQL 命令或数据值，可能会发生这种情况。（但是，如果应用程序通过 COPY IN 发送数据，则可能性更大。）为了防止出现这种情况并实现完全非阻塞的数据库操作，可以使用以下附加函数。

By using the functions described above, it is possible to avoid blocking while waiting for input from the database server. However, it is still possible that the application will block waiting to send output to the server. This is relatively uncommon but can happen if very long SQL commands or data values are sent. (It is much more probable if the application sends data via COPY IN, however.) To prevent this possibility and achieve completely nonblocking database operation, the following additional functions can be used.

  • PQsetnonblocking #

    • Sets the nonblocking status of the connection.

int PQsetnonblocking(PGconn *conn, int arg);

  • Sets the state of the connection to nonblocking if arg is 1, or blocking if arg is 0. Returns 0 if OK, -1 if error.

  • In the nonblocking state, successful calls to PQsendQuery, PQputline, PQputnbytes, PQputCopyData, and PQendcopy will not block; their changes are stored in the local output buffer until they are flushed. Unsuccessful calls will return an error and must be retried.

  • Note that PQexec does not honor nonblocking mode; if it is called, it will act in blocking fashion anyway.

    • PQisnonblocking #

  • Returns the blocking status of the database connection.

int PQisnonblocking(const PGconn *conn);

  • Returns 1 if the connection is set to nonblocking mode and 0 if blocking.

    • PQflush #

  • Attempts to flush any queued output data to the server. Returns 0 if successful (or if the send queue is empty), -1 if it failed for some reason, or 1 if it was unable to send all the data in the send queue yet (this case can only occur if the connection is nonblocking).

int PQflush(PGconn *conn);

在非阻塞连接上发送任何命令或数据后，调用 PQflush 。如果它返回 1，请等待套接字变为就绪以进行读取或写入。如果变为就绪以进行写入，请再次调用 PQflush 。如果变为就绪以进行读取，请依次调用 PQconsumeInput ，然后再次调用 PQflush 。重复此过程，直到 PQflush 返回 0。（必须使用 PQconsumeInput 检查是否可以进行读取并清空输入，因为服务器可能会阻塞，尝试向我们发送数据，例如 NOTICE 消息，在我们读取它的数据之前，它不会读取我们的数据。）一旦 PQflush 返回 0，请等待套接字变为就绪以进行读取，然后按照上述说明读取响应。

After sending any command or data on a nonblocking connection, call PQflush. If it returns 1, wait for the socket to become read- or write-ready. If it becomes write-ready, call PQflush again. If it becomes read-ready, call PQconsumeInput , then call PQflush again. Repeat until PQflush returns 0. (It is necessary to check for read-ready and drain the input with PQconsumeInput , because the server can block trying to send us data, e.g., NOTICE messages, and won’t read our data until we read its.) Once PQflush returns 0, wait for the socket to be read-ready and then read the response as described above.