Hologres:INSERT ON CONFLICT (UPSERT)

Concepts clés

L'instruction prend en charge trois modes de résolution des conflits :

Choisissez un mode :

• Utilisez InsertOrIgnore lorsque la première écriture prime et que vous ne souhaitez jamais écraser les données existantes.

• Utilisez InsertOrUpdate pour mettre à jour sélectivement certaines colonnes tout en préservant les autres.

• Utilisez InsertOrReplace pour remplacer entièrement la ligne, en définissant explicitement les colonnes manquantes sur null.

Cas d'utilisation

INSERT ON CONFLICT s'applique lors de l'importation de données via des instructions SQL.

Data Integration (DataWorks)

Data Integration intègre nativement la fonctionnalité INSERT ON CONFLICT. Configurez la Write Conflict Policy dans le Hologres Writer :

• Ignore — équivalent à InsertOrIgnore

• Replace — équivalent à InsertOrReplace

Cette politique s'applique à la synchronisation hors ligne et en temps réel. La table Hologres doit disposer d'une clé primaire pour permettre la mise à jour des données pendant la synchronisation.

Flink

La write conflict policy par défaut pour les écritures Flink est InsertOrIgnore. Cela nécessite une clé primaire sur la table de destination et conserve la première entrée en cas de doublon. Lorsque vous utilisez la syntaxe ctas, la valeur par défaut devient InsertOrUpdate.

Syntaxe

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 ]

À propos de excluded :

excluded est un alias de table virtuelle qui fait référence à la ligne proposée pour l'insertion, c'est-à-dire celle qui a déclenché le conflit. Ce n'est pas un alias de la table source. Utilisez excluded.<column_name> pour référencer une colonne spécifique de la ligne entrante, ou ROW(excluded.*) pour référencer toutes les colonnes dans l'ordre DDL.

Paramètres :

Fonctionnement

INSERT ON CONFLICT suit le même chemin interne que UPDATE. Les performances de mise à jour dépendent du format de stockage de la table.

Tables orientées colonne

Les tables sans clé primaire offrent le débit d'écriture le plus élevé. Pour les tables avec une clé primaire :

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

Tables orientées ligne

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

Pour optimiser les performances sur les tables orientées ligne, alignez l'ordre des colonnes dans la clause DO UPDATE SET sur celui de la clause INSERT et effectuez une mise à jour complète de la ligne :

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

Les colonnes dotées de valeurs par défaut ne sont pas mises à jour par DO UPDATE , ce qui réduit les performances. Pour implémenter InsertOrReplace via SQL, passez null explicitement dans la liste VALUES . Des outils comme Flink et Data Integration ajoutent null automatiquement pour les colonnes manquantes.

Limitations

• La clause ON CONFLICT doit référencer toutes les colonnes de la clé primaire.

• Lorsque le moteur High-QPS Engine (HQE) de Hologres exécute INSERT ON CONFLICT, l'ordre des opérations n'est pas garanti : les sémantiques « conserver le premier » et « conserver le dernier » ne sont pas prises en charge. Le comportement est « conserver n'importe lequel ». Pour forcer la conservation de la dernière ligne en cas de doublons dans la source, définissez :

  set hg_experimental_affect_row_multiple_times_keep_last = on;

• Les données source ne doivent pas contenir de lignes en double. La sémantique PostgreSQL exige que chaque ligne de la liste VALUES ou de la requête source soit unique. Si la source contient des doublons (par exemple, une sous-requête SELECT renvoyant deux lignes avec la même clé primaire), l'instruction échoue. Pour éviter cela, dédupliquez la source avant la clause 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.*);

Exemples

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

Dépannage

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

Étapes suivantes

• UPDATE — comprendre le mécanisme de mise à jour sous-jacent

• Aperçu de Serverless Computing — décharger les grandes tâches d'écriture vers des ressources serverless (Hologres V2.1.17+)

• Hologres Writer — configurer la politique de conflit d'écriture dans Data Integration