अनुमत सीमा से अधिक गहरी श्रेणियों को अस्वीकार करने के लिए AFTER STATEMENT ट्रिगर में सीमित रिकर्सिव क्वेरी का उपयोग करें। सीरियलाइज़ेबल राइट ट्रांज़ैक्शन अनिवार्य करें और सीरियलाइज़ेशन विफलताओं पर दोबारा प्रयास करें, ताकि एक साथ हुए बदलाव मिलकर अमान्य पदानुक्रम न बना सकें।

यह ट्यूटोरियल उस डेवलपर के लिए है जो स्थानीय PostgreSQL डेटाबेस वाले Mac पर Terminal में काम कर रहा है। ठोस लक्ष्य श्रेणी के अधिकतम तीन स्तर लागू करना है, जिसमें मौजूदा सबट्री को स्थानांतरित करने की स्थिति भी शामिल है। सभी उदाहरणों में कृत्रिम लेख-श्रेणियों का उपयोग किया गया है।

गहराई की सीमा का सटीक अर्थ तय करें

यहाँ मूल श्रेणी की गहराई 1 है। उसकी संतान की गहराई 2 और पौत्र श्रेणी की गहराई 3 है। चौथा स्तर अमान्य है। यदि आपका एप्लिकेशन मूल श्रेणियों को शून्य गहराई मानता है, तो सीमा चुनने से पहले उस परंपरा के अनुसार रूपांतरण करें।

तालिका में एडजेसेंसी लिस्ट का उपयोग होता है: हर श्रेणी में एक वैकल्पिक parent_id होता है जो किसी दूसरी श्रेणी की ओर संकेत करता है। फ़ॉरेन की यह सुनिश्चित करती है कि संदर्भित पैरेंट मौजूद हो, लेकिन गहराई के नियम के लिए कई पंक्तियों की जाँच करनी पड़ती है। PostgreSQL स्पष्ट रूप से ऐसी CHECK कंस्ट्रेंट का समर्थन नहीं करता जो दूसरी पंक्तियों पर निर्भर हो और जिसे लगातार लागू रहने की गारंटी के रूप में बनाए रखा जाए। ट्रैवर्सल को CHECK द्वारा बुलाए गए फ़ंक्शन में रखने से यह सीमा दूर नहीं होती। कंस्ट्रेंट।

नीचे दिया गया कार्यान्वयन PostgreSQL 18 के लिए है, जो इस लेख के लिए प्राप्त मौजूदा स्थिर दस्तावेज़ों का संस्करण है। इंस्टॉल किया हुआ PostgreSQL 18 सर्वर और क्लाइंट, साथ ही JavaScript उदाहरण के लिए Node.js 24 और npm का उपयोग करें। PostgreSQL के इससे पुराने संस्करणों की अनुकूलता का दावा इस ट्यूटोरियल में शामिल नहीं है। node-postgres दस्तावेज़ में Node.js 24 को अनुकूल संस्करणों में सूचीबद्ध किया गया है। PostgreSQL 18 दस्तावेज़, node-postgres अनुकूलता।

पूरे पदानुक्रम की जाँच हर स्टेटमेंट के बाद क्यों करें?

सबट्री को स्थानांतरित करने पर केवल नए पैरेंट के पूर्वजों की जाँच करना पर्याप्त नहीं है। मान लें कि Articles → Guides → Editing पहले से तीन स्तरों पर है। Articles को किसी अन्य मूल श्रेणी के नीचे ले जाने से Editing चौथे स्तर पर पहुँच जाती है, भले ही स्थानांतरित की गई पंक्ति स्वयं केवल दूसरे स्तर पर पहुँचे।

प्रस्तावित स्थानांतरण का निर्णय नियम यह है: सबट्री के मूल की नई गहराई + सबट्री की ऊँचाई अधिकतम सीमा से ज़्यादा नहीं होनी चाहिए, जहाँ ऊँचाई सबट्री के मूल से उसके सबसे गहरे वंशज तक के एज की संख्या है। उदाहरण के लिए, मूल की नई गहराई 2 और सबट्री की ऊँचाई 2 होने पर सबसे गहरी गहराई 4 होगी।

अलग-अलग पूर्वज और वंशज गणनाएँ बनाए रखने के बजाय, यह कार्यान्वयन हर संशोधन करने वाले स्टेटमेंट के बाद हर श्रेणी की जाँच करता है। PostgreSQL के दस्तावेज़ों के अनुसार, AFTER ट्रिगर को ऑपरेशन द्वारा किए गए बदलाव दिखाई देते हैं और स्टेटमेंट ट्रिगर हर स्टेटमेंट में एक बार चलता है। इससे पंक्तियों के संसाधन क्रम पर निर्भर हुए बिना कई पंक्तियों वाले इंसर्ट और अपडेट भी समर्थित होते हैं। CREATE TRIGGER।

सीमित ट्रैवर्सल हर श्रेणी से शुरू होता है और पैरेंट लिंक का अनुसरण करता है। किसी भी ऊपर की ओर जाने वाली खोज में चार नोड मिलना साबित करता है कि तीन-स्तरीय नियम का उल्लंघन हुआ है। साइकिल भी चार नोड वाली खोज बनाती है, क्योंकि वह किसी मूल तक नहीं पहुँचती। इसलिए यही सीमा बिना असीमित रिकर्सिव खोज के अत्यधिक गहराई और साइकिल—दोनों को अस्वीकार करती है। यह PostgreSQL के दस्तावेज़ित रिकर्सिव CTE मूल्यांकन से निकला डिज़ाइन है। WITH क्वेरी।

तालिका और गहराई ट्रिगर बनाएँ

अपने Mac टेक्स्ट एडिटर में नीचे दिया गया पूरा सेटअप category-depth.sql नाम से सेव करें। यह एक नया स्कीमा बनाता है, इसलिए इसे ऐसे स्थानीय डेवलपमेंट डेटाबेस में केवल एक बार चलाएँ जहाँ वह स्कीमा पहले से मौजूद न हो। यह मौजूदा तालिकाओं को हटाता या बदलता नहीं है।

ट्रिगर फ़ंक्शन trigger लौटाता है, जैसा कि PL/pgSQL डेटा-चेंज ट्रिगर के लिए आवश्यक है। यह अमान्य पदानुक्रम और गलत ट्रांज़ैक्शन सेटअप में अंतर बताने के लिए स्पष्ट एरर कोड के साथ RAISE EXCEPTION का उपयोग करता है। ट्रिगर फ़ंक्शन, त्रुटियाँ और संदेश।

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;

फ़ंक्शन PostgreSQL के दस्तावेज़ित current_setting फ़ंक्शन के माध्यम से ट्रांज़ैक्शन की आइसोलेशन सेटिंग पढ़ता है। एकमात्र max_levels कॉन्स्टेंट रिकर्शन सीमा और अस्वीकार करने की सीमा—दोनों को नियंत्रित करता है। कॉन्फ़िगरेशन सेटिंग फ़ंक्शन।

Terminal खोलें, उस डायरेक्टरी पर जाएँ जिसमें फ़ाइल है और नीचे दिया गया कमांड चलाएँ। यह मानता है कि आपकी मौजूदा स्थानीय कनेक्शन सेटिंग localhost पर मौजूद postgres डेटाबेस से कनेक्ट कर सकती हैं। -X विकल्प व्यक्तिगत psql स्टार्टअप फ़ाइलों को छोड़ देता है; ON_ERROR_STOP किसी त्रुटि पर फ़ाइल का प्रसंस्करण रोक देता है। psql।

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

फ़ॉरेन की का डिफ़ॉल्ट डिलीट व्यवहार बना रहता है: जिन श्रेणियों की संतानें अभी मौजूद हैं, उन्हें हटाने पर उनकी सबट्री चुपचाप हटने के बजाय अनुरोध अस्वीकार होगा। पैरेंट इंडेक्स parent_id के आधार पर लुकअप में मदद करता है; इससे ट्रिगर की पूरी तालिका की जाँच समाप्त नहीं होती। PostgreSQL फ़ॉरेन की की संदर्भित ओर पर अपने आप इंडेक्स नहीं बनाता। फ़ॉरेन की।

इंसर्ट, सबट्री स्थानांतरण और साइकिल जाँचें

एप्लिकेशन कनेक्ट करने से पहले, सीड किए गए डेटा पर सीमा से जुड़े मामलों की जाँच करें। नीचे दिए गए हर ऑपरेशन में माना गया है कि मूल सेटअप में कोई बदलाव नहीं हुआ है।

ऑपरेशन अपेक्षित परिणाम कारण
श्रेणी 5 को श्रेणी 3 के नीचे डालें अस्वीकार नई श्रेणी स्तर 4 पर होगी।
श्रेणी 1 को श्रेणी 4 के नीचे ले जाएँ अस्वीकार श्रेणी 3 स्तर 4 पर पहुँच जाएगी।
श्रेणी 1 को श्रेणी 3 के नीचे ले जाएँ अस्वीकार स्थानांतरण से साइकिल बनेगी।
श्रेणी 3 को श्रेणी 4 के नीचे ले जाएँ स्वीकार श्रेणी 3 स्तर 2 की श्रेणी बन जाएगी।
डिफ़ॉल्ट Read Committed ट्रांज़ैक्शन में नाम अपडेट करें अस्वीकार इस कार्यान्वयन में हर श्रेणी लेखन के लिए सीरियलाइज़ेबल ट्रांज़ैक्शन आवश्यक है।

सबट्री की ठोस जाँच के लिए psql -X -h localhost -d postgres का उपयोग करके इंटरैक्टिव सेशन शुरू करें, फिर यह ब्लॉक चलाएँ। अपडेट से गहराई संबंधी अपवाद आना चाहिए। ROLLBACK विफल ट्रांज़ैक्शन को समाप्त करता है, जिससे मूल पदानुक्रम बरकरार रहता है।

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;

ट्रैवर्सल जानबूझकर अत्यधिक गहराई और साइकिल—दोनों को एक साथ रिपोर्ट करता है। इसका काम नियम लागू करना है; यह साइकिल का सटीक प्रवेश-बिंदु नहीं बताता। कोई डायग्नोस्टिक क्वेरी देखी गई IDs को अलग से ट्रैक कर सकती है, लेकिन इस सीमित स्वीकार-अस्वीकार निर्णय के लिए यह ज़रूरी नहीं है।

समवर्ती लेखनों पर भी यही नियम लागू करें

केवल रिकर्सिव ट्रिगर से समवर्तीता की समस्या हल नहीं होती। दो ट्रांज़ैक्शन में से हर एक पदानुक्रम की जाँच कर सकता है, जबकि उसे दूसरे ट्रांज़ैक्शन का अप्रतिबद्ध बदलाव दिखाई नहीं देता। PostgreSQL के कंसिस्टेंसी दस्तावेज़ इस समस्या का वर्णन करते हैं और कई पंक्तियों से जुड़े नियम लागू करने के एक समर्थित तरीके के रूप में सीरियलाइज़ेबल ट्रांज़ैक्शन सुझाते हैं। एप्लिकेशन स्तर पर डेटा कंसिस्टेंसी की जाँच।

इसलिए इस डिज़ाइन में हर श्रेणी लेखन के लिए सीरियलाइज़ेबल आइसोलेशन आवश्यक है। सफल सीरियलाइज़ेबल ट्रांज़ैक्शन ऐसे व्यवहार करते हैं मानो वे एक-एक करके चले हों। यदि समवर्ती गतिविधि उस क्रम को असंभव बनाती है, तो PostgreSQL किसी ट्रांज़ैक्शन को अस्वीकार कर सकता है; इसलिए अपडेट तब तक अस्थायी है, जब तक उसका ट्रांज़ैक्शन कमिट न हो जाए। ट्रांज़ैक्शन आइसोलेशन।

दो कृत्रिम मूल श्रेणियों, 10 और 11, का उपयोग करके आप इस समस्या को दिखा सकते हैं। दो इंटरैक्टिव psql सेशन में, किसी भी सेशन में कमिट जारी करने से पहले हर सेशन का अपडेट चलाएँ।

-- 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;

इसके बाद हर सेशन में COMMIT जारी करें। पैरेंट के दोनों विपरीत बदलावों को कमिट नहीं होना चाहिए: मिलकर वे एक साइकिल बनाते हैं। सीरियलाइज़ेशन विफलता किसी स्टेटमेंट के दौरान या कमिट करते समय आ सकती है। यदि एक बदलाव दूसरे सेशन द्वारा अपडेट करने से पहले कमिट हो जाता है, तो ट्रिगर अब दिखाई देने वाली साइकिल को सीधे अस्वीकार कर सकता है। वैलिडेशन क्वेरी को सीरियलाइज़ेबल निष्पादन के साथ जोड़ने के ये अपेक्षित परिणाम हैं। ट्रांज़ैक्शन आइसोलेशन।

node-postgres के साथ श्रेणी स्थानांतरित करें

प्रोजेक्ट डायरेक्टरी में, यदि आपके पास पहले से पैकेज फ़ाइल नहीं है तो npm init -y चलाएँ, फिर npm install pg से ड्राइवर इंस्टॉल करें। नीचे दिया गया पूरा प्रोग्राम move-category.mjs नाम से सेव करें। ड्राइवर का दस्तावेज़ित इंस्टॉलेशन पैकेज pg है। node-postgres।

यह प्रोग्राम psql कमांड वाले उसी स्थानीय डेटाबेस से कनेक्ट होता है। कनेक्शन के अन्य फ़ील्ड ड्राइवर के एनवायरनमेंट-आधारित डिफ़ॉल्ट का उपयोग करते हैं। node-postgres की आवश्यकता के अनुसार हर ट्रांज़ैक्शन स्टेटमेंट एक ही चेक-आउट किए गए क्लाइंट का उपयोग करता है; ट्रांज़ैक्शन स्टेटमेंट अलग-अलग pool.query कॉल में नहीं बाँटने चाहिए। कनेक्ट करना, ट्रांज़ैक्शन।

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();
}

इसे node move-category.mjs से चलाएँ। मूल सीड डेटा के साथ श्रेणी 3, श्रेणी 4 के नीचे चली जाती है और लौटाई गई पंक्ति में parent_id: 4 होता है। क्वेरी $1 और $2 के माध्यम से वैल्यू देती है, जो ड्राइवर के दस्तावेज़ित पैरामीटर इंटरफ़ेस के अनुरूप है। क्वेरी।

प्रोग्राम finally में क्लाइंट लौटाता है, रोलबैक विफल होने पर उसे छोड़ देता है और स्क्रिप्ट पूरी होने पर पूल बंद करता है। ये लाइफ़साइकल ऑपरेशन दस्तावेज़ित पूल API के अनुरूप हैं। pg.Pool।

SQLSTATE 40001 पर पूरा ट्रांज़ैक्शन दोबारा चलाकर प्रयास करें, जिसमें स्थानांतरण तय करने वाली कोई भी रीड शामिल हो। ट्रिगर की 23514 गहराई त्रुटि पर अपने आप दोबारा प्रयास न करें: प्रस्तावित बदलाव में सुधार की आवश्यकता है। पाँच प्रयासों की सीमा और थोड़ी-थोड़ी बढ़ती देरी उदाहरण के लिए चुने गए एप्लिकेशन विकल्प हैं; ये PostgreSQL की आवश्यकताएँ नहीं हैं और न ही अंततः सफल होने की गारंटी देते हैं। सीरियलाइज़ेशन विफलता का प्रबंधन।

उदाहरण में बदलाव करने से पहले उसकी सीमाएँ समझें

यह कार्यान्वयन हर स्टेटमेंट के बाद पदानुक्रम की जाँच करता है, जिसमें केवल नाम अपडेट करने वाले स्टेटमेंट भी शामिल हैं। कोई ट्रांज़ैक्शन एक स्टेटमेंट में अस्थायी रूप से अधिक गहराई नहीं बना सकता और अगले स्टेटमेंट में उसे ठीक नहीं कर सकता। हर स्टेटमेंट के बाद पदानुक्रम वैध रहे, इस तरह योजना बनाएँ। PostgreSQL डिफ़र्ड कंस्ट्रेंट ट्रिगर का समर्थन करता है, लेकिन उन्हें AFTER ROW ट्रिगर होना चाहिए; इस स्टेटमेंट ट्रिगर को केवल डिफ़रेबल चिह्नित नहीं किया जा सकता। CREATE TRIGGER।

इसका लाभ यह है कि वैलिडेशन का काम अनुमानित रहता है: तीन की सीमा के लिए, हर श्रेणी पर रिकर्सिव रिलेशन में अधिकतम चार वॉक पंक्तियाँ होती हैं, हालाँकि वास्तविक निष्पादन लागत क्वेरी प्लान और डेटाबेस की सामग्री पर निर्भर करती है। बड़ी तालिकाओं या बार-बार होने वाले छोटे लेखनों के लिए, पूरी तालिका की जाँच अपनाने से पहले माप करें। इसे केवल प्रभावित सबट्री की जाँच से बदलने के लिए वंशजों की वैलिडेशन, साइकिल अस्वीकृति और समवर्तीता संबंधी तर्क सुरक्षित रखना होगा।

मौजूदा तालिका में ट्रिगर इंस्टॉल करने से संग्रहीत पंक्तियों का ऑडिट अपने आप नहीं होता। सामान्य लेखन रोककर, फ़ंक्शन की रिकर्सिव क्वेरी को स्टैंडअलोन ऑडिट के रूप में चलाएँ; max_levels को 3 से बदलें और मेल खाने वाली हर origin_id चुनें। रिपोर्ट किए गए पाथ ठीक करें और लेखन पथ सक्षम करने से पहले पैरेंट संदर्भों की अलग से जाँच करें।

उपयोगी नियम सीधा है: हर श्रेणी को तीन नोड के भीतर किसी मूल तक पहुँचना चाहिए। सीमित वॉक सबट्री स्थानांतरण और साइकिल के दौरान इस शर्त की जाँच करता है; सीरियलाइज़ेबल ट्रांज़ैक्शन और पूरे ट्रांज़ैक्शन के दोबारा प्रयास इसे समवर्ती अपडेट में भी लागू रखते हैं। अपनी श्रेणी तालिका के लिए उदाहरण में बदलाव करते समय इन हिस्सों को साथ रखें।

स्रोत