Edit report at http://bugs.php.net/bug.php?id=52294&edit=1
ID: 52294
Comment by: tommy at gildseth dot com
Reported by: skeptic2425 at hotmail dot com
Summary: Cannot use SAVEPOINTs to recover from
exceptions/errors
Status: Open
Type: Bug
Package: PostgreSQL related
Operating System: Linux
PHP Version: 5.2.13
Block user comment: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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 http://bugs.php.net/bug.php?id=52294&edit=1
