php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #52294 Cannot use SAVEPOINTs to recover from exceptions/errors
Submitted: 2010-07-08 22:10 UTC Modified: 2012-02-21 07:24 UTC
From: skeptic2425 at hotmail dot com Assigned:
Status: Not a bug Package: PostgreSQL related
PHP Version: 5.2.13 OS: Linux
Private report: No CVE-ID: None
 [2010-07-08 22:10 UTC] 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"


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-07-29 21:23 UTC] 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.
 [2012-02-21 07:24 UTC] rasmus@php.net
-Status: Open +Status: Not a bug
 [2012-02-21 07:24 UTC] rasmus@php.net
This doesn't look like a PHP bug if it is a bug at all. Check with the PostgreSQL 
folks for further clarification.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed May 22 10:01:31 2024 UTC