Re: How to don't update sequence on rollback of a transaction

On Fri, Aug 3, 2012 at 1:08 AM, Frank Lanitz <frank(at)frank(dot)uvena(dot)de> wrote:
> My understanding of all was that it includes sequences. Obviously, I'm
> wrong... but how to do it right?

Sequences are fast and lock-free, but don't guarantee absence of gaps.
Quite a few things can unexpectedly advance a sequence (including
master-slave failover in replication - I've noticed IDs jump by about
32).

What should happen when two transactions simultaneously want a new ID?
Should the second block, waiting for the first one to commit or roll
back? Or will you allow the gaps, just as long as they get filled in
later?

The easiest way is probably to have a dedicated table of available
numbers, and use DELETE ... RETURNING to get the next one.

ChrisA