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

37e24d9f09122860a62775f56ac7a013.jpeg

В 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;

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

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

9d2355c5c3835d26f808446aada19539.jpeg

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

© Habrahabr.ru