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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
