Thread

Commit effect on WAL-Writer behaviour

Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2025-10-07T11:00:54Z

Hello,



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.


  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?  Obviously TRAN1 2 and 3 are  IN-FLIGHT ( un committed ) at 0902 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.

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

If not what exactly does a COMMIT do? how can one force a write of the WAL-Buffers to disk with a SQL command?




Thank you for your time.


Ram




Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.





Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814





 
Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
 



 
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 





______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de