Заметки: PostgreSQL

Про ошибку в названии заметки я уже как-то писал. Напомню суть: платформа не переваривает даты позже 3999 года (на практике начинает стрелять с 3999-12-01). Теоретически такие даты вообще нельзя записать, но если очень хочется из-за ошибок в прикладном коде (и в коде самой платформы) это всё-таки возможно и время от времени случается.

Обычные симптомы — перестают работать какие-то отчёты, не проводятся какие-то документы, падает пересчёт итогов. В общем, страдает любой код, который трогает записи с проблемными датами.

Способ лечения, который я рамочно описал в заметке по ссылке выше — рабочий, но сравнительно медленный: нужно настроить ТЖ, собрать и распарсить результаты. Между тем, платформа падает на первом же обращении к битой дате, а их может быть много и валяться они могут в разных таблицах. То есть может потребоваться несколько подходов к снаряду: проверили, получили ошибку, исправили, проверили, получили следующую...

Желая решать проблему как-нибудь побыстрее, несколько лет назад я написал запрос для PostgreSQL. Общая идея:

1. Ищем в базе данных все поля с датами.
2. Строим мегазапрос к таким полям (ищем даты, которые выходят за лимит 1С).

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

Почему действуем на уровне СУБД? Ну, средствами 1С такой номер не отколоть — напомню, платформа падает при попытке потрогать проблемные записи. Это касается любого взаимодействия, включая чтение. Кроме того, в данном случае работать напрямую быстрее и удобнее.

На днях я переписал этот запрос для MS SQL. Вышло длиннее (в силу особенностей этой СУБД), но идея та же.

Если будете использовать, имейте в виду, что:

• Поле _Fld626 в тексте запроса — разделитель для фреша. В вашей базе может называться по-другому или вообще отсутствовать.
• Запрос написан для базы со сдвигом в 2000 лет. Если в вашей базе сдвига нет, нужно скорректировать условие (см. функцию DATEADD()).
• Трюк с выводом в XML (см. FOR XML) я добавил, чтобы не дать SSMS обрезать текст мегазапроса (показалось быстрее, чем возиться с приведением типов). Побочный эффект: в получившемся мегазапросе нужно заменить > на >, прежде чем выполнять его.

Разбирали на днях с коллегой проблему. Ничего особенно серьёзного, очередное расследование вида «какого черта этот запрос ведет себя странно?».

Упрощая, суть: читаем таблицу базы данных и кладем результат во временную таблицу. Если срабатывает определённое условие, нужно, чтобы временная таблица всё равно создавалась, но была пустой (независимо от того, есть строки в исходной таблице или нет).

Запрос был примерно такой:

SELECT
    Table.Field1 AS Field1
FROM
    Table AS Table
WHERE 
    &Parameter

Если нужно было отбирать записи из исходной таблицы во временную, в параметр передавался TRUE; если временную таблицу нужно было получить пустой — передавался FALSE.

Несмотря на кажущуюся простоту, такой трюк — проблема для производительности, если таблица, которую читает запрос — большая.

Причина в том, как СУБД работают с параметризованными запросами. И MS SQL, и PostgreSQL строят план выполнения запроса на основе его текста, и в примере выше значение параметра не повлияет на принятие решения, нужно читать таблицу или нет.

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

Решение тут простое — вставлять TRUE/FALSE в тело запроса как константу, не используя параметр. Либо использовать оператор TOP, так текст запроса будет даже проще:

SELECT TOP 0
    Table.Field1 AS Field1
FROM
    Table AS Table

Тут на уровне SQL мы получим что-то вроде «SELECT TOP 0 ... FROM Table» (для MS SQL) и «SELECT ... FROM Table LIMIT 0» (для PostgreSQL). В итоговом плане будет оператор чтения, но исполнитель фактически не запросит ни одной строки, так что реального сканирования данных не случится (ура).

P.S. Если не критично получать во временной таблице корректные типы колонок, можно вообще вот так:

SELECT TOP 0
    UNDEFINED AS Field1

Выигрыш в производительности, впрочем, будет таким копеечным, что можно не упарываться.

Помню, в каком восторге я был, когда впервые поработал с оконными функциями в PostgreSQL. Можно построчно регулировать окошко вычислений для каждой строки! И на сегменты предварительно нарезать! И всё это — нативно, внутри самого обычного запроса, никаких пристроек справа и слева. Стандартная агрегация с группировкой и подзапросами, к которой я привык в 1С, резко превратилась в тыкву, совсем как карета Золушки в полночь.

Последнее время осваиваю Blender и внезапно ощутил то же чувство по той же самой причине. То, что объект можно произвольно таскать по 3D Viewport через команду Move, было понятно ещё на берегу, ладно. Но когда до меня дошло, что каждый объект — это набор полигонов, и каждый из таких полигонов тоже можно произвольно таскать, и это приводит к естественному изменению геометрии объекта — вот тут-то меня и накрыло.

Технологии удивительны. Учиться — клёво. Ради таких озарений хочется делать это ещё и ещё :)

В очередной раз столкнулся с противным багом, при котором платформа ломает таблицу с метаданными истории данных.

Внешне он выглядит так: вы обновляете конфигурацию базы, и при попытке реструктуризации выскакивает ошибка "Таблица метаданных истории данных cодержит не уникальные записи, которые должны быть удалены".

При этом платформа не предлагает никакого понятного способа найти такие записи — иди туда, не знаю куда, сделай то, не знаю что.

Проблему можно решить, порывшись в базе данных. Таблица, на которую ссылается ошибка — _DataHistoryMetadata. В ней лежат версии метаданных каждого объекта, для которого ведётся история. Это позволяет платформе понимать, какие реквизиты были у объекта на любой момент времени, в течении которого для объекта велась история.

Как это работает? Ну, когда меняется состав реквизитов объекта (например, реквизит в справочнике добавили), платформа запоминает его метаданные: конкретно, добавляет в _DataHistoryMetadata новую запись и сохраняет в ней актуальный список реквизитов объекта, а также номер версии этого списка (например, в при включении истории для объекта сохраняется первая версия метаданных, при добавлении какого-нибудь реквизита — вторая и так далее).

Ещё платформа ставит в созданной записи отметку, что именно эта версия объекта — самая актуальная, после чего снимает этот флаг с той версии, которая была помечена актуальной до этого.

Так вот, проблема в том, что платформа иногда забывает сделать последний шаг и в таблице появляется сразу две версии, помеченные как актуальные. Конфигуратор понимает это, но сделать ничего не может.

Решение вытекает из алгоритма выше: нужно найти конфликтующие версии и отобрать признак актуальности у той, что старше. Лучше использовать запросы: историю данных включают, как правило, для кучи объектов и состав реквизитов у них постоянно меняется — в общем, версий в таблице будет столько, что черт ногу сломит.

Если вы тоже столкнулись с этой проблемой и поэтому читаете этот текст — можете воспользоваться запросами, что написал я:

1. get-issues.sql проверяет, что проблема есть: ищет версии метаданных, которые одновременно помечены как актуальные.
2. fix-issues.sql снимает признак актуальности с тех версий, которые на самом деле устарели.

Оба запроса написаны для Microsoft SQL Server. Если вы используете PostgreSQL, то вот они же для этой СУБД.

Запросы потребуют небольшой адаптации под конкретную базу: в них используется поле _fld626, в котором хранится разделитель данных. В вашей таблице _DataHistoryMetadata это поле может называться иначе, поэтому нужно обновить его имя на актуальное. Ошибиться будет трудно — у таблицы только одно поле с префиксом _fld.

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

Наткнулся на хороший текст об основной проблеме, которую таскает с собой UUID. Для 1С она тоже актуальна: все ссылочные объекты платформы (элементы справочников, документы и так далее) имеют собственные UUID. Они хранятся в БД, активно используются при поиске и, понятно, обильно индексируются (со всеми вытекающими последствиями).

1С старается компенсировать проблему, создавая последовательные UUID. Пусть не идеально, но в целом эта штука работает и индексы получаются более-менее ровными. Да и вообще в сообществе об этом говорят довольно давно: вот, например, бородатый топик на Мисте (правда, тут диалог быстро перерос в курятник и из шести десятков комментариев от силы полтора — по делу).

P.S. Рассмешила ремарка про вероятность создать в одной базе два одинаковых UUID:

As an aside, for those worried about collisions: you should take up the lottery, since winning the jackpot twice in a row is a much more likely outcome than your system ever generating two identical random 128 bit numbers.

На прошлой неделе сгонял в Москву на Infostart Event 2021:

• Послушать дюжину докладов о разработке и близких темах — check;
• Развиртуализоваться с несколькими классными людьми — check;
• Поболтать с друзьями — check!

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

Чтобы два раза не вставать летать, сдал в офисе PostgresPro входной тест по администрированию PostgreSQL. Впереди ещё три, а финального пока вообще не существует — но дорогу осилит идущий, так вижу.

На курсе по PostgreSQL узнал смешную деталь: в 10-й версии СУБД разработчики переименовали папку pg_xlog (журналы предзаписи) в pg_wal, а папку pg_clog (статусы транзакций) — в pg_xact.

Знаете, почему? Из-за не слишком опытных, но уже достаточно смелых администраторов, которые триггерились на слово «log» в названии папки. Мол, мне нужно место на диске освободить, а тут СУБД забила всё своими дурацкими логами. Некогда разбираться, rm -rf их и порядок!

В общем, в трубу одновременно вылетала и защита работы с данными в буферном кэше, и многоверсионность. После чего кластер умирал в муках. Свободного места на диске получалось много, но радоваться этому, боюсь, приходилось недолго :-)