Laurent Birtz wrote:
> loop
> BEGIN;
> SELECT hits FROM webpages WHERE url = ...;
> -- client internally computes $newval = $hits + 1
> UPDATE webpages SET hits = $newval WHERE url = ..;
> if (no error)
> break out of loop;
> else
> ROLLBACK;
> end loop
> COMMIT;
I think you should be able to shave some milliseconds off that loop by
using SAVEPOINTs instead of a full-blown transaction for each tuple.
Something like
BEGIN
outer loop
inner loop
SAVEPOINT foo;
SELECT hits ...
UPDATE webpages SET hits = ...
if (no error)
break inner loop
else
ROLLBACK TO foo
end inner loop
end outer loop
COMMIT;
> However, I am having problem implementing this scheme in C with libpq.
> Transactions can be aborted because a deadlock occurred or another
> transaction already made some changes to the database.
>
> My question is how exactly do I detect that this occurred?
IIRC you can apply PQresultStatus to the PGresult returned by the UPDATE.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
