Bug #52294 [Opn->Nab]: Cannot use SAVEPOINTs to recover from exceptions/errors

Edit report at https://bugs.php.net/bug.php?id=52294&edit=1 ID: 52294 Updated by: rasmus@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