Hologres:INSERT ON CONFLICT (UPSERT)

Temel kavramlar

Bu ifade üç çakışma çözümleme modunu destekler:

Mod seçimi:

• İlk yazan kazanır kuralını uygulamak ve mevcut verilerin üzerine yazılmamasını sağlamak istediğinizde InsertOrIgnore kullanın.

• Seçici sütun güncellemelerine ihtiyaç duyduğunuzda ve eksik sütunların korunması gerektiğinde InsertOrUpdate kullanın.

• Satırın tamamını değiştirmek ve eksik sütunları açıkça null olarak ayarlamak istediğinizde InsertOrReplace kullanın.

Kullanım durumları

INSERT ON CONFLICT ifadesi, verileri SQL deyimleri kullanarak aktardığınızda geçerlidir.

Data Integration (DataWorks)

Data Integration, yerleşik bir INSERT ON CONFLICT özelliğine sahiptir. Hologres Writer içindeki Write Conflict Policy seçeneğini yapılandırın:

• Ignore — InsertOrIgnore moduna eşdeğerdir

• Replace — InsertOrReplace moduna eşdeğerdir

Bu ilke hem çevrimdışı hem de gerçek zamanlı senkronizasyon için geçerlidir. Senkronizasyon sırasında verileri güncellemek için Hologres tablosunda bir birincil anahtar bulunmalıdır.

Flink

Flink yazma işlemleri için varsayılan write conflict policy değeri InsertOrIgnore şeklindedir. Bu, havuz tablosunda bir birincil anahtar gerektirir ve yinelenen bir kayıtla karşılaşıldığında ilk girişi korur. ctas sözdizimini kullandığınızda varsayılan değer InsertOrUpdate olarak değişir.

Sözdizimi

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 ]

excluded hakkında:

excluded , eklenmesi önerilen (çakışmayı tetikleyen) satıra başvurmak için kullanılan sanal bir tablo takma adıdır. Kaynak tablo için bir takma ad değildir. Gelen satırdaki belirli bir sütuna başvurmak için excluded.<column_name> veya DDL sırasındaki tüm sütunlara başvurmak için ROW(excluded.*) kullanın.

Parametreler:

Çalışma biçimi

INSERT ON CONFLICT , UPDATE ile aynı dahili yolu izler. Güncelleme performansı tablonun depolama formatına bağlıdır.

Sütun yönelimli tablolar

Birincil anahtarı olmayan tablolar en yüksek yazma aktarım hızını sunar. Birincil anahtarı olan tablolar için performans sıralaması şöyledir:

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

Satır yönelimli tablolar

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

Satır yönelimli tablolarda en iyi performansı elde etmek için DO UPDATE SET yan tümcesindeki sütun sırasının INSERT yan tümcesiyle tutarlı olmasını sağlayın ve tam satır güncellemesi yapın:

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

Varsayılan değerlere sahip sütunlar DO UPDATE tarafından güncellenmez, bu da performansı düşürür. SQL aracılığıyla InsertOrReplace uygulamak için null değerini VALUES listesinde açıkça iletin. Flink ve Data Integration gibi araçlar, eksik sütunlar için null değerini otomatik olarak ekler.

Sınırlamalar

• ON CONFLICT yan tümcesi tüm birincil anahtar sütunlarına başvurmalıdır.

• Hologres Yüksek QPS Motoru (HQE), INSERT ON CONFLICT işlemini yürüttüğünde işlem sırası garanti edilmez; ilk-kazanır ve son-kazanır semantikleri desteklenmez. Davranış herhangi-birini-tut (keep-any) şeklindedir. Kaynaktaki yinelenen satırlarda son-kazanır (keep-last) davranışını zorlamak için aşağıdaki ayarı yapın:

  set hg_experimental_affect_row_multiple_times_keep_last = on;

• Kaynak veriler yinelenen satır içermemelidir. PostgreSQL semantiği, VALUES listesindeki veya kaynak sorgusundaki her satırın benzersiz olmasını gerektirir. Kaynakta yinelenenler varsa (örneğin, aynı birincil anahtara sahip iki satır döndüren bir SELECT alt sorgusundan) ifade başarısız olur. Bunu önlemek için ON CONFLICT yan tümcesinden önce kaynağı yinelmesiz hale getirin:

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

Örnekler

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

Sorun giderme

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

Sonraki adımlar

• Temel alınan güncelleme mekanizmasını anlamak için UPDATE konusuna bakın.

• Büyük yazma işlerini sunucusuz kaynaklara yönlendirmek için (Hologres V2.1.17+) Serverless Computing'a genel bakış konusuna bakın.

• Data Integration'da yazma çakışma ilkesini yapılandırmak için Hologres Writer konusuna bakın.