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

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

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

statement_timeout

Очевидное решение — использовать те средства, которые нам дает для этого сама база: установить устраивающее нас значение параметра 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;

Таки да, «в бесконечность» наш запрос не ушел, но и полезного мы ничего не получили — то есть попросту нагрузили базу бесполезной работой.

Неужели нет способа заставить запрос успеть вернуть хоть что-то за отмеренное ему время?

clock_timestamp

Оказывается, есть, если использовать одну из не очень известных функций работы с датой/временем — clock_timestamp:

Проверим:

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 — решайте сами.