xmax. Строка удалена или нет?

В статье «Что означает INSERT 0 1» я кратко затронул системные столбцы. Так получилось что я продолжаю с ними разбираться, сегодня тоже будет немного про них.

В частности — про столбцы xmin и xmax. Как можно прочитать в документации по ссылке:

xmin

Идентификатор (код) транзакции, добавившей строку этой версии. (Версия строки — это её индивидуальное состояние; при каждом изменении создаётся новая версия одной и той же логической строки.)

xmax

Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована, или удаление было отменено.

И меня зацепило вот это предложение: «Значение этого столбца может быть ненулевым и для видимой версии строки».

А как понять — удалена ли версия строки, если у этой версии xmax не нулевой?

На самом деле, в курсе DBA2 от компании Postgres Professional ответ на этот вопрос есть, в теме «2. Страницы и версии строк», в разделе «Версии строк». И то, что будет идти ниже — это, можно сказать, повторение из DBA2. Но повторение полезно. И в следующий раз пригодится:

Эксперимент:

Создаём простую таблицу, но — с ограничением not null:

postgres=# CREATE TABLE t_n(id integer not null);
CREATE TABLE

И нужно создать расширение pageinspect, оно входит в поставку сервера:

postgres=# CREATE EXTENSION pageinspect;
CREATE EXTENSION

Далее создаём представление (код взял из курса DBA2, но добавил поле to_hex):

postgres=# CREATE VIEW t_v_n 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,
       to_hex(t_infomask),
       t_ctid
FROM heap_page_items(get_raw_page('t_n',0))
ORDER BY lp;
CREATE VIEW

Подробнее про функцию get_raw_page можно почитать в разделе Функции общего назначения, а про функцию heap_page_items — в разделе Функции для исследования кучи. Грубо говоря — мы получаем образ страницы и выводим (в понятном виде) заголовки версий строк на ней.

Далее — начинаем транзакцию:

pavel=# BEGIN;
BEGIN

Добавляем строку в таблицу:

pavel=*# INSERT INTO t_n(id) VALUES (1);
INSERT 0 1

Выводим номер текущей транзакции:

pavel=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                847
(1 row)

С помощью созданного представления смотрим, что у нас находится в странице:

pavel=*# SELECT * FROM t_v_n;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | to_hex | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+--------+--------
 (0,1) | normal |  847 |    0 |        |        |        | t      | 800    | (0,1)
(1 row)

Часть этой информации можно получить и с помощью системных столбцов:

SELECT xmin, xmax, * FROM t_n;

 xmin | xmax | id 
------+------+----
  847 |    0 |  1
(1 row)

Фиксируем транзакцию:

pavel=*# COMMIT;
COMMIT

Смотрим что поменялось на странице после фиксации:

pavel=# SELECT * FROM t_v_n;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | to_hex | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+--------+--------
 (0,1) | normal |  847 |    0 |        |        |        | t      | 800    | (0,1)
(1 row)

Ничего не поменялось. Потому что сначала происходят изменения не в таблице, а в структуре CLOG. Для обновления информации нужно запросить данные из таблицы:

pavel=# SELECT * FROM t_n;
 id 
----
  1
(1 row)

И снова посмотрим что поменялось во вьюшке:

pavel=# SELECT * FROM t_v_n;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | to_hex | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+--------+--------
 (0,1) | normal |  847 |    0 | t      |        |        | t      | 900    | (0,1)
(1 row)

Там информация поменялась — стоит true в поле xmin_c — то есть, транзакция, которая добавила строку — закоммичена успешно.

Теперь удалим строку. Начинаем транзакцию:

pavel=# BEGIN;
BEGIN

Удаляем строку:

pavel=*# DELETE FROM t_n;
DELETE 1

У нас одна строка, она и удалена. Запросим идентификатор текущей транзакции:

pavel=*# SELECT pg_current_xact_id();
 pg_current_xact_id 
--------------------
                849
(1 row)

Смотрим что у нас во вьюшке:

pavel=*# SELECT * FROM t_v_n;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | to_hex | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+--------+--------
 (0,1) | normal |  847 |  849 | t      |        |        |        | 100    | (0,1)
(1 row)

Появился xmax. Отменим транзакцию:

pavel=*# ROLLBACK;
ROLLBACK

Информацию про xmin и xmax можно посмотреть прямо в таблице:

pavel=# SELECT xmin, xmax, * FROM t_n;
 xmin | xmax | id 
------+------+----
  847 |  849 |  1
(1 row)

Но непонятно теперь — а видна эта строка или нет? Вроде xmax заполнен, значит — строка удалена? Без просмотра CLOG (в нашем случае — с помощью вьюшки) не сказать:

pavel=# SELECT * FROM t_v_n;
 ctid  | state  | xmin | xmax | xmin_c | xmin_a | xmax_c | xmax_a | to_hex | t_ctid 
-------+--------+------+------+--------+--------+--------+--------+--------+--------
 (0,1) | normal |  847 |  849 | t      |        |        | t      | 900    | (0,1)
(1 row)

Видим что в поле xmax_a стоит true — так что на xmax можно внимания не обращать. И поэтому считаем, что строка НЕ удалена.

Вроде всё просто. Но я взял готовую вьюшку (из курса DBA2). В ней идёт обращение к полю t_infomask (во вьюшке — поле to_hex). Вот с ним тоже бы хотелось подробно разобраться, но — в другой раз.