pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sequence problem
3+ messages / 3 participants
[nested] [flat]

* sequence problem
@ 1970-01-01 00:00 gabi munteanu <gmunteanu@hotmail.com>
  2001-05-21 20:24 ` Re: sequence problem Peter Eisentraut <peter_e@gmx.net>
  2001-05-21 20:39 ` Re: sequence problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 2 replies; 3+ messages in thread

From: gabi munteanu @ 1970-01-01 00:00 UTC (permalink / raw)
  To: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: sequence problem
  1970-01-01 00:00 sequence problem gabi munteanu <gmunteanu@hotmail.com>
@ 2001-05-21 20:24 ` Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Peter Eisentraut @ 2001-05-21 20:24 UTC (permalink / raw)
  To: gabi munteanu <gmunteanu@hotmail.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: sequence problem
  1970-01-01 00:00 sequence problem gabi munteanu <gmunteanu@hotmail.com>
@ 2001-05-21 20:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-05-21 20:39 UTC (permalink / raw)
  To: gabi munteanu <gmunteanu@hotmail.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-05-21 20:39 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 sequence problem gabi munteanu <gmunteanu@hotmail.com>
2001-05-21 20:24 ` Peter Eisentraut <peter_e@gmx.net>
2001-05-21 20:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox