Re: Efficiently advancing a sequence without risking it going backwards. - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Efficiently advancing a sequence without risking it going backwards.
Date
Msg-id bd257c6b-9afe-bb7a-9ef7-2932f633fb8e@aklaver.com
Whole thread Raw
In response to Efficiently advancing a sequence without risking it going backwards.  (Paul McGarry <paul@paulmcgarry.com>)
List pgsql-general
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@aklaver.com



pgsql-general by date:

Previous
From: Paul McGarry
Date:
Subject: Efficiently advancing a sequence without risking it going backwards.
Next
From: Praveen Kumar K S
Date:
Subject: Re: [HELP] Regarding how to install libraries