I've been working my way through learning PostgreSQL (7.3.2) and PHP
(4.3.0) and have been really pleased how things have been coming
together. However, I have a question about handling transactions with
PostgreSQL and PHP.
Before I figured out how to successfully implement a transaction
through PHP, I bungled it quite a few times, in that the transaction
got aborted. Unfortunately, due to my inexperience, the COMMIT
statement didn't get sent after it was aborted—the PHP script just died.
After I finally figured out how to do transactions without crashing and
burning (or at least sending a COMMIT if it did abort), I would still
get SQL errors saying that the current SQL couldn't be processed
because a transaction had been aborted. Now, I know for a fact that the
scripts I was running then weren't issuing transactions. And it was
very sporadic. Sometimes I'd just be running the same script that had
just run successfully—no changes, nothing occurring in the interim—and
I'd get the "transaction aborted" SQL error.
I'm wondering if PHP is cycling through a number of different
connections to use to get to the database. A few of them had been
aborted and not committed, so when PHP selected that connection,
Postgres would send the transaction aborted error.
How do I clean up these aborted transactions? Of course, prevention is
better than cure, but we all get colds sometimes. :)
I've since restarted postmaster and the problem seems like it went
away. Scripts running fine. But I'd like to know how to fix this in the
future (without restrarting postmaster) when it happens again.
Thanks for any advice!