PostgreSQL UPDATE 之后旧行还在吗:用 pageinspect 看 xmin、xmax 和 ctid,DELETE 与 ROLLBACK 各留下什么,只更新非索引列 1000 次表仍是 1 页

    SQL> create table t(id int primary key, v text)
      CREATE TABLE
    SQL> insert into t values (1, 'a')
      INSERT 0 1
    SQL> select ctid, xmin, xmax, * from t
      ctid  | xmin | xmax | id | v 
      -------+------+------+----+---
       (0,1) |  732 |    0 |  1 | a
      (1 row)
    SQL> select lp, lp_flags, t_xmin as xmin, t_xmax as xmax, t_ctid, array_to_string((heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags, ',') as flags from heap_page_items(get_raw_page('t', 0)) order by lp
      lp | lp_flags | xmin | xmax | t_ctid |               flags                
      ----+----------+------+------+--------+------------------------------------
        1 |        1 |  732 |    0 | (0,1)  | HEAP_HASVARWIDTH,HEAP_XMAX_INVALID
      (1 row)

    SQL> update t set v = 'b' where id = 1
      UPDATE 1
    SQL> select ctid, xmin, xmax, * from t
      ctid  | xmin | xmax | id | v 
      -------+------+------+----+---
       (0,2) |  733 |    0 |  1 | b
      (1 row)
    SQL> select lp, lp_flags, t_xmin as xmin, t_xmax as xmax, t_ctid, array_to_string((heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags, ',') as flags from heap_page_items(get_raw_page('t', 0)) order by lp
      lp | lp_flags | xmin | xmax | t_ctid |                              flags                              
      ----+----------+------+------+--------+-----------------------------------------------------------------
        1 |        1 |  732 |  733 | (0,2)  | HEAP_HASVARWIDTH,HEAP_HOT_UPDATED
        2 |        1 |  733 |    0 | (0,2)  | HEAP_HASVARWIDTH,HEAP_XMAX_INVALID,HEAP_UPDATED,HEAP_ONLY_TUPLE
      (2 rows)
  表面上只有 1 行,但堆页里有 2 个行指针:
    SQL> select count(*) as 行指针数 from heap_page_items(get_raw_page('t', 0))
      行指针数 
      ----------
              2
      (1 row)

    SQL> create table d(id int primary key, v text)
      CREATE TABLE
    SQL> insert into d values (1, 'x')
      INSERT 0 1
    会话 A:begin isolation level repeatable read; select count(*) from d -> 1
    SQL> delete from d where id = 1
      DELETE 1
    会话 B:delete from d where id = 1(已提交)
    会话 A(事务还没结束):select count(*) from d -> 1
    新会话 C:select count(*) from d -> 0
    会话 A 提交之后:select count(*) from d -> 0
    SQL> select lp, lp_flags, t_xmin as xmin, t_xmax as xmax, t_ctid, array_to_string((heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags, ',') as flags from heap_page_items(get_raw_page('d', 0)) order by lp
      lp | lp_flags | xmin | xmax | t_ctid |                         flags                          
      ----+----------+------+------+--------+--------------------------------------------------------
        1 |        1 |  735 |  736 | (0,1)  | HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_KEYS_UPDATED
      (1 row)
  行还在堆页里(xmax 已经是删除它的事务号),只是没有人能再看到它,等 VACUUM 来回收

    SQL> create table r(id int primary key, v text)
      CREATE TABLE
    SQL> insert into r values (1, 'keep')
      INSERT 0 1
    SQL> select ctid, xmin, xmax, * from r
      ctid  | xmin | xmax | id |  v   
      -------+------+------+----+------
       (0,1) |  738 |  739 |  1 | keep
      (1 row)
    SQL> select lp, lp_flags, t_xmin as xmin, t_xmax as xmax, t_ctid, array_to_string((heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags, ',') as flags from heap_page_items(get_raw_page('r', 0)) order by lp
      lp | lp_flags | xmin | xmax | t_ctid |                                       flags                                       
      ----+----------+------+------+--------+-----------------------------------------------------------------------------------
        1 |        1 |  738 |  739 | (0,2)  | HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID,HEAP_HOT_UPDATED
        2 |        1 |  739 |    0 | (0,2)  | HEAP_HASVARWIDTH,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED,HEAP_ONLY_TUPLE
      (2 rows)
  回滚后页里仍然有 2 个版本;读 r 时可见的只有 xmin 已提交的那个版本(ctid (0,1))

    SQL> create table u(id int primary key, v int) with (fillfactor = 100)
      CREATE TABLE
    SQL> insert into u values (1, 0)
      INSERT 0 1
  更新 1000 次之后:
    SQL> select count(*) as 行指针数, count(*) filter (where lp_flags = 1) as 正常指针, count(*) filter (where lp_flags = 2) as 重定向指针, count(*) filter (where lp_flags = 0) as 未使用指针 from heap_page_items(get_raw_page('u', 0))
      行指针数 | 正常指针 | 重定向指针 | 未使用指针 
      ----------+----------+------------+------------
            204 |      188 |          1 |         15
      (1 row)
  表大小:更新前 8192 字节,更新后 8192 字节;页数 1
    SQL> select n_tup_upd, n_tup_hot_upd, n_dead_tup from pg_stat_user_tables where relname = 'u'
      n_tup_upd | n_tup_hot_upd | n_dead_tup 
      -----------+---------------+------------
            1000 |          1000 |        187
      (1 row)