sequence advances on failed insert

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