|  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
Avg. Score:5.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: ben dot pineau at gmail dot com Assigned: yohgaki (profile)
Status: Closed Package: Documentation problem
PHP Version: Irrelevant OS: all
Private report: No CVE-ID: None
 [2012-03-03 13:42 UTC] ben dot pineau at gmail dot com
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:


  static void php_pgsql_get_result_info(INTERNAL_FUNCTION_PARAMETERS, int 
        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:

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);");

Expected result:

Actual result:


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


AllCommentsChangesGit/SVN commitsRelated reports
 [2012-03-08 08:31 UTC]
-Status: Open +Status: Wont fix
 [2012-03-08 08:31 UTC]
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]
-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]
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)

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

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

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
 [2013-06-28 08:45 UTC]
-Package: PostgreSQL related +Package: Documentation problem
 [2013-06-28 08:45 UTC]
Ok, them we should document this.
 [2014-01-29 05:59 UTC]
Automatic comment from SVN on behalf of yohgaki
Log: Fixed Bug #61266pg_affected_rows inconsistent behavior (depends on PostgreSQL server version)
 [2014-01-29 06:00 UTC]
-Status: Re-Opened +Status: Closed -Assigned To: +Assigned To: yohgaki
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Sun Nov 19 01:31:42 2017 UTC