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?
Thanks for any help.
Paul