Efficiently advancing a sequence without risking it going backwards.

From: Paul McGarry <paul(at)paulmcgarry(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Efficiently advancing a sequence without risking it going backwards.
Date: 2020-07-07 02:06:11
Message-ID: CAPrE0SYW_P6vh37F_BO7tBJkMGSCVRz5KZHPW5Qb3icuNRDJ1Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-07-07 02:25:06 Re: Efficiently advancing a sequence without risking it going backwards.
Previous Message David G. Johnston 2020-07-06 22:48:34 Re: Is postgres able to share sorts required by common partition window functions?