Re: Effect of COMMIT on WAL-Buffers + Effect of Check pointer. - Mailing list pgsql-admin

On Tue, 2025-10-07 at 11:22 +0000, Subramanian,Ramachandran wrote:
> Coming from a Db2 – mainframe world trying to understand Postgres.
> Kindly forgive my ignorance and the somewhat long winded question.
>
> When a particular transaction TRAN1, inserts/updates/deletes data,
> the changes are made to the memory blocks in the  Shared Buffer
> ( data buffers ) and corresponding Undo and Redo Logs are written
> to the Log buffers.  While TRAN1 is running , TRAN2 TRAN3 …. TRAN4
> can run concurrently and be writing information tot he WAL-Buffers.

There are no undo logs.  PostgreSQL doesn't ever undo work.
Otherwise, yes.

> Let us assume that TRAN1 began at 0000 Hours and at has updated 1
> rows at 0001 Hours.
>
> Let us further assume for simplicity that TRAN1 TRAN2 TRAN3 and
> TRAN4 have updated 1 row each and written 2 WAL-Records each in the
> WAL-Buffer BUT  NOT issued a COMMIT yet.
>
> Now at 0002 Hours TRAN4 alone has issued a COMMIT.
>
> Will all the 8  WAL-Buffer records be written to the WAL files?

Yes, unless "wal_writer_delay" has expired first, and the WAL
writer process has already written the data.

> Obviously TRAN1 2 and 3 are  IN-FLIGHT ( un committed ) at 0002
> Hours, while TRAN4 is committed.  ( This is how DB2 works . When a
> COMMIT is issued by any transaction ALL the log buffers are written
> to disk, immaterial of if they are commited or not. There is a BEGIN
> Unit of Recovery Log record, a END Unit of Recovery log Record
> associated with each transaction . Each Unit of Recovery is an
> unique identifier. Every log record that belongs to this Unit of
> Recovery ID has this identifier in it.  So after a crash, the logs
> are scanned forward since the last check point and only those
> logrecords with a matching BEGIN UR and END UR are redone, and
> those with just a BEGIN UR and no matching END UR are rolled back.

In PostgreSQL, *all* the WAL is replayed, even the changes from
uncommitted transactions.

Nothing is ever rolled back.

Instead, PostgreSQL records the status of all transactions in the
*commit log*.  Changes from transactions that didn't commit simple
remain invisible, and the next VACUUM run will clean them up.

> Does a COMMIT even cause the ALL the WAL-Buffers to be written to
> WAL-Files in Postgres?

All the WAL up to and including the COMMIT, yes.

> Additionally, after the check pointer externalizes all the comitted
> Shared Buffer Data to disk, does it write a check point record to
> the WAL-Buffer alone?

Yes, and that is flushed to disk.

> if the check point information is just written to the WAL-Buffer
> by the Check-Pointer background process and before it is copied down
> to a file on the disk,  Postgres crashes, is this check point not lost ?
> Does a Check point record force a WAL-Buffer write to WAL-Files ?

Sure.  Once a checkpoint is complete, all data it wrote are safely
on disk.

Yours,
Laurenz Albe