pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout sequences that works BAD !!!!
3+ messages / 3 participants
[nested] [flat]
* About sequences that works BAD !!!!
@ 2006-06-14 18:02 Alexis Palma Espinosa <apalma@uci.cu>
0 siblings, 2 replies; 3+ messages in thread
From: Alexis Palma Espinosa @ 2006-06-14 18:02 UTC (permalink / raw)
To: pgsql-sql
Hello everyone:
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...¿What we can do about it?
We hope you can help us.....
Alexis Palma Espinosa.
Ingeniero en Informática.
"If you are not part of the solution...you are part of the problem"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: About sequences that works BAD !!!!
@ 2006-06-14 18:28 Richard Huxton <dev@archonet.com>
parent: Alexis Palma Espinosa <apalma@uci.cu>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2006-06-14 18:28 UTC (permalink / raw)
To: Alexis Palma Espinosa <apalma@uci.cu>; +Cc: pgsql-sql
Alexis Palma Espinosa wrote:
> Hello everyone:
>
>
>
> 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...¿What 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
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: About sequences that works BAD !!!!
@ 2006-06-14 18:39 Terry Lee Tucker <terry@esc1.com>
parent: Alexis Palma Espinosa <apalma@uci.cu>
1 sibling, 0 replies; 3+ messages in thread
From: Terry Lee Tucker @ 2006-06-14 18:39 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 14 June 2006 02:02 pm, "Alexis Palma Espinosa" <apalma@uci.cu>
thus communicated:
--> Hello everyone:
-->
-->
-->
--> 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...¿What we can do about it? -->
-->
-->
--> We hope you can help us.....
-->
-->
-->
This is doing exactly what it is supposed to do. See the docs:
http://www.postgresql.org/docs/7.4/static/functions-sequence.html
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-06-14 18:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-14 18:02 About sequences that works BAD !!!! Alexis Palma Espinosa <apalma@uci.cu>
2006-06-14 18:28 ` Richard Huxton <dev@archonet.com>
2006-06-14 18:39 ` Terry Lee Tucker <terry@esc1.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