Hologres:INSERT ON CONFLICT (UPSERT)

Conceitos principais

A instrução suporta três modos de resolução de conflitos:

Escolha um modo:

• Use InsertOrIgnore quando a primeira gravação deve prevalecer e você não deseja sobrescrever dados existentes.

• Use InsertOrUpdate se forem necessárias atualizações seletivas de colunas, preservando aquelas não especificadas.

• Use InsertOrReplace para substituição completa da linha, definindo explicitamente as colunas ausentes como null.

Casos de uso

A instrução INSERT ON CONFLICT aplica-se à importação de dados por meio de instruções SQL.

Data Integration (DataWorks)

O Data Integration possui um recurso nativo de INSERT ON CONFLICT. Configure a Write Conflict Policy no Hologres Writer:

• Ignore — equivalente a InsertOrIgnore

• Replace — equivalente a InsertOrReplace

Essa política vale tanto para sincronização offline quanto em tempo real. Para atualizar dados durante a sincronização, a tabela do Hologres precisa ter uma chave primária.

Flink

A write conflict policy padrão para gravações via Flink é InsertOrIgnore. Esse comportamento exige uma chave primária na tabela de destino e mantém a primeira entrada ao encontrar duplicatas. Ao usar a sintaxe ctas, o padrão passa a ser InsertOrUpdate.

Sintaxe

INSERT INTO <table_name> [ AS <alias> ] [ ( <column_name> [, ...] ) ]
    { VALUES ( { <expression> } [, ...] ) [, ...] | <query> }
    [ ON CONFLICT [ conflict_target ] conflict_action ]

-- conflict_target
ON CONSTRAINT constraint_name

-- conflict_action
DO NOTHING
DO UPDATE SET { <column_name> = { <expression> } |
                ( <column_name> [, ...] ) = ( { <expression> } [, ...] ) |
              } [, ...]
          [ WHERE condition ]

Sobre excluded:

O termo excluded é um alias de tabela virtual que referencia a linha proposta para inserção — ou seja, a linha que originou o conflito. Não se trata de um alias para a tabela de origem. Use excluded.<column_name> para referenciar uma coluna específica da linha recebida ou ROW(excluded.*) para acessar todas as colunas na ordem definida no DDL.

Parâmetros:

Funcionamento

A instrução INSERT ON CONFLICT segue o mesmo caminho interno de execução do UPDATE. O desempenho da atualização varia conforme o formato de armazenamento da tabela.

Tabelas orientadas a colunas

Tabelas sem chave primária oferecem o maior throughput de gravação. Para tabelas com chave primária:

InsertOrIgnore > InsertOrReplace >= InsertOrUpdate (full row) > InsertOrUpdate (partial column)

Tabelas orientadas a linhas

InsertOrReplace = InsertOrUpdate (full row) >= InsertOrUpdate (partial column) >= InsertOrIgnore

Para obter o melhor desempenho em tabelas orientadas a linhas, mantenha a ordem das colunas na cláusula DO UPDATE SET consistente com a cláusula INSERT e execute uma atualização de linha completa:

INSERT INTO test1 (a, b, c)
SELECT d, e, f FROM test2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

Colunas com valores padrão não são atualizadas pelo DO UPDATE , o que reduz o desempenho. Para implementar InsertOrReplace via SQL, passe null explicitamente na lista VALUES . Ferramentas como Flink e Data Integration adicionam null automaticamente para colunas ausentes.

Limitações

• A cláusula ON CONFLICT deve referenciar todas as colunas da chave primária.

• Quando o Hologres High-QPS Engine (HQE) executa INSERT ON CONFLICT, a ordem das operações não é garantida — as semânticas de manter a primeira ou a última entrada não têm suporte. O comportamento adotado é manter qualquer uma. Para impor a retenção da última entrada em linhas duplicadas na origem, defina:

  set hg_experimental_affect_row_multiple_times_keep_last = on;

• Os dados de origem não devem conter linhas duplicadas. A semântica do PostgreSQL exige que cada linha na lista VALUES ou na consulta de source seja única. Se a source contiver duplicatas (por exemplo, devido a uma subconsulta SELECT que retorna duas linhas com a mesma chave primária), a instrução falhará. Para evitar isso, remova as duplicatas na source antes da cláusula ON CONFLICT:

  -- Deduplicate the source with a subquery before upserting
  INSERT INTO target_table (a, b, c)
  SELECT a, MAX(b), MAX(c) FROM source_table GROUP BY a
  ON CONFLICT (a)
  DO UPDATE SET (a, b, c) = ROW(excluded.*);

Exemplos

BEGIN;
CREATE TABLE test1 (
    a int NOT NULL PRIMARY KEY,
    b int,
    c int
);
COMMIT;

INSERT INTO test1 VALUES (1, 2, 3);

INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO NOTHING;

a | b | c
1 | 2 | 3    -- unchanged

-- Partial-column update: only b is updated, c keeps its value
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b;

a | b | c
1 | 1 | 3    -- c unchanged

-- Method 1: list all columns explicitly
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

-- Method 2: use ROW(excluded.*) shorthand
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

a | b | c
1 | 1 | 1

INSERT INTO test1 (a, b, c) VALUES (1, 1, null)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

a | b | c
1 | 1 | \N

-- Prepare source table
CREATE TABLE test2 (
    d int NOT NULL PRIMARY KEY,
    e int,
    f int
);
INSERT INTO test2 VALUES (1, 5, 6);

-- Replace rows in test1 with matching rows from test2
INSERT INTO test1 (a, b, c)
SELECT d, e, f FROM test2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

a | b | c
1 | 5 | 6

INSERT INTO test1 (a, b, c)
SELECT d, e, f FROM test2
ON CONFLICT (a)
DO UPDATE SET (a, c, b) = ROW(excluded.*);

a | b | c
1 | 6 | 5

Solução de problemas

duplicate key value violates unique constraint
-- or --
Update row with Key (xxx)=(yyy) multiple times

-- This fails: the source contains (1, 2, 3) twice
INSERT INTO test1 VALUES (1, 2, 3), (1, 2, 3)
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);
-- ERROR: internal error or constraint violation

set hg_experimental_affect_row_multiple_times_keep_last = on;

call public.hg_remove_duplicated_pk('<schema>.<table_name>');

BEGIN;
CREATE TABLE tbl_1 (a int NOT NULL PRIMARY KEY, b int, c int);
CREATE TABLE tbl_2 (d int NOT NULL PRIMARY KEY, e int, f int);
CALL set_table_property('tbl_2', 'time_to_live_in_seconds', '300');
COMMIT;

INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4);
INSERT INTO tbl_2 VALUES (1, 5, 6);

-- After 300 seconds, insert a row with the same primary key into tbl_2.
INSERT INTO tbl_2 VALUES (1, 3, 6);

-- This fails: tbl_2 now has duplicate primary keys caused by TTL expiry.
INSERT INTO tbl_1 (a, b, c)
SELECT d, e, f FROM tbl_2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);
-- ERROR: internal error: Duplicate keys detected when building hash table.

-- Clean up duplicate primary keys in tbl_2, then retry.
call public.hg_remove_duplicated_pk('tbl_2');

-- The import now succeeds.
INSERT INTO tbl_1 (a, b, c)
SELECT d, e, f FROM tbl_2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

Total memory used by all existing queries exceeded memory limitation

Próximos passos

• UPDATE — compreenda o mecanismo subjacente de atualização

• Visão geral do Serverless Computing — descarregue grandes tarefas de gravação em recursos serverless (Hologres V2.1.17+)

• Hologres Writer — configure a política de conflito de gravação no Data Integration