postgresql pg_send_query()：无法将连接设置为阻塞模式？

pg_send_query(): Cannot set connection to blocking mode?

提问by Frank Farmer

I have a long-running script that seems to occasionally report the following NOTICE-level error: pg_send_query(): Cannot set connection to blocking mode

我有一个长时间运行的脚本，它似乎偶尔会报告以下通知级别的错误：pg_send_query()：无法将连接设置为阻塞模式

It seems to continue to send queries afterward, but it's unclear if it successfully sends the query that generates the error.

之后似乎继续发送查询，但不清楚它是否成功发送了产生错误的查询。

What is this a symptom of?

这是什么症状？

Edit:There are no entries in the postgres log at the time the error occurred, suggesting this is solely a connection error, not something going wrong on postgres' side (e.g. probably not the result of postgres crashing and restarting or something)

编辑：发生错误时 postgres 日志中没有条目，这表明这仅仅是连接错误，而不是 postgres 方面出现问题（例如，可能不是 postgres 崩溃和重新启动的结果或其他原因）

Edit:As far as I can tell, my INSERT statements are succeeding, one way or another, when this error is triggered.

编辑：据我所知，当触发此错误时，我的 INSERT 语句以一种或另一种方式成功。

Edit:Looks like this may have been fixed in June 2013: https://bugs.php.net/bug.php?id=65015

编辑：看起来这可能已在 2013 年 6 月修复：https: //bugs.php.net/bug.php?id=65015

回答by Henrik Opel

It is a symptom of pg_send_query()not being able to successfully switch the connection back to blocking mode. Looking at the source code in PHPs pgsql.c, you can find:

这是pg_send_query()无法成功将连接切换回阻塞模式的症状。查看PHPs pgsql.c中的源码，可以发现：

/* {{{ proto bool pg_send_query(resource connection, string query)
   Send asynchronous query */
PHP_FUNCTION(pg_send_query)
{

<... snipped function setup stuff ...>

 if (PQ_SETNONBLOCKING(pgsql, 1)) {
  php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Cannot set connection to nonblocking mode");
  RETURN_FALSE;
 }

<... snipped main function execution stuff ...>

 if (PQ_SETNONBLOCKING(pgsql, 0)) {
  php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Cannot set connection to blocking mode");
 }
 RETURN_TRUE;
}

So the error gets raised at the end of the function, after the main work is done. This fits with your observation that your INSERT statements get executed.

因此，在主要工作完成后，该错误会在函数结束时引发。这符合您对 INSERT 语句执行的观察。

The whole purpose of the two PQ_SETNONBLOCKINGcalls is to put the connection in non blocking mode to allow asynchronous execution and putting it back to the default blocking behaviour afterwards. From the documentation of PQsetnonblocking: (PQ_SETNONBLOCKING is just an alias defined for that function):

这两个PQ_SETNONBLOCKING调用的全部目的是将连接置于非阻塞模式以允许异步执行，然后将其恢复为默认阻塞行为。来自PQsetnonblocking的文档：（PQ_SETNONBLOCKING 只是为该函数定义的别名）：

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, calls to PQsendQuery, PQputline, PQputnbytes, and PQendcopy will not block but instead return an error if they need to be called again.

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

设置连接的非阻塞状态。

int PQsetnonblocking(PGconn *conn, int arg);

如果 arg 为 1，则将连接的状态设置为非阻塞，如果 arg 为 0，则将连接的状态设置为阻塞。如果正常则返回 0，如果错误则返回 -1。

在非阻塞状态下，对 PQsendQuery、PQputline、PQputnbytes 和 PQendcopy 的调用不会阻塞，而是在需要再次调用时返回错误。

注意 PQexec 不支持非阻塞模式；如果它被调用，它无论如何都会以阻塞方式运行。

Looking further at the source of PQsetnonblocking (in PostgeSQLs fe-exec.c), there are two possible reasons why the call could fail:

进一步查看 PQsetnonblocking 的来源（在 PostgeSQLs fe-exec.c 中），调用失败有两个可能的原因：

/* PQsetnonblocking:
 * sets the PGconn's database connection non-blocking if the arg is TRUE
 * or makes it non-blocking if the arg is FALSE, this will not protect
 * you from PQexec(), you'll only be safe when using the non-blocking API.
 * Needs to be called only on a connected database connection.
 */
int
PQsetnonblocking(PGconn *conn, int arg)
{
 bool  barg;

 if (!conn || conn->status == CONNECTION_BAD)
  return -1;

 barg = (arg ? TRUE : FALSE);

 /* early out if the socket is already in the state requested */
 if (barg == conn->nonblocking)
  return 0;

 /*
  * to guarantee constancy for flushing/query/result-polling behavior we
  * need to flush the send queue at this point in order to guarantee proper
  * behavior. this is ok because either they are making a transition _from_
  * or _to_ blocking mode, either way we can block them.
  */
 /* if we are going from blocking to non-blocking flush here */
 if (pqFlush(conn))
  return -1;

 conn->nonblocking = barg;

 return 0;
}

So either the connection got lost somehow, or pqFlush did not finish successfully, indicating leftover stuff in the connection output buffer.

所以要么连接不知何故丢失，要么 pqFlush 没有成功完成，表明连接输出缓冲区中有剩余的东西。

The first case would be harmless, as your script would certainly notice the lost connection for later calls and react to that (or fail more noticeable).

第一种情况是无害的，因为您的脚本肯定会注意到以后调用丢失的连接并对此做出反应（或失败更明显）。

This leaves the second case, which would mean you have a connection in the non default, non blocking state. I do not know if this could affect later calls that would reuse this connection. If you want to play it safe, you'd close the connection in this case and use a new/other one.

这就留下了第二种情况，这意味着您的连接处于非默认、非阻塞状态。我不知道这是否会影响以后会重用此连接的调用。如果您想安全一点，在这种情况下您可以关闭连接并使用新的/其他连接。

回答by Bill Karwin

It sounds like you're trying to use the pg_send_query()function for sending asynchronous queries to PostgreSQL. The purpose of this function is to allow your PHP script to continue executing other code while waiting for PostgreSQL to execute your query and make a result ready.

听起来您正在尝试使用该pg_send_query()函数向 PostgreSQL 发送异步查询。此函数的目的是让您的 PHP 脚本在等待 PostgreSQL 执行您的查询并准备好结果的同时继续执行其他代码。

The example given in the docsfor pg_send_query()suggest that you shouldn't send a query if PostgreSQL is already chewing on another query:

在给出的示例文档的pg_send_query()建议，如果是PostgreSQL已经在另一个查询嚼不应该发出一个查询：

if (!pg_connection_busy($dbconn)) {
  pg_send_query($dbconn, "select * from authors; select count(*) from authors;");
}

Is there a reason you're using pg_send_query()instead of pg_query()? If you can allow your script to block waiting for query execution, I'm guessing (admittedly without having tried it) that you won't see these errors.

你有理由使用pg_send_query()而不是pg_query()吗？如果您可以允许您的脚本阻止等待查询执行，我猜（诚然没有尝试过）您不会看到这些错误。

回答by Vektah

I've recently had the same problem, and with the help from Henrik Opels answer realized that PHP does not actually wait for the buffer to flush before setting the connection back to blocking mode.

我最近遇到了同样的问题，在 Henrik Opels 的帮助下，我意识到 PHP 在将连接设置回阻塞模式之前实际上并不等待缓冲区刷新。

The 'cannot set connection to blocking mode' is trivially repeatable with large enough queries to fill the send buffer (padding with spaces at the end is enough). With smaller queries I imagine it is dependent on load, and rather intermittent.

“无法将连接设置为阻塞模式”可以通过足够大的查询来填充发送缓冲区（末尾填充空格就足够了）。对于较小的查询，我想它取决于负载，而且是间歇性的。

if you do actually need asynchronous mode then try the patch at https://bugs.php.net/bug.php?id=65015

如果您确实需要异步模式，请尝试https://bugs.php.net/bug.php?id=65015 上的补丁

回答by Zenithies

I encountered same error message with PHP 5.6.9

我在 PHP 5.6.9 中遇到了相同的错误消息

It occurs when persistent connection made by pg_pconnect()is lost and pgsql.auto_reset_persistentis set to Off.

当pg_pconnect()建立的持久连接丢失并且 pgsql.auto_reset_persistent设置为Off时，就会发生这种情况。

Connection might get lost when:

在以下情况下连接可能会丢失：

1. PHP Session expires
2. Connection to DB timeouts
3. Webserver / DB server is restarted

1. PHP 会话过期
2. 连接到数据库超时
3. 网络服务器/数据库服务器重新启动

You can check PHP.ini for pgsql.auto_reset_persistentand set it to On.

您可以检查 PHP.ini 中的pgsql.auto_reset_persistent并将其设置为On。

With pgsql.auto_reset_persistentenabled, each time pg_pconnect()is being called, connection link is checked, if it is still valid. This requires a little overhead, but fixies error message when conncetion is lost.

随着pgsql.auto_reset_persistent启用，每次pg_pconnect（）被调用，连接环节进行检查，如果它仍然是有效的。这需要一点开销，但会在连接丢失时修复错误消息。

回答by nfrozi

I got that error too. I solve my problem by restarting the web server (Apache).

我也遇到了那个错误。我通过重新启动 Web 服务器 (Apache) 解决了我的问题。

回答by J. Costa

This could occur if you are using threadsand the connection is being reused. If is this the case you could use the PGSQL_CONNECT_FORCE_NEWlike this:

如果您正在使用线程并且正在重用连接，则可能会发生这种情况。如果是这种情况，您可以PGSQL_CONNECT_FORCE_NEW像这样使用：

pg_connect("...", PGSQL_CONNECT_FORCE_NEW)

This will force a new database connection resource but be advised: you could run out of connections clients, so be carefully using this inside threads so don't forget to use pg_close().

这将强制使用新的数据库连接资源，但请注意：您可能会用完连接 clients，因此请小心在线程内部使用它，因此不要忘记使用pg_close().