PostgreSQL UPSERT作業中處理缺失結果行
在PostgreSQL 9.5中，結合使用RETURNING和ON CONFLICT有時會在並發事務更新指定的衝突目標時導致行缺失。
目前已接受答案的缺點
目前已接受的答案在並發交易更新目標表多行時容易出現問題。雖然它可以防止行缺失，但它會引入其他副作用和與空更新相關的效能損失。
替代方案
無併發寫入負載：
<code class="language-sql">WITH input_rows(usr, contact, name) AS (
VALUES
(text 'foo1', text 'bar1', text 'bob1') -- 第一行中的类型转换
, ('foo2', 'bar2', 'bob2')
-- 更多？
)
, ins AS (
INSERT INTO chats (usr, contact, name)
SELECT * FROM input_rows
ON CONFLICT (usr, contact) DO NOTHING
RETURNING id --, usr, contact -- 返回更多列？
)
SELECT 'i' AS source -- 'i' 代表'插入'
, id --, usr, contact -- 返回更多列？
FROM ins
UNION ALL
SELECT 's' AS source -- 's' 代表'选择'
, c.id --, usr, contact -- 返回更多列？
FROM input_rows
JOIN chats c USING (usr, contact); -- 唯一索引的列</code>有並發寫入負載
為了解決多個並發事務的競爭條件：
<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... ) -- 如上所示
, ins AS (
INSERT INTO chats AS c (usr, contact, name)
SELECT * FROM input_rows
ON CONFLICT (usr, contact) DO NOTHING
RETURNING id, usr, contact -- 我们需要唯一的列来进行后续连接
)
, sel AS (
SELECT 'i'::"char" AS source -- 'i' 代表'插入'
, id, usr, contact
FROM ins
UNION ALL
SELECT 's'::"char" AS source -- 's' 代表'选择'
, c.id, usr, contact
FROM input_rows
JOIN chats c USING (usr, contact)
)
, ups AS ( -- 罕见的极端情况
INSERT INTO chats AS c (usr, contact, name) -- 另一个UPSERT，不仅仅是UPDATE
SELECT i.*
FROM input_rows i
LEFT JOIN sel s USING (usr, contact) -- 唯一索引的列
WHERE s.usr IS NULL -- 缺失！
ON CONFLICT (usr, contact) DO UPDATE -- 我们第一次已经礼貌地请求了……
SET name = c.name -- ……这次我们用旧值覆盖
RETURNING 'u'::"char" AS source -- 'u' 代表更新
, id --, usr, contact -- 返回更多列？
)
SELECT source, id FROM sel
UNION ALL
TABLE ups;</code>關鍵點：
sel CTE 傳回插入的行和選取的行，提供完整的輸出結果集。 ups CTE 處理缺少的行，用現有值更新它們。 ups處理它們。 以上是如何使用並發事務處理 PostgreSQL UPSERT 中的缺失行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
