Batalha de MERGE. Crônica com conclusões e moralidade

Algumas semanas antes do importante festival de commit - o último antes da versão de feature freeze do PostgreSQL 11 - os boletins de hackers , comprimindo o chipset no pacote esquerdo, assistiram ao thriller da MERGE . O diretor de suspense e CEO da 2ndQuadrant, Simon Riggs , tentou lançar um patch que implementa a sintaxe do comando MERGE com impressionante perseverança e engenhosidade. Riggs é um comediante desde 2009 e, com o status de comediante, você pode aprovar patches. Ele foi criticado por comitês e veteranos do PostgreSQL não menos respeitados. As paixões fervilhavam de forma clara e implícita, nem chegou a insultar diretamente - um fato surpreendente para os frequentadores de muitos fóruns domésticos. No entanto, alguma tensão permaneceu até agora quando a questão foi resolvida, e não há nada para discutir.

Mas paixões são paixões (elas serão discutidas mais adiante), e eu gostaria de separar desapaixonadamente a essência desse problema completamente rebuscado.

MERGE fora

Se é completamente simplificador, o problema é o seguinte: temos 2 tabelas com os mesmos campos e dados diferentes. Suponha nome e idade. Precisamos combiná-los em um. Mas seria necessário decidir o que fazer com as personalidades que estão nas duas tabelas. O mais provável é que desejemos tudo na mesa final e atualizemos as informações para corresponder aos indivíduos. É claro que, mesmo nesse cenário, essa é uma tarefa muito comum. Pode ser resolvido sem MERGE , fazendo uma solicitação complexa, você pode usar gatilhos e assim por diante. Mas é inconveniente. No entanto, a versão não canônica do MERGE, chamada UPSERT (UPdate + inSERT), resolve esse problema.

O operador MERGE está no padrão SQL-2003 e já está em toda a sua glória no SQL-2008. É implementado no Oracle, DB2 e no MS SQL, o que significa que a falta de MERGE incomodará aqueles que estão pensando em mudar desses DBMSs para o PostgreSQL. O desejo de Simon Riggs o mais rápido possível, já no PostgreSQL 11, foi alimentado pelos desejos dos clientes do 2ndQuadrant, e não pela ambição ou brigas.

De fato, o MERGE possui recursos avançados, os dados não precisam ser extraídos de tabelas, especialmente de estruturas similares.

A sintaxe do comando é a seguinte:

  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 ...]); 

Você pode, no entanto, assim:

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

Essa sintaxe é implementada no Oracle. Em palavras, MERGE executa ações que modificam os registros na tabela de destino target_table_name usando data_source em um único comando SQL, que pode, de acordo com as condições, INSERT, UPDATE ou DELETE com relação aos registros em target_table_name. Nesse caso, target_table_name pode ser uma visualização e data_source pode ser um conjunto de tabelas ou visualizações, o resultado de uma subconsulta .

Primeiro, a MERGE executa uma left outer join no data_source com target_table_name , sugerindo 0 ou mais registros de alteração de candidatos; WHEN cláusulas WHEN são calculadas na ordem especificada; assim que a condição for atendida, a ação correspondente será executada. Palavras-chave WHEN [NOT] MATCH THEN não é muito comum no SQL , portanto, lembramos que essa é uma construção de controle como if-else em outros idiomas. MERGE atua da mesma maneira que UPDATE, INSERT ou DELETE em relação a target_table_name , apenas a sintaxe de todo o comando é diferente.

Uma cláusula com ON deve fazer uma conexão em todas as colunas da chave primária ou, se outras colunas forem especificadas, algum índice exclusivo deve ser usado para que as condições [NOT] MATCHED determinem imediatamente as ações do registro candidato, a fim de excluir a interação com outras transações.

Comando determinístico MERGE : você não pode atualizar o mesmo registro várias vezes no mesmo comando MERGE.
Um exemplo:

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

ou com uma subconsulta:

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

No IBM DB2, a sintaxe também funcionará. Como se costuma dizer , "sob o capô", isso será feito da mesma forma que a construção UPDATE FROM .
Desde 2008, o MS SQL também possui MERGE .

Mas, mesmo atrás de uma única sintaxe padrão, começa o problema de escolher entre um número considerável de mecanismos e métodos de implementação. A equipe deve trabalhar em diferentes níveis de isolamento de transação, com diferentes algoritmos de bloqueio, concentrando-se no modo de operação altamente competitivo ou não. E, como você pode imaginar, para implementar essa lógica complicada, você precisa tocar em muitos componentes do DBMS.

UPSERT, pseudo-MERGE

É claro que os desenvolvedores de DBMS estavam procurando soluções comprometidas, recusando-se a reproduzir literalmente a sintaxe padrão. A vantagem dessa abordagem é a liberdade. Você pode usar mecanismos orgânicos para um DBMS específico. Você pode otimizar a implementação para tarefas que considere mais relevantes para seus usuários.

Por exemplo, no MySQL, há um comando REPLACE que funciona como um INSERT , mas se as linhas novas e antigas tiverem os mesmos valores no índice PRIMARY KEY ou UNIQUE , a linha antiga será eliminada antes da inserção da nova. Mas também há INSERT ... ON DUPLICATE KEY UPDATE onde INSERT e UPDATE ocorrem (em vez de DELETE in REPLACE ). Isso é UPSERT . E existe o INSERT IGNORE , que simplesmente não realiza a inserção, sem gerar um erro (mas aviso) sob certas restrições na tabela de destino.

Crônicas de PG MERGE

Na comunidade PostgreSQL, as discussões sobre o MERGE começaram em 2005, quando Jaime Casanova perguntou se alguém da comunidade havia começado a desenvolver o MERGE . Peter Eisentraut sugeriu discutir se o PostgreSQL deve desenvolver uma das opções do MERGE: semelhante à implementação do MySQL, ou melhor, direcionar o esforço para uma versão funcionalmente leve do tipo MERGE da Oracle. No entanto, vale a pena fazer esforços nesse sentido?

No meio de uma breve discussão, o protagonista desta narrativa Simon Riggs aparece com as palavras:
MERGE é útil tanto para sistemas OLTP quanto para DW (Data Warehouse - data warehouses, ou seja, aplicativos analíticos em que consultas complexas, mas ambientes e dados não muito competitivos, raramente são atualizados e, se atualizados, geralmente em grandes blocos. <...> Podemos implementar o MERGE como uma variante do COPY FROM, será muito legal.

Todo mundo concorda: sim, legal. Mais precisamente, quase tudo: Stephen Frost : Acho que não sou o único que diz que preciso de um padrão MERGE completo e compatível.

Bruce Momjian tem uma proposta diferente e mais pragmática: parece-me que precisamos implementar no MERGE algumas opções que podemos implementar e, no restante, cometeremos um erro (e nos casos em que será necessário bloquear a tabela inteira). E depois de recebermos o feedback dos usuários e pensaremos no que fazer a seguir.

Mas até agora nada está acontecendo.

O gelo quebrou

Em 2008, Simon Riggs novamente pediu para lidar com o MERGE - qual das maneiras de escolher (até então uma nova versão do MERGE no padrão SQL-2008, que ainda está em rascunho, já está aparecendo). Ele pinta detalhadamente a implementação naquele momento do Oracle, IBM e MS SQL e sintaxe alternativa do MySQL e Teradata. E um pouco mais tarde, ele já menciona o início dos trabalhos no 2ndQuadrant nessa direção.

Peter Eisentraut escreve em seu blog : Obviamente, Riggs é um dos especialistas mais qualificados, ele pode liderar o trabalho de implementação do MERGE.

Mas aqui vem a primeira virada inesperada: um aluno está envolvido no problema - um participante no desenvolvimento do programa GSoC , ou seja, o Google Summer of Code. O nome dele é Boxuan Bxzhai - não pretendo transcrever o sobrenome. Logo ele escreve que o trabalho está quase pronto.

Mas quase não conta. Greg Smith, do 2ndQuadrant (ou seja, Simon Riggs aliado) escreve:
Portanto, temos um patch no código do qual meia dúzia de problemas sérios não resolvidos. Fico em silêncio sobre os mesquinhos. Os problemas são muito profundos para finalizar o código para o commitfest. Enquanto isso, nada foi ouvido de Boxuan por um longo tempo. Nós poderíamos ajudá-lo, mas onde ele está? Quem está a par?

Uma discussão sobre caminhos de implementação surge novamente em 2014 , mas novamente nada acontece: não há código.

Finalmente, já em 2017, Simon Riggs escreve:
Estou trabalhando no código para confirmar o MERGE no PostgreSQL versão 11 . Usamos os mesmos mecanismos subjacentes ao INSERT ON CONFLICT , que já está funcionando, para que não sejam necessárias alterações na infraestrutura, basicamente implementando a sintaxe além do que está disponível. Mas eu escrevo meu código do zero, não uso desenvolvimentos anteriores.

Estamos falando de Peter Geoghegan ( VMware ) implementado na época já na 9.5 sintaxe alternativa INSERT .. ON CONFLICT UPDATE , diferente do padrão SQL, mas ainda relacionado a MERGE e REPLACE no MySQL.

A princípio, o trabalho de Simon foi recebido com exclamações do belo trabalho! No entanto, Robert Haas , apesar de favorável, alerta para possíveis anomalias de serialização. Por exemplo, para lidar com INSERT .. ON CONFLICT UPDATE , sem MERGE em sua base, é de alguma forma mais calmo.

O UPSERT autor do PostgreSQL UPSERT :
Eu não misturaria o MERGE ON CONFLICT DO UPDATE e MERGE . <...> Para carregar grandes quantidades de dados ( bulk load ), por exemplo, usaria o algoritmo de merge join . <...> Em geral, as vantagens do MERGE estariam relacionadas ao fato de que as conexões normais funcionariam lá da maneira usual: nested loop, hash, merge . E em INSERT … ON CONFLICT não há junções.

Haas: Como Peter, acho que, se feito dessa maneira, um bloqueio tão forte ao executar uma solicitação DML parece mais ou menos. É improvável que alguém fique satisfeito com o fato de apenas uma pessoa poder trabalhar com o MERGE por vez.

Para os curiosos: Geigan desmonta as sutilezas e diferenças UPSERT entre MERGE e MERGE aqui e aqui (armazenamos a correspondência arquivada do PostgreSQL em nosso site).

Simon resiste. Ele apela à história recente. Como sobre o corte, eles também disseram "uma nova sintaxe, nada mais". Mas acabou sendo uma coisa muito útil. Mas não me proponho a perceber imediatamente tudo o que está em MERGE. Faremos o mesmo que com o particionamento - dividimos o desenvolvimento em fases.

E mais um argumento, na minha opinião, é muito convincente: bom. Mas vamos escolher. Eu sugiro uma opção prática. Em breve, 10 anos virão da primeira tentativa séria de desenvolver o MERGE . Não é hora de começar a fazer alguma coisa, obter uma solução útil, em vez de esperar mais 10 anos da Solução Perfeita? Supondo que exista.

Finalmente, o patch chega à comunidade. Que data? Imagine por favor. Não, eles não adivinharam: Simon o envia em 30 de dezembro de 2017. E estipula que este é um patch WIP, ou seja, Work in Progress - um patch em trabalho.

Simon, janeiro:
O patch é concluído sem erros especiais. 1200 linhas de código, além de testes e documentação. Vou comprometê-lo com este commitfest e concluiremos o RLS (Segurança no nível da linha - proteção no nível da gravação) e o suporte ao particionamento posteriormente.

Casta de comissões

Aqui temos que dar um passo à parte e explicar o papel do comissário na comunidade. As funções do comissário, isto é, aquele com poderes para aceitar o patch na próxima versão, mudaram historicamente. Era uma vez, quando havia poucos desenvolvedores, o direito de confirmar era distribuído generosamente. Por exemplo, o famoso (em um campo completamente diferente) Julian Assange recebeu o título de comandante, sendo o autor de apenas seis patches. Agora não é fácil se tornar um comissário, não há empresas iniciantes na lista de algumas dúzias de pessoas. Boyus Momdjan ( EnterpriseDB ) possui 13.363 commits, Tom Lane (Tom Lane, Crunchy Data ) 13127, Robert Haas ( EnterpriseDB ) - 2074. A propósito, o único committer da Rússia é Fedor Sigaev ( Postgres Professional ) com seus 383 commits . O próprio Simon Riggs possui 449. Repito: ele, como comissário, tem autoridade suficiente para receber e corrigir patches - ele e seus funcionários. Outra coisa é que dificilmente vale a pena fazer isso, negligenciando francamente as opiniões de outros comitês-luminares importantes. Eles também podem privar o status de um comissário, mas pelo menos revert patch.

Fratura em batalha

Obviamente, no patch "sem esperança", feito, em geral, às pressas, eles encontram novos erros. Novas versões são lançadas em resposta.

No final de janeiro, um novo personagem aparece: o desenvolvedor do 2ndQuadrant Pavan (seu nome é todo mundo pelo nome; completamente Pavan Deolasee). Agora a comunidade está lidando com um conjunto: Pavan envia novas versões e agradece as críticas, e Simon as quebra com uma pressão de marketing notável.

Haas: Não acho que valha a pena tomar decisões unilaterais sobre a exclusão de recursos que funcionam em qualquer lugar. Se concordarmos que alguns recursos não serão incluídos neste patch - isso é uma coisa. E é completamente diferente que, nos comentários desta ocasião, todos tenham expressado discordância. E, na verdade, não ouvimos as razões pelas quais esses recursos deveriam ser excluídos.

A lógica foi apresentada da seguinte forma:

• a priori, existem problemas sérios porque eles não podem deixar de estar nos desenvolvimentos no estilo de "ataque de cavalaria".
• Suporte para recursos importantes, como novo particionamento nas versões 10-11, CTE (Common Table Expressions = WITH query) ou RLS (Row Level Security) podem ser concluídos mesmo depois que o patch for aceito na versão atual, mas somente se a arquitetura proposta for adequada para construção em cima a funcionalidade desejada.

O segundo Peter Geigan formula isso:
Normalmente presto atenção ao suporte de várias funcionalidades, pois, se for, reforça a crença geral de que o design é feito como deveria . E se tais problemas são causados ​​pelo suporte das expressões WITH [ou seja, CTE ], entendi que a arquitetura subjacente é tal que causará problemas aqui e ali.

Enquanto isso, a hora X (a última comitê) está se aproximando, e as nuvens sobre MERGE estão se aproximando. Não é que os pais fundadores procurassem especificamente problemas sérios na arquitetura dos patches feitos por Simon e depois por Pavan. Não precisava procurar problemas, eles se abriram de bom grado.

O desenlace está se aproximando

O enredo está acelerando. Apesar da atitude descolada de outros comitês em relação a seu empreendimento, em 2 de abril, Simon decide cometer o patch Command: SQL: 2016 , adiciona os arquivos, Depesz (Hubert Lubachevsky) consegue anunciá-lo em seu blog, mas no mesmo dia Simon reverte tudo porque erros.

No dia seguinte, confirme novamente adicionando o suporte WITH .

Em resposta, as alegações são realmente graves. Andres Freund ( EnterpriseDB ) escreve:
A arquitetura do MERGE no analisador e no executor não me impressionou de maneira confiável. Criar junções ocultas durante a análise de análise é uma péssima idéia. Essa estrutura do executor deve ser completamente alterada.

Tom Lane:
O design da árvore de análise é fraco.



Você sobrecarrega a função InsertStmt , ele continua, ela não faz INSERT , mas aleatoriamente tem os mesmos campos que o original. E não todos, mas alguns. Isso é ruim, leva à confusão.

Vamos adicionar observações de Fedor Sigayev :
No analisador, os nós INSERT relacionados ao MERGE apareceram, pendurados com MERGE campos adicionais. Se você observar o plano de execução em ANALIZE , não entenderá imediatamente se está lidando com um INSERT regular ou com o MERGE : para entender, é necessário examinar campos adicionais.


Simon, calmamente: OK, mudaremos isso e enviaremos um novo arquivo amanhã .
Haas: Eu concordo com Peter. A escolha da arquitetura não teve êxito.

Simon não desiste. O dia 6 de abril, em resposta às críticas a Tom Lane, comete um novo patch, conforme corrigido no analisador.

Negociação e entrega

Bruce Momjan 6 de abril :
Quero observar que as pessoas não pediram para você trabalhar duro para corrigir algo com urgência. Eles pediram para você retirar o patch. Você pode, é claro, trabalhar duro, esperando que eles mudem de idéia, mas - novamente - eles não perguntaram sobre isso.

Simon: Se Tom [Lane] e Andres [Freund] pelos próximos dias ainda sentirem que seus medos não foram dissipados, terei prazer em reverter o patch sem mais delongas.

Tom Lane: Eu ainda voto para que o patch seja revertido. Mesmo se ele fosse perfeito agora, agora as pessoas não têm tempo para se convencer disso - na garganta de outros assuntos urgentes.

Só isso.

Simon disse que MERGE bem, e a batalha na MERGE terminado. Todos os patches foram revertidos, o tópico foi movido para o próximo commitfest com o status "Aguardando conclusão do autor". Os participantes do show fizeram as pazes.


No entanto, a julgar pela correspondência das últimas semanas, alguma tensão parece permanecer.

Moralidade Prometida

• Felizmente, a comunidade do PostgreSQL possui mecanismos naturais e formais para a triagem (quase) livre de conflitos de tentativas de soluções imaturas. Mesmo que sejam atingidos por desenvolvedores respeitados no posto de chefe da empresa, cuja contribuição para o desenvolvimento do PostgreSQL é enorme. E os clientes que não têm funcionalidade estão pressionando para investir.
• Infelizmente, a comunidade geralmente pára. É inercial na adoção de desenvolvimentos mesmo inequívocos. Às vezes, o perfeccionismo irracional está incluído. A experiência do Postgres Professional, onde trabalho, confirma isso. Nós perfuramos um patch grande e importante dos índices INCLUDE por 3 anos. Uma série útil de correções para trabalhar com JSON / JSONB ainda está aguardando. A expressão "dar seu desenvolvimento à comunidade" não significa realmente dar, mas dar um soco : o hóspede é recebido de braços abertos e escoltado para quarentena.

PS: Isenção de responsabilidade do autor : nós apenas queríamos mostrar um pedaço da vida da comunidade. Todas as correspondências de nomes são aleatórias :)
PPS: Samurai Natalia Levshina .