Efficiently advancing a sequence without risking it going backwards.

[Paul McGarry <paul@xxxxxxxxxxxxxxx>]

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