Re: PostgreSQL 8.4.8 bringing my website down every evening

On 23 Jun 2011, at 19:48, Alexander Farber wrote:

> Sorry for the late reply - but I still haven't found a solution,
> for example I have a PHP script with 5 consecutive SELECT
> statements (source code + problem described again under:
>
> http://stackoverflow.com/questions/6458246/php-and-pgbouncer-in-transaction-mode-current-transaction-is-aborted
>
> ) and if I add $db->beginTransaction(); $db->commit();
> around each $db->prepare("select ..."); execute();
> then my script will fail very often with
>
> SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current
> transaction is aborted, commands ignored until end of transaction
> block

You almost got it right, but you added too many beginTransaction/commit pairs.
You only need a beginTransaction after you set up your database connection in your function, and a commit at the very end. You're supposed to put related queries in one transaction so that they either all commit, or none of them do if there's an error in one of the statements.

As another possible improvement, I'd probably not create a new connection in every function call, but use a global $db object instead. Creating DB-connections is relatively expensive, so you don't want to do that more often than strictly necessary. In that case you could probably use beginTransaction at the start of your script and commit at the end.
But that all depends on your requirements, of course.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4e05c52212095034320330!