Главная » Хабрахабр » Битва при MERGE. Хроника с выводами и моралью

Битва при MERGE. Хроника с выводами и моралью

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

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

MERGE снаружи

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

Он реализован в Oracle, DB2 и в MS SQL, а значит отсутствие MERGE огорчит тех, кто подумывает перейти с этих СУБД на PostgreSQL. Оператор MERGE есть в стандарте SQL-2003 и уже во всей красе в SQL-2008. Страстное желание Саймона Риггса как можно быстрее, уже в 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_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, отличается только синтаксис всей команды.

По этой причине MERGE не может работать с секционированными таблицами [Это цитируется по описанию MERGE в Oracle, наиболее полном. Предложение с ON должно делать соединение по всем столбцам первичного ключа или, если указаны другие столбцы, то должен использоваться какой-либо уникальный индекс, чтобы условия [NOT] MATCHED сразу определяли действия для записи-кандидата, чтобы исключить взаимодействие с другими транзакциями. Вообще утверждение дискуссионно: дальше, в Хронике Битвы будет видно, что в PostgreSQL как раз обсуждалась поддержка секций]

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

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

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

Хроники PG MERGE

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

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

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

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

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

Лёд тронулся

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

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

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

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

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

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

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

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

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

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

Для любопытствующих: Гейган разбирает тонкости и грубости отличий 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 (Хуберт Любачевски) успевает www.depesz.com/2018/04/10/waiting-for-postgresql-11-merge-sql-command-following-sql2016 >проанонсировать его в своем блоге, но в тот же день Саймон откатывает все обратно из-за ошибок.

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

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

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

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

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

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

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

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

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

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

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

Всё.

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


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

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

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

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


Оставить комментарий

Ваш email нигде не будет показан
Обязательные для заполнения поля помечены *

*

x

Ещё Hi-Tech Интересное!

Actions on Google: пишем простое приложение для Google Ассистента на Dialogflow и Cloud Functions for Firebase

В конце прошлого месяца состоялся официальный релиз Google Ассистента на русском языке, так что самое время разобраться, как делать свои приложения (экшены) для Ассистента на стандартном технологическом стеке Google. В этой статье мы рассмотрим создание экшена в Actions on Google, ...

[Из песочницы] Проблемы интерфейсов наземных переходов

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