Re: PHP PDO-PGSQL and transactions

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,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.