如何可靠地使用 PostgreSQL 的 RETURNING 子句和 ON CONFLICT DO NOTHING？

PostgreSQL 的 RETURNING 子句和 ON CONFLICT DO NOTHING

PostgreSQL 的 RETURNING 子句对于在 INSERT 操作后从新插入的行中检索数据非常有用。这对于使用 ON CONFLICT 的 UPSERT（插入或更新）特别有用。 使用 ON CONFLICT DO NOTHING 时会出现一个常见的挑战：如果不存在冲突，RETURNING 不会产生任何结果。

虽然尝试更新匹配行以触发 RETURNING 似乎是一个解决方案，但强烈建议不要这样做。 潜在的缺点包括：

• 不必要的触发激活
• 无意的行写锁定
• 将现有行错误地表示为新行
• 由于行版本控制过多而导致性能下降

存在更高效、更强大的替代方案。

处理没有并发写入的场景：

对于没有大量并发写入活动的环境，CTE（通用表表达式）与最终的 JOIN 相结合提供了一个优雅的解决方案。 这可确保插入的行和现有的行都包含在输出中：

<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>

即使在并发写入压力下，这些方法也能可靠地返回插入行和现有行的数据。

以上是如何可靠地使用 PostgreSQL 的 RETURNING 子句和 ON CONFLICT DO NOTHING？的详细内容。更多信息请关注PHP中文网其他相关文章！