public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Subramanian,Ramachandran <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Effect of COMMIT on WAL-Buffers + Effect of Check pointer.
Date: Tue, 07 Oct 2025 15:49:28 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Effect of COMMIT on WAL-Buffers + Effect of Check pointer.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox