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

On 07/01/2011 02:20 AM, Dmitry Koterov wrote:
>
> The problem is that these INSERTs are rolled back oftenly (i.e. they
> are executed within a transaction block which is rolled back time to
> time), this is an existing design of the current architecture and
> unfortunately we have to live with it. And I need as compact uniq_id
> generation (with minimum "holes") as it possible - this is a VERY
> important requirement (to export these values into external systems
> which accepts only IDs limited from 1 to 100000).

What you want is often referred to as a "gapless sequence". Searching
the mailing list archives for that will find you lots of information.

> but seems it performs too hard locking - time to time this query is
> timed out (or sometimes deadlocks with other queries).
You'll have that problem with any gapless sequence approach. You'll have
to be prepared to re-try failed transactions after deadlocks, or be
*extremely* strict about the order in which you perform operations so
you avoid any deadlocks.

--
Craig Ringer