Re: Advice on implementing counters in postgreSQL

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