PostgreSQL 15 या उसके बाद के संस्करण में, DEFERRABLE घोषित UNIQUE NULLS NOT DISTINCT constraint का उपयोग करें, फिर रीऑर्डर ट्रांज़ैक्शन के भीतर उसे defer करें। इससे आर्टिकल पंक्तियाँ अपनी पोज़िशन आपस में बदल सकती हैं और अंतिम क्रम विशिष्ट रहता है, उन पंक्तियों के लिए भी जिनका पैरेंट NULL है। PostgreSQL constraint सिंटैक्स

यह ट्यूटोरियल Mac Terminal में काम कर रहे ऐसे डेवलपर के लिए है जिसे एक पैरेंट समूह के भीतर सिंथेटिक आर्टिकल पंक्तियों का क्रम बदलना है। इसमें SQL प्रदर्शन, अनुरोधित क्रम की पुष्टि करने वाला पूरा node-postgres प्रोग्राम और PostgreSQL 14 का विकल्प शामिल है। आर्टिकल को दूसरे पैरेंट से जोड़ना इस उदाहरण के दायरे में नहीं है।

नल योग्य पैरेंट पोज़िशन की विशिष्टता को जटिल क्यों बनाता है?

मान लें कि हर आर्टिकल में एक id, एक नल योग्य parent_id और एक धनात्मक पूर्णांक position है। एक ही पैरेंट वाले आर्टिकल एक क्रमबद्ध सहोदर समूह बनाते हैं। इस मॉडल में, नल पैरेंट वाले सभी आर्टिकल एक ही रूट समूह में आते हैं।

सादा UNIQUE (parent_id, position) उस रूट-समूह नियम को लागू नहीं करता। PostgreSQL सामान्यतः विशिष्टता जाँचते समय नल को अलग-अलग मानता है, इसलिए दो रूट आर्टिकल दोनों 1 पोज़िशन पर हो सकते हैं। NULLS NOT DISTINCT जोड़ने से संयुक्त कुंजी की जाँच में उन नल पैरेंट मानों को समान माना जाता है। अलग-अलग गैर-नल पैरेंट की अपनी-अपनी 1 पोज़िशन हो सकती है। PostgreSQL: विशिष्टता प्रतिबंध

रीऑर्डर करने से समय-संबंधी एक अलग समस्या आती है। यदि आर्टिकल 1 पोज़िशन 1 पर है और आर्टिकल 2 पोज़िशन 2 पर है, तो आर्टिकल 2 को पोज़िशन 1 पर ले जाने से अस्थायी रूप से आर्टिकल 1 की पोज़िशन दोहर जाती है। गैर-स्थगनीय विशिष्टता constraint हर डाली या संशोधित की गई पंक्ति को तुरंत जाँचता है। स्थगनीय constraint जाँच को कथन के अंत तक रोक सकता है, या स्पष्ट रूप से defer किए जाने पर ट्रांज़ैक्शन के अंत तक। PostgreSQL: CREATE TABLE

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

अपने PostgreSQL संस्करण में कौन-सी परिभाषा इस्तेमाल करें?

PostgreSQL 15 में विशिष्टता constraints और indexes के लिए NULLS NOT DISTINCT आया। नीचे दिए गए मुख्य उदाहरणों के लिए सर्वर संस्करण 15 या बाद का होना चाहिए; PostgreSQL 14 के लिए आगे दिखाया गया विकल्प इस्तेमाल करें। जुड़े हुए सर्वर का संस्करण जाँचें, इंस्टॉल किए गए कमांड-लाइन क्लाइंट के संस्करण से उसका अनुमान न लगाएँ। PostgreSQL 15 रिलीज़ नोट्स

स्थिति समर्थित तरीका
PostgreSQL 15 या बाद का संस्करण, नई टेबल नामित UNIQUE NULLS NOT DISTINCT constraint जिसमें DEFERRABLE INITIALLY IMMEDIATE हो
मौजूदा सामान्य विशिष्टता constraint इसकी परिभाषा बदलें; ALTER CONSTRAINT विशिष्टता constraint की deferrability नहीं बदल सकता
मौजूदा expression या partial unique index enforcement डिज़ाइन बदलें; इसे सामान्य विशिष्टता constraint के रूप में जोड़ा नहीं जा सकता
PostgreSQL 14 जनरेटेड grouping कॉलम के साथ स्थगनीय विशिष्टता constraint इस्तेमाल करें

मौजूदा ऑब्जेक्ट पर ये सीमाएँ ALTER TABLE में दस्तावेज़ित हैं: इस तरह constraint attributes बदलना केवल foreign keys तक सीमित है, और विशिष्टता constraint के रूप में जोड़े गए index में expressions नहीं हो सकते और वह partial नहीं हो सकता। अकेले CREATE UNIQUE INDEX में DEFERRABLE clause भी नहीं होता। PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

SQL में दो रूट आर्टिकल की अदला-बदली कैसे करें?

इसके लिए पहले से मौजूद स्थानीय PostgreSQL 15 या बाद का डेटाबेस और काम करता हुआ psql कनेक्शन चाहिए। Mac Terminal खोलें और अपनी कॉन्फ़िगर की हुई कनेक्शन सेटिंग के साथ क्लाइंट शुरू करें। नीचे दिया गया कमांड स्टार्टअप कस्टमाइज़ेशन छोड़ता है और किसी त्रुटि पर स्क्रिप्ट का निष्पादन रोक देता है। PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

नीचे दिया गया पूरा SQL प्रदर्शन उस सत्र में पेस्ट करें। यह केवल सिंथेटिक आर्टिकल वाली अस्थायी टेबल बनाता है; सत्र समाप्त होने पर अस्थायी टेबल मिट जाती हैं। उस सत्र में टेबल का नाम पहले से इस्तेमाल नहीं होना चाहिए। PostgreSQL: अस्थायी टेबल

SHOW server_version;

CREATE TEMP TABLE article_order_demo (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    CONSTRAINT article_order_demo_parent_position_key
        UNIQUE NULLS NOT DISTINCT (parent_id, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo (id, parent_id, title, position)
VALUES
    (1, NULL, 'Article A', 1),
    (2, NULL, 'Article B', 2),
    (3, NULL, 'Article C', 3),
    (4, 1, 'Article A: Overview', 1),
    (5, 1, 'Article A: Examples', 2);

BEGIN;
SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED;

UPDATE article_order_demo
SET position = 1
WHERE id = 2 AND parent_id IS NULL;

UPDATE article_order_demo
SET position = 2
WHERE id = 1 AND parent_id IS NULL;

SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo
WHERE parent_id IS NULL
ORDER BY position;

पहले अपडेट के बाद, ट्रांज़ैक्शन में अस्थायी रूप से पोज़िशन 1 पर दो रूट होते हैं। दूसरे अपडेट के बाद उनकी पोज़िशन फिर विशिष्ट हो जाती हैं। constraint को वापस IMMEDIATE पर सेट करने से उस समय तक के लंबित बदलाव जाँचे जाते हैं। यदि कोई डुप्लिकेट बचा है, तो वह कमांड विफल हो जाता है; अन्यथा ट्रांज़ैक्शन commit किया जा सकता है। Defer करने से केवल यह बदलता है कि सत्यापन कब होता है। PostgreSQL: SET CONSTRAINTS

अपेक्षित अंतिम रूट क्रम यह है:

पोज़िशन आर्टिकल ID शीर्षक
1 2 आर्टिकल B
2 1 आर्टिकल A
3 3 आर्टिकल C

चाइल्ड आर्टिकल अपनी पोज़िशन पर रहते हैं, क्योंकि कोई भी अपडेट उन्हें चुनता नहीं है। यदि इंटरैक्टिव ट्रांज़ैक्शन में त्रुटि आती है, तो दोबारा प्रयास करने से पहले ROLLBACK; चलाएँ। ट्रांज़ैक्शन संबंधित कथनों को COMMIT या ROLLBACK तक एक साथ रखता है। PostgreSQL: BEGIN

node-postgres से पूरे सहोदर समूह का क्रम कैसे बदलें?

पूरे ट्रांज़ैक्शन के लिए एक ही checked-out client इस्तेमाल करें। node-postgres इसकी स्पष्ट माँग करता है: अलग-अलग pool.query() कॉल ट्रांज़ैक्शन के लिए उपयुक्त नहीं हैं, क्योंकि वे अलग-अलग कनेक्शन पर चल सकती हैं। node-postgres: ट्रांज़ैक्शन

नीचे दिया गया प्रोग्राम सहोदर ID की पूरी क्रमबद्ध सूची स्वीकार करता है। इसमें एक अतिरिक्त निर्णय नियम है: दी गई सूची वर्तमान सहोदर सेट का permutation होनी चाहिए। डुप्लिकेट, छूटे हुए ID और दूसरे समूह के ID अस्वीकार करने से ऐसा आभास नहीं होता कि आंशिक रीऑर्डर सफल रहा।

स्थानीय Node.js प्रोजेक्ट में pg इंस्टॉल करें, नीचे दिए गए पूरे प्रोग्राम को reorder.cjs नाम से सेव करें और चलाएँ। प्रोग्राम मानता है कि आपकी मौजूदा स्थानीय कनेक्शन सेटिंग PostgreSQL environment variables के ज़रिए उपलब्ध हैं। node-postgres वही कनेक्शन environment variables समर्थित करता है जो psql करता है। node-postgres: कनेक्ट करना

npm install pg
node reorder.cjs

const { Pool } = require('pg');

function validateOrder(ids, rows) {
  if (!Array.isArray(ids) || !ids.every(Number.isSafeInteger) ||
      new Set(ids).size !== ids.length) {
    throw new Error('Order must contain distinct integer IDs');
  }
  const existing = new Set(rows.map(row => row.id));
  if (rows.length !== ids.length || ids.some(id => !existing.has(id))) {
    throw new Error('Order must include every sibling exactly once');
  }
}

async function main() {
  const pool = new Pool();
  let client;
  try {
    client = await pool.connect();
    await client.query(`
      CREATE TEMP TABLE article_order_demo (
        id integer PRIMARY KEY,
        parent_id integer REFERENCES article_order_demo(id),
        title text NOT NULL,
        position integer NOT NULL CHECK (position > 0),
        CONSTRAINT article_order_demo_parent_position_key
          UNIQUE NULLS NOT DISTINCT (parent_id, position)
          DEFERRABLE INITIALLY IMMEDIATE
      )
    `);
    await client.query(`
      INSERT INTO article_order_demo (id, parent_id, title, position)
      VALUES (1, NULL, 'Article A', 1),
             (2, NULL, 'Article B', 2),
             (3, NULL, 'Article C', 3),
             (4, 1, 'Article A: Overview', 1),
             (5, 1, 'Article A: Examples', 2)
    `);

    const parentId = null;
    const orderedIds = [2, 1, 3];
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    try {
      await client.query(
        'LOCK TABLE article_order_demo IN SHARE ROW EXCLUSIVE MODE'
      );
      const siblings = await client.query(
        `SELECT id FROM article_order_demo
         WHERE parent_id IS NOT DISTINCT FROM $1::integer`,
        [parentId]
      );
      validateOrder(orderedIds, siblings.rows);
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED'
      );
      for (const [index, id] of orderedIds.entries()) {
        const result = await client.query(
          `UPDATE article_order_demo SET position = $1
           WHERE id = $2
             AND parent_id IS NOT DISTINCT FROM $3::integer`,
          [index + 1, id, parentId]
        );
        if (result.rowCount !== 1) {
          throw new Error('Expected exactly one updated article');
        }
      }
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE'
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }

    const result = await client.query(
      `SELECT id, title, position FROM article_order_demo
       WHERE parent_id IS NOT DISTINCT FROM $1::integer
       ORDER BY position`,
      [parentId]
    );
    console.table(result.rows);
  } finally {
    if (client) client.release();
    await pool.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

IS NOT DISTINCT FROM पैरेंट फ़िल्टर को नल और गैर-नल, दोनों मानों के लिए काम करने देता है। नल पैरामीटर के साथ सामान्य समानता रूट नहीं चुनती। इसके बजाय नमूना चाइल्ड का क्रम बदलने के लिए, parentId को 1 और orderedIds को [5, 4] में बदलें। PostgreSQL: तुलना फ़ंक्शन और ऑपरेटर

$1-शैली के placeholders मानों को SQL टेक्स्ट से अलग भेजते हैं। स्पष्ट integer cast से उस स्थिति में भी इच्छित प्रकार बताया जाता है जब पैरेंट पैरामीटर नल हो। node-postgres: क्वेरीज़

सहोदर पंक्तियाँ पढ़ने से पहले लॉक क्यों लें?

अस्थायी प्रदर्शन में कोई प्रतिस्पर्धी सत्र नहीं है। टेबल लॉक छोटे साझा टेबल के लिए जान-बूझकर व्यापक तरीका दिखाता है: SHARE ROW EXCLUSIVE सामान्य टेबल लेखन और अपने जैसे अन्य लॉक से टकराता है, जबकि सामान्य रीड की अनुमति देता है। ट्रांज़ैक्शन के दौरान इसे बनाए रखने से सत्यापन और अपडेट के बीच सहोदर समूह के सदस्य स्थिर रहते हैं। इससे असंबंधित पैरेंट समूहों में लिखने पर भी रोक लगती है, इसलिए व्यस्त टेबल पर इसका उपयोग करने से पहले इस लेन-देन का आकलन करें। PostgreSQL: स्पष्ट लॉकिंग

प्रोग्राम स्पष्ट रूप से READ COMMITTED चुनता है। इस isolation स्तर पर, सहोदर क्वेरी को अपने शुरू होने के समय तक किए गए committed बदलाव दिखते हैं, जिनमें पिछले लॉक के इंतज़ार के समाप्त होने से पहले commit हुए बदलाव भी शामिल हैं। PostgreSQL: BEGIN, PostgreSQL: ट्रांज़ैक्शन आइसोलेशन

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

PostgreSQL 14 में समर्थित विकल्प क्या है?

PostgreSQL 14 में grouping expression को generated columns के रूप में साकार करें, फिर उन कॉलमों पर स्थगनीय विशिष्टता constraint लागू करें। Stored generated columns पंक्तियाँ लिखे जाने पर गणना किए जाते हैं और सामान्य मानों से बदले नहीं जा सकते। PostgreSQL 14: Generated Columns

यह स्वतंत्र SQL उदाहरण पैरेंट समूह को दर्शाने के लिए दो generated मान इस्तेमाल करता है। Boolean, रूट को 0 की वास्तविक पैरेंट ID से अलग करता है, इसलिए COALESCE fallback के लिए कोई ID आरक्षित रखने की ज़रूरत नहीं है।

CREATE TEMP TABLE article_order_demo14 (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo14(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    parent_is_root boolean
        GENERATED ALWAYS AS (parent_id IS NULL) STORED,
    parent_group integer
        GENERATED ALWAYS AS (COALESCE(parent_id, 0)) STORED,
    CONSTRAINT article_order_demo14_parent_position_key
        UNIQUE (parent_is_root, parent_group, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo14 (id, parent_id, title, position)
VALUES (1, NULL, 'Article A', 1),
       (2, NULL, 'Article B', 2);

BEGIN;
SET CONSTRAINTS article_order_demo14_parent_position_key DEFERRED;
UPDATE article_order_demo14 SET position = 1 WHERE id = 2;
UPDATE article_order_demo14 SET position = 2 WHERE id = 1;
SET CONSTRAINTS article_order_demo14_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo14
WHERE parent_id IS NULL
ORDER BY position;

रूट के लिए generated grouping मान (true, 0) हैं; पैरेंट 0 के लिए वे (false, 0) हैं। हर गैर-नल पैरेंट अपना integer मान बनाए रखता है। इस तरह constraint PostgreSQL 15 के नल व्यवहार पर निर्भर हुए बिना इच्छित grouping व्यक्त करता है। PostgreSQL 14 नामित कॉलमों पर स्थगनीय विशिष्टता constraints समर्थित करता है। PostgreSQL 14: CREATE TABLE

मौजूदा टेबल को अनुकूलित करने से पहले क्या जाँचें?

COALESCE(parent_id, 0) पर expression index expression-आधारित विशिष्टता लागू कर सकता है, लेकिन उसे सीधे स्थगनीय नहीं बनाया जा सकता और न ही सामान्य विशिष्टता constraint के रूप में जोड़ा जा सकता है। रूट पंक्तियों के लिए partial index पर भी यही जोड़ने की सीमा लागू होती है। यदि इनमें से कोई तत्काल enforcement ऑब्जेक्ट बना रहता है, तो स्थगनीय constraint जोड़ने के बाद भी अदला-बदली रुक सकती है। PostgreSQL: expressions पर indexes, PostgreSQL: ALTER TABLE

मौजूदा परिभाषा बदलने से पहले, पैरेंट-पोज़िशन के डुप्लिकेट जोड़े जाँचें, खासकर वे रूट जिन्हें पहले सामान्य नल योग्य विशिष्टता के कारण अनुमति मिली थी। विशिष्टता constraint जोड़ने पर मौजूदा पंक्तियों का सत्यापन होता है; विशिष्टता constraints के लिए NOT VALID उपलब्ध नहीं है। बदलाव का प्रयास करने से पहले अस्पष्ट पोज़िशनों का सोच-समझकर समाधान करें। PostgreSQL: ALTER TABLE

constraint की गारंटी को एप्लिकेशन के क्रम-नियम से अलग रखें। केवल विशिष्टता से पोज़िशन 1, 4 और 9 जैसे अंतराल की अनुमति रहती है। पूरा-सूची वाला प्रोग्राम जान-बूझकर 1 से शुरू होने वाली लगातार पोज़िशन देता है। सदस्यता की जाँच सुनिश्चित करती है कि हर सहोदर शामिल हो।

इस काम के लिए, ऐसा constraint चुनें जो नल पैरेंट समूहों को दर्शाता हो, टकराने वाले पहले अपडेट से पहले उसे defer करें, अनुरोधित सहोदर सेट की पूरी जाँच करें, और commit करने से पहले सभी अपडेट एक ही client पर पूरे करें। यह संयोजन रूट और चाइल्ड, दोनों के क्रम बदलने देता है और हर सहोदर की अंतिम पोज़िशन विशिष्ट रखता है।

स्रोत