Практикум

Подготовка

Откройте два графических эмулятора терминала или же просто два терминала. Задайте в них переменной PS1 такой вид приглашения командной строки, чтобы их можно было отличить друг от друга.

В первом терминале (далее будем выделять синим цветом):
```
[student@p620 ~]$ PS1=$(echo $PS1 | sed 's/\\h/TERM1/') [student@TERM1 ~]$
```
Во втором (красный цвет для отличия):
```
[student@p620 ~]$ PS1=$(echo $PS1 | sed 's/\\h/TERM2/') [student@TERM2 ~]$
```
В примере для первого терминала в приглашении командной строки теперь видно TERM1, а во втором терминале - TERM2. Вид приглашения командной строки оболочки Shell задает переменная PS1 (Prompt String).
В третьей вкладке или терминале запустите оболочку от имени postgres и зайдите в сеанс psql (черный цвет для отличия):
```
[student@p620 ~]$ sudo -iu postgres [postgres@p620 ~]$ psql
psql (15.5)
Type "help" for help.
postgres=#
```
Сбросьте все настройки в postgresql.auto.conf, выполнив команду ALTER SYSTEM RESET ALL:
```
postgres=# alter system reset all; ALTER SYSTEM
```
В одном из сеансов student перезапустите сервер PostgreSQL:
```
[student@TERM1 ~]$ sudo systemctl restart postgresql
```

Рестартуйте сессию postgres в клиенте psql:

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

Удалите все БД, кроме template1, template0 и postgres:

postgres=# \l
List of databases
Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access
privileges
-----------+----------+----------+------------+------------+------------+-----------------+---------
arch_db   | student  | UTF8
localdb   | postgres | UTF8
postgres  | postgres | UTF8
repbase   | postgres | UTF8
student   | student  | UTF8
template0 | postgres | UTF8
|| template1 | postgres | UTF8
||
(7 rows)
postgres=# DROP DATABASE arch_db; DROP DATABASE
postgres=# DROP DATABASE localdb; DROP DATABASE
postgres=# DROP DATABASE repbase; DROP DATABASE
postgres=# DROP DATABASE student; DROP DATABASE

Эти БД были удалены в качестве примера. В Вашей системе набор БД может быть другой, получите его в сеансе postgres в psql метакомандой \l.

В СУБД должны быть зарегистрированы только две роли: postgres и student. Все остальные роли должны быть удалены:

postgres=# \du
List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
dbuser1   |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
student   |
user1     |
postgres=# DROP ROLE dbuser1; DROP ROLE
postgres=# DROP ROLE user1; DROP ROLE

Метакоманда \du выводит в psql список зарегистрированных в СУБД ролей.

Создайте БД student, владельцем которой будет роль student:

postgres=# CREATE DATABASE student OWNER student; CREATE DATABASE
postgres=# \l student
                                                 List of databases
Name   |  Owner  | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider | Access | privileges
---------+---------+----------+------------+------------+------------+-----------------+------------
student | student | UTF8     | en_US.UTF8 | en_US.UTF8 |            | libc            |
(1 row)

Транзакции

В первом терминале зайдите в сеанс ролью student, подключившись к одноименной БД:
```
[student@TERM1 ~]$ psql psql (15.5)
Type "help" for help.
student@student=> SELECT user; user
---------
student
(1 row)
student@student=> SELECT current_catalog; current_catalog
-----------------
student
(1 row)
```
Функция SQL user возвращает имя роли в сеансе, функция current_catalog - имя БД, к которой выполнено подключение.
В первом терминале создайте таблицу stab с текстовым столбцом:
```
student@student=> CREATE TABLE stab(msg text); CREATE TABLE
student@student=> \d
List of relations
Schema | Name | Type  |  Owner
--------+------+-------+---------
public | stab | table | student
(1 row)
student@student=> \d stab
Table "public.stab"
Column | Type | Collation | Nullable | Default
-------+------+-----------+----------+---------
msg    | text |           |          |
```
Метакоманда \d без аргументов выдает список таблиц (и других отношений). Если метакоманде \d задать имя существующей таблицы, она напечатает структуру этой таблицы.
Начните транзакцию в первом терминале:
```
student@student=> BEGIN; BEGIN
student@student=*>
```
Обратите внимание на изменение приглашения командной строки psql. Там появился символ звездочки, который и говорит о том, что в сеансе запущена транзакция.
Получите номер текущей транзакции в первом терминале:
```
student@student=*> SELECT pg_current_xact_id(); pg_current_xact_id
--------------------
                885
(1 row)
```
Функция pg_current_xact_id() возвращает номер XID транзакции, если он был назначен. Если он не был назначен - назначает.
Вставьте в таблицу произвольную строку:
```
student@student=*> INSERT INTO stab VALUES ('Вставка'); INSERT 0 1
student@student=*> SELECT * FROM stab; msg
---------
Вставка
(1 row)
```
Изменения данных, производимые командами DML в транзакции видны сразу.
Проверьте, видны ли изменения в таблице stab вне транзакции, обратившись к таблице с запросом на чтение во втором сеансе:
```
[student@TERM2 ~]$ psql psql (15.5)
Type "help" for help.
student@student=> SELECT * FROM stab; msg
-----
(0 rows)
```
Обратите внимание, что активная пишущая транзакция, в которой изменяются данные таблицы stab, не блокирует операции чтения. Однако, система MVCC предоставляет командам и транзакциям разные представления данных - снимки. Во втором сеансе снимок данных таблицы stab не содержит в себе строк. Это связано с тем, что транзакция в первом сеансе не зафиксирована.
Зафиксируйте транзакцию и проверьте содержимое таблицы в обоих сеансах. В первом сеансе:
```
COMMIT
student@student=> SELECT * FROM stab; msg
---------
Вставка
(1 row)
```
Во втором:
```
student@student=> SELECT * FROM stab; msg
---------
Вставка
(1 row)
```
После фиксации транзакции внесенные ей изменения в данных видны всем командам и транзакциям.

Откат при ошибке в командах транзакции

Начните транзакцию, но сделайте в какой-либо команде транзакции ошибку. Попробуйте зафиксировать транзакцию:
```
student@student=> BEGIN; BEGIN
student@student=*> амбигоус_цомманд;
ERROR: syntax error at or near "амбигоус_цомманд" LINE 1: амбигоус_цомманд;
^
student@student=!> SELECT pg_current_xact_id();
ERROR: current transaction is aborted, commands ignored until end of transaction block
student@student=!> COMMIT; ROLLBACK
```
В режиме по умолчанию, если в какой-либо команде транзакции произошла ошибка, транзакция отменяется и при попытке ее фиксации происходит откат.
Получите текущее значение встроенной переменной psql ON_ERROR_ROLLBACK. Установите значение этой переменной в on:
```
student@student=> \echo :ON_ERROR_ROLLBACK off
student@student=> \set ON_ERROR_ROLLBACK on
student@student=> \echo :ON_ERROR_ROLLBACK on
```
Снова повторите действия и пункта 3.1. Проверьте отличия:
```
student@student=> BEGIN; BEGIN
student@student=*> амбигоус_цомманд;
ERROR: syntax error at or near "амбигоус_цомманд" LINE 1: амбигоус_цомманд;
^
student@student=*> SELECT pg_current_xact_id();
pg_current_xact_id
--------------------
886
(1 row)
student@student=*> COMMIT; COMMIT
```
Установка ON_ERROR_ROLLBACK on неявно устанавливает перед каждой командой транзакции точку сохранения SAVEPOINT, и при возникновения ошибки не происходит откат всей транзакции, а откат к предыдущей успешной команде транзакции. https://www.postgresql.org/docs/15/sql-savepoint.html. Однако, в этом есть существенный минус - значительно возрастает нагрузка на сервер из-за обилия точек отката. Поэтому такой настройкой не следует злоупотреблять.

Сбросьте ON_ERROR_ROLLBACK:

student@student=> \unset ON_ERROR_ROLLBACK
student@student=> \echo :ON_ERROR_ROLLBACK off

Версии строк

Начните транзакцию в первом терминале и получите ее номер:

student@student=> BEGIN; BEGIN
student@student=*> SELECT pg_current_xact_id(); pg_current_xact_id
 --------------------
                887
(1 row)

Выполните запрос, выводящий поля xmin и xmax из заголовка строки:
```
student@student=*> SELECT xmin, xmax, * FROM stab; xmin | xmax | msg
------+------+---------
885 |    0 | Вставка
(1 row)
```
Поле xmin - номер транзакции, создавшей эту версию строки. Строка в примере была создана транзакцией 885. Поле xmax - номер транзакции, удалившей версию строки. Пока эту версию строки никакая транзакция не удаляла.
Во втором терминале также запустите транзакцию и выполните такой же запрос:
```
student@student=> BEGIN; BEGIN
student@student=*> SELECT xmin, xmax, * FROM stab; xmin | xmax | msg
------+------+---------
885 |    0 | Вставка
(1 row)
```
Пока результат ничем не отличается от транзакции в первом сеансе.
В первом сеансе измените строку командой UPDATE:
```
student@student=*> UPDATE stab SET msg = 'Обновление'; UPDATE 1
student@student=*> SELECT xmin, xmax, * FROM stab; xmin | xmax | msg
------+------+------------
887 |    0 | Обновление
(1 row)
```
Изменения, производимые в транзакции должны быть видны в ней, поэтому старую версию строки, созданную транзакцией 885 больше не видно. Но она никуда не исчезла, старая версия строки до сих пор на своем месте в странице, хотя в заголовке этой строки произошли изменения. Как их увидеть?
В снимке данных второй транзакции до сих пор должно быть видно старую версию строки, ведь фиксации первой транзакции не было:
```
student@student=*> SELECT xmin, xmax, * FROM stab;
xmin | xmax |   msg
------+------+---------
885 |  887 | Вставка
(1 row)
```
Что произошло? Во второй транзакции видно старую версию строки, созданную транзакцией 885, которая была ранее зафиксирована. В транзакции 887 была вызвана команда UPDATE, которая изменила строку: внесла в поле xmax старой версии строки значение 887 - номер транзакции, удалившей эту версию строки. Также транзакция 887 создала новую версию строки, которая в ней видна, но не видна никаким другим транзакциям, так как 887 транзакция не была зафиксирована.

Команда UPDATE эквивалентна действию DELETE, удаляющей версию строки (помечая xmax номером транзакции), плюс INSERT, добавляющей новую версию строки (помечая xmin номером этой же транзакции).
В первом терминале зафиксируйте транзакцию. Во втором в активной транзакции проверьте изменения. Первый терминал:

student@student=*> COMMIT; COMMIT

Второй терминал:

student@student=*> SELECT xmin, xmax, * FROM stab; xmin | xmax | msg
------+------+------------
887 |    0 | Обновление
(1 row)

Во втором терминале транзакция работает на уровне изоляции Read Committed. На этом уровне изоляции снимки данных строятся для каждой команды транзакции индивидуально. То есть, когда выполнялась команда SELECT в активной транзакции для нее снимок данных был построен в момент времени, когда транзакция в первом терминале была уже зафиксирована. Измененные зафиксированными транзакциями данные должны быть видны в последующих снимках. 7. Откатите транзакцию во втором терминале:

student@student=*> ROLLBACK; ROLLBACK
student@student=> SELECT xmin, xmax, * FROM stab; xmin | xmax | msg
------+------+------------
887 |    0 | Обновление
(1 row)

Уровни изоляции

Проверьте, какой уровень изоляции установлен по умолчанию:

student@student=> \dconfig def*iso*
List of configuration parameters
Parameter           |     Value
-------------------------------+----------------
default_transaction_isolation | read committed
(1 row)

В первом терминале запустите транзакцию на уровне изоляции Repeatable Read, при котором на все команды транзакции строится единственный снимок, который не изменяется невзирая на фиксацию других транзакций:
```
student@student=> BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN
student@student=*> SELECT * FROM stab; msg
------------
Обновление
(1 row)
```
Снимок данных строится при вызове первой команды в транзакции. В данном случае - SELECT. Так как уровень изоляции установлен Repeatable Read, снимок, построенный для первой команды транзакции, будет оставаться неизменным до ее завершения.
Во втором терминале выполните команду INSERT и прочитайте таблицу:
```
student@student=> INSERT INTO stab VALUES ('Еще вставка'); INSERT 0 1
student@student=> SELECT * FROM stab; msg
-------------
Обновление
Еще вставка
(2 rows)
```
В psql по умолчанию работает режим автофиксации:
```
student@student=> \echo :AUTOCOMMIT on
```
Поэтому выполнение команд DML, изменяющих данные автоматически фиксируется после каждой выполненной команды.
Изменения, внесенные INSERT, зафиксированы, но их не должно быть видно в снимке данных транзакции в первом терминале, так как снимок был получен до выполнения INSERT:
```
student@student=*> SELECT * FROM stab; msg
------------
Обновление
(1 row)
```
Зафиксируйте транзакцию. Снимок, сделанный во время работы транзакции, больше не существует:
```
student@student=*> COMMIT; COMMIT
student@student=> SELECT * FROM stab; msg
-------------
Обновление
Еще вставка
(2 rows)
```

Блокировки

В первом терминале запустите транзакцию и измените UPDATE строку "Обновление", переведя ее в верхний регистр:

student@student=> BEGIN; BEGIN
student@student=*> UPDATE stab SET msg = upper(msg) WHERE msg ~ '^Об'; UPDATE 1
student@student=*> SELECT * FROM stab; msg
-------------
Еще вставка
ОБНОВЛЕНИЕ
(2 rows)

Во втором терминале проверьте, блокируется ли чтение таблицы:
```
student@student=> SELECT * FROM stab; msg
-------------
Обновление
Еще вставка
(2 rows)
```
Чтение не блокируется изменением строк.
Во втором терминале выполните очистку командой VACUUM:
```
student@student=> VACUUM VERBOSE stab; VACUUM
```
Команда очистки не мешает параллельному выполнению запросов.
Во втором терминале выполните команду UPDATE строки "Еще вставка":
```
student@student=> UPDATE stab SET msg = upper(msg) WHERE msg ~ '^Ещ'; UPDATE 1
student@student=> SELECT * FROM stab; msg
-------------
Обновление
ЕЩЕ ВСТАВКА
(2 rows)
```
Так как транзакция на первом терминале изменила строку "Обновление" и она еще не зафиксирована, то эта строка заблокирована. Но все остальные строки - нет.
Попробуйте изменить в правом терминале заблокированную транзакцией в первом терминале строку:
```
student@student=> UPDATE stab SET msg = upper(msg) WHERE msg ~ '^Об';
```
Команда "зависла" и не выполняется. На самом деле она ожидает блокировку, занятую транзакцией в первом терминале.
Зафиксируйте транзакцию в первом терминале. При этом во втором терминале команда UPDATE выполнит свою работу. В первом терминале:
```
student@student=*> COMMIT;
COMMIT
```
Во втором терминале:
```
UPDATE 0
```

Очистка

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

student@student=> BEGIN; BEGIN
student@student=*> SELECT * FROM stab; msg
-------------
ОБНОВЛЕНИЕ
ЕЩЕ ВСТАВКА
(2 rows)

Во втором терминале выполните команду VACUUM FULL для таблицы:
```
student@student=> VACUUM FULL stab;
```
Команда не возвращает командную строку, так как ждет освобождения блокировки, наложенной командой SELECT. Обычная команда VACUUM выполнила бы очистку, не мешая работе других запросов, но команда VACUUM FULL требует эксклюзивную блокировку всей таблицы, так как должна полностью перестроить таблицу.
Завершите транзакцию и проверьте, отработает ли VACUUM FULL. В первом терминале:
```
student@student=*> END; COMMIT
```
Во втором терминале:
```
VACUUM
```
После освобождения блокировки, наложенной командой SELECT, команда очистки VACUUM FULL отработала и вернула командную строку.

Подготовка

Транзакции​

Откат при ошибке в командах транзакции​

Версии строк​

Уровни изоляции​

Блокировки​

Очистка​