Edit report at https://bugs.php.net/bug.php?id=61266&edit=1
ID: 61266
Comment by: ben dot pineau at gmail dot com
Reported by: ben dot pineau at gmail dot com
Summary: pg_affected_rows inconsistent behavior (depends on
PostgreSQL server version)
Status: Re-Opened
Type: Bug
Package: PostgreSQL related
Operating System: all
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
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 b...@php.net).
Previous Comments:
------------------------------------------------------------------------
[2013-06-26 22:06:48] yohg...@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-03-26 17:17:46] b...@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.
------------------------------------------------------------------------
[2012-03-08 08:31:22] cataphr...@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.
------------------------------------------------------------------------
[2012-03-03 13:42:36] 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)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=61266&edit=1
