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
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? |