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

From Jeremy Schneider
Subject Re: Efficiently advancing a sequence without risking it going backwards.
Date
Msg-id C0CDE83F-17EB-4FE5-AF73-67EB53AFBD2A@ardentperf.com
Whole thread Raw
In response to Re: Efficiently advancing a sequence without risking it going backwards.  (Christopher Browne <cbbrowne@gmail.com>)
Responses Re: Efficiently advancing a sequence without risking it going backwards.
List pgsql-general


On Jul 9, 2020, at 14:08, Christopher Browne <cbbrowne@gmail.com> wrote:


On Thu, 9 Jul 2020 at 12:59, Jeremy Schneider <schneider@ardentperf.com> wrote:

> On Jul 6, 2020, at 19:06, Paul McGarry <paul@paulmcgarry.com> wrote:
>
> 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.

An ability to “lock” the sequence momentarily would give you the tool you need, but I don’t think it’s there.

The use-case where you need a lock on the value so that there can't possibly be a hole in the sequence

OP asked for a way to call setval() with a guarantee the sequence will never go backwards IIUC. His code can check that the new value he wants to set is higher than the current value, but there’s a race condition where a second connection could quickly advance the sequence between the check and the setval() call and then cause duplicates from the next call which is bad.

The ideal solution is a setval_forward_only() or setval_no_duplicates() function that does it atomically or something. If it were possible to “lock” the entire sequence to prevent any other sessions from using it at all, that would work too. Not locking a value, locking the whole thing. Very bad hack solution is renaming the sequence then renaming it back as a blunt form of locking... and to be clear I don’t think is a good idea I just was saying that technically it might work.  :)

-Jeremy

Sent from my TI-83

pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: invalid non-zero objectSubId for object class
Next
From: Tom Lane
Date:
Subject: Re: invalid non-zero objectSubId for object class