>> On Jul 9, 2020, at 14:08, Christopher Browne <cbbro...@gmail.com> wrote:
>
>> On Thu, 9 Jul 2020 at 12:59, Jeremy Schneider <schnei...@ardentperf.com>
>> wrote:
>
>>
>> > On Jul 6, 2020, at 19:06, Paul McGarry <p...@paulmcgarry.com> wrote:
>> >
>> > I don't think I can use setval(), because it risks making sequences go
>> > backwards, eg:
>> >
>> > 1) Check values
>> > DB1sequence: 1234
>> > DB2sequence: 1233 (1 behind)
>> > 2) setval('DB2sequence',1234);
>> >
>> > but if between (1) and (2) there are 2 nextval(DB2sequence) calls on
>> > another process, (2) would take the sequence back from 1235 to 1234 and I
>> > would end up trying to create a duplicate key ID from the sequence.
>>
>> An ability to “lock” the sequence momentarily would give you the tool you
>> need, but I don’t think it’s there.
>
> The use-case where you need a lock on the value so that there can't possibly
> be a hole in the sequence
OP asked for a way to call setval() with a guarantee the sequence will never go
backwards IIUC. His code can check that the new value he wants to set is higher
than the current value, but there’s a race condition where a second connection
could quickly advance the sequence between the check and the setval() call and
then cause duplicates from the next call which is bad.
The ideal solution is a setval_forward_only() or setval_no_duplicates()
function that does it atomically or something. If it were possible to “lock”
the entire sequence to prevent any other sessions from using it at all, that
would work too. Not locking a value, locking the whole thing. Very bad hack
solution is renaming the sequence then renaming it back as a blunt form of
locking... and to be clear I don’t think is a good idea I just was saying that
technically it might work. :)
-Jeremy
Sent from my TI-83
