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?

Answer 1

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;