Commit effect on WAL-Writer behaviour
От | Subramanian,Ramachandran |
---|---|
Тема | Commit effect on WAL-Writer behaviour |
Дата | |
Msg-id | 55cdbd7dba444b08ba6c66d671248e58@alte-leipziger.de обсуждение исходный текст |
Список | pgsql-novice |
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.
______________________ ALH Gruppe Alte Leipziger Lebensversicherung a. G., Alte Leipziger-Platz 1, 61440 Oberursel 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 (1) Nr. 5 b VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
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
Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG
В списке pgsql-novice по дате отправления: