PG mvcc sample case
mydb=# select * from car;
id | name
----+------
1 | aa
2 | bb
3 | cc
4 | ddd
(4 rows)
Time: 0.592 ms
mydb=# select *,ctid,xmin,xmax from car;
id | name | ctid | xmin | xmax
----+------+--------+------+------
1 | aa | (0,41) | 1162 | 0
2 | bb | (0,42) | 1162 | 0
3 | cc | (0,43) | 1162 | 0
4 | ddd | (0,44) | 1162 | 1168
(4 rows)
Time: 0.198 ms
mydb=# \df+ pg_switch_wal
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------------+------------------+---------------------+------+------------+----------+----------+----------+---------------------+----------+---------------+------------------------
pg_catalog | pg_switch_wal | pg_lsn | | func | volatile | safe | postgres | invoker | postgres=X/postgres | internal | pg_switch_wal | switch to new wal file
(1 row)
mydb=# select pg_switch_wal();
pg_switch_wal
---------------
0/19006D98
(1 row)
Time: 56.363 ms
mydb=# select *,ctid,xmin,xmax from car;
id | name | ctid | xmin | xmax
----+------+--------+------+------
1 | aa | (0,41) | 1162 | 0
2 | bb | (0,42) | 1162 | 0
3 | cc | (0,43) | 1162 | 0
4 | ddd | (0,44) | 1162 | 1168
(4 rows)
Time: 0.294 ms
mydb=# select pg_current_xact_id();
pg_current_xact_id
--------------------
1174
(1 row)
Time: 0.480 ms
mydb=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_NAME_OFFSET(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+--------------------------------
0/1A0000C0 | 00000002000000000000001A | (00000002000000000000001A,192)
(1 row)
Time: 0.207 ms
mydb=# begin;
BEGIN
Time: 0.067 ms
mydb=*# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_NAME_OFFSET(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+--------------------------------
0/1A0000C0 | 00000002000000000000001A | (00000002000000000000001A,192)
(1 row)
Time: 0.113 ms
mydb=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
1175
(1 row)
Time: 0.104 ms
mydb=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
1175
(1 row)
Time: 0.101 ms
mydb=*# select *,ctid,xmin,xmax from car;
id | name | ctid | xmin | xmax
----+------+--------+------+------
1 | aa | (0,41) | 1162 | 0
2 | bb | (0,42) | 1162 | 0
3 | cc | (0,43) | 1162 | 0
4 | ddd | (0,44) | 1162 | 1168
(4 rows)
Time: 0.391 ms
mydb=*# select *,ctid,xmin,xmax from car;
id | name | ctid | xmin | xmax
----+------+--------+------+------
1 | aa | (0,41) | 1162 | 0
2 | bb | (0,42) | 1162 | 0
3 | cc | (0,43) | 1162 | 0
4 | ddd | (0,44) | 1162 | 1168
(4 rows)
Time: 0.177 ms
mydb=*# update car set name = 'dd' where id =4 ;
UPDATE 1
Time: 0.511 ms
mydb=*# select *,ctid,xmin,xmax from car;
id | name | ctid | xmin | xmax
----+------+--------+------+------
1 | aa | (0,41) | 1162 | 0
2 | bb | (0,42) | 1162 | 0
3 | cc | (0,43) | 1162 | 0
4 | dd | (0,46) | 1175 | 0
(4 rows)
Time: 0.164 ms
mydb=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
1175
(1 row)
Time: 0.100 ms
mydb=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
1175
(1 row)
Time: 0.218 ms
mydb=*# commit;
COMMIT
Time: 3.861 ms
mydb=# select pg_current_xact_id();
pg_current_xact_id
--------------------
1177
(1 row)
Time: 0.532 ms
mydb=# select pg_current_xact_id();
pg_current_xact_id
--------------------
1178
(1 row)
Time: 0.233 ms
wal日志：
mydb=# select pg_relation_filenode('car');
pg_relation_filenode
----------------------
34055
(1 row)
Time: 0.586 ms
mydb=# select pg_relation_filepath('car');
pg_relation_filepath
----------------------
base/16384/34055
(1 row)
[postgres@harbor pg_wal]$ pg_waldump -f 00000002000000000000001A
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1A000028, prev 0/19006D80, desc: RUNNING_XACTS nextXid 1174 latestCompletedXid 1173 oldestRunningXid 1174
rmgr: Transaction len (rec/tot): 34/ 34, tx: 1174, lsn: 0/1A000060, prev 0/1A000028, desc: COMMIT 2022-07-20 13:49:22.793491 CST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1A000088, prev 0/1A000060, desc: RUNNING_XACTS nextXid 1175 latestCompletedXid 1174 oldestRunningXid 1175
rmgr: Heap len (rec/tot): 65/ 1745, tx: 1175, lsn: 0/1A0000C0, prev 0/1A000088, desc: HOT_UPDATE off 44 xmax 1175 flags 0x00 ; new off 46 xmax 0, blkref #0: rel 1663/16384/34055 blk 0 FPW
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/1A000798, prev 0/1A0000C0, desc: RUNNING_XACTS nextXid 1176 latestCompletedXid 1174 oldestRunningXid 1175; 1 xacts: 1175
rmgr: Transaction len (rec/tot): 34/ 34, tx: 1176, lsn: 0/1A0007D0, prev 0/1A000798, desc: COMMIT 2022-07-20 13:50:54.674831 CST
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/1A0007F8, prev 0/1A0007D0, desc: RUNNING_XACTS nextXid 1177 latestCompletedXid 1176 oldestRunningXid 1175; 1 xacts: 1175
rmgr: Transaction len (rec/tot): 34/ 34, tx: 1175, lsn: 0/1A000830, prev 0/1A0007F8, desc: COMMIT 2022-07-20 13:51:43.477334 CST
rmgr: Transaction len (rec/tot): 34/ 34, tx: 1177, lsn: 0/1A000858, prev 0/1A000830, desc: COMMIT 2022-07-20 13:51:45.690891 CST
rmgr: Transaction len (rec/tot): 34/ 34, tx: 1178, lsn: 0/1A000880, prev 0/1A000858, desc: COMMIT 2022-07-20 13:51:49.435323 CST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1A0008A8, prev 0/1A000880, desc: RUNNING_XACTS nextXid 1179 latestCompletedXid 1178 oldestRunningXid 1179
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1A0008E0, prev 0/1A0008A8, desc: RUNNING_XACTS nextXid 1179 latestCompletedXid 1178 oldestRunningXid 1179
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/1A000918, prev 0/1A0008E0, desc: CHECKPOINT_ONLINE redo 0/1A0008E0; tli 2; prev tli 2; fpw true; xid 0:1179; oid 34444; multi 1; offset 0; oldest xid 726 in DB 13892; oldest multi 1 in DB 16384; oldest/newest commit timestamp xid: 0/0; oldest running xid 1179; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1A000990, prev 0/1A000918, desc: RUNNING_XACTS nextXid 1179 latestCompletedXid 1178 oldestRunningXid 1179
