PHP PDO-PGSQL and transactions
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.
Mark that I've only included part of the script. In the foreach loop several other updates and inserts happen.
Basically one of those updates and inserts failing should just be logged and the code should continue.
When the loop has finished all commands that executed without failure should be commited.
How should I change my code to make that happen?
Thanks for any help.
----------------------------------------------
$dbh = new PDO('pgsql:host='.$dbIP.';dbname='.$dbName.';port='.$dbPort, $dbUser, $dbPwd, array(PDO::ATTR_PERSISTENT => true));
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$checkSubscription = $dbh->prepare("select uid from subscriptions where uid=:uid and aboname=:subItem");
$insertReminderEntry = $dbh->prepare("insert into reminders (uid,nl) values (:uid,:aboname)");
$subscription=array('uktapsps','uktapsem');
$uidArray['uid'] = 'W200705084162';
$dbh->beginTransaction();
foreach ($subscription as $subItem) {
$checkSubscription->bindParam(':uid',$uidArray['uid']);
$checkSubscription->bindParam(':subItem',$subItem);
try {
$checkSubscription->execute();
}
catch (Exception $e) {
$msg.="ERROR: Subscription could not be checked! aboname: $subItem\n";
}
$insertReminderEntry->bindParam(':uid',$uidArray['uid']);
$insertReminderEntry->bindParam(':aboname',$subItem);
try {
$insertReminderEntry->execute();
}
catch (Exception $e) {
$msg.="ERROR: Entry could not be inserted into the reminder table! aboname: $subItem\n";
}
}
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@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
Thanks Martijn!
Martijn van Oosterhout <kleptog@svana.org> 2008-12-01 10:54 >>>
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@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
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