pg_send_query(): Cannot set connection to blocking mode?

Question

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

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)

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

Answer 1

This message is from PHP. Try to update PHP to the latest version - maybe this is a bug in PHP and it is already fixed.

Answer 2

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.

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

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.

Answer 3

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:

/* {{{ 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.

The whole purpose of the two PQ_SETNONBLOCKING calls 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):

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.

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

/* 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.

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.