Несколько недель перед важным комитфестом — последним перед feature freeze версии PostgreSQL 11 — читатели рассылки hackers, сжимая в левой пакет с чипсами, следили за триллером MERGE. Режиссер триллера, глава компании 2ndQuadrant Саймон Риггс (Simon Riggs), с впечатляющей настойчивостью и изобретательностью пытался протащить в версию патч, реализующий синтаксис команды MERGE. Риггс комитер с 2009 года, а со статусом комитера можно самому утверждать патчи. Ему противостояли не менее уважаемые комитеры и ветераны PostgreSQL. Страсти кипели явно и подспудно, до прямых оскорблений все же не дошло — факт удивительный для завсегдатаев многих отечественных форумов. Однако некоторое напряжение осталось до сих пор, когда вопрос утрясли, и спорить уже не о чем.

Но страсти страстями (о них еще будет дальше), а хотелось бы бесстрастно разобраться в сути этой совсем не надуманной проблемы.

MERGE снаружи

Если совсем упрощая, то дело вот в чем: у нас есть 2 таблицы с одинаковыми полями и разными данными. Допустим ФИО и возраст. Нам надо объединить их в одну. Но надо бы определиться, что делать с теми личностями, которые имеются в обеих таблицах. Скорее всего мы захотим, чтобы в итоговой таблице оказались все, а совпадающим личностям подновить информацию. Понятно, что даже в такой постановке это весьма распространенная задача. Ее можно решить и без MERGE, составив сложный запрос, можно использовать триггеры и так далее. Но это неудобно. Впрочем, эту задачу решает неканонический вариант MERGE, который называют UPSERT (UPdate+inSERT).

Оператор MERGE есть в стандарте SQL-2003 и уже во всей красе в SQL-2008. Он реализован в Oracle, DB2 и в MS SQL, а значит отсутствие MERGE огорчит тех, кто подумывает перейти с этих СУБД на PostgreSQL. Страстное желание Саймона Риггса как можно быстрее, уже в PostgreSQL 11, подпитывалось желаниями клиентов 2ndQuadrant, а не амбициями или сварливостью.

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

Синтаксис команды таков:

 MERGE INTO tablename USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

Можно, впрочем, и вот так:

MERGE [hint] INTO [schema .] {table | view} [table_alias] USING { subquery | [schema .] { table | view}} [table_alias] ON ( condition ) [ merge_update_clause ] [ merge_insert_clause ] [ error_logging_clause ] ;

Этот синтаксис реализован в Oracle. Если словами, то MERGE выполняет действия, изменяющие записи в целевой таблице target_table_name используя data_source в единой SQL-команде, которая может в соответствии с условиями делать INSERT, UPDATE или DELETE в отношении записей в target_table_name. При этом target_table_name может быть представлением, а data_source может быть набором таблиц или представлений, результатом подзапроса.Сначала оператор MERGE выполняет left outer join над data_source с target_table_name, предлагая 0 или более записей-кандидатов на изменение; в заданном порядке вычисляются предложения WHEN; как только условие удовлетворено, производится соответствующее действие. Ключевые слова WHEN [NOT] MATCH THEN встречается в SQL не слишком часто, поэтому напоминаем, что это управляющая конструкция типа if-else в других языках. MERGE действует так же, как и собственно UPDATE, INSERT или DELETE в отношении target_table_name, отличается только синтаксис всей команды.

Предложение с ON должно делать соединение по всем столбцам первичного ключа или, если указаны другие столбцы, то должен использоваться какой-либо уникальный индекс, чтобы условия [NOT] MATCHED сразу определяли действия для записи-кандидата, чтобы исключить взаимодействие с другими транзакциями.

MERGE детерминированная команда: нельзя обновить одну и ту же запись несколько раз в одной и той же команде MERGE.
Пример:

MERGE CustomerAccount CA
USING RecentTransactions T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED THEN
  UPDATE SET Balance = Balance + TransactionValue
WHEN NOT MATCHED THEN
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue);

или с подзапросом:

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

В IBM DB2 синтаксис тоже будет работать. Как пишут, «под капотом» это будет выполняться аналогично конструкции UPDATE FROM. В MS SQL с 2008 года также есть MERGE.

Но даже за единым, стандартном синтаксисом начинается проблема выбора из немалого количества механизмов и способов реализации. Команда должна работать на различных уровнях изоляции транзакций, с разными алгоритмами блокировки, ориентацией на высоко-конкурентный или не очень режим работы. И, как можно догадаться, чтобы реализовать эту непростую логику, надо затронуть немало компонент СУБД.

UPSERT, псевдо-MERGE

Понятно, что разработчики СУБД искали компромиссные решения, отказавшись от буквального воспроизведения синтаксиса стандарта. Плюс такого подхода — свобода. Можно использовать механизмы, органичные для конкретной СУБД, можно оптимизировать реализацию под задачи, которые считаешь самыми актуальными для своих пользователей.

Например, в MySQL есть команда REPLACE, которая работает как INSERT, но если в новой и старой строке те же значения в PRIMARY KEY или UNIQUE-индексе, то старая строка убивается перед тем, как вставляется новая. Но есть также и INSERT ... ON DUPLICATE KEY UPDATE, где происходит INSERT и UPDATE (вместо DELETE в REPLACE). Это UPSERT. А еще там имеется INSERT IGNORE, которая просто не выполняет вставку, не выдавая ошибку (но предупреждая) при определенных ограничениях на целевой таблице.

Хроники PG MERGE

В сообществе PostgreSQL разговоры о MERGE начались в 2005, когда Джейми Казанова (Jaime Casanova) спросил: а не занялся ли кто-нибудь в сообществе разработкой MERGE. Питер Айзентраут (Peter Eisentraut) предложил обсудить, стоит ли разработать для PostgreSQL какой-то из вариантов MERGE: похожий на реализацию в MySQL, или лучше направить силы на облегченный по функциональности вариант типа MERGE от Oracle. Впрочем, стоит ли вообще предпринимать усилия в этом направлении?

В середине некороткого обсуждения появляется главный герой этого повествования Саймон Риггс со словами:
MERGE полезна и для OLTP-систем и для DW (Data Warehouse — склады данных, то есть аналитические приложения, где сложные запросы, но не слишком конкурентная среда и данные обновляются редко, а если и обновляются, то обычно сразу большими порциями. <…> Мы можем реализовать MERGE как вариант COPY FROM, это будет очень cool.

Все соглашаются: да, кул. Точнее, почти все: Стивен Фрост (Stephen Frost): думаю, я не единственный, кто говорит, что нужен полноценный, соответствующий стандарту MERGE.

У Брюса Момджана (Bruce Momjian) другое, более прагматичное предложение: мне кажется, надо реализовать в MERGE некоторые варианты, которые нам по силам реализовать, а в остальных будем выдавать ошибку (и в тех случаях, когда потребуется блокировать всю таблицу). А после получим обратную связь от пользователей и будем думать, что делать дальше.

Но пока что ничего не происходит.

Лёд тронулся

В 2008 Саймон Риггс снова призывает разобраться с MERGE — какой из путей избрать (к тому времени уже появляется новая версия MERGE в стандарте SQL-2008, пока черновом). Он расписывает подробно актуальные на тот момент реализации Oracle, IBM и MS SQL и альтернативный синтаксис от MySQL и Teradata. И чуть позже уже упоминает начало работ в 2ndQuadrant в этом направлении.

Питер Айзентраут пишет в своем блоге: безусловно, Риггс из самых квалифицированных специалистов, он может возглавить работы по реализации MERGE.

Но тут происходит первый неожиданный поворот: к проблеме подключают студента — участника разработок по программе GSoC, то есть Google Summer of Code. Его зовут Boxuan Bxzhai — фамилию я не берусь транскрибировать. Вскоре он пишет, что работа почти сделана.

Но почти не считается. Грег Смит (Greg Smith) из 2ndQuadrant (то есть соратник Саймона Риггса) пишет:
Итак, у нас есть патч, в коде которого полдюжины серьезных нерешенных проблем. О мелких я уже молчу. Проблемы слишком глубоки, чтобы доработать код к комитфесту. Между тем, от Боксуана давно ничего не слышно. Мы бы могли помочь ему, но где он? Кто в курсе?

Обсуждение путей реализации опять вспыхивает в 2014, но опять ничего не происходит: кода нет.

Наконец, уже в 2017 Саймон Риггс пишет:
Я работаю над кодом, чтобы закомитить MERGE в версию PostgreSQL 11. Мы используем те же механизмы, что лежат в основе уже работающей INSERT ON CONFLICT, так что инфраструктурных изменений не понадобится, в основном просто реализация синтаксиса поверх имющегося. Но свой код я пишу с нуля, предыдущие наработки не использую.

Речь идет о реализованному к тому времени Питером Гейганом (Peter Geoghegan, VMware) уже в 9.5 альтернативном синтаксисе INSERT .. ON CONFLICT UPDATE, отличном от стандарта SQL, но все же родственном MERGE и REPLACE в MySQL.

Поначалу работа Саймона была встречена возгласами Nice work! Однако, Роберт Хаас (Robert Haas), хотя и поддерживает, но предупреждает о возможных аномалиях сериализации. Мол, иметь дело с INSERT .. ON CONFLICT UPDATE, без MERGE на ее базе, как-то спокойней.

Сам автор UPSERT в PostgreSQL, Питер Гейган:
Я бы не стал перемешивать код ON CONFLICT DO UPDATE и MERGE. <...> Для загрузки больших порций данных (bulk load) я бы, например, использовал алгоритм merge join. <...> Вообще, преимущества MERGE были бы связаны с тем, что там работали бы обычным образом обычные соединения: nested loop, hash, merge. А в INSERT … ON CONFLICT никаких джойнов вообще нет.

Хаас: Как и Питер, я думаю, что если делать таким образом, то такая сильная блокировка при исполнении DML-запроса выглядит так себе. Вряд ли кого-то порадует, что работать с MERGE в одно время может только один человек.

Для любопытствующих: Гейган разбирает тонкости и грубости отличий UPSERT от MERGE здесь и здесь (мы храним архивную переписку PostgreSQL на нашем сайте).

Саймон сопротивляется. Он аппелирует к Новейшей Истории. Мол, про секционирование тоже говорили «новый синтаксис, не более того». А оказалась очень полезная вещь. Я же не предлагаю реализовать сразу всё, что есть в MERGE. Поступим так же, как с секционированием — разобьем разработку на фазы.

И еще один аргумент, на мой взгляд весьма убедительный: Хорошо. Но давайте выбирать. Я предлагаю практичный вариант. Скоро стукнет 10 лет с первой серьезной попытки разработать MERGE. Не пора ли все-таки начать что-то делать, получить какое-то полезное решение, вместо того, чтобы подождать еще 10 лет Совершенного Решения? Если предположить, что оно вообще существует.

Наконец, патч прибывает в сообщество. Какого числа? Предположите, пожалуйста. Нет, не угадали: Саймон присылает его 30 декабря 2017 года. И оговаривает, что это WIP-патч, то есть Work in Progress — патч в работе.

Саймон, январь:
Патч доделан и без особых багов. 1200 строк кода плюс тесты и документация. Я собираюсь закомитить его к этому комитфесту, а RLS (Row Level Security — защита на уровне записи) и поддержку секционирования доделаем потом.

Каста комитеров

Здесь нам придется сделать еще шаг в сторону и пояснить роль комитера в сообществе. Функции комитера, то есть того, кто наделен властью принять патч в очередную версию, исторически менялись. Когда-то, во времена, когда разработчиков было еще мало, право комитить раздавали щедро. Например, знаменитый (на совсем другом поприще) Джулиан Ассанж получил титул комитера, будучи автором всего шести патчей. Сейчас стать комитером непросто, выскочек в списке из пары дюжин человек нет. У Боюса Момджана (EnterpriseDB) 13363 комитов, у Тома Лейна (Tom Lane, Crunchy Data) 13127, у Роберта Хааса (EnterpriseDB) — 2074. Кстати, единственный комитер из России — Федор Сигаев (Teodor Sigaev, Postgres Professional) с его 383 комитами. У самого Саймона Риггса их 449. Повторюсь: у него, как комитера, достаточно полномичий, чтобы взять и закомитить патчи — свои и своих сотрудников. Другое дело, что делать это, откровенно пренебрегая мнением других корифеев-комитеров, вряд ли стоит. Могут и лишить статуса комитера, но как минимум откатят (revert) патч обратно.

Перелом в битве

Конечно, в «безбажном» патче, сделанном, в общем, наспех, находят всё новые ошибки. Новые версии сыплются в ответ.

В конце января появляется новое действующее лицо: разработчик 2ndQuadrant Паван (его так и зовут все, по имени; полностью Pavan Deolasee). Теперь сообщество имеет дело с тандемом: Паван присылает новые версии и благодарит за критику, а Саймон пробивает их с недюжинным маркетинговым напором.

Хаас: Я не думаю, что стоит принимать односторонние решения об исключении фич, которые работают везде. Если мы соглашаемся о том, что некоторые фичи не войдут в этот патч — это одно дело. И совсем другое то, что в комментариях по этому поводу все выражали несогласие. И мы на самом деле не услышали причин, по которым эти фичи надо исключить.

Логика предъявлялась такая:

• априори серьезные проблемы есть потому, что их не может не быть в разработках в стиле «кавалерийская атака».
• доделать поддержку даже важных фич как новое секционирование в версиях 10-11, CTE (Common Table Expressions = WITH-запросы) или RLS (Row Level Security), можно и после принятия патча в текущую версию, но только если предлагаемая архитектура пригодна для построения поверх нее нужного функционала.

Второе Питер Гейган формулирует так:
Обычно я обращаю внимание на поддержку разнообразной функциональности, так как если она есть, то это укрепляет общую уверенность в том, что дизайн сделан как надо. А если такие проблемы вызывает поддержка выражений WITH [то есть CTE], то у меня возникает мысль, что заложенная архитектура такова, что вызывет проблемы здесь и там.

Между тем, час Х (последний комитфест) приближается, а тучи над MERGE сгущаются. Не то, чтобы отцы-основатели специально выискивали серьезные проблемы в архитектуре аврально делавшихся Саймоном и потом Паваном патчей. Проблемы искать не пришлось, они охотно вскрылись сами.

Развязка близится

Сюжет ускоряется. Не смотря на прохладное отношение других комитеров к своей затее, 2 апреля Саймон решается закомитить патч Command following SQL:2016, добавляет файлы, Depesz (Хуберт Любачевски) успевает <a href www.depesz.com/2018/04/10/waiting-for-postgresql-11-merge-sql-command-following-sql2016 >проанонсировать его в своем блоге, но в тот же день Саймон откатывает все обратно из-за ошибок.

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

В ответ — обвинения поистине тяжкие. Андрес Фройнд (Anders Freund, EnterpriseDB) пишет:
Архитектура для MERGE в парсере и экзекъютере не произвела на меня впечатления надежной. Создание скрытых джойнов во время парсинга-анализа — это совсем плохая идея. Такую структуру экзекъютера надо целиком менять.

Том Лейн:
Дизайном дерева парсинга слабенький.

Вы перегружаете функцию InsertStmt, — продолжает он — она делает вовсе не INSERT, но при этом случайным образом имеет поля те же, что исходная. Причем не все, а некоторые. Это плохо, это приводит к путанице.

Дабавим наблюдения Федора Сигаева:
В парсере появлялись связанные с MERGE узлы INSERT, обвешанные кучей дополнительных полей. Если смотреть план исполнения в ANALIZE, то не сразу поймешь, имеешь ли дело с обычным INSERT, или с MERGE: чтобы разобраться, надо просмотреть дополнительные поля.

Саймон, невозмутимо: OK, это мы поменяем и завтра пришлем новый файл.Хаас: Согласен с Питером. Выбор архитектуры неудачен.

Саймон не сдается. 6 апреля, отвечая на критику Тома Лейна, комитит новый патч с поправками в парсере.

Переговоры и капитуляция

Брюс Момджан 6 апреля:
Я хочу заметить, что люди не просили тебя вкалывать, чтобы срочно исправить что-то. Они просили тебя отозвать патч. Можешь, конечно, трудиться в поте лица, в надежде, что они изменят мнение, но — еще раз — они просили тебя не об этом.

Саймон: Если Том [Лейн] и Андрес [Фройнд] за эти оставшиеся несколько дней все еще будут чувствовать, что их опасения не развеяны, я буду счастлив откатить патч без лишних разговоров.

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

Всё.

Саймон сказал ОК, и на этом битва при MERGE закончилась. Все патчи откачены назад, тема перенесена на следующий комитфест со статусом «Ожидает доделок автора». Участники шоу замирились.


Впрочем, судя по переписке последних недель, некоторая напряженность, похоже, осталось.

Обещанная мораль

• К счастью, в сообществе PostgreSQL есть естественные и формальные механизмы (почти) бесконфликтного отсеивания попыток незрелых решений. Даже если их пробивают уважаемые разработчики в ранге главы компании, вклад которой в развитие PostgreSQL огромен. А вкладывать толкают заказчики, которым не хватает функциональности.
• К несчастью, сообщество нередко пробуксовывает. Оно инерционно в принятии даже однозначно актуальных разработок. Иногда включается иррациональный перфекционизм. Опыт компании Postgres Professional, где я работаю, это подтверждает. Большой и важный патч INCLUDE-индексов мы пробивали 3 года. Полезнейшая серия патчей для работы с JSON/JSONB все еще ждет. Выражение «отдать свою разработку в сообщество» означает на самом деле не отдать, а пробить: гостя встречают с распростертыми объятиями и провожают в карантины.

P.S.: Дисклеймер от автора: мы всего лишь хотели показать кусочек из жизни сообщества. Все совпадения имён случайны :)
P.P.S.: Самураи Натальи Лёвшиной.