How RDBMs like PostgreSQL implement durable sequences and yet not be parts of normal transactions?

Question

In PostgreSQL, you can rollback a transaction and the sequences continue forward.

But then how are the Sequences stored in a durable way? I wanna know how this is implemented (I'm doing an RDBMs) and it looks to me as if exist 2 databases, one for sequences and another for regular tables.

So, it looks like things work this way:


BEGIN TRANSACTION

INSERT table (id, name) values (
   next = sequence.next()
   COMMIT(sequence)
   ,
   "hello"
);

ROLLBACK TRANSACTION

But how the inner COMMIT not get rolled back?

Postgres is open source and so is [their implementation](https://github.com/postgres/postgres/blob/master/src/backend/commands/sequence.c) of a sequence — , Oct 12 '22 at 05:54

Laurenz Albe · Answer 1 · 2022-10-12T20:02:50.847

As with all other objects, changes to sequences are logged to WAL, so that recovery can restore the state from a backup or after a crash. Since writing WAL impacts performance, not each call to nextval will log to WAL. Rather, the first call logs a value 32 numbers ahead of the current value, and the next 32 calls to nextval don’t log anything.

You can find this information and more on the topic of sequences and transactions in my article.

Since you wonder how the COMMIT for sequences works in the case of a ROLLBACK: there is no special commit for sequences. A WAL record is logged and replayed. In PostgreSQL, transactions are replayed during recovery, no matter if the transaction is committed or not. Normal table modifications become "invisible" if the transaction is rolled back. But the modifications to the sequence are in place (the sequence row gets overwritten), and nothing becomes invisible after a rollback.

What is puzzling to me is how the COMMIT for sequences works in the case of a ROLLBACK. — mamcx, Oct 12 '22 at 19:27

How RDBMs like PostgreSQL implement durable sequences and yet not be parts of normal transactions?

1 Answers1