Thread: BigSerial and txid issuance

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

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.klaver@aklaver.com

On Wed, 2020-07-08 at 14:09 +0000, Yorwerth, Adam wrote:
> We’re trying to solve a problem that relies on BigSerial and txid (as returned by txid_current() ) values being
issuedconsistently.
 
> 
> 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
offset11?
 

Sure.

Transaction IDs are assigned when a transaction is about to modify data,
and for identity columns you get the next value when the backing sequence is called.

There is no guarantee that both have to happen in the same order?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com