Thread: sequence problem

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.

Can anyone help me?

Thanx in advance

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

gabi munteanu writes:

> if I remove a record my ids should always be like this:
>
> 1,2,3,4... and not 1,2,4,5,9,...

Sequences don't make a guarantee that the generated numbers are without
"holes".  Implementing that is rather complex.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

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).