Re: BigSerial and txid issuance

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

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