Обсуждение: sequence advances on failed insert
I'm noticing that a sequence is advancing even if the insertion fails. Is this weird or expected? Matt Terenzio
On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote: > I'm noticing that a sequence is advancing even if the insertion > fails. Is this weird or expected? It's expected. Sequences are guaranteed to generate unique IDs. These happen to be an increasing sequence of integers, but there is no attempt to make this a gap-free sequence, and your apps should not depend on the actual value of said ID. HTH :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
David Fetter presumably uttered the following on 04/07/05 20:16: > On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote: > >>I'm noticing that a sequence is advancing even if the insertion >>fails. Is this weird or expected? > > > It's expected. Sequences are guaranteed to generate unique IDs. > These happen to be an increasing sequence of integers, but there is no > attempt to make this a gap-free sequence, and your apps should not > depend on the actual value of said ID. I assume by "not depend on the actual value" that one should not assume that the next value will be one increment higher than the current highest value in the table; because it is guaranteed to be unique, I would think it to be an excellent way to assign a customer id, for example, which can then be referenced (foreign key, etc) by other tables after a new record is added. Unless there is some other reason one should not use a sequence value as any type of identifier? Sven
On Thu, Apr 07, 2005 at 11:27:41PM -0400, Sven Willenberger wrote: > > > David Fetter presumably uttered the following on 04/07/05 20:16: > >On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote: > > > >>I'm noticing that a sequence is advancing even if the insertion > >>fails. Is this weird or expected? > > > > > >It's expected. Sequences are guaranteed to generate unique IDs. > >These happen to be an increasing sequence of integers, but there is > >no attempt to make this a gap-free sequence, and your apps should > >not depend on the actual value of said ID. > > I assume by "not depend on the actual value" that one should not > assume that the next value will be one increment higher than the > current highest value in the table; Yes. > because it is guaranteed to be unique, I would think it to be an > excellent way to assign a customer id, for example, which can then > be referenced (foreign key, etc) by other tables after a new record > is added. It's designed for just that purpose :) > Unless there is some other reason one should not use a sequence > value as any type of identifier? Relational purists sometimes insist that artificial keys cause more problems than they solve, but I'm not in that camp most of the time. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Apr 7, 2005, at 11:33 PM, David Fetter wrote: > Relational purists sometimes insist that artificial keys cause more > problems than they solve That's interesting. It seems to come in handy for me. It is interesting though.
Yes, he meant that one should not assume that the next value will be one increment higher than the current highest value in the table. You shouldn't rely on them being sequential because they will not always be that way. Sven Willenberger wrote: > > > David Fetter presumably uttered the following on 04/07/05 20:16: > >> On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote: >> >>> I'm noticing that a sequence is advancing even if the insertion >>> fails. Is this weird or expected? >> >> >> >> It's expected. Sequences are guaranteed to generate unique IDs. >> These happen to be an increasing sequence of integers, but there is no >> attempt to make this a gap-free sequence, and your apps should not >> depend on the actual value of said ID. > > > I assume by "not depend on the actual value" that one should not assume > that the next value will be one increment higher than the current > highest value in the table; because it is guaranteed to be unique, I > would think it to be an excellent way to assign a customer id, for > example, which can then be referenced (foreign key, etc) by other tables > after a new record is added. Unless there is some other reason one > should not use a sequence value as any type of identifier? > > Sven > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: info@interspire.com web: http://www.interspire.com