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.klaver@xxxxxxxxxxx
