Re: BigSerial and txid issuance

On 7/8/20 7:09 AM, Yorwerth, Adam wrote:
> Hi Everyone,
>
> We’re trying to solve a problem that relies on BigSerial and txid (as
> returned by txid_current() ) values being issued consistently.
>
> Is it possible for two transactions to interleave their issuance of
> these two variables?
>
> For example:
>
> Schema:
>
> CREATE TABLE EXAMPLE(
>   offset bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
>   txid BIGINT NOT NULL DEFAULT 1
> );
>
> Insert statement:
>
> "INSERT INTO EVENTS (txid) VALUES (txid_current());";
>
> Prior to transactions executing offset is 10 and txid is 1000.
>
> Transaction 1 and 2 occur concurrently.
>
> Is it possible for transaction 1 to be issued txid 1001 and offset 12
> and transaction 2 to be issued txid 1002 and offset 11?

Well IDENTITY is backed by a SEQUENCE and:

https://www.postgresql.org/docs/12/sql-createsequence.html

"Unexpected results might be obtained if a cache setting greater than
one is used for a sequence object that will be used concurrently by
multiple sessions. Each session will allocate and cache successive
sequence values during one access to the sequence object and increase
the sequence object's last_value accordingly. Then, the next cache-1
uses of nextval within that session simply return the preallocated
values without touching the sequence object. So, any numbers allocated
but not used within a session will be lost when that session ends,
resulting in “holes” in the sequence.

Furthermore, although multiple sessions are guaranteed to allocate
distinct sequence values, the values might be generated out of sequence
when all the sessions are considered. For example, with a cache setting
of 10, session A might reserve values 1..10 and return nextval=1, then
session B might reserve values 11..20 and return nextval=11 before
session A has generated nextval=2. Thus, with a cache setting of one it
is safe to assume that nextval values are generated sequentially; with a
cache setting greater than one you should only assume that the nextval
values are all distinct, not that they are generated purely
sequentially. Also, last_value will reflect the latest value reserved by
any session, whether or not it has yet been returned by nextval."

>
> Any help would be much appreciated.
>
> Regards,
>
> Adam
>
> This is a confidential email. Tesco may monitor and record all emails.
> The views expressed in this email are those of the sender and not Tesco.
> Tesco Stores Limited Company Number: 519500 Registered in England
> Registered Office: Tesco House, Shire Park, Kestrel Way, Welwyn Garden
> City, AL7 1GA VAT Registration Number: GB 220 4302 31

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com