Gunakan pertanyaan rekursif berbatas dalam pencetus AFTER STATEMENT untuk menolak kategori yang lebih dalam daripada had yang dibenarkan. Wajibkan transaksi tulis bersiri dan cuba semula kegagalan pensirian supaya perubahan serentak tidak dapat bersama-sama mewujudkan hierarki yang tidak sah.

Tutorial ini ditujukan kepada pembangun yang bekerja dalam Terminal pada Mac dengan pangkalan data PostgreSQL setempat. Tugas khususnya ialah menguatkuasakan maksimum tiga aras kategori, termasuk apabila subpokok sedia ada dipindahkan. Semua contoh menggunakan kategori artikel sintetik.

Takrifkan dengan tepat maksud had kedalaman

Di sini, kategori akar mempunyai kedalaman 1. Anak kategorinya mempunyai kedalaman 2, dan cucunya mempunyai kedalaman 3. Aras keempat tidak sah. Jika aplikasi anda mengira akar sebagai kedalaman sifar, sesuaikan konvensyen itu sebelum memilih had.

Jadual ini menggunakan senarai bersebelahan: setiap kategori menyimpan parent_id pilihan yang menunjuk kepada kategori lain. Kunci asing memastikan bahawa induk yang dirujuk memang wujud, tetapi peraturan kedalaman memerlukan pemeriksaan berbilang baris. PostgreSQL secara jelas tidak menyokong kekangan CHECK yang bergantung pada baris lain sebagai jaminan yang diselenggara secara berterusan. Meletakkan traversal dalam fungsi yang dipanggil oleh CHECK tidak mengatasi batasan itu. Kekangan.

Pelaksanaan di bawah menyasarkan PostgreSQL 18, versi dokumentasi stabil semasa yang diperoleh untuk artikel ini. Gunakan pelayan dan klien PostgreSQL 18 yang telah dipasang, serta Node.js 24 dan npm untuk contoh JavaScript. Versi PostgreSQL yang lebih awal tidak termasuk dalam tuntutan keserasian tutorial ini. Dokumentasi node-postgres menyenaraikan Node.js 24 sebagai salah satu versi yang serasi. Dokumentasi PostgreSQL 18, keserasian node-postgres.

Mengapa mengesahkan seluruh hierarki selepas sesuatu pernyataan?

Memeriksa hanya leluhur induk baharu tidak mencukupi untuk pemindahan subpokok. Andaikan Articles → Guides → Editing sudah pun merangkumi tiga aras. Memindahkan Articles ke bawah akar lain menjadikan Editing kategori aras keempat, walaupun baris yang dipindahkan itu sendiri hanya mencapai kedalaman dua.

Peraturan keputusan untuk pemindahan yang dicadangkan ialah: kedalaman baharu akar subpokok + ketinggian subpokok tidak boleh melebihi had maksimum, dengan ketinggian mengira sisi dari akar subpokok ke keturunan terdalamnya. Contohnya, kedalaman akar baharu 2 ditambah ketinggian subpokok 2 menghasilkan kedalaman terdalam 4.

Daripada mengekalkan pengiraan leluhur dan keturunan secara berasingan, pelaksanaan ini memeriksa setiap kategori selepas setiap pernyataan yang mengubah data. PostgreSQL mendokumenkan bahawa pencetus AFTER melihat perubahan yang dibuat oleh operasi tersebut, dan pencetus pernyataan berjalan sekali bagi setiap pernyataan. Ini turut menyokong sisipan dan kemas kini berbilang baris tanpa bergantung pada tertib pemprosesan baris. CREATE TRIGGER.

Traversal berbatas bermula pada setiap kategori dan mengikuti pautan induk. Menemui empat nod dalam mana-mana perjalanan ke atas membuktikan bahawa peraturan tiga aras dilanggar. Kitaran juga menghasilkan perjalanan empat nod kerana ia tidak pernah mencapai akar. Oleh itu, had yang sama menolak kedalaman berlebihan dan kitaran tanpa carian rekursif tanpa batas. Reka bentuk ini diterbitkan daripada penilaian CTE rekursif yang didokumenkan oleh PostgreSQL. Pertanyaan WITH.

Cipta jadual dan pencetus kedalaman

Dalam editor teks Mac anda, simpan persediaan lengkap berikut sebagai category-depth.sql. Ia mencipta skema baharu, jadi jalankannya sekali sahaja dalam pangkalan data pembangunan setempat yang belum mempunyai skema tersebut. Ia tidak menggugurkan atau menggantikan jadual sedia ada.

Fungsi pencetus memulangkan trigger, seperti yang diperlukan untuk pencetus perubahan data PL/pgSQL. Fungsi ini menggunakan RAISE EXCEPTION dengan kod ralat yang jelas untuk membezakan hierarki tidak sah daripada persediaan transaksi yang salah. Fungsi Pencetus, Ralat dan Mesej.

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 tetapan pengasingan transaksi melalui fungsi current_setting yang didokumenkan oleh PostgreSQL. Pemalar max_levels tunggal mengawal had rekursi dan ambang penolakan. Fungsi Tetapan Konfigurasi.

Buka Terminal, tukar ke direktori yang mengandungi fail tersebut, kemudian jalankan perintah berikut. Perintah ini mengandaikan tetapan sambungan setempat sedia ada anda boleh menyambung ke pangkalan data postgres pada localhost. Pilihan -X melangkau fail permulaan psql peribadi; ON_ERROR_STOP menghentikan pemprosesan fail jika berlaku ralat. psql.

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

Kunci asing mengekalkan tingkah laku pemadaman lalainya: pemadaman kategori yang masih mempunyai anak akan ditolak dan bukannya memadamkan subpokoknya secara senyap. Indeks induk menyokong carian mengikut parent_id; indeks itu tidak menghapuskan pengesahan seluruh jadual oleh pencetus. PostgreSQL tidak mencipta indeks secara automatik pada bahagian yang merujuk bagi kunci asing. Kunci Asing.

Uji sisipan, pemindahan subpokok dan kitaran

Sebelum menyambungkan aplikasi, sahkan kes sempadan menggunakan data yang telah diisi. Setiap operasi di bawah mengandaikan persediaan asal tidak berubah.

Operasi Hasil dijangka Sebab
Sisipkan kategori 5 di bawah kategori 3 Tolak Kategori baharu itu akan berada pada aras 4.
Pindahkan kategori 1 ke bawah kategori 4 Tolak Kategori 3 akan berpindah ke aras 4.
Pindahkan kategori 1 ke bawah kategori 3 Tolak Pemindahan itu mewujudkan kitaran.
Pindahkan kategori 3 ke bawah kategori 4 Terima Kategori 3 menjadi kategori aras 2.
Kemas kini nama dalam transaksi Read Committed lalai Tolak Pelaksanaan ini memerlukan transaksi bersiri bagi setiap penulisan kategori.

Untuk ujian subpokok yang khusus, mulakan sesi interaktif menggunakan psql -X -h localhost -d postgres, kemudian jalankan blok ini. Kemas kini itu sepatutnya mencetuskan pengecualian kedalaman. ROLLBACK menamatkan transaksi yang gagal itu, lalu mengekalkan hierarki asal.

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;

Traversal ini sengaja melaporkan kedalaman berlebihan dan kitaran secara bersama. Tugasnya ialah menguatkuasakan invarian; ia tidak mengenal pasti titik masuk tepat bagi kitaran. Pertanyaan diagnostik boleh menjejak ID yang telah dilawati secara berasingan, tetapi itu tidak diperlukan untuk keputusan terima atau tolak yang berbatas ini.

Pastikan penulisan serentak mematuhi peraturan yang sama

Pencetus rekursif sahaja tidak menyelesaikan isu keserentakan. Dua transaksi boleh mengesahkan hierarki masing-masing tanpa dapat melihat perubahan belum komit transaksi yang satu lagi. Dokumentasi konsistensi PostgreSQL menerangkan masalah ini dan mengesyorkan transaksi bersiri sebagai salah satu cara yang disokong untuk menguatkuasakan peraturan yang melibatkan berbilang baris. Pemeriksaan Konsistensi Data pada Peringkat Aplikasi.

Oleh itu, reka bentuk ini mewajibkan pengasingan bersiri bagi setiap penulisan kategori. Transaksi bersiri yang berjaya berkelakuan seolah-olah ia dijalankan satu demi satu. PostgreSQL boleh menolak transaksi apabila aktiviti serentak menghalang susunan sedemikian, maka kemas kini yang berjaya masih bersifat sementara sehingga transaksinya dikomit. Pengasingan Transaksi.

Anda boleh menunjukkan isu ini menggunakan dua akar sintetik, 10 dan 11. Dalam dua sesi psql interaktif, jalankan kemas kini bagi setiap sesi sebelum mengeluarkan arahan komit dalam mana-mana 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 dalam setiap sesi. Kedua-dua perubahan induk yang bertentangan itu tidak boleh sama-sama dikomit: bersama-sama ia membentuk kitaran. Kegagalan pensirian mungkin berlaku semasa pernyataan atau ketika komit. Jika satu perubahan dikomit sebelum sesi satu lagi menjalankan kemas kininya, pencetus sebaliknya boleh menolak kitaran yang kini kelihatan secara langsung. Ini ialah akibat yang dijangka apabila pertanyaan pengesahan digabungkan dengan pelaksanaan bersiri. Pengasingan Transaksi.

Jalankan pemindahan kategori dengan node-postgres

Dalam direktori projek, jalankan npm init -y jika anda belum mempunyai fail pakej, kemudian pasang pemacu dengan npm install pg. Simpan program lengkap di bawah sebagai move-category.mjs. Pakej pemasangan pemacu yang didokumenkan ialah pg. node-postgres.

Program ini menyambung ke pangkalan data setempat yang sama seperti perintah psql. Medan sambungan lain menggunakan nilai lalai berasaskan persekitaran bagi pemacu tersebut. Setiap pernyataan transaksi menggunakan satu klien yang telah diperoleh daripada pool, seperti yang diwajibkan oleh node-postgres; pernyataan transaksi tidak boleh diagihkan melalui panggilan pool.query yang berasingan. Menyambung, 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();
}

Jalankannya dengan node move-category.mjs. Dengan data awal asal, kategori 3 berpindah ke bawah kategori 4 dan baris yang dipulangkan mengandungi parent_id: 4. Pertanyaan itu membekalkan nilai melalui $1 dan $2, mengikut antara muka parameter yang didokumenkan oleh pemacu. Pertanyaan.

Program ini memulangkan klien dalam finally, membuangnya jika rollback gagal, dan menutup pool apabila skrip selesai. Operasi kitar hayat ini mengikut API pool yang didokumenkan. pg.Pool.

Cuba semula SQLSTATE 40001 dengan mengulangi seluruh transaksi, termasuk sebarang bacaan yang menentukan pemindahan tersebut. Jangan cuba semula secara automatik ralat kedalaman 23514 daripada pencetus: perubahan yang dicadangkan itu perlu diperbetulkan. Had lima percubaan dan kelewatan meningkat yang ringkas ialah pilihan aplikasi sebagai contoh, bukannya keperluan PostgreSQL atau jaminan bahawa proses akhirnya akan selesai. Pengendalian Kegagalan Pensirian.

Fahami batasan sebelum menyesuaikan contoh

Pelaksanaan ini memeriksa hierarki selepas setiap pernyataan, termasuk kemas kini nama sahaja. Transaksi tidak boleh mewujudkan kedalaman berlebihan buat sementara dalam satu pernyataan lalu membaikinya dalam pernyataan kemudian. Rancang setiap pernyataan supaya hierarki kekal sah. PostgreSQL menyokong pencetus kekangan tertunda, tetapi pencetus itu mestilah pencetus AFTER ROW; pencetus pernyataan ini tidak boleh sekadar ditandakan sebagai boleh ditangguhkan. CREATE TRIGGER.

Pertimbangannya ialah kerja pengesahan yang boleh dijangka: bagi had tiga, hubungan rekursif mengandungi paling banyak empat baris perjalanan bagi setiap kategori, walaupun kos pelaksanaan sebenar bergantung pada pelan pertanyaan dan kandungan pangkalan data. Jadual besar atau penulisan kecil yang kerap wajar diukur sebelum imbasan seluruh jadual digunakan. Menggantikannya dengan pemeriksaan subpokok yang terjejas memerlukan pengesahan keturunan, penolakan kitaran dan hujah keserentakan dikekalkan.

Bagi jadual sedia ada, pemasangan pencetus tidak mengaudit baris yang telah disimpan dengan sendirinya. Dengan penulis biasa dijeda, gunakan pertanyaan rekursif daripada fungsi itu sebagai audit kendiri, gantikan max_levels dengan 3 dan pilih setiap origin_id yang sepadan. Selesaikan mana-mana laluan yang dilaporkan dan sahkan rujukan induk secara berasingan sebelum mendayakan laluan penulisan.

Invarian yang berguna adalah mudah: setiap kategori mesti mencapai akar dalam tiga nod. Perjalanan berbatas memeriksa syarat itu merentas pemindahan subpokok dan kitaran; transaksi bersiri serta percubaan semula penuh mengekalkannya semasa kemas kini serentak. Pastikan semua bahagian itu kekal bersama apabila menyesuaikan contoh ini kepada jadual kategori anda.

Sumber