Re: Advice on implementing counters in postgreSQL

Marco Bizzarri wrote:
> 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?

As far as I know it's not significantly different, though I expect it'd
be somewhat more efficient. However, support for UPDATE ... RETURNING
was only added in 8.2 (or somewhere around there) anyway, so if you need
to work with old versions like 7.4 it's no good to you anyway.

I take it there's no way you can present the gapless identifiers at the
application level, leaving the actual tables with nice SEQUENCE
numbering? Or, alternately, insert them by timestamp/sequence (leaving
the user-visible ID null) then have another transaction come back and
assign them their gapless numeric identifiers in a single simple pass later?

You're really going to suffer on concurrency if you have to acquire
values from a gapless sequence as part of a transaction that does much
other work.

--
Craig Ringer