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:

Функция	Результат
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 - решайте сами.