ON CONFLICT DO NOTHING과 함께 PostgreSQL의 RETURNING 절을 안정적으로 사용하는 방법은 무엇입니까?

PostgreSQL의 RETURNING 절과 ON CONFLICT DO NOTHING

PostgreSQL의 RETURNING 절은 INSERT 작업 후에 새로 삽입된 행에서 데이터를 검색하는 데 매우 유용합니다. 이는 ON CONFLICT을 사용하는 UPSERT(삽입 또는 업데이트)에 특히 유용합니다. ON CONFLICT DO NOTHING을 사용할 때 일반적인 문제가 발생합니다. 충돌이 없으면 RETURNING에서는 결과가 나오지 않습니다.

RETURNING을 트리거하기 위해 일치하는 행을 업데이트하는 것이 해결책처럼 보일 수 있지만 이는 권장되지 않습니다. 잠재적인 단점은 다음과 같습니다.

• 불필요한 트리거 활성화
• 의도하지 않은 행 쓰기 잠금
• 기존 행을 새 행으로 허위 표시
• 과도한 행 버전 관리로 인한 성능 저하

더 효율적이고 강력한 대안이 있습니다.

동시 쓰기 없이 시나리오 처리:

중요한 동시 쓰기 활동이 없는 환경의 경우 최종 JOIN과 결합된 CTE(공통 테이블 표현식)가 우아한 솔루션을 제공합니다. 이렇게 하면 삽입된 행과 기존 행이 모두 출력에 포함됩니다.

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

동시 쓰기 로드 처리:

동시 쓰기 작업으로 인해 복잡성이 발생합니다. INSERT이 완료되기 전에 다른 트랜잭션이 행을 수정하면 불일치가 발생할 수 있습니다.

이 문제를 완화하려면 기존 행을 조기에 잠그는 것이 좋습니다. 두 번째 UPSERT 단계에서는 누락된 행을 처리할 수 있습니다.

<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 UPDATE
   SET    name = name WHERE FALSE  -- Locks the row without updating
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats AS c (usr, contact, name)
   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' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;</code>

이러한 접근 방식은 동시 쓰기 압력이 있는 경우에도 삽입된 행과 기존 행 모두에 대한 데이터를 안정적으로 반환합니다.

위 내용은 ON CONFLICT DO NOTHING과 함께 PostgreSQL의 RETURNING 절을 안정적으로 사용하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!