Edit report at https://bugs.php.net/bug.php?id=52294&edit=1
ID: 52294
Updated by: ras...@php.net
Reported by: skeptic2425 at hotmail dot com
Summary: Cannot use SAVEPOINTs to recover from
exceptions/errors
-Status: Open
+Status: Not a bug
Type: Bug
Package: PostgreSQL related
Operating System: Linux
PHP Version: 5.2.13
Block user comment: N
Private report: N
New Comment:
This doesn't look like a PHP bug if it is a bug at all. Check with the
PostgreSQL
folks for further clarification.
Previous Comments:
------------------------------------------------------------------------
[2010-07-29 21:23:58] tommy at gildseth dot com
This sounds like it is expected behaviour. From the postgresql documentation
for PQexec which is the underlying function called by pg_query:
http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html#LIBPQ-EXEC-MAIN
-------------------------------------------------------------------------------
It is allowed to include multiple SQL commands (separated by semicolons) in the
command string. Multiple queries sent in a single PQexec call are processed in
a single transaction, unless there are explicit BEGIN/COMMIT commands included
in the query string to divide it into multiple transactions. Note however that
the returned PGresult structure describes only the result of the last command
executed from the string. Should one of the commands fail, processing of the
string stops with it and the returned PGresult describes the error condition.
-------------------------------------------------------------------------------
Note specifically the last sentence.
------------------------------------------------------------------------
[2010-07-08 22:10:24] skeptic2425 at hotmail dot com
Description:
------------
Cannot use SAVEPOINTs to recover from errors/exceptions in a transaction.
This method of wrapping multiple statements in a transaction, one or more of
which can fail without aborting the transaction is outlined here :
http://wiki.postgresql.org/wiki/Transactions_recovering_failures_in_scripts
Test script:
---------------
$dbconn = pg_connect("dbname=test") or die("Could not connect");
$query = "BEGIN;
CREATE TABLE mytable(id INT CHECK (id < 5));
SAVEPOINT savepoint1;
INSERT INTO mytable VALUES (8);
RELEASE savepoint1;
SAVEPOINT savepoint1;
ROLLBACK TO savepoint1;
RELEASE savepoint1;
SAVEPOINT savepoint1;
INSERT INTO mytable VALUES (1);
RELEASE savepoint1;
SAVEPOINT savepoint1;
ROLLBACK TO savepoint1;
RELEASE savepoint1;
COMMIT;";
pg_query($dbconn, $query);
echo pg_last_error();
Expected result:
----------------
The query should the table, fail the first insert, rollback, second insert
succeeds then commits. This is what the SQL will do when put into psql
(postgresql console).
Actual result:
--------------
Transaction fails entirely on the first check violation and does not continue:
ERROR: new row for relation "mytable" violates check constraint
"mytable_id_check"
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=52294&edit=1
