SQL HowTo: наперегонки со временем

GuDron

dumpz.ws
Admin
Регистрация
28 Янв 2020
Сообщения
7,552
Реакции
1,435
Credits
24,378

SQL HowTo: наперегонки со временем​

37e24d9f09122860a62775f56ac7a013.jpeg

В PostgreSQL несложно написать запрос, который уйдет в глубокую рекурсию или просто будет выполняться гораздо дольше, чем нам хотелось бы. Как от этого защититься?
А чтобы еще и полезную работу сделать? Например, набрать следующий сегмент данных при постраничной навигации со сложным условием фильтрации.

statement_timeout​

Очевидное решение - использовать те средства, которые нам дает для этого сама база: установить устраивающее нас значение параметра Для просмотра ссылки Войди или Зарегистрируйся:
Задаёт максимальную длительность выполнения оператора, при превышении которой оператор прерывается.
Ну-ка, ну-ка... Возьмем тестовый запрос, который ждет 20 раз по 100ms и попробуем остановить его через секунду:
Код:
BEGIN;
SET LOCAL statement_timeout = '1s';
SELECT
i
, pg_sleep(0.1)
FROM
generate_series(1, 20) i
-- ERROR:  canceling statement due to statement timeout
ROLLBACK;

Таки да, "в бесконечность" наш запрос не ушел, но и полезного мы ничего не получили - то есть попросту нагрузили базу бесполезной работой.
Неужели нет способа заставить запрос успеть вернуть хоть что-то за отмеренное ему время?
9d2355c5c3835d26f808446aada19539.jpeg

clock_timestamp​

Оказывается, есть, если использовать одну из не очень известных функций работы с датой/временем - Для просмотра ссылки Войди или Зарегистрируйся:
Функция
Результат
transaction_timestamp()
now()
CURRENT_TIMESTAMP​
момент начала текущей транзакции​
statement_timestamp()​
момент начала текущего запроса​
clock_timestamp()​
момент вычисления​
timeofday()​
момент вычисления (строка)​
Проверим:
Код:
BEGIN;
SELECT
transaction_timestamp() -- 2022-07-13 18:00:27.352057+03
, statement_timestamp() -- 2022-07-13 18:00:29.782563+03
, clock_timestamp() -- 2022-07-13 18:00:29.805303+03
, timeofday(); -- Wed Jul 13 18:00:29.805304 2022 MSK
ROLLBACK;

Обратим внимание, что даже у вызванных последовательно clock_timestamp/timeofday возникла разница на 1 микросекунду - то есть значение действительно получается в момент вычисления.
Перепишем немного запрос:
Код:
SELECT
i
, clock_timestamp() - now() diff
, pg_sleep(0.1)
FROM
generate_series(1, 20) i
WHERE
clock_timestamp() - now() < '1 sec'::interval; -- волшебное условие
i | diff | pg_sleep
1 | 00:00:00.0002 |
2 | 00:00:00.100966 |
3 | 00:00:00.202966 |
4 | 00:00:00.303682 |
5 | 00:00:00.404945 |
6 | 00:00:00.50603 |
7 | 00:00:00.607661 |
8 | 00:00:00.7084 |
9 | 00:00:00.808655 |
10 | 00:00:00.908728 |

Смотрите-ка, PostgreSQL "спит" не ровно по 100ms, а чуть-чуть больше, зато в секунду он четко уложился, да еще и вернул при этом какой-то полезный контент - ровно что мы и хотели.
А если посложнее, и нам надо ограничить рекурсивный запрос? Настолько же просто:
Код:
WITH RECURSIVE T AS (
SELECT
0 i
, clock_timestamp() - now() diff
, NULL::void
UNION ALL
SELECT
i + 1
, clock_timestamp() - now() diff
, pg_sleep(0.1)
FROM
T
WHERE
clock_timestamp() - now() < '1 sec'::interval -- то же самое условие
)
TABLE T;
i | diff | void
0 | 00:00:00.000291 |
1 | 00:00:00.000304 |
2 | 00:00:00.101989 |
3 | 00:00:00.203279 |
4 | 00:00:00.304524 |
5 | 00:00:00.406191 |
6 | 00:00:00.507255 |
7 | 00:00:00.608043 |
8 | 00:00:00.70816 |
9 | 00:00:00.808379 |
10 | 00:00:00.90908 |
Ну, а каким контентом вы будете грузить базу вместо pg_sleep - решайте сами.