Bagaimana Menggunakan Klausa KEMBALI PostgreSQL dengan Amanah dengan ON CONFICT DO NOTHING?

Fasal RETURNING PostgreSQL dan ON CONFLICT DO NOTHING

Fasal

PostgreSQL RETURNING amat berharga untuk mendapatkan semula data daripada baris yang baru dimasukkan selepas operasi INSERT. Ini amat berguna dengan UPSERT (masukkan atau kemas kini) menggunakan ON CONFLICT. Cabaran biasa timbul apabila menggunakan ON CONFLICT DO NOTHING: jika tiada konflik wujud, RETURNING tidak membuahkan hasil.

Semasa cuba mengemas kini baris yang sepadan untuk mencetuskan RETURNING mungkin kelihatan seperti penyelesaian, ini amat tidak digalakkan. Kelemahan yang berpotensi termasuk:

• Pengaktifan pencetus yang tidak perlu
• Penguncian tulis baris yang tidak disengajakan
• Menyesatkan baris sedia ada sebagai baharu
• Kemerosotan prestasi disebabkan versi baris yang berlebihan

Alternatif yang lebih cekap dan mantap wujud.

Mengendalikan Senario Tanpa Tulisan Serentak:

Untuk persekitaran tanpa aktiviti tulis serentak yang ketara, CTE (Ungkapan Jadual Biasa) digabungkan dengan JOIN akhir menyediakan penyelesaian yang elegan. Ini memastikan kedua-dua baris yang disisipkan dan yang sedia ada disertakan dalam output:

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

Mengatasi Beban Tulis Serentak:

Operasi tulis serentak memperkenalkan kerumitan. Jika transaksi lain mengubah suai baris sebelum INSERT anda selesai, ketidakkonsistenan boleh timbul.

Untuk mengurangkan masalah ini, pertimbangkan untuk mengunci baris sedia ada lebih awal. Langkah UPSERT kedua boleh mengendalikan mana-mana baris yang hilang:

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

Pendekatan ini dapat mengembalikan data untuk kedua-dua baris yang disisipkan dan sedia ada, walaupun di bawah tekanan tulis serentak.

Atas ialah kandungan terperinci Bagaimana Menggunakan Klausa KEMBALI PostgreSQL dengan Amanah dengan ON CONFICT DO NOTHING?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!