Re: creating "job numbers"

"postgresql" <pgsql(at)symcom(dot)com> writes:
> Using the serial data type... I don't understand when the backend
> would skip a number.

The value returned by a nextval() call will not be returned again by
other nextval() calls, even if the surrounding transaction is later
rolled back. Agreed, this isn't in line with full transactional
semantics, but it was deemed the more useful thing to do precisely
because of that. If you want the other behavior you can build it
yourself, whereas there's no way to build the actual behavior of
sequence objects in plain SQL.

The reason why this is more useful is that with this behavior,
acquirers of serial numbers don't need to wait for each other. A
no-skipped-numbers implementation requires each would-be acquirer to
block waiting to see if previous acquirers commit or not. You get no
concurrency at all if you build your system like that.

regards, tom lane