Gunakan kueri rekursif berbatas dalam pemicu AFTER STATEMENT untuk menolak kategori yang lebih dalam daripada batas yang diizinkan. Wajibkan transaksi penulisan serializable dan ulangi jika terjadi kegagalan serialisasi agar perubahan bersamaan tidak dapat bersama-sama menciptakan hierarki yang tidak valid.

Tutorial ini ditujukan bagi pengembang yang bekerja di Terminal pada Mac dengan basis data PostgreSQL lokal. Tugas konkretnya adalah menerapkan batas maksimum tiga tingkat kategori, termasuk saat subpohon yang sudah ada dipindahkan. Semua contoh menggunakan kategori artikel sintetis.

Tetapkan dengan jelas arti batas kedalaman

Di sini, kategori akar memiliki kedalaman 1. Anak kategori tersebut memiliki kedalaman 2, dan cucunya memiliki kedalaman 3. Tingkat keempat tidak valid. Jika aplikasi Anda menghitung akar sebagai kedalaman nol, sesuaikan konvensi itu sebelum menentukan batasnya.

Tabel ini menggunakan daftar ketetanggaan: setiap kategori menyimpan parent_id opsional yang menunjuk ke kategori lain. Kunci asing memastikan bahwa induk yang dirujuk memang ada, tetapi aturan kedalaman memerlukan pemeriksaan beberapa baris. PostgreSQL secara eksplisit tidak mendukung batasan CHECK yang bergantung pada baris lain sebagai jaminan yang terus dipelihara. Menempatkan penelusuran di dalam fungsi yang dipanggil oleh CHECK tidak menghilangkan keterbatasan tersebut. Batasan.

Implementasi di bawah ini ditujukan untuk PostgreSQL 18, versi stabil yang dokumentasi terkininya diambil untuk artikel ini. Gunakan server dan klien PostgreSQL 18 yang sudah terpasang, serta Node.js 24 dan npm untuk contoh JavaScript. Versi PostgreSQL yang lebih lama berada di luar klaim kompatibilitas tutorial ini. Dokumentasi node-postgres mencantumkan Node.js 24 sebagai salah satu versi yang kompatibel. Dokumentasi PostgreSQL 18, kompatibilitas node-postgres.

Mengapa memvalidasi seluruh hierarki setelah satu pernyataan?

Memeriksa hanya leluhur induk baru tidak cukup untuk memindahkan subpohon. Misalkan Artikel → Panduan → Penyuntingan sudah menempati tiga tingkat. Memindahkan Artikel ke bawah akar lain akan membuat Penyuntingan menjadi kategori tingkat keempat, meskipun baris yang dipindahkan itu sendiri hanya mencapai tingkat kedua.

Aturan untuk memutuskan apakah pemindahan yang diajukan valid adalah: kedalaman baru akar subpohon + tinggi subpohon tidak boleh melebihi batas maksimum, dengan tinggi dihitung berdasarkan jumlah sisi dari akar subpohon ke turunannya yang terdalam. Contohnya, kedalaman akar baru 2 ditambah tinggi subpohon 2 menghasilkan kedalaman terdalam 4.

Alih-alih memelihara perhitungan leluhur dan turunan secara terpisah, implementasi ini memeriksa setiap kategori setelah setiap pernyataan yang mengubah data. Dokumentasi PostgreSQL menyebutkan bahwa pemicu AFTER melihat perubahan yang dibuat oleh operasi tersebut, dan pemicu tingkat pernyataan dijalankan sekali untuk setiap pernyataan. Ini juga mendukung penyisipan dan pembaruan beberapa baris tanpa bergantung pada urutan pemrosesan baris. CREATE TRIGGER.

Penelusuran berbatas dimulai dari setiap kategori dan mengikuti tautan induk. Menemukan empat simpul dalam penelusuran ke atas mana pun membuktikan bahwa aturan tiga tingkat dilanggar. Siklus juga menghasilkan penelusuran empat simpul karena tidak pernah mencapai akar. Dengan demikian, batas yang sama menolak kedalaman berlebih dan siklus tanpa penelusuran rekursif yang tidak berbatas. Ini merupakan rancangan yang diturunkan dari evaluasi CTE rekursif yang didokumentasikan PostgreSQL. Kueri WITH.

Buat tabel dan pemicu kedalaman

Di editor teks Mac Anda, simpan penyiapan lengkap berikut sebagai category-depth.sql. Penyiapan ini membuat skema baru, jadi jalankan sekali di basis data pengembangan lokal yang belum memiliki skema tersebut. Penyiapan ini tidak menghapus atau mengganti tabel yang sudah ada.

Fungsi pemicu mengembalikan trigger, sebagaimana diwajibkan untuk pemicu perubahan data PL/pgSQL. Fungsi ini menggunakan RAISE EXCEPTION dengan kode kesalahan eksplisit untuk membedakan hierarki yang tidak valid dari penyiapan transaksi yang keliru. Fungsi Pemicu, Kesalahan dan Pesan.

BEGIN ISOLATION LEVEL SERIALIZABLE;

CREATE SCHEMA category_depth_demo;

CREATE TABLE category_depth_demo.category (
    id integer PRIMARY KEY,
    name text NOT NULL,
    parent_id integer REFERENCES category_depth_demo.category(id),
    CONSTRAINT category_not_own_parent
        CHECK (parent_id IS NULL OR parent_id <> id)
);

CREATE INDEX category_parent_idx
    ON category_depth_demo.category(parent_id);

CREATE FUNCTION category_depth_demo.enforce_depth()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    max_levels CONSTANT integer := 3;
    bad_id integer;
BEGIN
    IF current_setting('transaction_isolation') <> 'serializable' THEN
        RAISE EXCEPTION
            'Category writes require a SERIALIZABLE transaction'
            USING ERRCODE = '25000';
    END IF;

    WITH RECURSIVE walk(origin_id, parent_id, levels) AS (
        SELECT id, parent_id, 1
        FROM category_depth_demo.category

        UNION ALL

        SELECT w.origin_id, p.parent_id, w.levels + 1
        FROM walk AS w
        JOIN category_depth_demo.category AS p
          ON p.id = w.parent_id
        WHERE w.levels <= max_levels
    )
    SELECT origin_id INTO bad_id
    FROM walk
    WHERE levels = max_levels + 1
    LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION
            'Category % exceeds % levels or belongs to a cycle',
            bad_id, max_levels
            USING ERRCODE = '23514';
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER category_depth_guard
AFTER INSERT OR UPDATE OR DELETE
ON category_depth_demo.category
FOR EACH STATEMENT
EXECUTE FUNCTION category_depth_demo.enforce_depth();

INSERT INTO category_depth_demo.category(id, name, parent_id)
VALUES
    (1, 'Articles', NULL),
    (2, 'Guides', 1),
    (3, 'Editing', 2),
    (4, 'Reference', NULL),
    (10, 'Notes', NULL),
    (11, 'Examples', NULL);

COMMIT;

Fungsi ini membaca pengaturan isolasi transaksi melalui fungsi current_setting yang didokumentasikan PostgreSQL. Konstanta tunggal max_levels mengatur batas rekursi sekaligus ambang penolakan. Fungsi Pengaturan Konfigurasi.

Buka Terminal, pindah ke direktori yang berisi berkas tersebut, lalu jalankan perintah berikut. Perintah ini mengasumsikan pengaturan koneksi lokal Anda yang sudah ada dapat tersambung ke basis data postgres di localhost. Opsi -X melewati berkas awal psql pribadi; ON_ERROR_STOP menghentikan pemrosesan berkas jika terjadi kesalahan. psql.

psql -X -h localhost -d postgres -v ON_ERROR_STOP=1 -f category-depth.sql

Perilaku penghapusan bawaan kunci asing tetap berlaku: kategori yang masih memiliki anak tidak dapat dihapus, alih-alih subpohonnya ikut terhapus tanpa pemberitahuan. Indeks induk mendukung pencarian berdasarkan parent_id; indeks ini tidak menghilangkan validasi seluruh tabel oleh pemicu. PostgreSQL tidak otomatis membuat indeks pada sisi yang merujuk dari kunci asing. Kunci Asing.

Periksa penyisipan, pemindahan subpohon, dan siklus

Sebelum menyambungkan aplikasi, verifikasi kasus batas terhadap data awal. Setiap operasi di bawah ini mengasumsikan penyiapan awal belum diubah.

Operasi Hasil yang diharapkan Alasan
Sisipkan kategori 5 di bawah kategori 3 Tolak Kategori baru akan menempati tingkat 4.
Pindahkan kategori 1 ke bawah kategori 4 Tolak Kategori 3 akan berpindah ke tingkat 4.
Pindahkan kategori 1 ke bawah kategori 3 Tolak Pemindahan ini membuat siklus.
Pindahkan kategori 3 ke bawah kategori 4 Terima Kategori 3 menjadi kategori tingkat 2.
Perbarui nama dalam transaksi Read Committed bawaan Tolak Implementasi ini mewajibkan transaksi serializable untuk setiap penulisan kategori.

Untuk menguji pemindahan subpohon secara langsung, mulai sesi interaktif menggunakan psql -X -h localhost -d postgres, lalu jalankan blok ini. Pembaruan tersebut seharusnya memunculkan pengecualian kedalaman. ROLLBACK mengakhiri transaksi yang gagal sehingga hierarki awal tetap utuh.

BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE category_depth_demo.category
SET parent_id = 4
WHERE id = 1;

ROLLBACK;

SELECT id, name, parent_id
FROM category_depth_demo.category
ORDER BY id;

Penelusuran ini sengaja melaporkan kedalaman berlebih dan siklus secara bersamaan. Tugasnya adalah menegakkan invarian; penelusuran ini tidak menunjukkan titik masuk siklus secara tepat. Kueri diagnostik dapat melacak ID yang sudah dikunjungi secara terpisah, tetapi hal itu tidak diperlukan untuk keputusan terima atau tolak yang berbatas ini.

Pastikan penulisan bersamaan mematuhi aturan yang sama

Pemicu rekursif saja tidak menyelesaikan masalah konkurensi. Dua transaksi dapat memvalidasi hierarki masing-masing tanpa dapat melihat perubahan transaksi lain yang belum di-commit. Dokumentasi konsistensi PostgreSQL menjelaskan masalah ini dan merekomendasikan transaksi serializable sebagai salah satu cara yang didukung untuk menegakkan aturan yang melibatkan beberapa baris. Pemeriksaan Konsistensi Data di Tingkat Aplikasi.

Karena itu, rancangan ini mewajibkan isolasi serializable untuk setiap penulisan kategori. Transaksi serializable yang berhasil berperilaku seolah-olah dijalankan satu per satu. PostgreSQL dapat menolak transaksi jika aktivitas bersamaan menghalangi urutan tersebut, jadi pembaruan yang berhasil masih bersifat sementara sampai transaksinya di-commit. Isolasi Transaksi.

Anda dapat menunjukkan masalah ini menggunakan dua akar sintetis, 10 dan 11. Dalam dua sesi psql interaktif, jalankan pembaruan untuk masing-masing sesi sebelum mengeluarkan perintah commit di salah satu sesi.

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 11
WHERE id = 10;

-- Session B, in a separate psql session
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 10
WHERE id = 11;

Kemudian keluarkan COMMIT di setiap sesi. Kedua perubahan induk yang berlawanan tidak boleh sama-sama di-commit: jika digabungkan, keduanya membentuk siklus. Kegagalan serialisasi dapat terjadi saat pernyataan dijalankan atau ketika commit. Jika satu perubahan di-commit sebelum sesi lain menjalankan pembaruannya, pemicu dapat langsung menolak siklus yang kini terlihat. Inilah konsekuensi yang diharapkan dari menggabungkan kueri validasi dengan eksekusi serializable. Isolasi Transaksi.

Jalankan pemindahan kategori dengan node-postgres

Di direktori proyek, jalankan npm init -y jika belum memiliki berkas paket, lalu pasang driver dengan npm install pg. Simpan program lengkap berikut sebagai move-category.mjs. Paket instalasi yang didokumentasikan untuk driver ini adalah pg. node-postgres.

Program ini tersambung ke basis data lokal yang sama dengan perintah psql. Kolom koneksi lainnya menggunakan nilai bawaan driver yang bersumber dari lingkungan. Setiap pernyataan transaksi menggunakan satu klien yang sudah diambil dari pool, sebagaimana diwajibkan node-postgres; pernyataan transaksi tidak boleh disebarkan melalui beberapa pemanggilan pool.query yang terpisah. Menyambungkan, Transaksi.

import pg from 'pg';
import { setTimeout as delay } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({
  host: 'localhost',
  database: 'postgres',
  connectionTimeoutMillis: 5000
});

pool.on('error', (error) => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

async function moveCategory(id, parentId) {
  const maxAttempts = 5;

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    const client = await pool.connect();
    let discard = false;
    let retry = false;

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

      const result = await client.query(
        `UPDATE category_depth_demo.category
         SET parent_id = $1
         WHERE id = $2
         RETURNING id, name, parent_id`,
        [parentId, id]
      );

      if (result.rowCount !== 1) {
        throw new Error(`Category ${id} does not exist`);
      }

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

      retry = !discard && error.code === '40001'
        && attempt < maxAttempts;

      if (!retry) throw error;
    } finally {
      client.release(discard);
    }

    if (retry) await delay(25 * attempt);
  }
}

try {
  const moved = await moveCategory(3, 4);
  console.log(moved);
} catch (error) {
  console.error(error.code ?? 'APP_ERROR', error.message);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Jalankan dengan node move-category.mjs. Dengan data awal, kategori 3 dipindahkan ke bawah kategori 4 dan baris yang dikembalikan berisi parent_id: 4. Kueri memberikan nilai melalui $1 dan $2, mengikuti antarmuka parameter yang didokumentasikan driver. Kueri.

Program mengembalikan klien di finally, membuangnya jika rollback gagal, dan menutup pool saat skrip selesai. Operasi siklus hidup ini mengikuti API pool yang didokumentasikan. pg.Pool.

Ulangi seluruh transaksi, termasuk pembacaan apa pun yang menentukan pemindahan, jika SQLSTATE 40001 terjadi. Jangan otomatis mengulangi kesalahan kedalaman 23514 dari pemicu: perubahan yang diajukan perlu diperbaiki. Batas lima percobaan dan jeda singkat yang bertambah hanyalah pilihan ilustratif dalam aplikasi, bukan persyaratan PostgreSQL ataupun jaminan bahwa transaksi pada akhirnya akan berhasil. Penanganan Kegagalan Serialisasi.

Pahami batasannya sebelum menyesuaikan contoh

Implementasi ini memeriksa hierarki setelah setiap pernyataan, termasuk pembaruan nama saja. Transaksi tidak dapat sementara membuat kedalaman berlebih dalam satu pernyataan lalu memperbaikinya dalam pernyataan berikutnya. Rencanakan setiap pernyataan agar hierarki tetap valid. PostgreSQL mendukung pemicu batasan tertunda, tetapi pemicu tersebut harus berupa pemicu AFTER ROW; pemicu tingkat pernyataan ini tidak dapat begitu saja ditandai sebagai dapat ditunda. CREATE TRIGGER.

Konsekuensinya adalah beban validasi yang dapat diperkirakan: untuk batas tiga, relasi rekursif memuat paling banyak empat baris penelusuran per kategori, meskipun biaya eksekusi sebenarnya bergantung pada rencana kueri dan isi basis data. Tabel besar atau penulisan kecil yang sering perlu diukur sebelum menerapkan pemindaian seluruh tabel. Menggantinya dengan pemeriksaan subpohon yang terdampak mengharuskan validasi turunan, penolakan siklus, dan argumen konkurensi tetap dipertahankan.

Untuk tabel yang sudah ada, memasang pemicu tidak otomatis mengaudit baris yang tersimpan. Dengan menghentikan sementara penulis biasa, gunakan kueri rekursif dari fungsi sebagai audit mandiri, ganti max_levels dengan 3, lalu pilih setiap origin_id yang cocok. Selesaikan jalur yang dilaporkan dan verifikasi referensi induk secara terpisah sebelum mengaktifkan jalur penulisan.

Invarian yang perlu dijaga sederhana: setiap kategori harus mencapai akar dalam tiga simpul. Penelusuran berbatas memeriksa kondisi ini saat subpohon dipindahkan dan ketika ada siklus; transaksi serializable dan pengulangan transaksi secara utuh menjaga kondisi ini selama pembaruan bersamaan. Pertahankan semua bagian tersebut saat menyesuaikan contoh ini untuk tabel kategori Anda.

Sumber