Обсуждение: 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);
$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)");
$insertReminderEntry = $dbh->prepare("insert into reminders (uid,nl) values (:uid,:aboname)");
$subscription=array('uktapsps','uktapsem');
$uidArray['uid'] = 'W200705084162';
$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";
}
}
$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/ > 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/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
>>> 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/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout wrote:
Raymond
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.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,
Raymond