9.5. Writing of XLOG Records

This section provides a comprehensive overview of the process for writing XLOG records.

First, consider the following statement to explore PostgreSQL internals:

testdb=# INSERT INTO tbl VALUES ('A');

Executing this statement invokes the internal function exec_simple_query().

The pseudocode of exec_simple_query() is shown below:

exec_simple_query() @postgres.c

(1) ExtendCLOG() @clog.c                  /* Write the state of this transaction
                                           * "IN_PROGRESS" to the CLOG.
                                           */
(2) heap_insert()@heapam.c                /* Insert a tuple, creates a XLOG record,
                                           * and invoke the function XLogInsert.
                                           */
(3)   XLogInsert() @xloginsert.c (9.4 or earlier, xlog.c)
                                          /* Write the XLOG record of the inserted tuple
                                           *  to the WAL buffer, and update page's pd_lsn.
                                           */
(4) finish_xact_command() @postgres.c     /* Invoke commit action.*/
      XLogInsert() @xloginsert.c (9.4 or earlier, xlog.c)
                                          /* Write a XLOG record of this commit action
                                           * to the WAL buffer.
                                           */
(5)   XLogWrite() @xloginsert.c (9.4 or earlier, xlog.c)
                                          /* Write and flush all XLOG records on
                                           * the WAL buffer to WAL segment.
                                           */
(6) TransactionIdCommitTree() @transam.c  /* Change the state of this transaction
                                           * from "IN_PROGRESS" to "COMMITTED"
                                           * on the CLOG.
                                           */

The following descriptions explain each line of the pseudocode to illustrate XLOG record writing. Figures 9.12 and 9.13 provide visual representations of this process.

• (1) The function ExtendCLOG() writes the transaction state ‘IN_PROGRESS’ in the (in-memory) CLOG.

• (2) The function heap_insert() inserts a heap tuple into the target page in the shared buffer pool, creates the XLOG record for that page, and invokes XLogInsert().

• (3) The function XLogInsert() writes the XLOG record, created by heap_insert(), to the WAL buffer at LSN_1. It then updates the modified page’s pd_lsn from LSN_0 to LSN_1.

• (4) The function finish_xact_command() executes to commit this transaction. It creates the XLOG record for the commit action, and then XLogInsert() writes this record to the WAL buffer at LSN_2.

• (5) The function XLogWrite() writes and flushes all XLOG records from the WAL buffer to the WAL segment file.

  If the wal_sync_method parameter is set to ‘open_sync’ or ‘open_datasync’, the records are written synchronously. In this case, the function writes all records using the open() system call with the ‘O_SYNC’ or ‘O_DSYNC’ flag.

  If the parameter is set to ‘fsync’, ‘fsync_writethrough’, or ‘fdatasync’, the system executes the respective system call: fsync(), fcntl() with the F_FULLFSYNC option, or fdatasync().

  These calls ensure all XLOG records are written into storage.

• (6) The function TransactionIdCommitTree() changes the transaction state from ‘IN_PROGRESS’ to ‘COMMITTED’ on the CLOG.

In the above example, the commit action triggered the writing of XLOG records to the WAL segment. However, such writing occurs in any of the following cases:

1. A running transaction commits or aborts.

2. The WAL buffer becomes full. (The WAL buffer size depends on the wal_buffers parameter.)

3. A WAL writer process writes periodically. (See Section 9.6.1.)

If any of these occur, all WAL records in the WAL buffer are written into a WAL segment file regardless of the commit status of their transactions.

9.5.1. Remark on Writing XLOG records

DML (Data Manipulation Language) operations typically generate XLOG records, but non-DML operations can also create them.

For instance:

• A commit action writes an XLOG record containing the ID of the committed transaction.
• A checkpoint action writes an XLOG record containing general information about the checkpoint.

In special cases, even SELECT statements generate XLOG records:

• A SELECT FOR UPDATE statement generates XLOG records for all target tuple locks (ROW SHARE LOCK) ¹.
• During Heap-Only Tuple (HOT) operations, the system writes XLOG records of tuple deletion and page defragmentation to the WAL buffer.

Additionally, if the wal_level parameter is set to ‘replica’ or higher, PostgreSQL also records ACCESS EXCLUSIVE LOCKS as XLOG records. This occurs when an ACCESS EXCLUSIVE LOCK is explicitly acquired using the LOCK command, or when commands like DROP TABLE and TRUNCATE execute. Refer to Section 11.2.4 for details.