Уровень 3.0

Предусловия:

Изучен модуль «Структура данных» данного курса

Анализ операций с данными на низком уровне

Откройте терминал и подключитесь в psql к базе данных postgres c ролью postgres:

[student@pkles-gt0040523 ~]$ sudo -iu postgres

-bash-4.4$ psql

psql (15.5)
Введите "help", чтобы получить справку.

Создайте базу данных и подключитесь к ней с ролью postgres:

postgres=# CREATE DATABASE mvcc_db;

CREATE DATABASE

postgres=# \c mvcc_db

Вы подключены к базе данных "mvcc_db" как пользователь "postgres".

Создайте расширение pageinspect:
```
mvcc_db=# CREATE EXTENSION pageinspect;
```
```
CREATE EXTENSION
```
Расширение pageinspect позволяет исследовать страницы данных на низком уровне. Функции указанного расширения доступны только суперпользователям.
Создайте простую таблицу и индекс типа B-дерево по одному столбцу:
```
mvcc_db=# CREATE TABLE some_table(id integer, value integer) WITH (autovacuum_enabled=false);
```
```
CREATE TABLE
```
```
mvcc_db=# CREATE INDEX value_idx ON some_table(value);
```
```
CREATE INDEX
```
Обратите внимание, что таблица создана с параметром хранения autovacuum_enabled=false. Это необходимо для того, чтобы процесс автоочистки не удалял «мертвые» версии строк в ходе проведения экспериментов.
Вставьте одну строку в созданную таблицу:
```
mvcc_db=# INSERT INTO some_table VALUES (1, 100);
```
```
INSERT 0 1
```
Обратите внимание, что в psql любая команда вне транзакции неявно оборачивается командами BEGIN и COMMIT. В данном случае результат вставки строки неявно был зафиксирован.

Заголовок страницы

С использованием расширения pageinspect выполните запрос, показывающий данные заголовка табличной страницы:
```
mvcc_db=# SELECT lsn, checksum, lower, upper, special, pagesize FROM page_header(get_raw_page('some_table', 'main', 0));
```
```
    lsn     | checksum | lower | upper | special | pagesize
------------+----------+-------+-------+---------+----------
 3/15D462D8 |    32161 |    28 |  8136 |    8168 |     8192
(1 строка)
```
С использованием функции get_raw_page была получена нулевая страница основного ("main") слоя таблицы some_table в двоичном виде. С использованием функции page_header получены данные заголовка страницы, в том числе:
- lsn — ссылка на последнюю запись в журнале WAL, связанную с этой страницей
- checksum — контрольная сумма страницы
- lower — начало свободного пространства
- upper — начало пространства с элементами (версиями строк)
- special — начало специального пространства
- pagesize — размер страницы
Выполните аналогичный запрос для индексной страницы:
```
mvcc_db=# SELECT lsn, checksum, lower, upper, special, pagesize FROM page_header(get_raw_page('value_idx', 'main', 1));
```
```
    lsn     | checksum | lower | upper | special | pagesize
------------+----------+-------+-------+---------+----------
 3/15D46378 |   -12271 |    28 |  8160 |    8176 |     8192
(1 строка)
```
В данном случае получен заголовок первой индексной страницы, содержащей непосредственно индексные строки. Также можно было получить и заголовок нулевой страницы, содержащей метаинформацию.

Информация об элементах на странице

Создайте функцию, которая будет выводить информацию о версиях строк в заданной табличной странице:
```
mvcc_db=# CREATE OR REPLACE FUNCTION get_tuples_info(relname text, pagenum bigint)
RETURNS TABLE(ctid text, xmin xid, xmax xid, next_ctid text,
    xmin_committed boolean, xmin_aborted boolean,
    xmax_committed boolean, xmax_aborted boolean)
AS $$
SELECT '(' || $2 || ', ' || lp || ')', t_xmin, t_xmax, t_ctid,
    'HEAP_XMIN_COMMITTED' = ANY(raw_flags),
    'HEAP_XMIN_INVALID' = ANY(raw_flags),
    'HEAP_XMAX_COMMITTED' = ANY(raw_flags),
    'HEAP_XMAX_INVALID' = ANY(raw_flags)
         FROM heap_page_items(get_raw_page($1, 'main', $2)),
           LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
         WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL
    ORDER BY lp;
$$ LANGUAGE SQL;
```
```
CREATE FUNCTION
```
На вход функции передается имя отношения (relname) и номер страницы (pagenum). В результате выполнения для каждой версии строки в странице функция выдает следующую информацию:
- ctid — идентификатор версии строки (номер страницы и номер указателя на версию строки в странице)
- xmin — номер транзакции, создавшей версию строки
- xmax — номер транзакции, удалившей версию строки
- next_ctid — ctid следующей версии той же строки
- xmin_committed и xmin_aborted — информационные биты статуса транзакции xmin
- xmax_committed и xmax_aborted— информационные биты статуса транзакцииxmax`
В запросе помимо уже знакомых функций get_raw_page и heap_page_items использовалась также функция heap_tuple_infomask_flags, возвращающая проставленные информационные биты в виде массива.
Выполните запрос с использованием созданной функции:
```
mvcc_db=# select * from get_tuples_info('some_table', 0);
```
```
  ctid  | xmin  | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+-------+------+-----------+----------------+--------------+----------------+--------------
 (0, 1) | 92610 |    0 | (0,1)     | f              | f            | f              | t
(1 строка)
```
Единственную строку вставила транзакция с номером xmin. Значение xmax = 0 в совокупности с проставленным битом xmax_aborted означает то, что версия строки никакими транзакциями не удалялась.

Обратите внимание, что информационный бит xmin_committed не проставлен, несмотря на то что вставка строки осуществлялась с фиксацией транзакции. В действительности после вставки строки к ней обращений больше не было, поэтому соответствующие информационные биты не проставлены.
Проверьте, что транзакция с номером xmin действительно зафиксирована:
```
mvcc_db=# SELECT pg_xact_status('92610');
```
```
 pg_xact_status
----------------
 committed
(1 строка)
```
Для этого была использована функция pg_xact_status, которая получает статус транзакции из журнала CLOG.
Теперь запросите строку, вставленную транзакцией с номером xmin, и убедитесь, что в странице проставился информационный бит xmin_committed.
```
mvcc_db=# SELECT * FROM some_table WHERE xmin = 92610;
```
```
 id | value
----+-------
  1 |   100
(1 строка)
```
```
mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin  | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+-------+------+-----------+----------------+--------------+----------------+--------------
 (0, 1) | 92610 |    0 | (0,1)     | t              | f            | f              | t
(1 строка)
```
Теперь бит xmin_committed проставлен. Таким образом, выполняя запрос на чтение была физически изменена страница данных. Это в определенных случаях может приводить к негативным последствиям, поскольку измененные («грязные») страницы при вытеснении из кеша буферов записываются в энергонезависимое хранилище. Помимо этого при изменении страницы порождаются новые записи журнала WAL.

Также обратите внимание, что в SQL-запросе было получено значение xmin. Таким же образом можно получить значения xmax и ctid, что, строго говоря, нарушает свойство изоляции транзакций. Имеется возможность «подсмотреть» за действиями других транзакций.
Получите информацию об индексных строках в странице:
```
mvcc_db=# SELECT itemoffset, ctid FROM bt_page_items('value_idx', 1);
```
```
 itemoffset | ctid
------------+-------
          1 | (0,1)
(1 строка)
```
Для просмотра индексных строк была использована еще одна функция расширения pageinspect — bt_page_items.

В результате выполнения функции для каждой индексной строки на странице получены ее смещение относительно начала страницы (itemoffset) и ссылка на версию строки в табличной странице (ctid).

Операции обновления

Создайте функцию, которая будет выводить информацию о статусах транзакций из журнала CLOG, номера которых встречаются в полях xmin и xmax в версиях строк в заданной табличной странице:
```
mvcc_db=# CREATE OR REPLACE FUNCTION get_clog_info(relname text, pagenum bigint)
RETURNS TABLE(xact_id xid, status text)
AS $$
WITH xacts AS (
    SELECT xmin AS xact_id FROM get_tuples_info($1, $2)
    UNION
    SELECT xmax AS xact_id FROM get_tuples_info($1, $2))
SELECT xact_id , pg_xact_status(xact_id::text::xid8)
FROM xacts WHERE xact_id != 0
ORDER BY xact_id::text;
$$ LANGUAGE SQL;
```
```
CREATE FUNCTION
```
В указанной функции использовалась ранее созданная функция get_tuples_info для получения значений xmin и xmax, а также функция pg_xact_status для определения статусов транзакций с номерами xmin и xmax.
Начните транзакцию и обновите единственную строку в таблице:
```
mvcc_db=# BEGIN;
```
```
BEGIN
```
```
UPDATE some_table SET value = 200 WHERE id = 1;
```
```
UPDATE 1
```
Получите информацию из табличной страницы, определите номер текущей транзакции и узнайте статусы транзакций, указанных в полях xmin и xmax:
```
mvcc_db=# select * from get_tuples_info('some_table', 0);
```
```
  ctid  | xmin  | xmax  | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+-------+-------+-----------+----------------+--------------+----------------+--------------
 (0, 1) | 92610 | 92624 | (0,2)     | t              | f            | f              | f
 (0, 2) | 92624 |     0 | (0,2)     | f              | f            | f              | t
(2 строки)
```
```
mvcc_db=*# SELECT pg_current_xact_id();
```
```
 pg_current_xact_id
--------------------
              92624
(1 строка)
```
```
mvcc_db=*# SELECT * FROM get_clog_info('some_table', 0);
```
```
 xact_id  |  status
----------+-----------
   92610  | committed
   92624  | in progress
(2 строки)
```
В данном примере также была использована функция pg_current_xact_id(), которая выдает идентификатор текущей транзакции. Если у текущей транзакции еще нет идентификатора (она не успела выполнить какие-либо изменения), он будет ей назначен.

При обновлении старая версия строки помечается удаленной посредством указания номера транзакции в поле xmax и сброса информационного бита xmax_aborted. Далее создана новая версия строки так же, как и при вставке.

Значение xmax старой версии строки совпадает со значением xmin новой версии строки, в поле next_ctid старой версии строки записан ctid новой версию строки.

Выставленное значение xmax, соответствующее незавершенной транзакции, является признаком блокировки строки.

По информации из журнала CLOG (см. функцию pg_xact_status) транзакция, создавшая первую версию строки (xid = 92610), зафиксирована, а транзакция, выполнившая обновление (xid = 92624), еще не завершена, что естественно, поскольку она является текущей транзакцией.
Получите информацию из индексной страницы:
```
mvcc_db=# SELECT itemoffset, ctid FROM bt_page_items('value_idx', 1);
```
```
 itemoffset | ctid
------------+-------
          1 | (0,1)
          2 | (0,2)
(2 строки)
```
В индексную страницу добавилась вторая индексная строка, ссылающаяся на вторую версию строки в табличной странице. Обратите внимание, что в индексной странице нет информации о версионности строк.

Зафиксируйте транзакцию, получите информацию из табличной страницы и журнала CLOG:

mvcc_db=*# COMMIT;

COMMIT

mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

  ctid  | xmin  | xmax  | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+-------+-------+-----------+----------------+--------------+----------------+--------------
 (0, 1) | 92610 | 92624 | (0,2)     | t              | f            | f              | f
 (0, 2) | 92624 |     0 | (0,2)     | f              | f            | f              | t
(2 строки)

mvcc_db=# SELECT * FROM get_clog_info('some_table', 0);

 xact_id  |  status
----------+-----------
   92610  | committed
   92624  | committed
(2 строки)

Как видно, при фиксации транзакции единственное, что было выполнено, — это простановка бита committed в журнале CLOG. Табличная страница осталась в неизменном виде.

Операции удаления

Начните транзакцию, удалите единственную строку в таблице и отмените транзакцию:
```
mvcc_db=# BEGIN;
```
```
BEGIN
```
```
mvcc_db=*# DELETE FROM some_table WHERE id=1;
```
```
DELETE 1
```
```
mvcc_db=# ROLLBACK;
```
```
ROLLBACK
```

Получите информацию из табличной и индексной страниц и журнала CLOG:

mvcc_db=# SELECT * FROM get_tuples_info('some_table', 0);

  ctid  | xmin  | xmax  | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+-------+-------+-----------+----------------+--------------+----------------+--------------
 (0, 1) | 92610 | 92624 | (0,2)     | t              | f            | t              | f
 (0, 2) | 92624 | 92625 | (0,2)     | t              | f            | f              | f
(2 строки)

mvcc_db=# SELECT itemoffset, ctid FROM bt_page_items('value_idx', 1);

 itemoffset | ctid
------------+-------
          1 | (0,1)
          2 | (0,2)
(2 строки)

mvcc_db=# SELECT * FROM get_clog_info('some_table', 0);

 xact_id  |  status
----------+-----------
   92610  | committed
   92624  | committed
   92625  | aborted
(3 строки)

Как видно, при удалении строки в поле xmax был указан номер транзакции 92625 и сброшен бит xmax_aborted. Указатель на версию строки при этом не освобожден, как и не удалена соответствующая индексная строка.

В данном случае транзакция, удалившая строку (xid = 92625), была отменена, что отражено в журнале CLOG. Информационный бит xmax_aborted в версии строки пока на проставлен.

Выполните команду очистки устаревших версий строк и получите информацию из табличной и индексной страниц:

mvcc_db=# VACUUM some_table;

VACUUM

mvcc_db=# select * from get_tuples_info('some_table', 0);

  ctid  | xmin  | xmax  | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+-------+-------+-----------+----------------+--------------+----------------+--------------
 (0, 2) | 92624 | 92625 | (0,2)     | t              | f            | f              | t
(1 строка)

mvcc_db=# SELECT itemoffset, ctid FROM bt_page_items('value_idx', 1);

 itemoffset | ctid
------------+-------
          1 | (0,2)
(1 строка)

«Мертвая» версия строки сtid = (0, 1) очищена, как и соответствующая индексная строка.

Завершение

Подключитесь к базе данных postgres:

postgres=# \c postgres

Вы подключены к базе данных "postgres" как пользователь "postgres".

Удалите базу данных mvcc_db и завершите сеанс psql:
```
postgres=# DROP DATABASE mvcc_db;
```
```
DROP DATABASE
```
```
postgres=# \q
```

Самопроверка

Вопрос 1

В сеансе psql последовательно выполнены следующие команды:

mvcc_db=# CREATE TABLE t (num integer) WITH (autovacuum_enabled=false);
CREATE TABLE

mvcc_db=# INSERT INTO t VALUES(1);
INSERT 0 1

mvcc_db=# BEGIN;
BEGIN

mvcc_db=*# UPDATE t SET num = 2;
UPDATE 1

mvcc_db=*# ROLLBACK;
ROLLBACK

При выполнении команд параллельных транзакций не выполнялось.

Какое количество версий строк оказалось в нулевой странице таблицы t после выполнения последней команды?

Вопрос 2

Состояние заголовков версий строк табличной страницы с соответствующими идентификаторами ctid представлено ниже:

| ctid | xmin | xmax | xmin committed | xmin aborted | xmax committed | xmax aborted | | --- | --- | --- | --- | --- | --- | --- | | (0,1) | 500 | 503 | 1 | | | 1 | | (0,2) | 503 | 0 | | 1 | | 1 | | (0,3) | 501 | 502 | 1 | | | |

Состояние журнала CLOG представлено ниже:

| xid | committed | aborted | | --- | --- | --- | | 500 | 1 | | | 501 | 1 | | | 502 | | | | 503 | | 1 |

Какие версии строк являются заблокированными?

(0,1)(0,2)(0,3)

Вопрос 3

В сеансе psql последовательно выполнены следующие команды:

mvcc_db=# CREATE TABLE t (num integer) WITH (autovacuum_enabled=false);
CREATE TABLE

mvcc_db=# BEGIN;
BEGIN

mvcc_db=*# INSERT INTO t VALUES(1);
INSERT 0 1

mvcc_db=*# SELECT pg_current_xact_id();
pg_current_xact_id
--------------------
81099
(1 строка)

mvcc_db=*# UPDATE t SET num = 2;
UPDATE 1

mvcc_db=*# COMMIT;
COMMIT

mvcc_db=# SELECT xmax FROM t WHERE num = 2;

При выполнении команд параллельных транзакций не выполнялось.

Какое число будет получено в результате выполнения последнего запроса?

Вопрос 4

Состояние журнала CLOG представлено ниже:

| xid | committed | aborted | | --- | --- | --- | | 500 | 1 | | | 501 | 1 | | | 502 | | | | 503 | | 1 |

Какие версии строк являются заблокированными?

(0,1)

(0,2)

(0,3)

Анализ операций с данными на низком уровне​

Заголовок страницы​

Информация об элементах на странице​

Операции обновления​

Операции удаления​

Завершение​

Самопроверка​