Greenplum：在違反唯一約束時插入非重複行

我有一個執行以下操作的儲存過程：

INSERT INTO schema.my_unique_values
    SELECT DISTINCT id, value
      FROM schema.a_huge_table
     WHERE NOT EXISTS (SELECT 1
                         FROM schema.my_unique_values)

總之，此查詢將從 a_huge_table 插入唯一行到 my_unique_values 表中。

我遇到的問題是，當我出於某種原因在多執行緒腳本中同時執行此查詢時，它可以插入重複的行。我曾嘗試使用可序列化來避免這種“幻讀”，但仍然沒有運氣。我的想法是允許 ​​Postgres 僅在違反唯一約束時插入非重複行。但這可能嗎？我目前的經驗是，當遇到唯一約束違規時，它將取消整個事務，因此不會插入非重複行。我怎樣才能實現我的目標？

注意：我使用的是使用 Postgres 8.2 的 Greenplum 4.3.11，因此我可以使用的查詢存在限制。

謝謝..

首先，我認為您需要稍微更改您的查詢，因為現在寫的 WHERE NOT EXISTS 將在您的表有一些行時為假。您需要指定一個 WHERE 子句：

INSERT INTO schema.my_unique_values
    SELECT DISTINCT id, value
      FROM schema.a_huge_table a
     WHERE NOT EXISTS 
            (SELECT 1
               FROM schema.my_unique_values m
              WHERE m.id = a.id AND m.value = a.value)

INSERT，無論是在事務中，還是作為單語句事務，都將是原子的。所以，如果一行失敗，一切都會失敗。在任何情況下，您都不能在事務隔離級別中進行幻讀。SERIALIZABLE如果您處於該隔離級別，請確保您NOT EXISTS (...)確實擁有正確編寫的WHERE子句。

從 PostgreSQL 版本 9.5 開始，有一個新子句可以是 an 的一部分，INSERT並且完全適合您的案例。您可以修改您的查詢以使用ON CONFLICT DO NOTHING：

INSERT INTO schema.my_unique_values
    SELECT DISTINCT id, value
      FROM schema.a_huge_table a
ON CONFLICT DO NOTHING ;

注意：該ON CONFLICT子句適用於每一行，而不是INSERT整體。

另一種想法，這是一個記憶體表。我不是那些的粉絲。如果從(id,value)中刪除會發生什麼schema.a_huge_table？

CREATE MATERIALIZED VIEW schema.my_unique_values AS
 SELECT DISTINCT id, value
 FROM schema.a_huge_table;

然後當你想刷新它..

REFRESH MATERIALIZED VIEW schema.my_unique_values

你甚至可以刷新它CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY schema.my_unique_values

刷新物化視圖而不鎖定物化視圖上的並發選擇。如果沒有此選項，影響大量行的刷新將傾向於使用更少的資源並更快地完成，但可能會阻止嘗試從物化視圖讀取的其他連接。在少量行受到影響的情況下，此選項可能會更快。

並不是說它總是完美的解決方案。但是，我通常發現它對於合理範圍內的大多數事情是一個更好的解決方案。

引用自：https://dba.stackexchange.com/questions/161903