
Воспроизвести HOT-обновление
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Создаем базу данных и таблицу.

psql 

=> create database db5;
CREATE DATABASE

=> \c db5
You are now connected to database "db5" as user "postgres".

=> create extension pageinspect;
CREATE EXTENSION

=> create table t(id serial, s text) with (fillfactor=80);
CREATE TABLE

=> create index t_s on t(id);
CREATE INDEX

Создадим представление для просмотра нулевой страницы:

=> create view t0_v as
=>   select '(0,'||lp||')' as ctid,
=>          case lp_flags
=>            when 0 then 'unused'
=>            when 1 then 'normal'
=>            when 2 then 'redirect to '||lp_off
=>            when 3 then 'dead'
=>          end as state,
=>          t_xmin as xmin,
=>          t_xmax as xmax,
=>          case when (t_infomask & 256) > 0 then 't' end as xmin_c,
=>          case when (t_infomask & 512) > 0 then 't' end as xmin_a,
=>          case when (t_infomask & 1024) > 0 then 't' end as xmax_c,
=>          case when (t_infomask & 2048) > 0 then 't' end as xmax_a,
=>          case when (t_infomask2 & 16384) > 0 then 't' end as hhu,
=>          case when (t_infomask2 & 32768) > 0 then 't' end as hot,
=>          t_ctid
=>   from heap_page_items(get_raw_page('t',0))
=>   order by lp;
CREATE VIEW

И такое же для первой страницы:

=> create view t1_v as
=>   select '(1,'||lp||')' as ctid,
=>          case lp_flags
=>            when 0 then 'unused'
=>            when 1 then 'normal'
=>            when 2 then 'redirect to '||lp_off
=>            when 3 then 'dead'
=>          end as state,
=>          t_xmin as xmin,
=>          t_xmax as xmax,
=>          case when (t_infomask & 256) > 0 then 't' end as xmin_c,
=>          case when (t_infomask & 512) > 0 then 't' end as xmin_a,
=>          case when (t_infomask & 1024) > 0 then 't' end as xmax_c,
=>          case when (t_infomask & 2048) > 0 then 't' end as xmax_a,
=>          case when (t_infomask2 & 16384) > 0 then 't' end as hhu,
=>          case when (t_infomask2 & 32768) > 0 then 't' end as hot,
=>          t_ctid
=>   from heap_page_items(get_raw_page('t',1))
=>   order by lp;
CREATE VIEW

И представление для индекса:

=> create view t_s_v as
=>   select itemoffset,
=>          ctid
=>   from bt_page_items('t_s',1);
CREATE VIEW

Будем добавлять строки размером 1K.

=> insert into t(s) select repeat('1',1024);
INSERT 0 1

=> update t set s = repeat('2',1024);
UPDATE 1

=> update t set s = repeat('3',1024);
UPDATE 1

=> update t set s = repeat('4',1024);
UPDATE 1

=> update t set s = repeat('5',1024);
UPDATE 1

=> update t set s = repeat('6',1024);
UPDATE 1

=> update t set s = repeat('7',1024);
UPDATE 1

Можем наблюдать цепочку HOT-обновлений:

=> select * from t0_v;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | hhu | hot | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+-----+-----+--------
 (0,1) | normal | 4026 | 4027 | t      |        | t      |        | t   |     | (0,2)
 (0,2) | normal | 4027 | 4028 | t      |        | t      |        | t   | t   | (0,3)
 (0,3) | normal | 4028 | 4029 | t      |        | t      |        | t   | t   | (0,4)
 (0,4) | normal | 4029 | 4030 | t      |        | t      |        | t   | t   | (0,5)
 (0,5) | normal | 4030 | 4031 | t      |        | t      |        | t   | t   | (0,6)
 (0,6) | normal | 4031 | 4032 | t      |        |        |        | t   | t   | (0,7)
 (0,7) | normal | 4032 |    0 |        |        |        | t      |     | t   | (0,7)
(7 rows)


Воспроизвести ситуацию внутристраничной очистки
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

=> update t set s = repeat('8',1024);
UPDATE 1

=> select * from t0_v;
 ctid  |     state     | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | hhu | hot | t_ctid 
-------+---------------+------+------+--------+--------+--------+--------+-----+-----+--------
 (0,1) | redirect to 7 |      |      |        |        |        |        |     |     | 
 (0,2) | normal        | 4033 |    0 |        |        |        | t      |     | t   | (0,2)
 (0,3) | unused        |      |      |        |        |        |        |     |     | 
 (0,4) | unused        |      |      |        |        |        |        |     |     | 
 (0,5) | unused        |      |      |        |        |        |        |     |     | 
 (0,6) | unused        |      |      |        |        |        |        |     |     | 
 (0,7) | normal        | 4032 | 4033 | t      |        |        |        | t   | t   | (0,2)
(7 rows)


Воспроизвести ситуацию, при которой новая версия помещается на другую страницу
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Снова заполним страницу, но помешаем внутристраничной очистке -
поместим текущую версию строки в снимок.

        |  psql 

        |  => \c db5
        |  You are now connected to database "db5" as user "postgres".

        |  => begin;
        |  BEGIN

        |  => set transaction isolation level repeatable read;
        |  SET

        |  => select count(*) from t;
        |   count 
        |  -------
        |       1
        |  (1 row)
        |  

=> update t set s = repeat('9',1024);
UPDATE 1

=> update t set s = repeat('A',1024);
UPDATE 1

=> update t set s = repeat('B',1024);
UPDATE 1

=> update t set s = repeat('C',1024);
UPDATE 1

Текущая ситуация:

=> select * from t0_v;
 ctid  |     state     | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | hhu | hot | t_ctid 
-------+---------------+------+------+--------+--------+--------+--------+-----+-----+--------
 (0,1) | redirect to 7 |      |      |        |        |        |        |     |     | 
 (0,2) | normal        | 4033 | 4034 | t      |        | t      |        | t   | t   | (0,3)
 (0,3) | normal        | 4034 | 4035 | t      |        | t      |        | t   | t   | (0,4)
 (0,4) | normal        | 4035 | 4036 | t      |        | t      |        | t   | t   | (0,5)
 (0,5) | normal        | 4036 | 4037 | t      |        |        |        | t   | t   | (0,6)
 (0,6) | normal        | 4037 |    0 |        |        |        | t      |     | t   | (0,6)
 (0,7) | normal        | 4032 | 4033 | t      |        | t      |        | t   | t   | (0,2)
(7 rows)


Обновляем строку, но так, чтобы для новой версии уже не хватило места:

=> update t set s = repeat('D',2000);
UPDATE 1

=> select * from t0_v;
 ctid  |     state     | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | hhu | hot | t_ctid 
-------+---------------+------+------+--------+--------+--------+--------+-----+-----+--------
 (0,1) | redirect to 7 |      |      |        |        |        |        |     |     | 
 (0,2) | normal        | 4033 | 4034 | t      |        | t      |        | t   | t   | (0,3)
 (0,3) | normal        | 4034 | 4035 | t      |        | t      |        | t   | t   | (0,4)
 (0,4) | normal        | 4035 | 4036 | t      |        | t      |        | t   | t   | (0,5)
 (0,5) | normal        | 4036 | 4037 | t      |        | t      |        | t   | t   | (0,6)
 (0,6) | normal        | 4037 | 4038 | t      |        |        |        |     | t   | (1,1)
 (0,7) | normal        | 4032 | 4033 | t      |        | t      |        | t   | t   | (0,2)
(7 rows)


Новая версия (1,1) записана на следующую страницу:

=> select * from t1_v;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | hhu | hot | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+-----+-----+--------
 (1,1) | normal | 4038 |    0 |        |        |        | t      |     |     | (1,1)
(1 row)


А в индексе теперь две ссылки, а не одна:

=> select * from t_s_v;
 itemoffset | ctid  
------------+-------
          1 | (1,1)
          2 | (0,1)
(2 rows)


Таким образом, цепочка HOT-обновлений прервалась.

=> \q
        |  => \q
