php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #61266 pg_affected_rows inconsistent behavior (depends on PostgreSQL server version)
Submitted: 2012-03-03 13:42 UTC Modified: 2014-01-29 06:00 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: ben dot pineau at gmail dot com Assigned: yohgaki
Status: Closed Package: Documentation problem
PHP Version: Irrelevant OS: all
Private report: No CVE-ID:
 [2012-03-03 13:42 UTC] ben dot pineau at gmail dot com
Description:
------------
According to the manual, pg_affected_rows should returns "the number of tuples 
(instances/records/rows) affected by INSERT, UPDATE, and DELETE queries.". The 
manual details : "The number of rows affected by the query. If no tuple is 
affected, it will return 0.".

PHP pg_affected_rows uses libpq's PQcmdTuples() to implement this:

  PHP_FUNCTION(pg_affected_rows)
  {
     
php_pgsql_get_result_info(INTERNAL_FUNCTION_PARAM_PASSTHRU,PHP_PG_CMD_TUPLES);
  }

  static void php_pgsql_get_result_info(INTERNAL_FUNCTION_PARAMETERS, int 
entry_type)
  {
  [...]
        case PHP_PG_CMD_TUPLES:
            Z_LVAL_P(return_value) = atoi(PQcmdTuples(pgsql_result));

But server's answers to PQcmdTuples() commands changed since PostgreSQL 9.0. 
When executed after a SELECT, PostgreSQL < 9.0 returned 0 (as in "0 rows were 
affected"); starting with PostgreSQL 9.0, the server returns the number of 
SELECTed rows.

See how the PQcmdTuples documentation was updated after pg 9:
http://www.postgresql.org/docs/8.4/interactive/libpq-exec.html#LIBPQ-EXEC-
SELECT-INFO
http://www.postgresql.org/docs/9.1/interactive/libpq-exec.html#LIBPQ-EXEC-
SELECT-INFO

PostgreSQL C API doesn't actually offers a "tell me how many rows were 
written/modified" function.  But we can restore the previous pg_affected_rows 
behavior, and enjoy consistent results no matter which server version we run 
against, by unconditionally returning 0 after a SELECT.

This is what the attached patch does, identifying the SELECT with 
PQresultStatus() value (which returns PGRES_COMMAND_OK after a successful DML, 
as opposed to PGRES_TUPLES_OK after a SELECT, etc).

If you ask so, I can also provide an alternative patch (which tests the string 
returned by PQcmdStatus(), a bit ugly imo) and/or an unit test script for PHP's 
test framework.


Test script:
---------------
// Bug on a PostgreSQL >= 9.0 server, ok on older versions.
$dbh = pg_pconnect("dbname=postgres host=localhost user=postgres port=5432");
$q = pg_query($dbh, "SELECT * from generate_series(1, 42);");
var_dump(pg_affected_rows($q));

Expected result:
----------------
int(0)

Actual result:
--------------
int(42)

Patches

unittest_pg_affected_rows_returns_0_after_select (last revision 2012-03-03 13:45 UTC) by ben dot pineau at gmail dot com)
pg_affected_rows_returns_0_after_select.patch (last revision 2012-03-03 13:43 UTC) by ben dot pineau at gmail dot com)

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-03-08 08:31 UTC] cataphract@php.net
-Status: Open +Status: Wont fix
 [2012-03-08 08:31 UTC] cataphract@php.net
I don't think PHP should apply compatibility shims on top of libpq, especially when the new functionality has low impact and actually adds functionality. The case for your PDO bug report, however, is much more compelling.
 [2013-03-26 17:17 UTC] bs@php.net
-Summary: pg_affected_rows inconsistent behavior (depends on PostgreSQL server version) +Summary: bs -Status: Wont fix +Status: Re-Opened
 [2013-03-26 17:17 UTC] bs@php.net
It's actually possible that a writing command produces a result set (which can 
differ in the number of affected / returned rows):
Simple example would be:
INSERT INTO foo (bar, baz)
VALUES (DEFAULT, 'bang')
RETURNING (bar);

Therefore i don't see this has having only a low impact.
 [2013-03-26 17:19 UTC] bs@php.net
-Summary: bs +Summary: pg_affected_rows inconsistent behavior (depends on PostgreSQL server version)
 [2013-06-26 22:06 UTC] yohgaki@php.net
I guess no one was used pg_affected_rows() for SELECT query before PostgreSQL 
9.0.

Thank you for the patch, but I think it's nice to keep new behavior and document 
it.

Any comments?
 [2013-06-27 19:56 UTC] ben dot pineau at gmail dot com
We had code using pg_affected_rows() to detect if the previous query was a simple SELECT or a DML.
Yes, that's dumb and our code was fixed since then ;) I understand there may not be that many other persons affected.

Right, I also think the documentation deserves an update (including the case reported by bs@php.net).
 [2013-06-28 08:45 UTC] yohgaki@php.net
-Package: PostgreSQL related +Package: Documentation problem
 [2013-06-28 08:45 UTC] yohgaki@php.net
Ok, them we should document this.
 [2014-01-29 05:59 UTC] yohgaki@php.net
Automatic comment from SVN on behalf of yohgaki
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=332751
Log: Fixed Bug #61266pg_affected_rows inconsistent behavior (depends on PostgreSQL server version)
 [2014-01-29 06:00 UTC] yohgaki@php.net
-Status: Re-Opened +Status: Closed -Assigned To: +Assigned To: yohgaki
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Sat Apr 19 17:01:54 2014 UTC