|  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
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
Solve the problem:
29 + 26 = ?
Subscribe to this entry?

 [2010-07-08 22:10 UTC] skeptic2425 at hotmail dot com
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 :

Test script:
$dbconn = pg_connect("dbname=test") or die("Could not connect");

$query = "BEGIN;
CREATE TABLE mytable(id INT CHECK (id < 5));
SAVEPOINT savepoint1;
RELEASE savepoint1;
SAVEPOINT savepoint1;
ROLLBACK TO savepoint1;
RELEASE savepoint1;
SAVEPOINT savepoint1;
RELEASE savepoint1;
SAVEPOINT savepoint1;
ROLLBACK TO savepoint1;
RELEASE savepoint1;

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"


Add a Patch

Pull Requests

Add a Pull Request


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:
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]
-Status: Open +Status: Not a bug
 [2012-02-21 07:24 UTC]
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: Sat Jun 22 03:01:29 2024 UTC