PostgreSQL tidak boleh menggunakan ON CONFLICT DO UPDATE sebagai operasi umum “padankan mana-mana kunci unik”. Pilih satu kunci penentu, atau semak kedua-dua kunci secara jelas dalam transaksi dan tolak input yang mengenal pasti dua baris berbeza.

Tutorial ini membantu pembangun yang bekerja dalam Terminal pada Mac melaksanakan dasar kedua itu dengan node-postgres: masukkan apabila tiada kunci sepadan, kemas kini apabila tepat satu artikel sepadan, dan pulangkan konflik yang jelas apabila kunci merujuk kepada artikel yang berbeza. Semua baris adalah sintetik.

Contoh SQL menyasarkan PostgreSQL 18, versi yang digunakan oleh dokumentasi semasa yang dirujuk. Pelaksanaan ini menggunakan jadual biasa yang tidak dipartisi dengan dua kekangan unik serta-merta. Semak versi utama pelayan anda sebelum menyesuaikan contoh daripada manual versi lain. Dokumentasi INSERT PostgreSQL 18

Mengapa dua kekangan unik tidak boleh menjadi satu sasaran konflik

Katakan sesuatu artikel mempunyai rujukan luaran dan slug. Kedua-duanya mesti unik secara berasingan. Mulakan dengan persediaan lengkap ini dalam pangkalan data demonstrasi yang kosong; simpannya sebagai setup.sql.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

Pengisytiharan ini menguatkuasakan dua peraturan yang berasingan. Menggantikannya dengan UNIQUE (external_key, slug) akan mengubah model: hanya pasangan itu yang perlu unik, lalu membenarkan kunci luaran dan slug berulang dalam gabungan yang berbeza. Mengekalkan kedua-dua lajur sebagai NOT NULL juga mengelakkan tingkah laku kunci boleh nol daripada diperkenalkan dalam contoh ini. Kekangan PostgreSQL

Untuk DO UPDATE, PostgreSQL memerlukan sasaran konflik. Senarai lajur menerangkan lajur bagi indeks unik yang sepadan; ia tidak bermaksud “mana-mana lajur”. Oleh itu, ON CONFLICT (external_key, slug) tidak dapat membuat inferens tentang arbiter daripada dua kekangan berasingan di atas. Menamakan kekangan akan memilih kekangan itu, dan sintaksnya menyediakan satu klausa ON CONFLICT, bukannya rangkaian pengendali alternatif. INSERT PostgreSQL: Klausa ON CONFLICT

Masalah yang lebih mendalam ialah menentukan artikel sedia ada yang diwakili oleh baris masuk. Pertimbangkan nilai masuk feed-101 dan green-notes: kunci luaran milik artikel biru, manakala slug milik artikel hijau. Mengemas kini mana-mana baris dengan kedua-dua kunci masuk akan bercanggah dengan baris yang satu lagi. Memilih padanan pertama akan menyembunyikan keputusan input yang belum diselesaikan.

Tentukan tindakan bagi setiap gabungan padanan

Gunakan jadual keputusan berikut sebelum menulis pertanyaan. Ini ialah dasar aplikasi yang dicadangkan berdasarkan skema ini, bukannya tingkah laku yang PostgreSQL sediakan secara automatik.

Kunci luaran masuk Slug masuk Baris padanan berbeza Tindakan dalam tutorial ini
Tidak digunakan Tidak digunakan 0 Masukkan artikel
Sepadan dengan artikel Tidak digunakan 1 Kemas kini artikel itu, termasuk slugnya
Tidak digunakan Sepadan dengan artikel 1 Kemas kini artikel itu, termasuk kunci luarannya
Sepadan dengan artikel A Sepadan dengan artikel A 1 Kemas kini artikel A
Sepadan dengan artikel A Sepadan dengan artikel B 2 Tolak input; kekalkan kedua-dua artikel

Kes satu padanan sengaja membenarkan mana-mana kunci mengenal pasti artikel dan membolehkan kunci yang satu lagi berubah. Pastikan ini tingkah laku yang diperlukan oleh import anda. Jika kunci luaran tidak boleh berubah sama sekali, padanan slug sahaja sepatutnya menjadi konflik aplikasi.

Jika peraturan sebenar anda lebih mudah—“kunci luaran sentiasa menentukan artikel”—gunakan upsert bersasaran biasa. Pernyataan lengkap ini dijalankan selepas persediaan di atas:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

Di sini EXCLUDED membekalkan nilai yang dicadangkan. Kekangan unik yang satu lagi masih terpakai: menukar slug yang dicadangkan kepada green-notes akan menghasilkan konflik keunikan yang berasingan, bukannya beralih kepada artikel yang satu lagi. Jaminan atomik upsert bersasaran untuk operasi masukkan-atau-kemas kini bergantung pada ketiadaan ralat bebas yang lain. Dokumentasi INSERT PostgreSQL

Pastikan keputusan bagi mana-mana kunci kekal stabil semasa penulisan serentak

Carian yang diikuti dengan sisipan mewujudkan jurang antara keputusan dengan penulisan. Transaksi lain boleh memasukkan kunci yang sepadan dalam jurang itu. Menambah FOR UPDATE pada carian mengunci baris yang diperoleh; apabila tiada baris sepadan, tiada baris diperoleh untuk dikunci. Oleh itu, penguncian baris sahaja tidak mencukupi untuk laluan tiada padanan ini. Penguncian Eksplisit PostgreSQL

Untuk import kecil atau operasi desktop sekali-sekala, penyelesaian mudah ialah mengambil kunci jadual SHARE ROW EXCLUSIVE sebelum menyemak mana-mana kunci. Mod ini bercanggah dengan kunci ROW EXCLUSIVE yang diperoleh oleh sisipan, kemas kini dan pemadaman biasa, serta bercanggah dengan satu lagi kunci daripada mod yang sama. Bacaan biasa kekal serasi. Mod kunci aras jadual PostgreSQL

Urutan yang terhasil ialah: mulakan transaksi, ambil kunci jadual, cari baris berbeza yang sepadan, gunakan jadual keputusan, kemudian lakukan commit. Kunci eksplisit menunggu kunci yang tidak serasi dan kekal dipegang sehingga transaksi tamat. LOCK PostgreSQL

Gunakan READ COMMITTED secara eksplisit di sini. Carian itu melihat data yang telah di-commit sebelum carian bermula, termasuk penulisan yang selesai semasa menunggu kunci diperoleh. Menggabungkan tingkah laku petikan ini dengan kunci jadual yang diperoleh memberikan asas yang stabil untuk membuat keputusan dan menulis. Pengasingan Transaksi PostgreSQL

Ini ialah kunci yang luas: penulisan artikel yang tidak berkaitan juga akan menunggu. Pastikan transaksi singkat, tanpa gesaan interaktif atau kerja luaran antara penguncian dengan commit. Untuk import serentak yang kerap, pertimbangkan semula sama ada satu kunci kanonik boleh menentukan identiti sebelum menggunakan reka bentuk ini.

Laksanakan transaksi dengan node-postgres

Anda memerlukan pangkalan data demonstrasi PostgreSQL 18 yang sedang berjalan, arahan psql, dan versi Node.js yang disokong oleh pakej node-postgres yang dipasang. Dalam Terminal, pasang pakej dan muatkan fail persediaan. Arahan pertama mencipta metadata pakej jika direktori demonstrasi anda belum memilikinya. Pemasangan dan keserasian node-postgres

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

Arahan ini mengandaikan tetapan sambungan tempatan biasa anda telah memilih pangkalan data demonstrasi. Program ini menggunakan new Pool(), yang membaca pemboleh ubah persekitaran sambungan PostgreSQL yang sama seperti psql; tetapkan PGDATABASE kepada nama pangkalan data demonstrasi anda jika perlu. Menyambung dengan node-postgres

Simpan program lengkap ini sebagai upsert-articles.mjs. Program ini mengandaikan jadual persediaan tidak mempunyai pencetus atau peraturan tambahan yang mengubah tingkah laku padanan atau kemas kini. Nilai menggunakan parameter berposisi yang dibekalkan secara berasingan daripada teks SQL. Pertanyaan node-postgres

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

Setiap pernyataan transaksi menggunakan klien yang sama yang telah diperoleh, seperti yang dikehendaki oleh node-postgres. Menghantar pernyataan ini melalui panggilan pool.query() yang berasingan tidak akan mengekalkan keperluan sambungan tersebut. Transaksi node-postgres

Blok finally sentiasa melepaskan klien yang diperoleh. Jika rollback itu sendiri gagal, release(true) akan membuang sambungan tersebut. pool.end() di bahagian luar menutup pool apabila skrip desktop selesai. API Pool node-postgres

Jalankan program dengan node upsert-articles.mjs. Hasil yang dijangka ialah artikel biru dengan tajuk yang disemak dan kunci yang tidak berubah. Sintaks JavaScript telah disemak dengan Node.js; contoh pangkalan data telah dibandingkan dengan dokumentasi tetapi tidak dijalankan terhadap pelayan PostgreSQL di sini.

Semak setiap cabang dan kendalikan kegagalan secara jelas

Dengan menggunakan baris persediaan, ubah objek yang dihantar kepada upsertArticle() untuk menguji kes berikut. Jalankan setiap kes menggunakan keadaan persediaan asal, atau ambil kira perubahan yang dibuat oleh kes terdahulu.

externalKey slug Hasil yang dijangka
feed-303 amber-notes Masukkan artikel baharu
feed-101 blue-revised Kemas kini artikel biru dan gantikan slugnya
feed-303 blue-notes Kemas kini artikel biru dan gantikan kunci luarannya
feed-101 blue-notes Kemas kini tajuk artikel biru
feed-101 green-notes Lemparkan ARTICLE_KEY_CONFLICT; jangan ubah mana-mana artikel

Carian memulangkan baris, jadi artikel yang sepadan dengan kedua-dua syarat dikira sekali sahaja. Dengan dua kunci unik bebas yang tidak boleh nol ini, paling banyak dua baris berbeza boleh sepadan. Kod ini tidak pernah menyelesaikan hasil dua baris dengan menggunakan LIMIT 1, memadam artikel atau melangkau input secara senyap.

ARTICLE_KEY_CONFLICT ialah kod yang ditakrifkan aplikasi. 23505 PostgreSQL bermaksud unique_violation; ia merupakan keadaan yang berasingan. Jika keadaan yang kedua muncul selepas anda menyesuaikan contoh ini, periksa kekangan yang dilanggar dan sebarang tingkah laku jadual tambahan, bukannya menganggap semua pendua menghasilkan keputusan yang sama. PostgreSQL mengesyorkan supaya kod ralat diperiksa dan bukannya teks mesej dihuraikan. Kod Ralat PostgreSQL

Pemerolehan kunci mungkin perlu menunggu. Jika anda mahukan kegagalan serta-merta untuk tugasan desktop ini, gantikan pernyataan penguncian dengan LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL kemudian akan mengeluarkan ralat jika kunci tidak dapat diperoleh serta-merta, lalu program mengikuti laluan rollbacknya. LOCK PostgreSQL

Adakah DO NOTHING atau MERGE akan memudahkan tugasan ini?

ON CONFLICT DO NOTHING tanpa sasaran boleh melangkau sisipan yang bercanggah dengan mana-mana kekangan atau indeks unik yang boleh digunakan. Pilihnya apabila melangkau memang hasil yang dikehendaki; ia tidak melaksanakan dasar kemas kini yang dibangunkan di sini. Dokumentasi INSERT PostgreSQL

MERGE diperkenalkan dalam PostgreSQL 15. Walaupun ia membenarkan syarat cantuman yang melibatkan mana-mana kunci, cantuman itu masih memerlukan dasar untuk baris sumber yang sepadan dengan dua artikel sasaran berbeza. Tindakannya masih tertakluk kepada kekangan biasa, dan tingkah laku serentaknya tidak boleh saling menggantikan dengan upsert bersasaran. Nota keluaran PostgreSQL 15, MERGE PostgreSQL

Untuk tugasan yang melibatkan mana-mana kunci, jadikan bilangan padanan sebagai keputusan yang jelas: sifar memasukkan, satu mengemas kini dan dua menghentikan proses dengan konflik yang berguna. Kekalkan kekangan unik dan pilih strategi keserentakan yang mengekalkan keputusan itu sehingga penulisan selesai. Pelaksanaan dengan kunci jadual memberikan titik permulaan yang konkrit apabila pensirian penulisan artikel boleh diterima.

Sumber