🖥 Как обезопасить приложение от тяжёлых миграций, приводящих к блокированию запросов?

#PostgreSQL

Этот код не даёт 100% гарантии, а только уменьшает количество заблокированных запросов. После того, как блокировка взята, другие запросы, могут встать в очередь, ожидая отпускания блокировки.

Вначале каждой миграции, которая выполняется внутри транзакции, нужно изменить настройки конфигурации lock_timeout и statement_timeout и idle_in_transaction_session_timeout командой SET LOCAL. Действие SET LOCAL продолжается только до конца текущей транзакции, независимо от того, фиксируется она или нет. При выполнении такой команды вне блока транзакции выдаётся предупреждение и больше ничего не происходит.

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

BEGIN;
DO $$
DECLARE
exception_message text;
exception_context text;
BEGIN
/*
Задаёт максимальную длительность ожидания любым оператором получения блокировки таблицы, индекса, строки или другого объекта базы данных.
Если ожидание не закончилось за указанное время, оператор прерывается.
Это ограничение действует на каждую попытку получения блокировки по отдельности и применяется как к явным запросам блокировки
(например, LOCK TABLE или SELECT FOR UPDATE без NOWAIT), так и к неявным.
Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах.
*/
SET LOCAL lock_timeout TO '3s';
-- Максимальное время выполнения любого SQL запроса в этой транзакции. Если будет превышено, то транзакция откатится.
SET LOCAL statement_timeout TO '30min';
-- Максимальное время простаивания транзакции, PostgreSQL >= 10. Если будет превышено, то транзакция откатится.
SET LOCAL idle_in_transaction_session_timeout TO '10s';
EXCEPTION WHEN undefined_object THEN
GET STACKED DIAGNOSTICS
exception_message = MESSAGE_TEXT,
exception_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '%', exception_context;
RAISE NOTICE '%', exception_message;
END $$;


/*
Здесь SQL команды для наката (или отката) внутри транзакции
*/
COMMIT;

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

Если транзакция откатится, то есть 2 варианта: запустить повторно во время меньших нагрузок или оптимизировать код миграции, чтобы свести к минимуму блокировки.

@sqlhub

🖥 Работа с PostgreSQL в Python на русском

▪ Подключение к БД
▪ Создание таблиц
▪ Вставка и извлечение данных
▪ Обновление таблиц
▪ Удаление строк

#doc #python #postgresql #russian

@sqlhub