Re: PHP PDO-PGSQL and transactions

Martijn van Oosterhout wrote:
> On Mon, Dec 01, 2008 at 10:14:52AM +0100, Bart Degryse wrote:
>
>> I'm having a transaction problem when executing prepared statements using the PHP PDO-PGSQL module.
>> What happens is this:
>> On the first $subItem, $checkSubscription goes well, but $insertReminderEntry fails with error
>> "Duplicate key violates unique constraint"
>> This error should just be logged and the code should continue with the second $subItem.
>> And it does, but the second $checkSubscription also fails with error
>> "Current transaction is aborted, commands ignored until end of transaction block"
>> And that was not what I meant to happen.
>>
>
> That's the way postgresql works. Either all statements in a
> transaction succeed, or none of them do. Postgres doesn't do the
> half-half thing you're looking for.
>
> There are a number of ways to deal with it: first check if the row
> exists, use a stored procedure to make the checking transparent. You
> can use savepoints to acheive your goal also.
>
> The archives may describe other solutions.
>
> Have a nice day,
>
There's also the possibility that you're hitting a bug in some PHP
versions' PostgreSQL PDO support. Versions in the 5.1.x range cause
prepared statements to generate errors for some reason which kill
transactions, even if the SQL is flawless. This caused me a lot of
headaches until I did some digging, but it is easily cured with 5.2.x or
later.

Raymond