Thread: BUG #5027: SQL query error?

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?

On Tue, Sep 1, 2009 at 9:51 AM, Dmitry<master@hsdesign.ru> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05027
> Logged by: =A0 =A0 =A0 =A0 =A0Dmitry
> Email address: =A0 =A0 =A0master@hsdesign.ru
> PostgreSQL version: 8.3.5
> Operating system: =A0 ALT Linux
> Description: =A0 =A0 =A0 =A0SQL 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
> =A0 SET rate =3D sr.rate
> FROM (
> =A0 SELECT user_id, nextval('service.rate_seq') as rate
> =A0 FROM
> =A0 =A0 (
> =A0 =A0 =A0SELECT user_id, score FROM service.user
> =A0 =A0 =A0ORDER BY score DESC
> =A0 =A0 ) a
> =A0 ) sr
> WHERE u.user_id =3D sr.user_id;
> END;"
>
> all work fine, but if while this query is executing another process do
> "UPDATE service.user SET score=3Dscore+1" i get wrong sequence value; for
> example
> if user count =3D 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