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

On 7/6/20 7:06 PM, Paul McGarry wrote:
> I have two sequences in different dbs which I want to keep roughly in
> sync (they don't have to be exactly in sync, I am just keeping them in
> the same ballpark).
>
> Currently I have a process which periodically checks the sequences and does:
>
> 1) Check values
> DB1sequence: 1234
> DB2sequence: 1233 (1 behind)
> 2) while (nextval('DB2sequence')<=1234);
>
> which works fine, but is pretty inefficient if the discrepancy is large
> (ie calling nextval a hundred thousand times).
>
> 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.
>
> So what I really want is something equivalent to the setval, but with
> "where DB2sequence <1234" logic so it doesn't overwrite the value if it
> is already large.
>
> Is there such a mechanism?

Well sequences are designed to be operated on independently from each
session, so there is not much you can do about locking on a number. The
best you can do is use setval() to increment the number by enough to get
past any potential sequence advances in other sessions. Say advance by
10, 50 or 100 depending on what you think is a reasonable number of
other sessions also hitting the sequence.

>
> Thanks for any help.
>
> Paul

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com