Re: Advice on implementing counters in postgreSQL

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(at)postnewspapers(dot)com(dot)au> 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/