Уровень 3.0

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

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

Определение видимости строк

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

[student@pkles-gt0040964 ~]$ 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 позволяет исследовать страницы данных на низком уровне. Функции указанного расширения доступны только суперпользователям.
Создайте простую таблицу:

mvcc_db=# CREATE TABLE some_table(id integer, value integer) WITH (autovacuum_enabled=false);

CREATE TABLE

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

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).

Настройте приглашение в psql с указанием номера обслуживающего процесса:
```
mvcc_db=# \set PROMPT1 '%p%R%x%# '
```
```
10663=# \set PROMPT2 '%p%R%x%# '
```
Теперь в приглашении указан pid обслуживающего процесса. Это необходимо для различения разных сеансов psql.

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

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

bash-4.4$ psql -d mvcc_db

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

Настройте приглашение во втором сеансе psql с указанием номера обслуживающего процесса:
```
mvcc_db=# \set PROMPT1 '%p%R%x%# '
```
```
10670=# \set PROMPT2 '%p%R%x%# '
```

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

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

-bash-4.4$ psql -d mvcc_db

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

Настройте приглашение в третьем сеансе psql с указанием номера обслуживающего процесса:

mvcc_db=# \set PROMPT1 '%p%R%x%# '

10671=# \set PROMPT2 '%p%R%x%# '

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

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

-bash-4.4$ psql -d mvcc_db

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

Настройте приглашение в четвертом сеансе psql с указанием номера обслуживающего процесса:

mvcc_db=# \set PROMPT1 '%p%R%x%# '

13390=# \set PROMPT2 '%p%R%x%# '

Построение снимка и определение видимости строк

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

10663=# BEGIN;

BEGIN

10663=*# INSERT INTO some_table VALUES (1, 100);

INSERT 0 1

10663=*# SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                808
(1 строка)

Во втором сеансе сделайте то же самое, но с фиксацией транзакции:

10670=# BEGIN;

BEGIN

10670=*# INSERT INTO some_table VALUES (2, 200);

INSERT 0 1

10670=*# SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                809
(1 строка)

10670=*# COMMIT;

COMMIT

В третьем сеансе начните транзакцию с уровнем изоляции Read Committed и получите текущий снимок данных:
```
10671=# BEGIN;
```
```
BEGIN
```
```
10671=*# SELECT pg_current_snapshot();
```
```
 pg_current_snapshot
---------------------
 808:810:808
(1 строка)
```
Для получения снимка использована функция pg_current_snapshot, выдающая текущий снимок данных в формате xmin:xmax:xip_list:
- xmin — минимальный номер среди всех активных транзакций. В данном случае номер активной транзакции из первого сеанса
- xmax — номер, на один больший номера последней зафиксированной транзакции. В данном случае номер транзакции на один больше номера зафиксированной транзакции (xid=809) из второго сеанса
- xip_list — список номеров активных транзакций с номерами, меньшими xmax на момент построения снимка. В данном случае имеется одна активная транзакция — в первом сеансе
В четвертом сеансе начните транзакцию с уровнем изоляции Repeatable Read и получите текущий снимок данных:
```
13390=# BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```
BEGIN
```
```
13390=*# SELECT pg_current_snapshot();
```
```
 pg_current_snapshot
---------------------
 808:810:808
(1 строка)
```
В третьем сеансе выполните запрос всех строк таблицы и посмотрите содержимое соответствующей страницы данных:
```
10671=*# SELECT * FROM some_table;
```
```
 id | value
----+-------
  2 |   200
(1 строка)
```
```
10671=*# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+------+------+-----------+----------------+--------------+----------------+--------------
 (0, 1) |  808 |    0 | (0,1)     | f              | f            | f              | t
 (0, 2) |  809 |    0 | (0,2)     | t              | f            | f              | t
(2 строки)
```
Запрос выдал одну строку, вставленную во втором сеансе транзакцией с номером 809.

Указанная строка оказалась видна в соответствии с базовым правилом видимости: изменения транзакции с номером xmin в заголовке версии строки видны, а изменения транзакции с номером xmax в заголовке версии строки не видны в снимке.

Действительно, в снимке данных 808:810:808 видны изменения транзакции с номером 809, а также всех транзакций с номерами, меньшими 808, поэтому именно версия строки с ctid=(0,2) попала в снимок.
В первом сеансе зафиксируйте транзакцию:
```
10663=*# COMMIT;
```
```
COMMIT
```
В третьем сеансе выполните запрос всех строк таблицы, посмотрите содержимое соответствующей страницы данных и текущий снимок:
```
10671=*# SELECT * FROM some_table;
```
```
 id | value
----+-------
  1 |   100
  2 |   200
(2 строки)
```
```
10671=*# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+------+------+-----------+----------------+--------------+----------------+--------------
 (0, 1) |  808 |    0 | (0,1)     | t              | f            | f              | t
 (0, 2) |  809 |    0 | (0,2)     | t              | f            | f              | t
(2 строки)
```
```
10671=*# SELECT pg_current_snapshot();
```
```
 pg_current_snapshot
---------------------
 810:810:
(1 строка)
```
Теперь строка, вставленная транзакцией 808, стала видна. При этом снимок обновился. Теперь в снимке нет активных транзакций и в нем видны изменения всех транзакций с номерами меньше 810.

В данном случае при выполнении нового запроса был построен новый снимок, поскольку транзакция была начата с уровнем изоляции Read Committed. Если бы использовался уровень изоляции Repeatable Read или Serializable, то был бы использован первый построенный снимок и первую строку по-прежнему видно бы не было.

В третьем сеансе обновите строку с id=2, узнайте номер транзакции и зафиксируйте ее:

10671=*# UPDATE some_table SET value = 100 WHERE id = 2;

UPDATE 1

10671=*# SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                810
(1 строка)

10671=*# COMMIT;

COMMIT

В четвертом сеансе снова получите снимок, выполните запрос всех строк таблицы и посмотрите содержимое соответствующей страницы данных:
```
13390=*# SELECT pg_current_snapshot();
```
```
 pg_current_snapshot
---------------------
 808:810:808
(1 строка)
```
```
13390=*# SELECT * FROM some_table;
```
```
 id | value
----+-------
  2 |   200
(1 строка)
```
```
13390=*# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | next_ctid | xmin_committed | xmin_aborted | xmax_committed | xmax_aborted
--------+------+------+-----------+----------------+--------------+----------------+--------------
 (0, 1) |  808 |    0 | (0,1)     | t              | f            | f              | t
 (0, 2) |  809 |  810 | (0,3)     | t              | f            | f              | f
 (0, 3) |  810 |    0 | (0,3)     | f              | f            | f              | t
(3 строки)
```
После фиксации транзакции в третьем сеансе снимок не поменялся, поскольку транзакция начата с уровнем изоляции Repeatable Read.

Из трех версий строк, имеющихся на странице, в снимке видна только строка с ctid = (0, 2), поскольку изменения транзакции, создавшей версию строки (xmin=809), в снимке видны, а изменения транзакции, удалившей версию строки (xmax=810), в снимке не видны, несмотря на то что она зафиксирована (хотя информационный бит xmax_committed пока не проставлен).
Откатите транзакцию в четвертом сеансе:

13390=*# ROLLBACK;

ROLLBACK

Использование снимков подзапросами

В первом сеансе создайте изменчивую функцию, суммирующую значения столбца value таблицы some_table:
```
10663=# CREATE OR REPLACE FUNCTION get_sum_volatile()
RETURNS integer
AS $$
    SELECT sum(value) FROM some_table;
$$ VOLATILE LANGUAGE SQL;
```
```
CREATE FUNCTION
```
Функции с категориями изменчивости VOLATILE могут изменять базу данных. Поэтому в запросе, использующем изменчивую функцию, она будет выполняться заново каждый раз, когда потребуется ее результат с построением нового снимка данных.

Планировщик не будет пытаться заменить множество ее вызовов одним, поскольку она может выдавать разные результаты при одних и тех же входных параметрах.

Если явно не указывать категорию изменчивости при создании функции, то по умолчанию будет выбрана именно категория VOLATILE.
В первом сеансе создайте аналогичную функцию, но с категорией изменчивости STABLE:
```
10663=# CREATE OR REPLACE FUNCTION get_sum_stable()
RETURNS integer
AS $$
    SELECT sum(value) FROM some_table;
$$ STABLE LANGUAGE SQL;
```
```
CREATE FUNCTION
```
Функции с категориями изменчивости STABLE не могут вносить изменения в базу данных и должны выдавать один и тот же результат при одних их тех же входных данных.

Поэтому в запросе, использующем такую функцию, будет использован один и тот же снимок данных при различных ее вызовах. Более того, планировщик постарается заменить множество ее вызовов одним с сохранением результата.

В первом сеансе выполните следующий запрос:

10663=# SELECT pg_sleep(15), *,
    (SELECT sum(value) FROM some_table) subquery,
    get_sum_volatile(),
    get_sum_stable()
FROM some_table;

 pg_sleep | id | value | subquery | get_sum_volatile | get_sum_stable
----------+----+-------+----------+------------------+----------------
          |  1 |   100 |      200 |              200 |            200
          |  2 |   100 |      200 |              300 |            200
(2 строки)

Запрос будет выполнен с задержкой в 30 секунд благодаря вызову функции pg_sleep(15) для каждой из двух строк в таблице.

Во время задержки выполнения предыдущего запроса обновите строку с id=2 во втором сеансе с фиксацией транзакции:
```
10670=# UPDATE some_table SET value = 200 WHERE id = 2;
```
```
UPDATE 1
```
В результате (см. вывод запроса в предыдущем пункте) подзапрос SELECT sum(value) FROM some_table выполнился со снимком, построенным в начале всего запроса. Следовательно, он не увидел зафиксированных во время выполнения запроса изменений другой транзакции.

С тем же снимком была выполнена функция get_sum_stable с категорией изменчивости STABLE.

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

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

Однако к моменту вызова функции во второй раз параллельная транзакция успела зафиксировать изменения и они стали видны, но только в изменчивой функции (get_sum_volatile = 300), в то время как в полях value, subquery и get_sum_stable сохранились старые значения (200).

Это произошло, поскольку запрос выполнялся в транзакции с уровнем изоляции Read Committed (запрос невно был обернут в транзакцию с уровнем изоляции по умолчанию).

Если бы был использован уровень изоляции Repeatable Read или Serializable, тогда даже изменчивая функция выполнилась бы со снимком, построенным в начале транзакции.

В первом сеансе повторите эксперимент с использованием уровня изоляции Repeatable Read:

10663=# BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

10663=*# SELECT pg_sleep(15), *,
    (SELECT sum(value) FROM some_table) subquery,
    get_sum_volatile(),
    get_sum_stable()
FROM some_table;

 pg_sleep | id | value | subquery | get_sum_volatile | get_sum_stable
----------+----+-------+----------+------------------+----------------
          |  1 |   100 |      300 |              300 |            300
          |  2 |   200 |      300 |              300 |            300
(2 строки)

Во время задержки выполнения предыдущего запроса обновите строку с id=2 во втором сеансе с фиксацией транзакции:
```
10670=# UPDATE some_table SET value = 100 WHERE id = 2;
```
```
UPDATE 1
```
Теперь все подзапросы отработали с одним и тем же снимком данных (см. вывод запроса в предыдущем пункте).

Из эксперимента следует, что надо с большой осторожностью применять изменчивые функции в транзакциях с уровнем изоляции Read Committed, поскольку даже внутри одного запроса могут использоваться разные снимки данных.

Проблему усугубляет тот факт, что и уровень изоляции Read Committed, и категория изменчивости функции Volatile применяются по умолчанию.

Завершение

Откатите транзакцию в первом сеансе:
```
10663=*# ROLLBACK;
```
```
ROLLBACK
```

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

10663=# \c postgres

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

10670=# \q

10671=# \q

13390=# \q

В первом сеансе удалите базу данных mvcc_db и завершите сеанс psql:
```
15279=# DROP DATABASE mvcc_db;
```
```
DROP DATABASE
```
```
15279=# \q
```

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

Вопрос 1

В сеансе psql получен снимок данных следующей командой:

mvcc_db=*> SELECT pg_current_snapshot();
pg_current_snapshot
---------------------
1000:1004:1000,1002

Изменения транзакций с какими номерами видны в полученном снимке? Выберите все верные варианты ответа:

Вопрос 2

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

| ctid | xmin | xmax | | --- | --- | --- | | (0,1) | 100 | 0 | | (0,2) | 100 | 101 | | (0,3) | 101 | 0 | | (0,4) | 102 | 102 |

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

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

mvcc_db=*> SELECT pg_current_snapshot();
pg_current_snapshot
---------------------
101:103:101

Какие версии строк видны в полученном снимке? Выберите все верные варианты ответа:

(0,1)

(0,2)

(0,3)

(0,4)

Вопрос 3

В каком случае при выполнении одного запроса может быть использовано более одного снимка данных?

Запрос выполняется в транзакции с уровнем изоляции Read Committed и в нем вызывается функция с категорией изменчивости StableЗапрос выполняется в транзакции с уровнем изоляции Read Committed и в нем вызывается функция с категорией изменчивости VolatileЗапрос выполняется в транзакции с уровнем изоляции Repeatable Read и в нем вызывается функция с категорией изменчивости StableЗапрос выполняется в транзакции с уровнем изоляции Repeatable Read и в нем вызывается функция с категорией изменчивости Volatile

Определение видимости строк​

Построение снимка и определение видимости строк​

Использование снимков подзапросами​

Завершение​

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