Re: nextval() on serial using old, existing value on insert?

Agreed, it does not stop some human from going in there and putting in
higher values than the sequence, but it's good to realize that's what
happened, and we have workflows/restrictions in place where it's unlikely
to happen again.

On Wed, May 10, 2023 at 10:09 PM Keith <keith(at)keithf4(dot)com> wrote:

>
>
> On Thu, May 11, 2023 at 1:07 AM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
> wrote:
>
>> As a follow up, I've selected max(common_key) from the table and
>> setval()'ed on the sequence to that +1 and I think that should make this go
>> away. Any reason why that's insane?
>>
>> On Wed, May 10, 2023 at 10:02 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
>> wrote:
>>
>>> Ah, I think that must be it-- there are 200 some rows where manually
>>> supplied values for that common_key column are higher than the nextval() on
>>> the serial. So eventually they might be "re-used".
>>>
>>> On Wed, May 10, 2023 at 9:55 PM David G. Johnston <
>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>
>>>>
>>>> On Wednesday, May 10, 2023, Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> I have a simple table with a given column defined like so:
>>>>>
>>>>> common_key | integer | | not null |
>>>>> nextval('alias.identity_common_key_seq'::regclass) | plain
>>>>>
>>>>> Very very very infrequently, on an INSERT where this column is not
>>>>> specified, this column will be assigned a value that already exists in the
>>>>> table, versus the next presumably unused value in the sequence. I cannot
>>>>> figure this out. Is there any reason why this might be the case?
>>>>>
>>>>
>>>> Most likely someone inserted data without using the sequence and
>>>> eventually the sequence catches up with that previously inserted data.
>>>>
>>>> David J.
>>>>
>>>>
>>>
>> --
>> Wells Oliver
>> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>>
>
>
> That will make it go away for the values currently in the table, but does
> nothing to prevent it happening again in the future.
>
> Keith
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>