Thanks for the advice, Craig. I'm on a number of different PostgreSQL versions, ranging from 7.4 to 8.3, so I've to retain, where possible, compatibility with older versions. Is this better on a transaction/serialization point of view? Regards Marco On Sat, Aug 2, 2008 at 10:19 AM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote: > Marco Bizzarri wrote: >> Hi all. >> >> I need to keep a numer of counters in my application; my counters are >> currently stored in a table: >> >> name | next_value | year >> >> >> The counters must be progressive numbers with no holes in between >> them, and they must restart from 1 every year. What I've done so far >> is to access them while in SERIALIZABLE ISOLATION LEVEL, with the >> following: >> >> SELECT next_value FROM counters WHERE name = 'name' for update; >> UPDATE counters SET next_value = next_value + 1 WHERE name = 'name'; > > If you're using a sufficiently recent version of Pg you can use: > > UPDATE counters > SET next_value = next_value + 1 > WHERE name = 'name' > RETURNING next_value; > > instead, which is slightly nicer. It'll return the *new* value of > `next_value', so you'd have to make a few tweaks. > > -- > Craig Ringer > -- Marco Bizzarri http://iliveinpisa.blogspot.com/
