Меня зовут Фролков Иван, я работаю программистом с 1993 года, и уже восемь лет — в Postgres Professional. Периодически выступаю на конференциях. В этой статье я расскажу вам про темпоральные типы данных в PostgreSQL — доклад о них я читал на PGConf.Russia 2022. Почему меня это заинтересовало? Мне много раз приходилось сталкиваться с тем, что из-за разницы часовых поясов не сходились отчёты за месяц или даже за сутки. Подобные проблемы возникают из-за неаккуратной обработки даты и времени, которой можно избежать.

В чём проблема?

Часто мы начинаем сверять данные из разных мест, и они почему-то оказываются разными. Мало кто явно указывает часовой пояс при указании времени, что впоследствии приводит к ошибкам. Например, если в общий лог пишут и из Москвы, и из Новосибирска, а часовой пояс не указан, трудно понять, какое событие когда произошло.

У меня была ситуация, когда я работал в международной компании с серверами по всему миру. Паника из-за неверного построения отчётов там возникала дважды в сутки. Сначала поднимались московские менеджеры и ругались, что цифры получаются не те. Мы поправляли часовые пояса, и всё было хорошо до тех пор, пока не просыпались менеджеры в Сан-Франциско. Они тоже выдвигали претензии по цифрам, мы снова исправляли время, но после этого опять «уезжала» Москва.

Заря приходит с востока

С чем же связаны такие проблемы? С тем, что Земля круглая, и время наступает везде по-разному. Казалось бы, это тривиальное знание, но в реальности его мало кто учитывает. На востоке часовые пояса с плюсом, а на западе — с минусом. Где-то посередине располагается Гринвич, нулевой меридиан (кстати, в Лондоне есть летнее время, и оно не совпадает с гринвичским!). Есть ещё места вроде Непала и Бутана, где время сдвигается не на полные часы, а на 45 или 15 минут, и это может создать вам проблемы.

Данный материал был создан на основе одноимённого доклада на PGConf.Online, вошедшего в число самых популярных выступлений конференции. Поскольку тема ограничений по-прежнему сохраняет свою актуальность, а смотреть видео с мероприятий любят не все, появилась эта статья.

Концепция “тупого хранилища”

В последние годы разработчики ПО всё чаще утверждают, что база в их проекте “всего лишь тупое хранилище, и поэтому никакой логики в ней нет”. Откуда такой подход? Обычно он объясняется сложностями миграции, развёртывания, неудобствами при работе с системами контроля исходного кода. Не стоит списывать со счетов и простую человеческую лень: раз всё и так нормально, зачем связываться с логикой в СУБД? Создали таблицы (или, ещё лучше, пусть ORM их создаст!), и всё отлично.

NoSQL для документов

Случай с NoSQL ещё проще – не надо ничего создавать, контролировать и напрягать мозги, всё уже автоматизировано, оно само работает. Этого вполне достаточно, если из базы нужно просто доставать документы по идентификатору, но если требуется решать задачи посложнее, то всё-таки выбирают SQL СУБД. Их использование, однако, ограничивается созданием таблиц и индексов, логика на стороне СУБД и в этом случае видится избыточной.

СУБД: не только технология, но и бизнес-инструмент

Такой подход является очень распространённым (люди вообще ленивы!). Тем не менее, крайне наивно дистанцироваться от хороших возможностей только из-за нежелания заморачиваться и приобретать новые навыки. СУБД – это очень изощрённая система хранения (чтобы понять это, достаточно почитать про уровни изоляции или процедуры резервного копирования). СУБД помогает синхронизировать бизнес-процессы и избежать реальных убытков, иногда в очень крупном размере.

Расширение pg_variables

Часто при разрабоке прикладного ПО можно столкнуться с проблемой такого рода — для промежуточных данных требуется получить несколько результирующих наборов, например, для некоторых товаров надо иметь возможность получить их наличие в текущих заказах и сумму скидок, выданных для них ранее; или для некоторых пользователей получить список их друзей и сообщения этих пользователей в соцсетях и т.д и т.п.

Решение обычно выглядит вполне прямолинейным — сначала получаем список, скажем, пользователей, потом для них строим требуемый результирующий набор; потом опять получаем список пользователей и строим второй набор; и все бы хорошо, если бы построение такого списка не оказывалось бы достаточно затратной операцией — и, таким образом, если на основании этого списка надо построить несколько результатов, то получается, что этот список надо получить несколько раз со всеми сопутствующими накладными расходами. Очевидным решением этой проблемы кажутся временные таблицы, и это действительно так; к сожалению, с ними связан ряд не самых приятных особенностей — для каждой временной таблицы требуется создавать файл (а при уничтожении таблицы — удалять его). Кроме того, эти таблицы, разумеется, не видны для процессов автовакуума и, следовательно, не очищаются автоматически, и по ним не собирается статистика. Что еще хуже, при наличии длительных активных транзакций может происходить неограниченный рост системного каталога; более того, кеш операционной системы заполняется данными о созданных файлах для временных таблиц, что ведет к общей деградации производительности.

Следует также отметить, что так как имя таблицы должно быть известно при компиляции запроса, то использование разных таблиц может оказаться достаточно неуклюжим и заставляет прибегнуть к динамическому формированию запросов со всеми вытекающими последствиями; если же вспомнить, что plpgsql для динамических запросов не сохраняет план, то в случаях сложных запросов это может оказаться значительной проблемой.

select * from $1 where ...;

В ежедневной работе часто встает задача ясно и просто ссылаться на большие списки колонок и выражений в выборке, и/или обходиться с громоздкими и неясными условиями в предложении where. Обычно для этих целей используются представления, что вполне удобно и наглядно.