Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v65RO-00GZxL-Im for pgsql-novice@arkaria.postgresql.org; Tue, 07 Oct 2025 11:01:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v65RM-00G2pa-Ac for pgsql-novice@arkaria.postgresql.org; Tue, 07 Oct 2025 11:01:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v65RL-00G2pR-QO for pgsql-novice@lists.postgresql.org; Tue, 07 Oct 2025 11:01:08 +0000 Received: from mx2.alte-leipziger.de ([91.217.145.232]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v65RG-000Tmm-1c for pgsql-novice@lists.postgresql.org; Tue, 07 Oct 2025 11:01:07 +0000 X-CSE-ConnectionGUID: YzKcw4aQRKSia0n7UDJAbg== X-CSE-MsgGUID: 9bdfFgtZSoKDEhm7TvmNCQ== From: "Subramanian,Ramachandran" To: "pgsql-novice@lists.postgresql.org" Subject: Commit effect on WAL-Writer behaviour Thread-Topic: Commit effect on WAL-Writer behaviour Thread-Index: Adw3dmnEpGTbQmFOSui45p1VaCynsQ== Date: Tue, 7 Oct 2025 11:00:54 +0000 Message-ID: <55cdbd7dba444b08ba6c66d671248e58@alte-leipziger.de> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-tmase-matchedrid: cd4bo9JS5DQRz9YLdsFULH0tCKdnhB58Wyp9qNukZ1BvTOI9YAS51j4C ii3pSpSYJaQokcGSq+gsEFWH07Y1pf1N8S9tF60fwsybRluqYXpk9EiT3DtQkOUH2+bY0IGE x-tm-as-user-approved-sender: No x-tm-as-user-blocked-sender: No x-tmase-result: 10--5.490600-8.000000 x-tm-snts-smtp: AF84E8448459F1F1AC3166077759A4C9E48A1EB20A5DABD28E877417362EAA872000:8 x-alsentwithgood: false Content-Type: multipart/alternative; boundary="_000_55cdbd7dba444b08ba6c66d671248e58alteleipzigerde_" MIME-Version: 1.0 X-GBS-PROC: U4I20n4ULWTIC0V85elGeRGMphhnlfrSNCNmV5+1kG+w+/NfIxz9V1iyMqjEMBAn X-GBS-PROCJOB: =?utf-8?Q?yM/mMpsvfC9qaEcF60TDVWv3Bbw7+RJGQx11u7579hu6OBDBQzv4z4+Ii6BA?= =?utf-8?Q?XmUYrB7a1iv06OHMAsLZgs9PyYji0EAHpBQp9+6jorXlSlMI2rGy3nDFna6E?= =?utf-8?Q?oFb/U+uF/jDi54lLcbTBBr5/wzHMVW1uhbn75QaUA8AvRZGID4s=3D?= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_55cdbd7dba444b08ba6c66d671248e58alteleipzigerde_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, Coming from a Db2 - mainframe world trying to understand Postgres. Kindl= y 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 buffer= s ) and corresponding Undo and Redo Logs are written to the Log buffers. = While TRAN1 is running , TRAN2 TRAN3 .... TRAN4 can run concurrently and be wri= ting 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 ha= ve updated 1 row each and written 2 WAL-Records each in the WAL-Buffer BU= T 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=3F Obviou= sly TRAN1 2 and 3 are IN-FLIGHT ( un committed ) at 0902 Hours, while TR= AN4 is committed. ( This is how DB2 works . When a COMMIT is issued by a= ny 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 t= o 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 lo= grecords with a matching BEGIN UR and END UR are redone, and those with j= ust 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-Fil= es in Postgres=3F If not what exactly does a COMMIT do=3F how can one force a write of the = WAL-Buffers to disk with a SQL command=3F Thank you for your time. Ram Freundliche Gr=FC=DFe 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 Obe= rursel Vors. des Aufsichtsrats: Dr. Walter Botermann =B7 Vorstand: Christoph Boh= n (Vors.), Dr. J=FCrgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Joche= n Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcs= ek Sitz Oberursel (Taunus) =B7 Rechtsform VVaG =B7 Amtsgericht Bad Homburg v= . d. H. HRB 1583 =B7 USt.-IdNr. DE 114106814 =20 Hallesche Krankenversicherung a.G., L=F6ffelstra=DFe 34-38, 70597 Stuttg= art Vors. des Aufsichtsrats: Dr. Walter Botermann =B7 Vorstand: Christoph Boh= n (Vors.), Dr. J=FCrgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Joche= n Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek Sitz Stuttgart =B7 Rechtsform VVaG =B7 Amtsgericht Stuttgart HRB 2686 = =B7 USt.-IdNr. DE 147802285 Beitr=E4ge zu privaten Kranken- und Pflegekrankenversicherungen unterlieg= en nicht der Versicherungsteuer (=A7 4 Nr. 5 VersStG) =B7 Versicherungsle= istungen sowie Ums=E4tze aus Versicherungsvertreter-/Maklert=E4tigkeiten = sind umsatzsteuerfrei =20 =20 Die Pflichtangaben der ALH Gruppe gem=E4=DF =A7 35a GmbHG bzw. =A7 80 Akt= G finden Sie hier: https://www.alte-leipziger.de/impressum=20 ______________________ 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 --_000_55cdbd7dba444b08ba6c66d671248e58alteleipzigerde_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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/del= etes 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 concurren= tly 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 TRA= N3 and TRAN4 have updated 1 row each and written 2 WAL-Records each in th= e 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 f= iles=3F  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 d= isk, 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 ea= ch transaction . Each Unit of Recovery is an unique identifier. Every log record that belongs to this Unit of R= ecovery ID has this identifier in it.  So after a crash, the logs ar= e scanned forward since the last check point and only those logrecords wi= th a matching BEGIN UR and END UR are redone, and those with just a BEGIN UR and no matching END UR are rolled back. <= o:p>

 

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

 

If not what exactly does a COMMIT do=3F how can one force a wri= te of the WAL-Buffers to disk with a SQL command=3F

 

 

 

 

Thank you for your time.

 

 

Ram

 

 


Freundliche Gr=FC=DFe


i. A. Ramachandran Subrama= nian =20

Zentralbereich Informationst= echnologie

=20

Alte Leipziger Lebensversicherung a. G.<= /SPAN>

Hallesche Krankenversicherung a. G.

=20

______________________

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

Alte Leipziger Lebensversich= erung a. G., Alte Leipziger-Platz 1, 61440 Oberursel

Vors. des Aufsichtsra= ts: Dr. Walter Botermann =B7 Vorstand: Christoph Bohn (Vors.), Dr. J=FCrg= en Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexand= er Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek

Sitz Oberursel (Taunus) =B7 = Rechtsform VVaG =B7 Amtsgericht Bad Homburg v. d. H. HRB 1583 =B7 USt.-Id= Nr. DE 114106814


Hallesche Krankenversicherun= g a. G., L=F6ffelstra=DFe 34-38, 70597 Stuttgart

Vors. des Aufsichtsrats: Dr.= Walter Botermann =B7 Vorstand: Christoph Bohn (Vors.), Dr. J=FCrgen Bier= baum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Maye= r, Christian Pape, Wiltrud Pekarek, Udo Wilcsek

Sitz Stuttgart =B7 Rechtsfor= m VVaG =B7 Amtsgericht Stuttgart HRB 2686 =B7 USt.-IdNr. DE 147802285

Beitr=E4ge zu privaten Krank= en- und Pflegekrankenversicherungen unterliegen nicht der Versicherungste= uer (=A7 4 (1) Nr. 5 b VersStG) =B7 Versicherungsleistungen sowie Ums=E4t= ze aus Versicherungsvertreter-/Maklert=E4tigkeiten sind umsatzsteuerfrei<= /SPAN>


= Pflichtangaben der A= LH Gruppe gem=E4=DF =A7 35a GmbHG bzw. =A7 80 AktG --_000_55cdbd7dba444b08ba6c66d671248e58alteleipzigerde_--