Re: Efficiently advancing a sequence without risking it going backwards.

[Jeremy Schneider <schneider@xxxxxxxxxxxxxx>]

On Jul 9, 2020, at 14:08, Christopher Browne <cbbrowne@xxxxxxxxx> wrote:



On Thu, 9 Jul 2020 at 12:59, Jeremy Schneider <schneider@xxxxxxxxxxxxxx> wrote:

> On Jul 6, 2020, at 19:06, Paul McGarry <paul@xxxxxxxxxxxxxxx> 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