Effect of COMMIT on WAL-Buffers + Effect of Check pointer.

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 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.

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?

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?

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 ?

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(dot)subramanian(at)alte-leipziger(dot)de
www.alte-leipziger.de
www.hallesche.de