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 1v65mP-00Gf1Q-FX for pgsql-admin@arkaria.postgresql.org; Tue, 07 Oct 2025 11:22:53 +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 1v65mN-00GCuy-6o for pgsql-admin@arkaria.postgresql.org; Tue, 07 Oct 2025 11:22:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v65mM-00GCuq-PS for pgsql-admin@lists.postgresql.org; Tue, 07 Oct 2025 11:22:51 +0000 Received: from mx1.alte-leipziger.de ([91.217.145.231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v65mL-000qfT-0u for pgsql-admin@lists.postgresql.org; Tue, 07 Oct 2025 11:22:51 +0000 X-CSE-ConnectionGUID: L9JU/jWISBuJ787iNLSBSQ== X-CSE-MsgGUID: OhI3xXbyTwm7/G8OPCNyMg== From: "Subramanian,Ramachandran" To: "pgsql-admin@lists.postgresql.org" Subject: Effect of COMMIT on WAL-Buffers + Effect of Check pointer. Thread-Topic: Effect of COMMIT on WAL-Buffers + Effect of Check pointer. Thread-Index: Adw3fCPS546bqWs5SD6VerwvSRHG2Q== Date: Tue, 7 Oct 2025 11:22:43 +0000 Message-ID: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-tmase-matchedrid: PWKaPROO/YTkUB2YvI/ISwLidf65bNOXnHp0x5N6+xp1koH+sxwE+oJg 639c6bzEc3tla7jyNRZRAISAOsYkGeS62XWjuGwGHY/XeJlZKEUtzM8wdlz5DiTG1CpdES1kKF1 2+S6s3LCgixYqzOpNoiUQMvscwA3PA3t2za5okkBSKsXuA7t++TLai1uomgFq7YM/Pkp/zcA7KW IJsdWWxQpvh5lP2+jpeupR0OokItWDXnUNoIzPJxTRT6kU85pkjQmHD3Vo9Y6KUTQCH/QWFEaxx mOltC4v7qcd+5ntYbsFLvyziEfY0WWoTITjvA/oXNg6ACrBUzLOYb2maDydwklccemnFwv6AcqX fI3o+U++Y1/jBavGOioYghv6Nkw7Ad4fHpu8vnb4Mqyu6QZrLCtZLGvMAxm5BK4oo7tJnZHG97i 9pfCXPnORN81Ytt5p4SOao2eREbqjqyh/dGUGEsIAdvxcIhsGTen+5ZKqytFSpMzrff6lL56/nV saZPBIBfCx8jZ9Xfmi3rC9UbXBOp3IB1QxOCarWj/U/cEOmX8lPfchMsX8ywq5sd2MOgPohZwLn rpgQJjzsIInDJae2XTshITrK7E5D5teccobfJWT1WqWAtz5rEtY8CrYUSB0paOKCbE3QQ5ntwqw mEDLoHY9CnnSH7Yvy9J1SLLyTYZ9DKTnpx1AdBTAYSXzq3IhftwZ3X11IV0= x-tm-as-user-approved-sender: No x-tm-as-user-blocked-sender: No x-tmase-result: 10--22.969200-8.000000 x-tm-snts-smtp: 30D440012B578750214A903A5925463D3F23BF8CC5BA9DC35F4ADC19074E35252000:8 x-alsentwithgood: false Content-Type: multipart/alternative; boundary="_000_e28f45c7ba80434cadaf76cc5f54174balteleipzigerde_" MIME-Version: 1.0 X-GBS-PROC: EHofQTKKgmLjDkEgqCvtNa8znYLEDOK2ndv8y7xiPtc0Mq8Ne44fi080fxgbLIUe X-GBS-PROCJOB: =?utf-8?Q?l5RXSH8EcAyAR36X3tU26hoGWOS8F2x6v4v+FTitWoEjMlLF0ONaZyv9JdN+?= =?utf-8?Q?DVO/okSc9WCTuVbunouJRcZGd6QEycNKX3R8ZUloG3rPK6NDbbexv1b2IboK?= =?utf-8?Q?ICe7JZH1P3PkIF8m78aoiXvX5yeeoDDpsskqRexMCsPfFd6iPIU=3D?= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_e28f45c7ba80434cadaf76cc5f54174balteleipzigerde_ 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 0002 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 Additionally, after the check pointer externalizes all the comitted Share= d Buffer Data to disk, does it write a check point record to the WAL-Buff= er alone=3F 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 o= n the disk, Postgres crashes, is this check point not lost =3F Does a = Check point record force a WAL-Buffer write to WAL-Files =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_e28f45c7ba80434cadaf76cc5f54174balteleipzigerde_ 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 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 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

 

 

 


Additionally, after the check pointer externalizes all the comitted Share= d Buffer Data to disk, does it write a check point record to the WAL-Buff= er alone=3F

 

if the check point information is just written to the WAL-Buffe= r  by the Check-Pointer background process and before it is copied d= own to a file on the disk,  Postgres crashes, is this check point not lost =3F   Does a Check point record force a WAL-Buffer wr= ite to WAL-Files =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_e28f45c7ba80434cadaf76cc5f54174balteleipzigerde_--