Re: How to create "auto-increment" field WITHOUT a sequence object?

On 1/07/2011 4:21 PM, Chris Travers wrote:

> means a possibility of deadlocks and performance issues. These issues
> are inherent in gapless numbering because you can't get a gapless
> sequence when things roll back without such locks.

Actually, another approach that allows parallel transactions is (at
least theoretically) possible. You can hand out IDs as transactions
request them, and if a transaction rolls back you can abort it *and*
*all* *transactions* *given* *higher* *IDs*, then re-issue the lot. I
guess that could be useful if transaction failure was extremely unlikely
and you needed to have lots of work in flight at once.

In practice I don't think it'd be very useful to do this, because
transactions would still have to commit in the order they obtained IDs
in, so a slow or blocked transaction would still stall the system just
like it does with lock-based gapless numbering. Also, once a later
transaction had obtained an ID prior transactions couldn't obtain any
more IDs.

I'm not sure this is possible in Pg without modifying the server,
either. It'd be kind of interesting in a useless-toy-project kind of way.

--
Craig Ringer