PostgreSQL pg_current_xlog_insert_location()与pg_current_xlog_location()

PostgreSQL pg_current_xlog_insert_location()与pg_current_xlog_location() pg_current_xlog_insert_location指写入wal buffer的位置.pg_current_xlog_location返回已经write到wal文件的位置.我们来做个实验验证一下。查看目前两个函数结果是否相同。highgo=#  select pg_current_xlog_insert_location(),pg_current_xlog_location(); pg_current_xlog_insert_location | pg_current_xlog_location ---------------------------------+-------------------------- 0/B9000098                      | 0/B9000098(1 row)使用异步提交可以看到这个差异。synchronous_commit = off    #关闭同步提交wal_writer_delay = 10000ms  #开启延迟10000ms （1-10000 milliseconds）在一个窗口批量插入数据，打开另外的窗口查看。highgo=# insert into tbl_kenyon select generate_series(1,1000000),'kenyon good',md5('kenyon good boy');INSERT 0 1000000highgo=#  select pg_current_xlog_insert_location(),pg_current_xlog_location(); pg_current_xlog_insert_location | pg_current_xlog_location ---------------------------------+-------------------------- 0/C0379BE0                      | 0/BF37A000(1 row)通过以下函数，查看具体的xlog文件，可以发现两者对应的xlog文件不同。highgo=# select pg_xlogfile_name_offset('0/C0379BE0'),pg_xlogfile_name_offset('0/BF37A000');      pg_xlogfile_name_offset       |      pg_xlogfile_name_offset       ------------------------------------+------------------------------------ (0000000B00000000000000C0,3644384) | (0000000B00000000000000BF,3645440)(1 row)等待一段时间后，会发现两者再次相同。highgo=#  select pg_current_xlog_insert_location(),pg_current_xlog_location(); pg_current_xlog_insert_location | pg_current_xlog_location ---------------------------------+-------------------------- 0/C0379BE0                      | 0/C0379BE0(1 row)highgo=# select pg_xlogfile_name_offset('0/C0379BE0');      pg_xlogfile_name_offset       ------------------------------------ (0000000B00000000000000C0,3644384)(1 row)如上所示，也就是说pg_current_xlog_insert_location()是领先于pg_current_xlog_location()的。这也正好符合预期。因为数据变化会先写入wal buffer，然后再写入wal文件。参考德哥博客：http://blog.163.com/digoal@126/blog/static/1638770402014395222222/