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

> On Jul 6, 2020, at 19:06, Paul McGarry <paul(at)paulmcgarry(dot)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.

Total hack, but if your application or users can retry when the rare error is encountered then one idea is to rename the sequence momentarily while you do the setval() then rename it back. Do an initial check without renaming, then re-check after renaming and before the setval() call.

If you put retry logic into your application then make sure to include back-off logic so you don’t get an outage induced by thundering herd.

-Jeremy

Sent from my TI-83