The following bug has been logged online:
Bug reference: 5027
Logged by: Dmitry
Email address: master@hsdesign.ru
PostgreSQL version: 8.3.5
Operating system: ALT Linux
Description: SQL query error?
Details:
Hello,
I try to make user rating by this query:
"BEGIN;
ALTER SEQUENCE service.rate_seq RESTART 1;
UPDATE service.user u
SET rate = sr.rate
FROM (
SELECT user_id, nextval('service.rate_seq') as rate
FROM
(
SELECT user_id, score FROM service.user
ORDER BY score DESC
) a
) sr
WHERE u.user_id = sr.user_id;
END;"
all work fine, but if while this query is executing another process do
"UPDATE service.user SET score=score+1" i get wrong sequence value; for
example
if user count = 55000 i can get 512321 in sequence ;(
why? query error?