동시 트랜잭션을 사용하여 PostgreSQL UPSERT에서 누락된 행을 처리하는 방법은 무엇입니까?

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!