Postgres startup using PDO

Question

I found that previous postgres + PDO changes are automatically rolled back when an exception is thrown (EVEN WHEN THE EXCLUSION CONTINUES AND IS AFFECTED!). Example (in pseudo-code):

$transaction->begin(); try { $manager->insert("INSERT ..."); try { $manager->exec("A QUERY BREAKING SOME DB CONSTRAINT LIKE A UNIQUE INDEX ..."); } catch (\Exception $ex) { // IT IS CAUGHT AND SWALLOWED! } $transaction->commit(); } catch (Exception $ex) { $transaction->rollback(); // THIS CLEARLY DOES NOT RUN! } 

In postgres, the first insert is returned. In mysql no.

Can someone shed some light on this question? Can this ridiculous behavior be changed? I would like to do my rollbacks myself and not get pg to do this when he considers it appropriate.

Answer 1

This is not a PDO error inherent in PostgreSQL transaction management. Cm:

• How can I tell PostgreSQL to abort an entire transaction when one constraint fails?
• Can I ask Postgresql to ignore errors inside a transaction
• Rollback after transaction error

PostgreSQL does not roll back the transaction, but sets it to an aborted state, where it can only roll back, and where all statements except ROLLBACK report an error:

ERROR: current transaction is aborted, commands ignored until end of transaction block

(I am surprised to not find this in the official documentation, I think I need to write a patch to improve this.)

So. When you try / catch and swallow an exception in PDO, you mask the exception of the PHP side, but you do not change the fact that the PostgreSQL transaction is in an aborted state.

If you want to be able to swallow exceptions and continue to use the transaction, you must create a SAVEPOINT before each statement, which may fail. If this fails, you must ROLLBACK TO SAVEPOINT ...; . If it succeeds, you can RELEASE SAVEPOINT ...; . This imposes additional overhead on the database for transaction management, adds round trips and burns faster through transaction IDs (this means that PostgreSQL has to do additional work to clear the background).

It is usually preferable to create your own SQL code so that it does not fail under normal circumstances. For example, you can check most client-side constraints by treating server-side constraints as the second level of confidence, while trapping most client-side errors.

If this is not practical, make your application error bearable so that it can retry the failed transaction. Sometimes this is necessary in any case - for example, you cannot use savepoints at all to recover from transaction failures or serialization failures. It may also be helpful to keep the failure transactions as short as possible to do the minimum amount of work required, so you need to track and retry less.

So: where possible, instead of swallowing an exception, run the error-prone database code in a retry loop. Make sure your code stores a record of the information it needs to retry the entire transaction in error, and not just the latest statement.

Remember that any transaction may fail: the database administrator can restart the database to apply the patch, the system may exit RAM due to the cron job, etc. Thus, fault-tolerant applications are in any case a good design.

Suitable for you, at least for using PDO exceptions and exceptions for handling - you are far ahead of most developers.