# Занятие 07: Производительность / Транзакции и сеансы > Исходная страница в нативном Markdown: Производительность / Транзакции и сеансы --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [pgpro_07_transactions.html](https://edu.postgrespro.ru/16/pgpro-16/pgpro_07_transactions.html) > **Archive:** [PGPRO-handouts-16.zip](https://edu.postgrespro.ru/16/PGPRO-handouts-16.zip) > **Страниц PDF:** 36 ## Page 1: Производительность / Транзакции и сеансы {#source-page-001} ### Авторские права © Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - 64-битные номера транзакций - Автономные транзакции - Данные в памяти - Встроенный пул соединений ## Page 3: 64-битные транзакции {#source-page-003} **Формат данных** - Заморозка ## Page 4: 32-битные номера {#source-page-004} ![Source slide page 4: 32-битные номера](images/page-004.png){#source-figure-004 caption="Source slide page 4: 32-битные номера"} **PostgreSQL, Postgres Pro Standard** - 32-х разрядный xid (~ 4 млрд. транзакций) - зацикливание идентификаторов - необходимость заморозки ### Комментарии преподавателя 32-битные идентификаторы транзакций допускают примерно четыре миллиарда уникальных значений. В современных реалиях при большой нагрузке идентификаторы могут закончиться через несколько дней и работа СУБД станет невозможна. Поэтому применяют заморозку: версии строк с достаточно большими xmin помечаются как очень старые и, следовательно, видимые во всех снимках данных. После заморозки версий с определенным номером во всех таблицах кластера номер можно использовать повторно (т. н. transaction wraparound). Заморозка выполняется как одна из задач процесса очистки и активно использует подсистему ввода-вывода. При большой нагрузке требуется постоянно мониторить сервер и принимать оперативные меры, если заморозка не успевает освобождать номера транзакций. ## Page 5: 64-битные номера {#source-page-005} ![Source slide page 5: 64-битные номера](images/page-005.png){#source-figure-005 caption="Source slide page 5: 64-битные номера"} **Postgres Pro Enterprise** - 64-х разрядный счетчик транзакций - не подвержены зацикливанию ```text 00000006 ``` ```text 000000c3 0fe9a1be ``` ```text 000000c3 0fe9a1c4 ``` ### Комментарии преподавателя Использование 64-битного счетчика позволило бы иметь практически бесконечное число идентификаторов транзакций, отказаться от их повторного использования и связанных с этим действий по администрированию. Но простое увеличение разрядности идентификаторов привело бы к увеличению размера заголовков версий строк и, как следствие, к повышенному расходу дискового пространства и ресурсов ввода-вывода при обработке данных. Поэтому в Postgres Pro Enterprise реализован компромиссный вариант 64-х битных идентификаторов: в специальной области в конце страницы хранится 64-битное базовое значение xid, а в полях xmin и xmax заголовка версии строки — 32-битное смещение. 64-битный номер транзакции xmin или xmax вычисляется сложением базового значения и значения, хранящегося в заголовке версии строки. Такой же формат применяется для идентификаторов мультитранзакций: базовое значение хранится в специальной области страницы, а смещение — в поле xmax заголовка версии строки. ## Page 6: Заморозка {#source-page-006} Синхронная - если смещения не хватает и необходимо увеличить базовое значение - При очистке - уменьшает pg\_xact и pg\_multixact - vacuum\_freeze\_min\_age = 50 млн - vacuum\_freeze\_table\_age = 150 млн - autovacuum\_freeze\_max\_age = 10 млрд - vacuum\_failsafe\_age = 1.6 млрд - vacuum\_multixact\_freeze\_min\_age = 5 млн - vacuum\_multixact\_freeze\_table\_age = 150 млн - autovacuum\_multixact\_freeze\_max\_age = 20 млрд - vacuum\_multixact\_failsafe\_age = 1.6 млрд ### Комментарии преподавателя Поскольку смещения в заголовке версии строки 32-битные, диапазон значений xid в пределах страницы — от базового до базового плюс 232−1. Если в какую-либо версию строки на странице нужно записать больший номер, сервер увеличивает базовое значение xid. Если при этом версии строк с небольшими номерами xid препятствуют такому увеличению, сервер замораживает эти версии «на лету». Поскольку 64-битные номера транзакций не используются повторно, своевременная заморозка не критична для работы многоверсионности: параметр autovacuum\_freeze\_max\_age имеет значение по умолчанию 10 млрд вместо 200 млн в обычном PostgreSQL, и значение autovacuum\_multixact\_freeze\_max\_age также увеличено. Однако заморозка все же выполняется как часть процедуры очистки, чтобы уменьшать размер CLOG (подкаталога pg\_xact) и статусов мультитранзакций (pg\_multixact). Ограничением этого решения является то, что очень долгие транзакции (возраст которой превышает 232), удерживающие снимок, будут мешать замораживать версии строк. Поэтому, хотя необходимость в регулярной заморозке отпадает, долгих транзакций по-прежнему стоит избегать. В PostgresPro Enterprise 17 появилась фоновая заморозка: дополнительный процесс выполняет очистку и заморозку недавно измененных страниц. Ее рекомендуется включать, если автоочистка не справляется со своими задачами. -WRAPAROUND ## Page 7: Структура страниц {#source-page-007} Чтобы заглянуть в страницы, воспользуемся расширением pageinspect. ```sql => CREATE DATABASE pgpro_transactions; ``` ```sql CREATE DATABASE ``` ```sql => \c pgpro_transactions ``` ```text You are now connected to database "pgpro_transactions" as user "student". ``` ```sql => CREATE EXTENSION pageinspect; ``` ```sql CREATE EXTENSION ``` В тестовой таблице будет две страницы по две версии строки в каждой. ```sql => CREATE TABLE test( s char(300) ) WITH (fillfactor = 10); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO test SELECT g.n FROM generate_series(1,4) AS g(n); ``` ```sql INSERT 0 4 ``` В конце каждой страницы данных находится так называемая специальная область, в которой в том числе хранится базовое значение xid: ```sql => SELECT p.page, h.xid_base FROM (VALUES (0), (1)) p(page), page_header(get_raw_page('test', p.page)) h; ``` ```text page | xid_base ------+---------- 0 | 962 1 | 962 (2 rows) ``` В заголовках версий строк хранится 32-битное смещение (хотя значения xmin и xmax для удобства выводятся уже приведенными к 64-битному виду): ```sql => SELECT ctid, xmin, xmax FROM test; ``` ```text ctid | xmin | xmax -------+------+------ (0,1) | 965 | 0 (0,2) | 965 | 0 (1,1) | 965 | 0 (1,2) | 965 | 0 (4 rows) ``` Текущее значение счетчика транзакций: ```sql SELECT pg_current_xact_id(); ``` ```text 966 ``` Пусть теперь сервер выполнит 2^32 транзакций. Выполнение такого количества транзакций работающим сервером заняло бы очень много времени, поэтому остановим его и воспользуемся утилитой pg\_resetwal, чтобы поменять текущее значение счетчика. ```sh student$ sudo systemctl stop postgrespro-ent-16.service ``` ```sh postgres$ /opt/pgpro/ent-16/bin/pg_resetwal -x 4294968262 -D /var/lib/pgpro/ent-16 ``` ```text Write-ahead log reset ``` Для гарантированного запуска сервера после изменения счетчика создадим пустой файл статусов транзакций: ```sh student$ sudo -u postgres truncate /var/lib/pgpro/ent-16/pg_xact/000000000040 --size=16M ``` ```sh student$ sudo systemctl start postgrespro-ent-16.service ``` ## Page 8: Page 8 {#source-page-008} ```sh student$ psql pgpro_transactions ``` ```sql => SELECT pg_current_xact_id(); ``` ```text pg_current_xact_id -------------------- 4294968262 (1 row) ``` > [!NOTE] > **Внимание!** > Категорически запрещается применять утилиту pg\_resetwal в производственной среде за исключением использования ее по прямому указанию технической поддержки. Утилита изменяет внутренние структуры данных, что может привести к потере или искажению информации. Теперь изменим одну строку таблицы. ```sql => UPDATE test SET s = '10' WHERE s = '1'; ``` ```sql UPDATE 1 ``` При этом сервер запишет номер текущей транзакции в поле xmax старой версии строки и в поле xmin новой версии, которая будет размещена в нулевой странице. Поскольку номер текущей транзакции отличается от значений xmin в существующих версиях более чем на 2^32, сервер увеличит базовый xid и выполнит локальную заморозку в нулевой странице. Для этого он выставит в заголовках версий соответствующий флаг, а в поле xmin запишет зарезервированное значение 2, означающее «номер заморожен». В первой странице заморозка не делается, поэтому базовый номер xid изменился только в нулевой странице. ```sql => SELECT (p.page, i.lp) ctid, i.t_xmin, i.t_xmax, h.xid_base, f.combined_flags FROM (VALUES (0), (1)) p(page), heap_page_items(get_raw_page('test', p.page)) i, page_header(get_raw_page('test', p.page)) h, heap_tuple_infomask_flags(t_infomask, t_infomask2) f; ``` ```text ctid | t_xmin | t_xmax | xid_base | combined_flags -------+------------+------------+------------+-------------------- (0,1) | 2 | 4294968263 | 4278191045 | {HEAP_XMIN_FROZEN} (0,2) | 2 | 0 | 4278191045 | {HEAP_XMIN_FROZEN} (0,3) | 4294968263 | 0 | 4278191045 | {} (1,1) | 965 | 0 | 962 | {} (1,2) | 965 | 0 | 962 | {} (5 rows) ``` ## Page 9: Автономные транзакции {#source-page-009} **Устройство автономных транзакций** - Видимость данных в автономных транзакциях - Автономные транзакции в SQL - Автономные транзакции в PL/pgSQL и PL/Python - Ограничения автономных транзакций ## Page 10: Автономные транзакции {#source-page-010} Автономная транзакция — это независимая транзакция, которая запускается внутри родительской и должна быть завершена (зафиксирована или отменена) до завершения родительской. ```text BEGIN; команды ... BEGIN AUTONOMOUS [ISOLATION LEVEL ...]; команды ... COMMIT [AUTONOMOUS]; -- или ROLLBACK команды ... COMMIT; -- или ROLLBACK ``` Автономная транзакция может включать в себя другие автономные транзакции (транзакция верхнего уровня не может быть автономной). Когда вложенная автономная транзакция запускается, ее родительская транзакция приостанавливается и помещается в стек автономных транзакций. Когда автономная транзакция фиксируется или отменяется, родительская транзакция извлекается из стека и продолжает работу. Таким образом, на каждом уровне вложенности активной является только одна транзакция. Поэтому внутри автономной транзакции команды COMMIT или ROLLBACK будут завершать эту автономную транзакцию, а не родительскую. В отличие от вложенной транзакции, которая возникает в обычном PostgreSQL при выполнении команды SAVEPOINT (и, возможно, ROLLBACK TO), результат автономной транзакции не зависит от исхода родительской. В процедурных языках автономные транзакции тоже завершаются до завершения родительских, но происходит это неявно. Например, в PL/pgSQL добавлена конструкция для блока, операторы которого должны выполняться в автономной транзакции: ```text BEGIN AUTONOMOUS операторы ... END; ``` По сути, такая запись — просто сокращение для ```text BEGIN BEGIN AUTONOMOUS; операторы ... COMMIT; END; ``` Ошибка в блоке с автономной транзакцией приводит к ее откату, после чего ошибка обрабатывается в секции EXCEPTION или передается в вызывающий блок. В PL/Python добавлен метод, позволяющий выполнить SQL в автономной транзакции: ```text with plpy.autonomous(): plpy.execute(...) ``` ## Page 11: Видимость {#source-page-011} **Снимки родительской и автономных транзакций независимы** - Read Committed видит изменения - Repeatable Read, Serializable не видят изменений - Возможны взаимоблокировки ### Комментарии преподавателя Родительская и дочерняя автономная транзакция используют собственные снимки данных, время жизни которых зависит от уровня изоляции. Поэтому родительская транзакция с уровнем изоляции Read Committed будет видеть все изменения дочерних автономных транзакций, а родительская с уровнем изоляции Repeatable Read или Serializable не увидит никаких изменений. Автономная транзакция может вызвать взаимоблокировку, если она попытается получить блокировку ресурса, уже заблокированного родительской транзакцией. ## Page 12: Ограничения {#source-page-012} **max\\\_autonomous\\\_transactions = 100** - Вложенность до 128 уровней - Не поддерживается блокировка FOR UPDATE - Нельзя обращаться к временным схемам - Нельзя изменить уровень изоляции в PL/pgSQL и PL/Python - Не поддерживаются в расширениях - in\_memory - online\_analyze - Нельзя создавать слоты логической репликации ### Комментарии преподавателя Одновременно можно запустить не более max\_autonomous\_transactions автономных транзакций во всех сеансах (по умолчанию 100). Глубина вложенности автономных транзакций — не более 128. FOR UPDATE не поддерживается, поэтому, например, нельзя заблокировать строку в родительской транзакции, а в дочерней автономной пропустить ее с помощью SKIP LOCKED. В автономной транзакции нельзя обращаться к объектам временных схем, в частности, ко временным таблицам. В блоке PL/pgSQL и методе PL/Python нельзя задать уровень изоляции автономной транзакции, отличающийся от текущего. Автономные транзакции не работают с расширениями in\_memory и online\_analyze. Также нельзя создавать слоты логической репликации из автономной транзакции. ## Page 13: Использование автономных транзакций {#source-page-013} Обычно автономные транзакции используются для аудита, когда нужно сохранить информацию о событии, произошедшем в родительской транзакции, независимо от ее исхода. Опустошим таблицу: ```text => TRUNCATE test; ``` ```text TRUNCATE TABLE ``` Мы хотим записывать в таблицу test\_audit информацию обо всех изменениях таблицы test, в том числе и о тех, которые не были зафиксированы. ```sql => CREATE TABLE test_audit( time timestamptz, username text, operation text ); ``` ```sql CREATE TABLE ``` Для этого понадобятся триггер и триггерная функция, тело которой выполняется в автономной транзакции. ```sql => CREATE FUNCTION test_audit() RETURNS trigger AS $$ BEGIN AUTONOMOUS INSERT INTO test_audit VALUES (now(), current_user, tg_op); RETURN new; END; $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => CREATE TRIGGER test_audit AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE FUNCTION test_audit(); ``` ```sql CREATE TRIGGER ``` Теперь информация об изменениях будет сохраняться в таблице test\_audit, даже если транзакция впоследствии обрывается. ```text => BEGIN; ``` ```text BEGIN ``` ```sql => INSERT INTO test VALUES ('value1'),('value2'); ``` ```sql INSERT 0 2 ``` ```sql => UPDATE test SET s = 'value3'; ``` ```sql UPDATE 2 ``` ```sql => DELETE FROM test; ``` ```sql DELETE 2 ``` Откатим транзакцию: ```text => ROLLBACK; ``` ```text ROLLBACK ``` В таблице ничего не осталось: ```sql => SELECT * FROM test; ``` ```text s --- (0 rows) ``` ```sql => SELECT * FROM test_audit; ``` ## Page 14: Page 14 {#source-page-014} ```sql time | username | operation -------------------------------+----------+----------- 2026-07-27 23:39:08.42909+03 | student | INSERT 2026-07-27 23:39:08.42909+03 | student | INSERT 2026-07-27 23:39:08.488845+03 | student | UPDATE 2026-07-27 23:39:08.488845+03 | student | UPDATE 2026-07-27 23:39:08.537416+03 | student | DELETE 2026-07-27 23:39:08.537416+03 | student | DELETE (6 rows) ``` Однако попытки изменить данные записаны в таблицу аудита. ## Page 15: Данные в памяти {#source-page-015} **pg\\\_variables** - in\_memory ## Page 16: Расширение pg_variables {#source-page-016} **Функции для работы с переменными различных типов** - скалярные - массивы - коллекции записей и произвольных типов - Поддержка транзакционности - Хранение в памяти сеанса - работает на реплике - несовместимо с пулами соединений ### Комментарии преподавателя Расширение pg\_variables позволяет работать в памяти сеанса со скалярными переменными, массивами и коллекциями. Переменные объединяются в так называемые пакеты, выполняющие роль пространств имен. Коллекции представляют собой множества пар ключ—значение. В качестве таких пар могут выступать обычные составные значения (записи), в которых первый атрибут считается ключом. Это позволяет использовать коллекции в качестве быстрой альтернативы временным таблицам, которая к тому же работает и на физических репликах. Поддерживаются как транзакционные, так и нетранзакционные переменные. Изменения нетранзакционных переменных: создание, изменение, удаление – необратимы. Изменения транзакционных переменных можно отменить, откатив транзакцию полностью или до точки сохранения. Но в любом случае переменная существует только до конца сеанса и другим сеансам не видна. Утилита ora2pgpro, рассмотренная в теме «Миграция», использует pg\_variables для экспорта ассоциативных массивов Oracle в коллекции. Расширение несовместимо с пулом соединений, включая встроенный пул Postgres Pro. ## Page 17: Расширение pg_variables {#source-page-017} Установим расширение в базе данных: ```sql => CREATE EXTENSION pg_variables; ``` ```sql CREATE EXTENSION ``` Расширение оперирует термином «пакет», под которым понимается пространство имен переменных. Команды ниже автоматически создадут пакет vars (первый параметр), поместят в него две переменные int\_n и int\_t (второй параметр) целочисленного типа (тип определяется по фактическому типу третьего параметра) и зададут им значения. Первая переменная нетранзакционная (по умолчанию): ```sql => SELECT pgv_set('vars', 'int_n', 10); ``` ```text pgv_set --------- (1 row) ``` Вторая — транзакционная: ```sql => SELECT pgv_set('vars', 'int_t', 20, is_transactional => true); ``` ```text pgv_set --------- (1 row) ``` С помощью функций можно получить информацию о переменных и прочитать их значения: ```sql => SELECT * FROM pgv_list() ORDER BY package, name; ``` ```text package | name | is_transactional ---------+-------+------------------ vars | int_n | f vars | int_t | t (2 rows) ``` ```sql => SELECT pgv_get('vars', 'int_n', NULL::integer), pgv_get('vars', 'int_t', NULL::integer); ``` ```text pgv_get | pgv_get ---------+--------- 10 | 20 (1 row) ``` Обратите внимание, что функции необходимо передавать третий параметр, чтобы сообщить тип скалярной переменной. Проверим транзакционность переменных, откатив транзакцию: ```text => BEGIN; ``` ```text BEGIN ``` ```sql => SELECT pgv_set('vars', 'int_n', 111), pgv_set('vars', 'int_t', 222, true); ``` ```text pgv_set | pgv_set ---------+--------- | (1 row) ``` ```text => ROLLBACK; ``` ```text ROLLBACK ``` Читаем значения переменных: ```sql => SELECT pgv_get('vars', 'int_n', NULL::int), pgv_get('vars', 'int_t', NULL::int); ``` ```text pgv_get | pgv_get ---------+--------- 111 | 20 (1 row) ``` ## Page 18: Page 18 {#source-page-018} Обратите внимание, что необходимо указывать флаг is\_transactional при каждом изменении значения транзакционной переменной. В противном случае произойдет ошибка: ```sql => SELECT pgv_set('vars', 'int_t', 333); ``` ```text ERROR: variable "int_t" already created as TRANSACTIONAL ``` Работа с массивами организована аналогично. Рассмотрим работу с коллекциями записей. Запрос ниже вставляет в коллекцию tbl\_1 пакета tbls несколько записей. Если пакет или коллекция не существуют (как в нашем случае), они будут созданы автоматически: ```sql => SELECT pgv_insert('tbls', 'tbl_1', tbl) FROM (SELECT gs, 'Line_' || gs FROM generate_series(1, 4) AS gs) AS tbl; ``` ```text pgv_insert ------------ (4 rows) ``` Первое поле каждой записи выступает в качестве первичного ключа. Если при вставке обнаружится запись с таким же ключом, будет выдана ошибка: ```sql => SELECT pgv_insert('tbls', 'tbl_1', (2, 'Another 2nd line')); ``` ```text ERROR: there is an element in the variable "tbl_1" with same key ``` Мы можем изменять или удалять записи, обращаясь к ним по ключу: ```sql => SELECT pgv_update('tbls', 'tbl_1', (2, '2nd line again')); ``` ```text pgv_update ------------ t (1 row) ``` ```sql => SELECT pgv_delete('tbls', 'tbl_1', 3); ``` ```text pgv_delete ------------ t (1 row) ``` Что получилось в результате? Прочитаем все данные из коллекции: ```sql => SELECT pgv_select('tbls', 'tbl_1'); ``` ```text pgv_select ---------------------- (1,Line_1) (2,"2nd line again") (4,Line_4) (3 rows) ``` Данные в коллекциях хранятся в порядке возрастания, по записям удобно перемещаться с помощью итераторов: ```text => DO $$ DECLARE key integer; BEGIN key := pgv_first('tbls', 'tbl_1', NULL::int); WHILE (key IS NOT NULL) LOOP RAISE NOTICE '%', key; key := pgv_next('tbls', 'tbl_1', key); END LOOP; END; $$; ``` ```text NOTICE: 1 NOTICE: 2 NOTICE: 4 DO ``` Можно двигаться и в обратную сторону с помощью функций pgv\_last и pgv\_prior. ## Page 19: Page 19 {#source-page-019} Количество элементов в коллекции вернет функция pgv\_count: ```sql => SELECT pgv_count('tbls', 'tbl_1'); ``` ```text pgv_count ----------- 3 (1 row) ``` Получить список созданных пакетов и объем памяти, используемый переменными можно так: ```sql => SELECT * FROM pgv_stats(); ``` ```text package | allocated_memory ---------+------------------ tbls | 16384 vars | 16384 (2 rows) ``` Удалить переменную и весь пакет целиком можно при помощи функции pgv\_remove: ```sql => SELECT pgv_remove('vars', 'int_n'); -- удаляем переменную ``` ```text pgv_remove ------------ (1 row) ``` ```sql => SELECT pgv_exists('vars', 'int_n'); -- проверка существования переменной: ее уже нет ``` ```text pgv_exists ------------ f (1 row) ``` ```sql => SELECT pgv_remove('vars'); -- удаляем весь пакет ``` ```text pgv_remove ------------ (1 row) ``` Функция pgv\_free удаляет все пакеты и переменные: ```sql => SELECT pgv_free(); ``` ```text pgv_free ---------- (1 row) ``` ## Page 20: Расширение in_memory {#source-page-020} **Обертка сторонних данных для оперативных таблиц** - таблицы организованы по индексу - данные располагаются в листовых страницах B-дерева - Поддержка транзакционности - Хранение в общей памяти сервера - работает на реплике - несовместимо со встроенным пулом соединений и подготовленными - транзакциями ### Комментарии преподавателя С помощью расширения in\_memory можно создавать небольшие оперативные таблицы, то есть таблицы в общей памяти сервера. Работа с таблицей происходит с помощью обертки сторонних данных. Оперативные таблицы организованы по индексу — строки таблиц хранятся в страницах листьев индекса B-tree, что обеспечивает быстрый доступ к данным по первичному ключу (вторичные индексы не поддерживаются). Для экономии памяти используются небольшие страницы размером 1 Кбайт. Поскольку индекс B-tree должен содержать на странице минимум три записи, максимальная длина строки ограничена 304 байтами (TOAST не поддерживается). Когда строки удаляются из оперативной таблицы, соответствующие страницы данных не освобождаются; для освобождения ресурсов требуется опустошение или удаление таблицы. Оперативные таблицы полностью поддерживают транзакции, включая точки сохранения. Однако уровень изоляции Serializable не поддерживается, вместо него используется Repeatable Read. Оперативные таблицы существуют только в памяти: они не сохраняются на диск и не журналируются, но их можно использовать на физических репликах. Расширение нельзя использовать с подготовленными (двухфазными) транзакциями и при работе со встроенным пулом соединений. ## Page 21: Расширение in_memory {#source-page-021} Для работы расширения необходимо загрузить библиотеку и перезапустить СУБД: ```sql => ALTER SYSTEM SET shared_preload_libraries = 'in_memory'; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ```sh student$ psql pgpro_transactions ``` Теперь установим расширение в базу: ```sql => CREATE EXTENSION in_memory; ``` ```sql CREATE EXTENSION ``` Доступ к оперативным таблицам организуется с помощью обертки сторонних данных, для этого создан сторонний сервер in\_memory: ```text => \des ``` ```text List of foreign servers Name | Owner | Foreign-data wrapper -----------+---------+---------------------- in_memory | student | in_memory_fdw (1 row) ``` Далее следует создать стороннюю таблицу на этом сервере — это и будет оперативная таблица: ```sql => CREATE FOREIGN TABLE optable ( id integer NOT NULL, note text ) SERVER in_memory; ``` ```sql CREATE FOREIGN TABLE ``` По умолчанию в таблице будет создан уникальный индекс B-tree по первому столбцу, с сортировкой по возрастанию. При необходимости можно указать другую структуру индекса в параметре indices предложения OPTIONS (несмотря на множественное число названия параметра, индекс может быть только один): ```sql => CREATE FOREIGN TABLE optable_bis ( id integer, note text NOT NULL ) SERVER in_memory OPTIONS (indices 'UNIQUE (note)'); ``` ```sql CREATE FOREIGN TABLE ``` Для столбцов можно указать правило сортировки, но всегда используется класс операторов по умолчанию. Оперативные таблицы созданы: ```text => \det+ ``` ```text List of foreign tables Schema | Table | Server | FDW options | Description --------+-------------+-----------+---------------------------+------------- public | optable | in_memory | | public | optable_bis | in_memory | (indices 'UNIQUE (note)') | (2 rows) ``` С таблицами можно выполнять все основные операции DML. Внесем данные и прочитаем несколько строк: ```sql => INSERT INTO optable (SELECT gs, 'Line_' || gs FROM generate_series(1, 100) AS gs); ``` ```sql INSERT 0 100 ``` ```sql => SELECT * FROM optable LIMIT 3; ``` ## Page 22: Page 22 {#source-page-022} ```text id | note ----+-------- 1 | Line_1 2 | Line_2 3 | Line_3 (3 rows) ``` В отличие от коллекций расширения pg\_variables, оперативные таблицы хранятся в общей памяти сервера и доступны другим сеансам: ```sh student$ psql pgpro_transactions ``` ```sql => SELECT count(*) FROM optable; ``` ```text count ------- 100 (1 row) ``` ```sql => UPDATE optable SET note = 'Updated line' WHERE id = 100; ``` ```sql UPDATE 1 ``` ```sql => SELECT * FROM optable WHERE id = 100; ``` ```text id | note -----+-------------- 100 | Updated line (1 row) ``` Объем памяти, выделяемый для оперативных таблиц, задается параметром in\_memory.shared\_pool\_size. Количество использованных и свободных страниц можно получить с помощью функции: ```sql => SELECT * FROM in_memory_page_stats(); ``` ```text busy_pages | free_pages | all_pages ------------+------------+----------- 10 | 8182 | 8192 (1 row) ``` При удалении строк из оперативных таблиц командой DELETE страницы данных не освобождаются: ```sql => DELETE FROM optable; ``` ```sql DELETE 100 ``` ```sql => SELECT busy_pages FROM in_memory_page_stats(); ``` ```text busy_pages ------------ 10 (1 row) ``` Чтобы освободить страницы, можно опустошить (TRUNCATE) или удалить таблицу. Также содержимое таблицы пропадает при перезапуске сервера: ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ```sh student$ psql pgpro_transactions ``` ```sql => SELECT busy_pages FROM in_memory_page_stats(); ``` ```text busy_pages ------------ 2 (1 row) ``` ## Page 23: Пул соединений {#source-page-023} **Проблема и решения** - Встроенный пул - Подключение - Ограничения пула ## Page 24: Проблема и решения {#source-page-024} **Один сеанс — один обслуживающий процесс** - повышенный расход ресурсов - увеличение конкуренции - прогрев локальных кешей - Пул соединений в сервере приложений или драйвере - J2EE, JDBC, ODBC, node-postgres, Psycopg2 - Пул соединений как сторонний продукт - PgBouncer, Odyssey - Встроенный пул Postgres Pro Enterprise сохраняет контекст сеанса ### Комментарии преподавателя Простейшая архитектура, при которой для каждого клиентского соединения запускается отдельный обслуживающий процесс, имеет ряд недостатков, которые при большом количестве клиентов становятся критичными. Увеличивается конкуренция при обращении к ресурсам, растут накладные расходы на запуск процессов (в частности, на наполнение кешей) и освобождение ресурсов при их завершении. Кроме того, размер многих структур в общей памяти сервера пропорционален числу клиентских соединений. По этим причинам в нагруженные многопользовательские системы добавляют пул соединений, позволяющий нескольким клиентам использовать одно соединение с сервером. Как правило, для этого либо применяют отдельное решение, такое как PgBouncer или Odyssey, либо задействуют возможности сервера приложений или драйвера PostgreSQL. При таком подходе администратору приходится обслуживать дополнительную компоненту архитектуры, а разработчику — учитывать особенности работы приложения с пулом соединений. Встроенный пул соединений Postgres Pro Enterprise позволяет воспользоваться преимуществами пула, сохранив при этом большинство возможностей, связанных с контекстом сеанса. В 17-й версии Postgres Pro Enterprise на смену встроенному пулу придет proxima — расширение, объединяющее функциональность прокси-сервера и пула соединений. ## Page 25: Встроенный пул {#source-page-025} ![Source slide page 25: Встроенный пул](images/page-025.png){#source-figure-025 caption="Source slide page 25: Встроенный пул"} ### Комментарии преподавателя Для каждой базы данных создается отдельный пул, количество пулов не ограничено. Если количество процессов в пуле меньше значения session\_pool\_size, для нового сеанса запускается новый процесс, а если предел достигнут, сеанс привязывается к одному из существующих процессов. Процесс, к которому привязано несколько сеансов, поочередно обслуживает их транзакции, сохраняя при этом контекст каждого сеанса. Поэтому значение session\_pool\_size следует задавать достаточно большим, чтобы клиент не ждал слишком долго, пока завершится транзакция другого соединения. Одновременно процесс обслуживает не больше max\_sessions сеансов. Если число соединений с одной базой достигнет max\_sessions × × session\_pool\_size, следующая попытка подключения вернет ошибку. Если клиент соединяется с базой данных, входящей в список dedicated\_databases, или подключается под ролью, входящей в dedicated\_users, для него будет создан выделенный процесс, не входящий в пулы. По умолчанию это сеансы пользователя postgres и подключения к базам template0, template1 и postgres. Количество выделенных процессов не ограничено. ## Page 26: Подключение {#source-page-026} ![Source slide page 26: Подключение](images/page-026.png){#source-figure-026 caption="Source slide page 26: Подключение"} ### Комментарии преподавателя Подключение нового клиента устроено следующим образом. Процесс postmaster слушает входящие соединения. Подключаясь, клиент передает стартовый пакет данных, в котором указаны параметры соединения — имя базы данных, имя роли. Postmaster передает этот пакет одному из процессов-приемников для анализа. Количество процессов-приемников определяется значением параметра connection\_pool\_workers и должно быть достаточным, чтобы анализ входящих соединений не стал узким местом. Получив от приемника информацию о базе данных и роли, postmaster направляет соединение в подходящий пул. Применяется одна из трех стратегий, которые можно указать в параметре session\_schedule: round-robin назначает процессы по очереди, random — случайным образом, load-balancing выбирает процесс с наименьшим числом сеансов. Когда все соединения, использующие какой-то процесс, завершаются, процесс продолжает работать. Автоматическое завершение неиспользуемого процесса по истечении определенного времени настраивается параметром idle\_pool\_backend\_timeout. Для перезагрузки рабочих процессов можно установить параметр restart\_pooler\_on\_reload = true и перечитать конфигурацию. ## Page 27: Ограничения пула {#source-page-027} **Не поддерживаются SSL-соединения** - Долгие транзакции задерживают работу других соединений - Не поддерживаются рекомендательные блокировки уровня сеанса - Нельзя перехватывать ошибку «connection to client lost» - Не работают модули, полагающиеся на состояние сеанса - Нельзя изменять параметры в других сеансах - Остановка сервера в режиме smart работает как fast - Параметр idle\_session\_timeout игнорируется ### Комментарии преподавателя Нужно учитывать следующие ограничения при использовании пула. ● SSL-соединения в текущей реализации не поддерживаются. ● Долгая транзакция будет задерживать работу транзакций в других соединениях, использующих тот же процесс. ● Ошибка «connection to client lost» обрабатывается пулом, чтобы завершать зависшие соединения по тайм-ауту, перехватывать ее не нужно. ● Не работают модули, использующие состояние на уровне сеанса, такие как plantuner, а также расширения multimaster, in\_memory, online\_analyze, pg\_variables. ● Нельзя изменять параметры других сеансов. ● Не поддерживается передача сообщений между процессами (команды LISTEN, NOTIFY и т. п.). ● Остановка сервера в режиме smart работает как fast. ● Решения 1С не поддерживаются, они используют собственный пул соединений. ● Тайм-аут idle\_session\_timeout не работает. ## Page 28: Встроенный пул соединений {#source-page-028} Выделим в пуле по два процесса для каждой базы данных: ```sql => ALTER SYSTEM SET session_pool_size=2; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` Если начать транзакцию в основном сеансе, ей будет выделен один из процессов: ```sh student$ psql ``` ```sql => \c pgpro_transactions ``` ```text You are now connected to database "pgpro_transactions" as user "student". ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => SELECT pg_backend_pid(); ``` ```text pg_backend_pid ---------------- 806894 (1 row) ``` Сеанс может хранить свое состояние в пользовательских параметрах: ```sql => SELECT set_config('my.name', 'Я — сеанс 1', false); ``` ```text set_config ------------- Я — сеанс 1 (1 row) ``` Транзакции, начавшейся в другом сеансе, будет выделен другой процесс: ```sh student$ psql ``` ```sql => \c pgpro_transactions ``` ```text You are now connected to database "pgpro_transactions" as user "student". ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => SELECT pg_backend_pid(); ``` ```text pg_backend_pid ---------------- 807105 (1 row) ``` ```sql => SELECT set_config('my.name', 'Я — сеанс 2', false); ``` ```text set_config ------------- Я — сеанс 2 (1 row) ``` По умолчанию процессы выделяются по очереди (стратегия round-robin), поэтому третьему сеансу будет назначен процесс 806894. Но этот процесс занят выполнением транзакции первого сеанса, поэтому третий сеанс будет ждать: ```sh student$ psql ``` ```sql => \c pgpro_transactions ``` Когда транзакция первого сеанса заканчивается... ```text => COMMIT; ``` ```text COMMIT ``` ...третий сеанс может воспользоваться освободившимся процессом 806894: ## Page 29: Page 29 {#source-page-029} ![Source slide page 29: Page 29](images/page-029.png){#source-figure-029 caption="Source slide page 29: Page 29"} ```text You are now connected to database "pgpro_transactions" as user "student". ``` ```sql => SELECT pg_backend_pid(); ``` ```text pg_backend_pid ---------------- 806894 (1 row) ``` ```sql => SELECT set_config('my.name', 'Я — сеанс 3', false); ``` ```text set_config ------------- Я — сеанс 3 (1 row) ``` Заметим, что состояние сеансов сохраняется, несмотря на то что процесс выполняет транзакции разных сеансов: ```sql => SELECT pg_backend_pid(), current_setting('my.name'); ``` ```text pg_backend_pid | current_setting ----------------+----------------- 806894 | Я — сеанс 1 (1 row) ``` ```sql => SELECT pg_backend_pid(), current_setting('my.name'); ``` ```text pg_backend_pid | current_setting ----------------+----------------- 806894 | Я — сеанс 3 (1 row) ``` ## Page 30: Итоги {#source-page-030} **64-битные идентификаторы транзакций упрощают обслуживание данных при высокой нагрузке** - Автономные транзакции дают возможность удобно и эффективно реализовать аудит - Расширения pg\_variables и in\_memory могут служить альтернативой временным таблицам - Встроенный пул позволяет ограничить число соединений, не жертвуя контекстом сеанса ## Page 31: Практика {#source-page-031} 1. Воспроизведите взаимоблокировку основной транзакции и вложенной в нее автономной. 2. Реализуйте аудит изменений данных в таблице двумя способами: используя расширение dblink и автономные транзакции. Сравните производительность. 3. Убедитесь, что при использовании встроенного пула соединений сохраняется содержимое временных таблиц уровня сеанса. ## Page 32: 1. Взаимоблокировка в автономной транзакции {#source-page-032} Создадим базу данных: ```sql => CREATE DATABASE pgpro_transactions; ``` ```sql CREATE DATABASE ``` ```sql => \c pgpro_transactions ``` ```text You are now connected to database "pgpro_transactions" as user "student". ``` ```sql => CREATE TABLE t1 AS SELECT 1 n; ``` ```sql SELECT 1 ``` Начинаем транзакцию и блокируем строку: ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t1 SET n=2; ``` ```sql UPDATE 1 ``` Пытаемся блокировать ту же строку в автономной транзакции и получаем ошибку: ```text => BEGIN AUTONOMOUS; ``` ```text BEGIN ``` ```sql => UPDATE t1 SET n=3; ``` ```text ERROR: an ATX waiting for an ancestor transaction will cause a deadlock ``` ```text => END AUTONOMOUS; ``` ```text ROLLBACK ``` Основную транзакцию можно зафиксировать: ```text => COMMIT; ``` ```text COMMIT ``` ### 2. Аудит: dblink и автономные транзакции Тестовая таблица: ```sql => CREATE TABLE test(n int); ``` ```sql CREATE TABLE ``` Таблица для аудита: ```sql => CREATE TABLE test_audit( time timestamptz, username text, operation text ); ``` ```sql CREATE TABLE ``` Первый вариант триггерной функции использует расширение dblink: ```sql => CREATE EXTENSION dblink; ``` ```sql CREATE EXTENSION ``` ```sql => CREATE OR REPLACE FUNCTION test_audit() RETURNS trigger AS $$ BEGIN PERFORM dblink( 'dbname='||current_database(), 'INSERT INTO test_audit VALUES (now(), current_user, '''||tg_op||''')' ); RETURN new; END; $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ## Page 33: Page 33 {#source-page-033} Триггер: ```sql => CREATE TRIGGER test_audit AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE FUNCTION test_audit(); ``` ```sql CREATE TRIGGER ``` Замеряем время. ```text => \timing on ``` ```text Timing is on. ``` ```sql => INSERT INTO test SELECT g.s FROM generate_series(1,500) AS g(s); ``` ```sql INSERT 0 500 Time: 8614,633 ms (00:08,615) ``` ```text => \timing off ``` ```text Timing is off. ``` Опустошим обе таблицы и заменим триггерную функцию на вариант, использующий автономную транзакцию. ```text => TRUNCATE test; ``` ```text TRUNCATE TABLE ``` ```text => TRUNCATE test_audit; ``` ```text TRUNCATE TABLE ``` ```sql => CREATE OR REPLACE FUNCTION test_audit() RETURNS trigger AS $$ BEGIN AUTONOMOUS INSERT INTO test_audit VALUES (now(), current_user, tg_op); RETURN new; END; $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` Еще раз замеряем время. ```text => \timing on ``` ```text Timing is on. ``` ```sql => INSERT INTO test SELECT g.s FROM generate_series(1,500) AS g(s); ``` ```sql INSERT 0 500 Time: 3739,022 ms (00:03,739) ``` ```text => \timing off ``` ```text Timing is off. ``` Автономные транзакции работают быстрее, поскольку нет накладных расходов на установку соединения. ### 3. Временные таблицы и пул соединений Включим пул соединений, выделив один процесс для каждой базы данных. Транзакции сеансов будут использовать его по очереди: ```sql => ALTER SYSTEM SET session_pool_size=1; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ```sh student$ psql ``` ```sh student$ psql ``` В каждом из двух сеансов создадим временную таблицу. ```sql => CREATE TEMP TABLE temp ( session int DEFAULT 1, pid int DEFAULT pg_backend_pid() ); ``` ```sql CREATE TABLE ``` ## Page 34: Page 34 {#source-page-034} ![Source slide page 34: Page 34](images/page-034.png){#source-figure-034 caption="Source slide page 34: Page 34"} ```sql => CREATE TEMP TABLE temp ( session int DEFAULT 2, pid int DEFAULT pg_backend_pid() ); ``` ```sql CREATE TABLE ``` Теперь пусть сеансы по очереди вставляют строки в таблицы. ```sql => INSERT INTO temp SELECT; ``` ```sql INSERT 0 1 ``` ```sql => INSERT INTO temp SELECT; ``` ```sql INSERT 0 1 ``` ```sql => INSERT INTO temp SELECT; ``` ```sql INSERT 0 1 ``` ```sql => INSERT INTO temp SELECT; ``` ```sql INSERT 0 1 ``` Вот содержимое временных таблиц: ```sql => SELECT * FROM temp; ``` ```text session | pid ---------+-------- 1 | 876870 1 | 876870 (2 rows) ``` ```sql => SELECT * FROM temp; ``` ```text session | pid ---------+-------- 2 | 876870 2 | 876870 (2 rows) ``` Встроенный пул соединений Postgres Pro Enterprise сохраняет временные таблицы уровня сеанса. При использовании сторонних менеджеров пула сохранение контекста сеанса не гарантируется. ### Уборка Удалим созданную базу данных и выключим пул соединений. ```sql => \c student ``` ```text You are now connected to database "student" as user "student". ``` ```sql => DROP DATABASE pgpro_transactions WITH (FORCE); ``` ```sql DROP DATABASE ``` ```sql => ALTER SYSTEM RESET session_pool_size; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ## Page 35: Практика PPEM {#source-page-035} 1. Создайте базу данных newbase и в ней схему dblink. 2. Установите расширение dblink в одноименную схему базы newbase. 3. Используя расширение dblink и консоль psql, выведите список моделей самолетов из базы данных demo. ### Комментарии преподавателя 3. Получить данные по моделям самолетов можно с помощью запроса SELECT \* FROM dblink.dblink( 'dbname=demo', 'SELECT model FROM aircrafts' ) AS (m text); ## Page 36: 1. Создание базы данных и схемы {#source-page-036} В навигационной панели выберите «Базы данных» и на главной странице нажмите «Создать базу данных». В открывшемся окне заполните необходимые поля и нажмите «Сохранить» — новая база появится в списке. Выбрав новую базу, нажмите «Создать схему», заполните поля на форме и выберите «Создать». ### 2. Установка расширения dblink Выберите новую базу в списке на главной странице и на вкладке «Расширения» навигационной панели нажмите «Установить расширение». В открывшейся форме выберите из списка баз newbase, в списке доступных расширений найдите dblink и выберите для установки созданную в п. 1 одноименную схему. Нажмите кнопку «Установить». ### 3. Работа с psql-консолью. Доступ к удаленной базе с помощью dblink В списке «Базы данных» главной страницы найдите newbase, и в столбце «Действия» нажмите на пиктограмму «\{/\}» (открыть PSQL-консоль) — откроется окно утилиты с приглашением, указывающим на подключение к выбранной базе. Для получения требуемой информации наберите и выполните запрос: ```sql SELECT * FROM dblink.dblink( 'dbname=demo', 'SELECT model FROM aircrafts' ) AS (m text); ``` Чтобы скопировать текст запроса из буфера обмена, используйте клавиши Ctrl+V, затем в контекстном меню выберите Paste или нажмите клавишу P.