Don't rollback on errors in transactions using PHP, PDO and Postgres

Question

I have a very extensive PHP update script based on PHP/Yii that updates a database on different database types (MSSQL, Postgres and MySQL).

The whole script runs within a transaction. However there are some statements that lead to a query error (for example if a certain key already exists on a table). I surrounded these with try/catch statements - this works fine so far in MySQL

However on Postgres once an invalid query was issued the transaction is automatically failing. The following error message is shown for all following statements:

CDbCommand failed to execute the SQL statement: SQLSTATE[25P02]: In failed sql transaction: ERROR: current transaction is aborted, commands ignored until end of transaction block

But what I need is Postgres to continue the transaction because I want to decide in the application when to roll back - or some way to clear out the error and continue the transaction.

How to do that?

What will be the next step if the script detects a "duplicate key error" ? — wildplasser
– wildplasser, Commented Dec 23, 2012 at 11:59

greg · Accepted Answer · 2012-12-23 11:21:57Z

5

Use a savepoint for that purpose:

BEGIN; -- transaction starts here

-- do things if you want

SAVEPOINT my_savepoint;

-- failing statement here
-- all other statements are ignored

ROLLBACK TO SAVEPOINT my_savepoint;

-- continue your transaction starting from my_savepoint

COMMIT;

answered Dec 23, 2012 at 11:21

greg

3,5351 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Don't rollback on errors in transactions using PHP, PDO and Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related