[PHP-BUG] Bug #61266 [NEW]: pg_affected_rows inconsistent behavior (depends on PostgreSQL server version)
From:
Operating system: all
PHP version: Irrelevant
Package: PostgreSQL related
Bug Type: Bug
Bug description:pg_affected_rows inconsistent behavior (depends on PostgreSQL
server version)
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 bug report at https://bugs.php.net/bug.php?id=61266&edit=1
--
Try a snapshot (PHP 5.4):
https://bugs.php.net/fix.php?id=61266&r=trysnapshot54
Try a snapshot (PHP 5.3):
https://bugs.php.net/fix.php?id=61266&r=trysnapshot53
Try a snapshot (trunk):
https://bugs.php.net/fix.php?id=61266&r=trysnapshottrunk
Fixed in SVN:
https://bugs.php.net/fix.php?id=61266&r=fixed
Fixed in SVN and need be documented:
https://bugs.php.net/fix.php?id=61266&r=needdocs
Fixed in release:
https://bugs.php.net/fix.php?id=61266&r=alreadyfixed
Need backtrace:
https://bugs.php.net/fix.php?id=61266&r=needtrace
Need Reproduce Script:
https://bugs.php.net/fix.php?id=61266&r=needscript
Try newer version:
https://bugs.php.net/fix.php?id=61266&r=oldversion
Not developer issue:
https://bugs.php.net/fix.php?id=61266&r=support
Expected behavior:
https://bugs.php.net/fix.php?id=61266&r=notwrong
Not enough info:
https://bugs.php.net/fix.php?id=61266&r=notenoughinfo
Submitted twice:
https://bugs.php.net/fix.php?id=61266&r=submittedtwice
register_globals:
https://bugs.php.net/fix.php?id=61266&r=globals
PHP 4 support discontinued:
https://bugs.php.net/fix.php?id=61266&r=php4
Daylight Savings:https://bugs.php.net/fix.php?id=61266&r=dst
IIS Stability:
https://bugs.php.net/fix.php?id=61266&r=isapi
Install GNU Sed:
https://bugs.php.net/fix.php?id=61266&r=gnused
Floating point limitations:
https://bugs.php.net/fix.php?id=61266&r=float
No Zend Extensions:
https://bugs.php.net/fix.php?id=61266&r=nozend
MySQL Configuration Error:
https://bugs.php.net/fix.php?id=61266&r=mysqlcfg
[PHP-BUG] Bug #61267 [NEW]: pdo_pgsql's PDO::exec() returns the number of SELECTed rows on postgresql >= 9.
From:
Operating system: all
PHP version: Irrelevant
Package: PDO related
Bug Type: Bug
Bug description:pdo_pgsql's PDO::exec() returns the number of SELECTed rows on
postgresql >= 9.
Description:
After executing a SELECT statement, PDO::exec() -using the pdo_pgsql driver
and
running against a PostgreSQL server older than 9.0- will always returns 0,
as
does the pdo_mysql and pdo_sqlite drivers. ie. this is the exepected
behaviour
(though to be precise, the PDO::exec() documentation stipulate :
"PDO::exec()
does not return results from a SELECT statement.").
But when executed against a PostgreSQL >= 9.0 server, this very same php
client
and select statement will return the number of selected rows. This is due
to a
server side change in how PostgreSQL servers answers to libpqs's
PQcmdTuples()
commands (PQcmdTuples() being used as the PDO::exec() return value, via
pdo_pgsql pgsql_handle_doer() function).
This server-side change is visible by comparing this command's
documentation for
different PostgreSQL versions :
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
See also the bug in pg_affected_rows https://bugs.php.net/bug.php?id=61266
I
just reported.
The attached patch does check whether the PDO::exec() param was a
(successful)
DML statement before using PQcmdTuples() to return the number of affected
rows.
Also attached, a test script for PHP's unit test infrastructure.
Test script:
---
// Bugs on PostgreSQL >= 9.0 server (but ok on previous versions)
$dbh = new PDO("pgsql:dbname=postgres ;host=localhost", 'postgres');
var_dump($dbh->exec("SELECT * from generate_series(1, 42);"));
Expected result:
int(0)
Actual result:
--
int(42)
--
Edit bug report at https://bugs.php.net/bug.php?id=61267&edit=1
--
Try a snapshot (PHP 5.4):
https://bugs.php.net/fix.php?id=61267&r=trysnapshot54
Try a snapshot (PHP 5.3):
https://bugs.php.net/fix.php?id=61267&r=trysnapshot53
Try a snapshot (trunk):
https://bugs.php.net/fix.php?id=61267&r=trysnapshottrunk
Fixed in SVN:
https://bugs.php.net/fix.php?id=61267&r=fixed
Fixed in SVN and need be documented:
https://bugs.php.net/fix.php?id=61267&r=needdocs
Fixed in release:
https://bugs.php.net/fix.php?id=61267&r=alreadyfixed
Need backtrace:
https://bugs.php.net/fix.php?id=61267&r=needtrace
Need Reproduce Script:
https://bugs.php.net/fix.php?id=61267&r=needscript
Try newer version:
https://bugs.php.net/fix.php?id=61267&r=oldversion
Not developer issue:
https://bugs.php.net/fix.php?id=61267&r=support
Expected behavior:
https://bugs.php.net/fix.php?id=61267&r=notwrong
Not enough info:
https://bugs.php.net/fix.php?id=61267&r=notenoughinfo
Submitted twice:
https://bugs.php.net/fix.php?id=61267&r=submittedtwice
register_globals:
https://bugs.php.net/fix.php?id=61267&r=globals
PHP 4 support discontinued:
https://bugs.php.net/fix.php?id=61267&r=php4
Daylight Savings:https://bugs.php.net/fix.php?id=61267&r=dst
IIS Stability:
https://bugs.php.net/fix.php?id=61267&r=isapi
Install GNU Sed:
https://bugs.php.net/fix.php?id=61267&r=gnused
Floating point limitations:
https://bugs.php.net/fix.php?id=61267&r=float
No Zend Extensions:
https://bugs.php.net/fix.php?id=61267&r=nozend
MySQL Configuration Error:
https://bugs.php.net/fix.php?id=61267&r=mysqlcfg
Bug #61266 [Com]: pg_affected_rows inconsistent behavior (depends on PostgreSQL server version)
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
#49985 [NEW]: pdo_pgsql prepare() re-use previous aborted transaction
From: ben dot pineau at gmail dot com
Operating system: Linux
PHP version: 5.2.11
PHP Bug Type: PDO related
Bug description: pdo_pgsql prepare() re-use previous aborted transaction
Description:
When prepar()ing a statement in a separate function, pdo_pgsql behaves as
if we hadn't rollbacked and started a new transaction.
But no such problem when either :
- we use another pdo driver (tested with sqlite)
- prepare() call is inlined in the same code block as beginTransaction
(rather than in a distinct function)
- the function contains a straigth "query()" instead of prepare() +
execute()
Reproduced with both php-5.2.6 and php-5.2.11, PostgreSQL 8.3.8.
Reproduce code:
---
/*
* CREATE TABLE test (a SERIAL PRIMARY KEY);
* INSERT INTO test VALUES (1);
*/
$cnx = new PDO('pgsql:dbname=testbase;host=localhost', 'postgres','');
$cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO test (a) VALUES (1)";
function prepare_and_exec_query(&$pdo, $sql)
{
$stmt = $pdo->prepare($sql);
$stmt->execute();
}
for ($i = 0; $i < 3; $i++) {
try {
$cnx->beginTransaction();
prepare_and_exec_query($cnx, $sql);
$cnx->commit();
} catch (Exception $e) {
echo "Error: " . $e->getMessage() . "\n";
if (true === $cnx->rollback()) echo "rollbacked ok\n";
}
}
Expected result:
Error: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value
violates unique constraint "test_pkey"
rollbacked ok
Error: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value
violates unique constraint "test_pkey"
rollbacked ok
Error: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value
violates unique constraint "test_pkey"
rollbacked ok
Actual result:
--
Error: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value
violates unique constraint "test_pkey"
rollbacked ok
Error: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current
transaction is aborted, commands ignored until end of transaction block
rollbacked ok
Error: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current
transaction is aborted, commands ignored until end of transaction block
rollbacked ok
--
Edit bug report at http://bugs.php.net/?id=49985&edit=1
--
Try a snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=49985&r=trysnapshot52
Try a snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=49985&r=trysnapshot53
Try a snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=49985&r=trysnapshot60
Fixed in SVN:
http://bugs.php.net/fix.php?id=49985&r=fixed
Fixed in SVN and need be documented:
http://bugs.php.net/fix.php?id=49985&r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=49985&r=alreadyfixed
Need backtrace:
http://bugs.php.net/fix.php?id=49985&r=needtrace
Need Reproduce Script:
http://bugs.php.net/fix.php?id=49985&r=needscript
Try newer version:
http://bugs.php.net/fix.php?id=49985&r=oldversion
Not developer issue:
http://bugs.php.net/fix.php?id=49985&r=support
Expected behavior:
http://bugs.php.net/fix.php?id=49985&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=49985&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=49985&r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=49985&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=49985&r=php4
Daylight Savings:http://bugs.php.net/fix.php?id=49985&r=dst
IIS Stability:
http://bugs.php.net/fix.php?id=49985&r=isapi
Install GNU Sed:
http://bugs.php.net/fix.php?id=49985&r=gnused
Floating point limitations:
http://bugs.php.net/fix.php?id=49985&r=float
No Zend Extensions:
http://bugs.php.net/fix.php?id=49985&r=nozend
MySQL Configuration Error:
http://bugs.php.net/fix.php?id=49985&r=mysqlcfg
#49985 [Com]: pdo_pgsql prepare() re-use previous aborted transaction
ID: 49985
Comment by: ben dot pineau at gmail dot com
Reported By: ben dot pineau at gmail dot com
Status: Open
Bug Type: PDO related
Operating System: Linux
PHP Version: 5.2.11
New Comment:
Look at the PostgreSQL logs above (running the provided test case
with log_statement='all' and log_min_messages='INFO'), keeping
in mind that:
- PostgreSQL automatically abort transactions when something fails.
- PostgreSQL does maintains prepared statements outside transaction
context. Prepared statements survive a rollback/abort, and last
for the duration of the current database session.
- It seems that when executed in another function/context than
PDO::beginTransaction, PDO tries to DEALLOCATE right after the
first transaction failure (vs. after the next BEGIN statement
when in same context).
Therefore we have the following flow:
BEGIN;
-- PDO successfully prepare the statement
PREPARE prep_statement_1 AS ...;
-- We execute a bogus statement that cause pg to automatically
-- abort the current transaction. PDO raise a proper exception
EXECUTE prep_statement_1;
-- This fails because we're in aborted transaction;
-- The prepared statement is thus left allocated.
DEALLOCATE prep_statement_1;
ROLLBACK;
-- And then we loop with
BEGIN;
-- This prep statement already exists (PDO failed to clean it)
-- so this fails and pg automatically abort this new transaction.
PREPARE prep_statement_1 AS ...;
-- Given the above failure, PDO tries again to clean up the
-- prepared stmt which will fail again.
-- Hence PDO raising a "current transaction is aborted" exception.
DEALLOCATE prep_statement_1;
ROLLBACK;
Surprisingly, when we call prepare() inline like this:
try {
$cnx->beginTransaction();
$stmt = $cnx->prepare($sql);
$stmt->execute();
instead of that :
function qexec(&$pdo, $sql) {
$stmt = $pdo->prepare($sql);
$stmt->execute();
}
try {
$cnx->beginTransaction();
qexec($cnx, $sql);
we do get a different (and preferable) behaviour:
BEGIN -> PREPARE -> EXECUTE -> ROLLBACK
-> BEGIN -> DEALLOCATE -> PREPARE -> EXECUTE -> ROLLBACK
-> BEGIN -> DEALLOCATE -> PREPARE -> EXECUTE -> ROLLBACK -> ...
vs.
BEGIN -> PREPARE -> EXECUTE -> DEALLOCATE -> ROLLBACK
-> BEGIN -> PREPARE (-> EXECUTE) -> DEALLOCATE -> ROLLBACK
-> BEGIN -> PREPARE (-> EXECUTE) -> DEALLOCATE -> ROLLBACK -> ...
Granted, preparing the same exact statement in loop (rather than
once then using varying parameters) is dumb, but...
PostgreSQL logs from the provided test case:
Oct 24 15:29:15 dev postgres[26864]: [5-1] LOG: statement: BEGIN
Oct 24 15:29:15 dev postgres[26864]: [6-1] LOG: execute
pdo_pgsql_stmt_01ef7698: INSERT INTO test (a) VALUES (1)
Oct 24 15:29:15 dev postgres[26864]: [7-1] ERROR: duplicate key value
violates unique constraint "test_pkey"
Oct 24 15:29:15 dev postgres[26864]: [7-2] STATEMENT: INSERT INTO test
(a) VALUES (1)
Oct 24 15:29:15 dev postgres[26864]: [8-1] LOG: statement: DEALLOCATE
pdo_pgsql_stmt_01ef7698
Oct 24 15:29:15 dev postgres[26864]: [9-1] ERROR: current transaction
is aborted, commands ignored until end of transaction block
Oct 24 15:29:15 dev postgres[26864]: [9-2] STATEMENT: DEALLOCATE
pdo_pgsql_stmt_01ef7698
Oct 24 15:29:15 dev postgres[26864]: [10-1] LOG: statement: ROLLBACK
Oct 24 15:29:15 dev postgres[26864]: [11-1] LOG: statement: BEGIN
Oct 24 15:29:15 dev postgres[26864]: [12-1] ERROR: prepared statement
"pdo_pgsql_stmt_01ef7698" already exists
Oct 24 15:29:15 dev postgres[26864]: [12-2] STATEMENT: INSERT INTO
test (a) VALUES (1)
Oct 24 15:29:15 dev postgres[26864]: [13-1] LOG: statement: DEALLOCATE
pdo_pgsql_stmt_01ef7698
Oct 24 15:29:15 dev postgres[26864]: [14-1] ERROR: current transaction
is aborted, commands ignored until end of transaction block
Oct 24 15:29:15 dev postgres[26864]: [14-2] STATEMENT: DEALLOCATE
pdo_pgsql_stmt_01ef7698
Oct 24 15:29:15 dev postgres[26864]: [15-1] ERROR: current transaction
is aborted, commands ignored until end of transaction block
Oct 24 15:29:15 dev postgres[26864]: [15-2] STATEMENT: INSERT INTO
test (a) VALUES (1)
Oct 24 15:29:15 dev postgres[26864]: [16-1] LOG: statement: ROLLBACK
Oct 24 15:29:15 dev postgres[26864]: [17-1] LOG: statement: BEGIN
Oct 24 15:29:15 dev postgres[26864]: [18-1] ERROR: prepared statement
"pdo_pgsql_stmt_01ef7698" already exists
Oct 24 15:29:15 dev postgres[26864]: [18-2] STATEMENT: INSERT INTO
test (a) VALUES (1)
Oct 24 15:29:15 dev postgres[26864]: [19-1] LOG: statement: DEALLOCATE
pdo_pgsql_stmt_01ef7698
Oct 24 15:29:15 dev postgres[26864]: [20-1] ERROR: current transaction
is aborted, commands ignored until end of transaction block
Oct 24 15:29:15 dev postgres[26864]: [20-2] STAT
#49985 [Com]: pdo_pgsql prepare() re-use previous aborted transaction
ID: 49985
Comment by: ben dot pineau at gmail dot com
Reported By: ben dot pineau at gmail dot com
Status: Open
Bug Type: PDO related
Operating System: Linux
PHP Version: 5.2.11
New Comment:
About the difference when running prepare+execute in a separate
function: we just get the same stmt pointer address in this case
(vs. a different pointer address when running everything in the
same context). Thus stmt_name remains identical among successive
calls, stmt_name being just set as
spprintf(&S->stmt_name, 0, "pdo_pgsql_stmt_%08x", (unsigned int)stmt);
This explains why prepare() fails in the first case and succeed
in the other.
Anyway, here is a patch (against PHP_5_2 svn branch's head as of now)
using savepoints around PQprepare attempts so we DEALLOCATE + retry
without silently killing the current transaction, if/when the first
prepare fails with code 42P05.
Savepoints support started with pg 8.0 and I didn't ifdefed/autoconf
checked for this (will do if you ask); the patch will probably be
mangled by the bugrackers autowrapping (if so, please find a copy
at http://zouh.org/ben/various/pgsql_statement.c.diff ).
Index: pgsql_statement.c
===
--- pgsql_statement.c (revision 289910)
+++ pgsql_statement.c (working copy)
@@ -134,6 +134,14 @@
/* using a prepared statement */
if (!S->is_prepared) {
+ /* don't break the whole current transaction
when the first
+* prepare tentative fails (happens when the
prepared statement
+* already exists). ignore those SAVEPOINT
queries results because
+* we don't care (may be outside
transaction?).
+*/
+ char buf[100]; /* stmt_name ==
"pdo_pgsql_cursor_%08x" */
+ snprintf(buf, sizeof(buf), "SAVEPOINT %s",
S->stmt_name);
+ PQexec(H->server, buf);
stmt_retry:
/* we deferred the prepare until now, because
we didn't
* know anything about the parameter types; now
we do */
@@ -153,12 +161,14 @@
/* 42P05 means that the
prepared statement already existed. this can happen if you use
* a connection pooling
software line pgpool which doesn't close the db-connection once
* php disconnects. if php dies
(no chance to run RSHUTDOWN) during execution it has no
-* chance to DEALLOCATE the
prepared statements it has created. so, if we hit a 42P05 we
-* deallocate it and retry ONCE
(thies 2005.12.15)
+* chance to DEALLOCATE the
prepared statements it has created. Also happens if we tried
+* to DEALLOCATE the same
statement name in an aborted transaction. so, if we hit a 42P05
+* we deallocate it and retry
ONCE (thies 2005.12.15)
*/
if (!strcmp(sqlstate, "42P05"))
{
- char buf[100]; /*
stmt_name == "pdo_pgsql_cursor_%08x" */
PGresult *res;
+ snprintf(buf,
sizeof(buf), "ROLLBACK TO SAVEPOINT %s", S->stmt_name);
+ PQexec(H->server,
buf);
snprintf(buf,
sizeof(buf), "DEALLOCATE %s", S->stmt_name);
res = PQexec(H->server,
buf);
if (res) {
@@ -166,11 +176,15 @@
}
goto stmt_retry;
} else {
+ snprintf(buf,
sizeof(buf), "RELEASE SAVEPOINT %s", S->stmt_name);
+ PQexec(H->server,
buf);
pdo_pgsql_error_stmt(stmt, status, sqlstate);
return 0;
}
}
}
+ snprintf(buf, sizeof(buf), "RELEASE SAVEPOINT
%s", S->stmt_name);
+ PQexec(H->server, buf);
}
S->result = PQexecPrepared(H->server, S->stmt_name,
stmt->boun
