CTE pengubah data PostgreSQL menggabungkan operasi menjadi satu pernyataan dengan syot kilat yang dikongsi. Pernyataan berasingan dalam transaksi berjalan mengikut turutan dan boleh membaca perubahan terdahulu. Buat pilihan berdasarkan cara setiap operasi perlu menerima inputnya.

Bagi pembangun yang bekerja dalam Terminal pada Mac, tugas praktikalnya ialah menentukan sama ada aliran kerja artikel sintetik patut menggunakan satu pernyataan SQL atau beberapa pertanyaan transaksi. Contoh di bawah menunjukkan perbezaan keterlihatan, aliran kerja CTE yang sesuai dan transaksi node-postgres yang lengkap.

Apakah yang berubah apabila anda menggabungkan operasi ke dalam CTE?

CTE pengubah data mengandungi INSERT, UPDATE, DELETE atau, dalam PostgreSQL semasa, MERGE di dalam WITH. Komponennya berkongsi syot kilat; turutan pengubahsuaian fizikalnya tidak dapat diramalkan. Salurkan baris yang berubah melalui RETURNING apabila komponen lain memerlukannya. Menulis CTE dari atas ke bawah tidak menetapkan turutan pelaksanaan prosedural. PostgreSQL: Pertanyaan WITH

Pernyataan SQL berasingan di dalam BEGIN dan COMMIT mempunyai sempadan pernyataan. Pada tahap pengasingan Read Committed lalai PostgreSQL, setiap pernyataan mengambil syot kilat baharu dan boleh melihat perubahan terdahulu yang dibuat oleh transaksinya sendiri. Komit daripada transaksi lain juga boleh kelihatan antara pernyataan. PostgreSQL: Pengasingan Transaksi

Kedua-dua pendekatan boleh menyediakan operasi semua-atau-tiada. PostgreSQL melaksanakan setiap pernyataan di dalam transaksi tersirat apabila tiada blok transaksi eksplisit; transaksi eksplisit mengumpulkan beberapa pernyataan. CTE pengubah data juga boleh menjadi satu pernyataan dalam transaksi yang lebih besar. PostgreSQL: Transaksi

Keperluan Titik permulaan yang sesuai
Salurkan baris yang diubah terus ke jadual lain CTE dengan RETURNING
Baca jadual selepas pernyataan terdahulu mengubahnya Pernyataan berjujukan dalam transaksi
Ubah baris yang sama melalui dua peringkat yang disengajakan Pernyataan berjujukan, atau gabungkan pengiraan akhir ke dalam satu kemas kini
Periksa hasil perantaraan sebelum memilih pertanyaan seterusnya Pernyataan berjujukan dengan logik aplikasi
Kurangkan masa menunggu antara permintaan bebas Nilai pilihan pengangkutan secara berasingan daripada semantik SQL

Panduan membuat keputusan ini mengikut peraturan keterlihatan yang didokumenkan. Mulakan dengan menerangkan kebergantungan: adakah operasi seterusnya memerlukan hubungan yang dikembalikan, bacaan jadual yang baharu, atau keputusan yang dibuat oleh JavaScript? Soalan itu lebih berguna daripada mengira bilangan operasi yang boleh dimuatkan dalam WITH.

Bagaimanakah anda boleh menghasilkan semula perbezaan keterlihatan secara setempat?

Gunakan sambungan PostgreSQL 18 sedia ada dalam psql atau editor SQL pada Mac anda. Jalankan skrip lengkap berikut dalam satu sesi. Skrip ini hanya mencipta jadual sementara, menggunakan satu artikel sintetik dan berakhir dengan ROLLBACK. Nilai yang dijangka diperoleh daripada dokumentasi; contoh pangkalan data tidak dijalankan semasa penyediaan.

BEGIN ISOLATION LEVEL READ COMMITTED;

CREATE TEMP TABLE cte_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO cte_demo_articles (id, title, stage)
VALUES (1, 'Sample article', 'draft');

WITH edited AS (
    UPDATE cte_demo_articles
    SET stage = 'edited'
    WHERE id = 1
    RETURNING id, stage
)
SELECT a.stage AS table_stage,
       e.stage AS returned_stage
FROM cte_demo_articles AS a
JOIN edited AS e ON e.id = a.id;
-- Expected: table_stage = draft, returned_stage = edited

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: edited, because this is a subsequent statement.

UPDATE cte_demo_articles SET stage = 'draft' WHERE id = 1;

UPDATE cte_demo_articles SET stage = 'edited' WHERE id = 1;
UPDATE cte_demo_articles
SET stage = 'ready'
WHERE id = 1 AND stage = 'edited';

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: ready

ROLLBACK;

Dalam pertanyaan pertama, imbasan jadual membaca draft, manakala output CTE mengandungi edited. Pernyataan seterusnya membaca edited daripada jadual. Ini menggambarkan perbezaan yang didokumenkan antara membaca jadual sasaran dalam pernyataan pengubah data dengan membaca baris yang dikembalikan oleh CTE. PostgreSQL: Pertanyaan WITH

Dua kemas kini terakhir sengaja mengubah baris yang sama dalam pernyataan berasingan. Menggabungkannya sebagai CTE pengubah data sebaya tidak disokong: PostgreSQL tidak melaksanakan dua pengubahsuaian pada baris yang sama dalam satu pernyataan secara konsisten. Merujuk CTE terdahulu tidak menghapuskan sekatan itu. PostgreSQL: SELECT

Bilakah CTE pengubah data sesuai digunakan?

Memindahkan baris artikel terpilih ke arkib ialah tugas hubungan yang berguna: pemadaman mengenal pasti baris yang tepat dan hubungan yang dikembalikannya menyediakan data untuk sisipan. Dengan DELETE, RETURNING mendedahkan kandungan baris yang dipadam; dengan UPDATE, output lalainya mengandungi nilai yang dikemas kini. PostgreSQL: Mengembalikan Data daripada Baris yang Diubah Suai

Contoh kendiri ini menyediakan jadual dan inputnya sendiri. Jalankannya dalam satu sesi selepas contoh terdahulu selesai.

BEGIN;

CREATE TEMP TABLE move_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

CREATE TEMP TABLE move_demo_archive (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO move_demo_articles (id, title, stage)
VALUES
    (1, 'Sample article A', 'ready'),
    (2, 'Sample article B', 'draft');

WITH moved AS (
    DELETE FROM move_demo_articles
    WHERE stage = 'ready'
    RETURNING id, title, stage
)
INSERT INTO move_demo_archive (id, title, stage)
SELECT id, title, stage
FROM moved
RETURNING id, title, stage;
-- Expected: article 1, with stage ready.

SELECT id, stage FROM move_demo_articles ORDER BY id;
-- Expected: article 2, with stage draft.

ROLLBACK;

Sisipan menggunakan moved secara langsung. WITH pengubah data mesti dilampirkan pada pernyataan peringkat teratas. CTE pengubah data berjalan sehingga selesai sekali walaupun outputnya tidak digunakan; jika RETURNING ditinggalkan, hubungan hasil yang boleh digunakan akan tiada, tetapi pengubahsuaiannya tetap berlaku. PostgreSQL: Pertanyaan WITH

Sempadan transaksi juga penting apabila berlaku kegagalan: kegagalan sisipan menghalang pemindahan daripada selesai dengan jayanya. Dalam transaksi eksplisit, keluarkan ROLLBACK selepas ralat. Jangan anggap penerimaan baris perantaraan dengan jayanya sebagai bukti bahawa transaksi yang mengelilinginya telah dikomit. PostgreSQL: Transaksi

Bagaimanakah node-postgres patut menjalankan pernyataan yang saling bergantung?

Untuk aliran kerja yang memeriksa satu hasil sebelum menghantar pertanyaan seterusnya, gunakan panggilan await secara berjujukan pada satu klien. node-postgres memerlukan klien yang sama untuk setiap pernyataan dalam transaksi; menghantar langkah transaksi melalui panggilan pool.query() yang berasingan tidak memastikan kesinambungan sambungan itu. node-postgres: Transaksi

Prasyaratnya ialah Node.js, npm dan sambungan pangkalan data PostgreSQL 18 setempat yang sedia ada. Dalam direktori projek Node anda di Terminal, jalankan npm install pg. Simpan program lengkap di bawah sebagai article-transaction.mjs, kemudian jalankan node article-transaction.mjs. new Client() menggunakan pemboleh ubah persekitaran sambungan PostgreSQL yang biasa; konfigurasikannya untuk pangkalan data sedia ada jika nilai lalai tidak sepadan. node-postgres: Menyambung

import pg from 'pg';

const { Client } = pg;
const client = new Client();
await client.connect();

try {
  await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

  await client.query(`
    CREATE TEMP TABLE articles (
      id integer PRIMARY KEY,
      title text NOT NULL,
      stage text NOT NULL
    ) ON COMMIT DROP
  `);

  await client.query(
    'INSERT INTO articles VALUES ($1, $2, $3)',
    [1, 'Sample article', 'draft']
  );

  const first = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 RETURNING id, stage`,
    ['edited', 1]
  );
  if (first.rowCount !== 1) {
    throw new Error('Expected one article');
  }

  const second = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 AND stage = $3
     RETURNING id, stage`,
    ['ready', 1, 'edited']
  );
  if (second.rowCount !== 1) {
    throw new Error('Expected one edited article');
  }

  await client.query('COMMIT');
  console.log(second.rows);
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  await client.end();
}

Baris yang dicetak dijangka mempunyai id: 1 dan stage: 'ready'. Jadual sementara digugurkan semasa komit. Sintaks JavaScript telah diperiksa dengan Node.js; pelaksanaan pangkalan data tidak dilakukan.

Panggilan pertanyaan menghantar nilai secara berasingan menggunakan $1, $2 dan $3, selaras dengan API pertanyaan berparameter node-postgres. Setiap panggilan mengandungi satu pernyataan SQL. node-postgres: Pertanyaan

Menyemak rowCount menjadikan kemas kini yang tidak menjangkakan sifar baris sebagai kegagalan yang jelas sebelum komit. rowCount melaporkan bilangan baris yang diproses oleh arahan, manakala rows mengandungi rekod yang dikembalikan; kemas kini tanpa RETURNING boleh menjejaskan baris sambil mengembalikan tatasusunan rows yang kosong. node-postgres: pg.Result

Adakah pengumpulan pernyataan mengubah semantik SQLnya?

Kelompok menerangkan cara kerja dihantar. Ia tidak menukar beberapa pernyataan SQL menjadi satu CTE pengubah data.

Protokol simple-query PostgreSQL menerima pernyataan yang dipisahkan dengan koma bertitik. Tanpa kawalan transaksi eksplisit, pernyataan dalam satu mesej Query ringkas berjalan dalam transaksi tersirat. Ralat menghentikan pernyataan selebihnya. Mesej Parse dalam protokol extended hanya menerima satu pernyataan, maka kelompok yang dipisahkan dengan koma bertitik bukan pengganti umum bagi pertanyaan berparameter yang berasingan. PostgreSQL: Aliran Mesej

Kelompok eksplisit juga memerlukan pengendalian ralat di luar kelompok: jika ralat berlaku sebelum ROLLBACK yang berada di hujung, arahan rollback itu tidak akan dicapai. Hantar permintaan rollback berasingan untuk menamatkan transaksi yang gagal. PostgreSQL: Pernyataan Berbilang dalam Pertanyaan Ringkas

Dokumentasi node-postgres semasa turut menerangkan pemipaan pilihan. Ia menghantar permintaan tanpa menunggu setiap respons, sementara PostgreSQL memprosesnya mengikut turutan. Dokumentasi mengesyorkan panggilan await secara berjujukan apabila pertanyaan seterusnya dalam transaksi bergantung pada hasil terdahulu. Pemipaan mengubah masa permintaan; ia tidak mewujudkan syot kilat CTE yang dikongsi. node-postgres: Pemipaan

Had versi dan pengasingan manakah yang patut memandu pilihan?

Dokumentasi PostgreSQL semasa yang diperoleh untuk artikel ini mengenal pasti versi 18. PostgreSQL 16 membenarkan INSERT, UPDATE dan DELETE sebagai isi CTE pengubah data, tetapi tidak membenarkan MERGE di situ. Dokumentasi versi 17 menyertakan MERGE, dan nota keluarannya mendokumentasikan penambahan MERGE RETURNING. Bagi aliran kerja PostgreSQL 16, gunakan pernyataan pengubah data yang disokong atau pernyataan berasingan dan bukannya menyalin sintaks CTE yang lebih baharu. PostgreSQL 16: Pertanyaan WITH, PostgreSQL 17: Pertanyaan WITH, Nota Keluaran PostgreSQL 17

Repeatable Read tidak menjadikan pernyataan berjujukan berkelakuan seperti CTE sebaya. Tahap pengasingan ini mengekalkan syot kilat transaksi yang ditetapkan oleh pernyataan bukan kawalan transaksi yang pertama, tetapi pernyataan seterusnya masih melihat penulisan terdahulu oleh transaksi itu sendiri. Aplikasi yang menggunakan tahap pengasingan ini juga mesti bersedia untuk mencuba semula transaksi selepas kegagalan pensirian. PostgreSQL: Tahap Pengasingan Repeatable Read

MATERIALIZED dan NOT MATERIALIZED tidak menyediakan penyelesaian alternatif bagi keterlihatan CTE pengubah data. PostgreSQL mengabaikan NOT MATERIALIZED untuk CTE yang tidak bebas daripada kesan sampingan. Kawalan perancang pertanyaan itu tidak dapat menetapkan turutan penulisan pada baris yang sama. PostgreSQL: SELECT

Pilih CTE pengubah data apabila baris yang dikembalikan secara semula jadi menjadi input operasi seterusnya. Pilih pernyataan berjujukan apabila kerja seterusnya memerlukan perubahan jadual terdahulu atau keputusan aplikasi. Kekalkan kebergantungan itu apabila mengoptimumkan masa permintaan, dan gunakan transaksi eksplisit apabila beberapa pernyataan mesti diselesaikan bersama.

Sumber