Message-ID: <ee1118fcd30c4e0c7b4dd21681a14a46a669d7be.camel@cybertec.at>
Subject: Re: Effect of COMMIT on WAL-Buffers + Effect of Check pointer.
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Subramanian,Ramachandran" <ramachandran.subramanian@alte-leipziger.de>,
  "pgsql-admin@lists.postgresql.org"	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 07 Oct 2025 15:49:28 +0200
In-Reply-To: <e28f45c7ba80434cadaf76cc5f54174b@alte-leipziger.de>
References: <e28f45c7ba80434cadaf76cc5f54174b@alte-leipziger.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ee1118fcd30c4e0c7b4dd21681a14a46a669d7be.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-10-07 at 11:22 +0000, Subramanian,Ramachandran wrote:
> Coming from a Db2 =E2=80=93 mainframe world trying to understand Postgres=
.
> Kindly forgive my ignorance and the somewhat long winded question. =20
> =20
> 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 =E2=80=A6. TRAN=
4
> can run concurrently and be writing information tot he WAL-Buffers.

There are no undo logs.  PostgreSQL doesn't ever undo work.
Otherwise, yes.
 =20
> Let us assume that TRAN1 began at 0000 Hours and at has updated 1
> rows at 0001 Hours.
>=20
> 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.
>=20
> Now at 0002 Hours TRAN4 alone has issued a COMMIT.
>=20
> 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.
 =20
> 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.
 =20
> 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