On Wed, 2009-07-15 at 22:34 -0700, John R Pierce wrote:
> sounds like you should be using a SERIAL (which is implemented as an
> INTEGER or BIGINT field with an associated SEQUENCE), as these DO work
> just fine under heavy concurrency without any gotchas.
There is one gotcha, though we're all so used to it (and/or never
would've thought to care about it) as to forget it:
With a SEQUENCE, as produced by the SERIAL pseudo-type, values
may be skipped if a transaction rolls back. That includes automatic
rollback on error or disconnect, not just explicit ROLLBACK of course.
If you're using sequences to generate synthetic keys that's exactly what
you want; you don't care about gaps and you want it fast and
concurrency-friendly.
If your application can't cope with gaps in the sequence then either (a)
fix it so it can, or (b) search this mailing list for gapless sequence
implementations and use one of them. Beware the nasty performance
implications.
--
Craig Ringer