CTE pengubah data PostgreSQL menggabungkan operasi menjadi satu pernyataan dengan snapshot bersama. Pernyataan terpisah dalam transaksi berjalan secara berurutan dan dapat membaca perubahan yang ditulis sebelumnya. Pilih berdasarkan cara setiap operasi perlu menerima inputnya.

Bagi pengembang yang bekerja di Terminal pada Mac, tugas konkretnya adalah memutuskan apakah alur kerja artikel sintetis sebaiknya ditangani dalam satu pernyataan SQL atau beberapa kueri transaksional. Contoh di bawah menunjukkan perbedaan visibilitas, alur kerja CTE yang sesuai, dan transaksi node-postgres yang lengkap.

Apa yang berubah saat Anda menggabungkan operasi ke dalam CTE?

CTE pengubah data berisi INSERT, UPDATE, DELETE, atau, pada PostgreSQL saat ini, MERGE di dalam WITH. Komponennya menggunakan snapshot bersama; urutan modifikasi fisiknya tidak dapat diprediksi. Teruskan baris yang berubah melalui RETURNING jika komponen lain membutuhkannya. Menulis CTE dari atas ke bawah tidak menetapkan urutan eksekusi prosedural. PostgreSQL: Kueri WITH

Pernyataan SQL terpisah di dalam BEGIN dan COMMIT memiliki batas pernyataan. Pada tingkat isolasi default PostgreSQL, Read Committed, setiap pernyataan mengambil snapshot baru dan dapat melihat perubahan sebelumnya yang dibuat oleh transaksinya sendiri. Commit dari transaksi lain juga dapat terlihat di antara pernyataan. PostgreSQL: Isolasi Transaksi

Kedua pendekatan dapat menyediakan operasi yang berhasil seluruhnya atau gagal seluruhnya. PostgreSQL mengeksekusi satu pernyataan di dalam transaksi implisit jika tidak ada blok transaksi eksplisit; transaksi eksplisit mengelompokkan beberapa pernyataan. CTE pengubah data juga dapat menjadi satu pernyataan di dalam transaksi yang lebih besar. PostgreSQL: Transaksi

Kebutuhan Titik awal yang sesuai
Meneruskan baris yang dimodifikasi langsung ke tabel lain CTE dengan RETURNING
Membaca tabel setelah perubahan dibuat oleh pernyataan sebelumnya Pernyataan berurutan dalam transaksi
Mengubah baris yang sama melalui dua tahap yang disengaja Pernyataan berurutan, atau gabungkan perhitungan akhir ke dalam satu pembaruan
Memeriksa hasil antara sebelum memilih kueri berikutnya Pernyataan berurutan dengan logika aplikasi
Mengurangi waktu tunggu di antara permintaan yang independen Evaluasi opsi transportasi secara terpisah dari semantik SQL

Panduan keputusan ini mengikuti aturan visibilitas yang terdokumentasi. Mulailah dengan menjelaskan dependensinya: apakah operasi berikutnya memerlukan relasi yang dikembalikan, pembacaan tabel baru, atau keputusan yang dibuat oleh JavaScript? Pertanyaan itu lebih berguna daripada menghitung berapa banyak operasi yang bisa dimasukkan ke dalam WITH.

Bagaimana cara mereproduksi perbedaan visibilitas secara lokal?

Gunakan koneksi PostgreSQL 18 yang sudah ada di psql atau editor SQL di Mac Anda. Jalankan skrip lengkap berikut dalam satu sesi. Skrip ini hanya membuat tabel sementara, menggunakan satu artikel sintetis, dan diakhiri dengan ROLLBACK. Nilai yang diharapkan diturunkan dari dokumentasi; contoh basis data tidak dijalankan selama persiapan.

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;

Pada kueri pertama, pemindaian tabel membaca draft, sedangkan output CTE berisi edited. Pernyataan berikutnya membaca edited dari tabel. Ini menggambarkan perbedaan yang terdokumentasi antara membaca tabel target di dalam pernyataan yang memodifikasinya dan membaca baris yang dikembalikan CTE. PostgreSQL: Kueri WITH

Dua pembaruan terakhir sengaja mengubah baris yang sama dalam pernyataan terpisah. Menggabungkannya sebagai CTE pengubah yang sejajar tidak didukung: PostgreSQL tidak dapat diandalkan untuk melakukan dua modifikasi pada baris yang sama dalam satu pernyataan. Mereferensikan CTE sebelumnya tidak menghapus batasan tersebut. PostgreSQL: SELECT

Kapan CTE pengubah data cocok digunakan?

Memindahkan baris artikel yang dipilih ke arsip merupakan tugas relasional yang sesuai: penghapusan menentukan baris yang tepat, dan relasi yang dikembalikannya menjadi input untuk penyisipan. Dengan DELETE, RETURNING menampilkan isi baris yang dihapus; dengan UPDATE, output defaultnya berisi nilai yang diperbarui. PostgreSQL: Mengembalikan Data dari Baris yang Dimodifikasi

Contoh mandiri ini menyediakan tabel dan inputnya sendiri. Jalankan dalam satu sesi setelah contoh sebelumnya 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;

Penyisipan menggunakan moved secara langsung. CTE pengubah WITH harus terhubung ke pernyataan tingkat atas. CTE pengubah dijalankan sampai selesai meskipun outputnya tidak digunakan; menghilangkan RETURNING menghapus relasi hasil yang dapat digunakan, bukan modifikasinya. PostgreSQL: Kueri WITH

Batas transaksi juga penting jika terjadi kegagalan: kegagalan penyisipan mencegah pemindahan berhasil diselesaikan. Dalam transaksi eksplisit, jalankan ROLLBACK setelah terjadi kesalahan. Jangan menganggap penerimaan baris antara dengan sukses sebagai bukti bahwa transaksi yang menaunginya sudah di-commit. PostgreSQL: Transaksi

Bagaimana sebaiknya node-postgres menjalankan pernyataan yang saling bergantung?

Untuk alur kerja yang memeriksa satu hasil sebelum mengirim kueri berikutnya, gunakan pemanggilan await secara berurutan pada satu klien. node-postgres mengharuskan klien yang sama untuk setiap pernyataan dalam transaksi; mengirim langkah transaksi melalui pemanggilan pool.query() terpisah tidak menjamin kesinambungan koneksi tersebut. node-postgres: Transaksi

Prasyaratnya adalah Node.js, npm, dan koneksi basis data PostgreSQL 18 lokal yang sudah tersedia. Di direktori proyek Node Anda dalam Terminal, jalankan npm install pg. Simpan program lengkap di bawah ini sebagai article-transaction.mjs, lalu jalankan node article-transaction.mjs. new Client() menggunakan variabel lingkungan koneksi PostgreSQL yang umum; atur variabel tersebut untuk basis data yang sudah ada jika nilai defaultnya tidak sesuai. node-postgres: Menghubungkan

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 diharapkan tercetak memiliki id: 1 dan stage: 'ready'. Tabel sementara dihapus saat commit. Sintaks JavaScript diperiksa dengan Node.js; eksekusi basis data tidak dilakukan.

Pemanggilan kueri meneruskan nilai secara terpisah menggunakan $1, $2, dan $3, sesuai API kueri berparameter node-postgres. Setiap pemanggilan berisi satu pernyataan SQL. node-postgres: Kueri

Memeriksa rowCount menjadikan pembaruan yang tidak terduga menghasilkan nol baris sebagai kegagalan eksplisit sebelum commit. rowCount melaporkan jumlah baris yang diproses oleh perintah, sedangkan rows berisi rekaman yang dikembalikan; pembaruan tanpa RETURNING dapat memengaruhi baris tetapi mengembalikan array rows yang kosong. node-postgres: pg.Result

Apakah pengelompokan pernyataan mengubah semantik SQL-nya?

Pengelompokan menjelaskan cara pekerjaan dikirim. Itu tidak mengubah beberapa pernyataan SQL menjadi satu CTE pengubah data.

Protokol kueri sederhana PostgreSQL menerima pernyataan yang dipisahkan titik koma. Tanpa kontrol transaksi eksplisit, pernyataan dalam satu pesan Query sederhana dijalankan dalam transaksi implisit. Kesalahan menghentikan pernyataan yang tersisa. Pesan Parse pada protokol extended hanya menerima satu pernyataan, sehingga pengelompokan pernyataan yang dipisahkan titik koma bukan pengganti umum untuk kueri berparameter terpisah. PostgreSQL: Alur Pesan

Pengelompokan eksplisit juga memerlukan penanganan kesalahan di luar pengelompokan tersebut: jika terjadi kesalahan sebelum ROLLBACK yang berada di bagian akhir, rollback itu tidak akan dijalankan. Kirim permintaan rollback terpisah untuk mengakhiri transaksi yang gagal. PostgreSQL: Beberapa Pernyataan dalam Kueri Sederhana

Dokumentasi node-postgres saat ini juga menjelaskan pipelining opsional. Fitur ini mengirim permintaan tanpa menunggu setiap respons, sementara PostgreSQL memprosesnya secara berurutan. Dokumentasi menyarankan pemanggilan await secara berurutan ketika kueri berikutnya dalam transaksi bergantung pada hasil sebelumnya. Pipelining mengubah waktu pengiriman permintaan; fitur ini tidak membuat snapshot CTE bersama. node-postgres: Pipelining

Batas versi dan isolasi mana yang perlu memandu pilihan?

Dokumentasi PostgreSQL terkini yang digunakan untuk artikel ini menyebutkan versi 18. PostgreSQL 16 mengizinkan INSERT, UPDATE, dan DELETE sebagai isi CTE pengubah, tetapi tidak mengizinkan MERGE di sana. Dokumentasi versi 17 menyertakan MERGE, dan catatan rilisnya mendokumentasikan penambahan MERGE RETURNING. Untuk alur kerja PostgreSQL 16, gunakan pernyataan pengubah yang didukung atau pernyataan terpisah, alih-alih menyalin sintaks CTE yang lebih baru. PostgreSQL 16: Kueri WITH, PostgreSQL 17: Kueri WITH, Catatan Rilis PostgreSQL 17

Repeatable Read tidak membuat pernyataan berurutan berperilaku seperti CTE yang sejajar. Tingkat isolasi ini mempertahankan snapshot transaksi yang ditetapkan oleh pernyataan pertama yang bukan kontrol transaksi, tetapi pernyataan berikutnya tetap melihat penulisan terdahulu oleh transaksinya sendiri. Aplikasi yang menggunakan tingkat isolasi ini juga harus siap mengulangi transaksi setelah kegagalan serialisasi. PostgreSQL: Tingkat Isolasi Repeatable Read

MATERIALIZED dan NOT MATERIALIZED tidak memberikan solusi alternatif untuk visibilitas CTE pengubah. PostgreSQL mengabaikan NOT MATERIALIZED untuk CTE yang tidak bebas efek samping. Kontrol perencana tersebut tidak dapat menetapkan urutan penulisan ke baris yang sama. PostgreSQL: SELECT

Pilih CTE pengubah ketika baris yang dikembalikan secara alami menjadi input bagi operasi berikutnya. Pilih pernyataan berurutan ketika pekerjaan selanjutnya memerlukan perubahan tabel sebelumnya atau keputusan aplikasi. Pertahankan dependensi tersebut saat mengoptimalkan waktu permintaan, dan gunakan transaksi eksplisit setiap kali beberapa pernyataan harus selesai bersama-sama.

Sumber