On Tue, Sep 1, 2009 at 9:51 AM, Dmitry<mas...@hsdesign.ru> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5027
> Logged by: Dmitry
> Email address: mas...@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?
Because sequences are non-transactional. See the description of
nextval(), here:
http://www.postgresql.org/docs/current/interactive/functions-sequence.html
...Robert
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
