Re: sequence advances on failed insert

From: Chris Smith <chris(at)interspire(dot)com>
To: Sven Willenberger <sven(at)dmv(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: sequence advances on failed insert
Date: 2005-04-08 04:37:23
Message-ID: 42560A83.9000801@interspire.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)interspire(dot)com
web: http://www.interspire.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2005-04-08 07:06:41 Re: Iterate OLD/NEW columns in a trigger?
Previous Message Matthew Terenzio 2005-04-08 03:47:32 Re: sequence advances on failed insert