Message-ID: <44905556.2060506@archonet.com>
Date: Wed, 14 Jun 2006 19:28:38 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.4 (X11/20060516)
MIME-Version: 1.0
To: Alexis Palma Espinosa <apalma@uci.cu>
Cc: pgsql-sql@postgresql.org
Subject: Re: About sequences that works BAD !!!!
References: <251EA6C55832BE4C80F868CC951BA8CE02764F94@ucixw2.uci.cu>
In-Reply-To: <251EA6C55832BE4C80F868CC951BA8CE02764F94@ucixw2.uci.cu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Alexis Palma Espinosa wrote:
> Hello everyone:
>=20
>=20
>=20
> We are working with serials fields and we found a problem with then:
> When we insert in a table that has e unique restrict, and this makes
> insert fails, the sequence increments anyway...=BFWhat we can do about
> it?

Nothing. The whole point of sequences is that they don't lock. They *do* =

guarantee unique numbers, but they *do not* guarantee no gaps.

If you really want a series of ID numbers with no gaps you'll want to do =

something like:

1. Begin transaction
2. Lock table exclusively
3. Find highest existing ID (...ORDER BY id DESC LIMIT 1)
4. Add one to it
5. Store new row.
6. commit transaction, freeing the lock

--=20
   Richard Huxton
   Archonet Ltd