> On Jul 6, 2020, at 19:06, Paul McGarry <paul@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
backfrom 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
thesequence momentarily while you do the setval() then rename it back. Do an initial check without renaming, then
re-checkafter 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
inducedby thundering herd.
-Jeremy
Sent from my TI-83