pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsequence 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