Re: sequence problem

On Mon, 21 May 2001, gabi munteanu wrote:

> I have the following problem.
>
> I have a table [friends] and it looks like this:
> id serial
> name varchar(25)
> phone varchar(15)
> After I created it tehre is also a sequence that generates me the ids
> friens_id_seq
>
> Let's say I have 5 records.
>
> If I remove the 3th my ids will be 1,2,4,5 and my friends_id_seq=5.
> if I remove the 5th my ids will be 1,2,4 and my friends_id_seq=5.
>
> I want the following:
> if I remove a record my ids should always be like this:
>
> 1,2,3,4... and not 1,2,4,5,9,...

> I made a trigger that does my friends_id_seq = max(id) after delete.

That won't help you if you remove the 3rd in a set of 5, and there are
locking issues to worry about if you have multiple transactions modifying
the table since you won't see the uncommitted rows (and reading those
wouldn't help anyway since they could rollback).

You could probably do it if you locked the table and generated the
id value (thus only allowing one transaction modification at a time
others blocking until commit or rollback) and you locked and
renumbered after a delete (since you don't want it in a hole state
until the next is inserted).