pg_send_query(): Cannot set connection to blocking mode

[Mikko Rantalainen]

[Impact]

pg_send_query() fails because of an implementation error if connection is done to remote host and query string is long OR transaction is very long. As a result, pg_send_query() is too unstable to be used at all if the connection is made to any other host but 'localhost'.

The problem is caused by the implementation not following the PostgreSQL documentation at http://www.postgresql.org/docs/9.1/static/libpq-async.html#LIBPQ-PQFLUSH

"PQflush: [...] After sending any command or data on a nonblocking connection, call PQflush. [...]"

Related PHP Bug: https://bugs.php.net/bug.php?id=65015
Patch: https://bugs.php.net/patch-display.php?bug_id=65015&patch=pg_send_query_flush_buffer.patch&revision=latest

[Test Case]

Test script (from PHP bug):
---
$len = 100000; // This may need to be increased, depending on db server.
$sql = "select 1" . str_repeat(' ', $len - 8);
$con = pg_connect('host=db-host.example.com dbname=postgres user=postgres password=password');
pg_send_query($con, $sql);
pg_get_result($con);
---

Note that the test script only fails if host is remote (connected using TCP/IP).

[Regression Potential]

Calling PQflush() may cause some additional overhead for the cases where flushing does not need to be done (the cases that happen to work as is). The patch only touches functions pg_send_query() and pg_send_query_params(), so any possible regression should affect only those methods. Both these methods suffer from the same issue and are already too broken to be used in production environment, unless the connection is made to 'localhost'.

The documentation of PQflush() says "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)." Calling PQflush() should be okay for blocking connections, too. In addition, the referenced patch does not even touch that code.

Source: http://www.postgresql.org/docs/9.1/static/libpq-async.html
Source: http://<email address hidden>

[Other Info]

ProblemType: Bug
DistroRelease: Ubuntu 12.04
Package: php5-pgsql 5.3.10-1ubuntu3.7
ProcVersionSignature: Ubuntu 3.8.0-27.40~precise3-generic 3.8.13.4
Uname: Linux 3.8.0-27-generic x86_64
ApportVersion: 2.0.1-0ubuntu17.4
Architecture: amd64
Date: Wed Sep 4 15:22:25 2013
InstallationMedia: Ubuntu 12.04.1 LTS "Precise Pangolin" - Release amd64 (20120823.1)
MarkForUpload: True
SourcePackage: php5
UpgradeStatus: No upgrade log present (probably fresh install)

[Robie Basak (racb)]

Thank you for taking the time to report this bug and helping to make Ubuntu better.

It looks like the current development version already has this issue fixed, so I'm marking this bug as Fix Released. I verified this by manually checking the ext/pgsql/pgsql.c file in the source against the patch provided.

It looks like upstream have not backported this fix into their 5.3 branch. But the patch seems trivial enough that we could backport it easily enough in order to fix 12.04, if we were to choose to do this.

So if you need a fix for an existing stable release, please comment with a justification against https://wiki.ubuntu.com/StableReleaseUpdates#When and complete steps 1 through 4 in https://wiki.ubuntu.com/StableReleaseUpdates#Procedure - and go ahead with all the steps if you can. Note that that SRU team would need to make a final decision.

I have one concern about any possible backport. The patch does not test the connection to make sure that it is non-blocking. Will calling PQflush on a blocking connection cause any issues here? Or is the connection only ever going to be non-blocking due to the way the existing code works?

[Mikko Rantalainen (mira)]

The justification for this fix is as follows:

- The patch is obviously safe (it does not touch code outside the functions mentioned in the description and those functions are already broken)

- The patch affects PostgreSQL library of PHP, no critical infrastructure or kernel

- The bug may cause loss of user data due to losing database connection. Actual data loss depends on PHP application code running on top of the library and may or may not happen in practice.