في PostgreSQL 15 أو أحدث، استخدم قيدًا من نوع UNIQUE NULLS NOT DISTINCT مُعرّفًا بأنه DEFERRABLE، ثم أجّل التحقق منه داخل معاملة إعادة الترتيب. يتيح ذلك لصفوف المقالات تبادل مواضعها مع فرض ترتيب نهائي فريد، بما في ذلك الصفوف التي يكون والدها NULL. صياغة قيود PostgreSQL

هذا البرنامج التعليمي موجّه إلى مطوّر يعمل في Mac Terminal ويحتاج إلى إعادة ترتيب صفوف مقالات اصطناعية ضمن مجموعة أب واحدة. ويتضمن عرضًا توضيحيًا لـ SQL، وبرنامجًا كاملًا باستخدام node-postgres يتحقق من الترتيب المطلوب، وبديلًا لـ PostgreSQL 14. تغيير آباء المقالات خارج نطاق هذا المثال.

لماذا يعقّد الوالد القابل لأن تكون قيمته NULL تفرد المواضع؟

لنفترض أن لكل مقال id، وparent_id قابلًا لأن تكون قيمته NULL، وposition عددًا صحيحًا موجبًا. تشكّل المقالات التي لها الوالد نفسه مجموعة أشقاء مرتبة. في هذا النموذج، تنتمي جميع المقالات التي والدها NULL إلى مجموعة جذر واحدة.

لا يفرض UNIQUE (parent_id, position) عادي قاعدة مجموعة الجذر هذه. يتعامل PostgreSQL عادةً مع قيم NULL على أنها غير متساوية عند فرض التفرد، لذا يمكن لمقالين جذريين أن يشغلا الموضع 1 معًا. تؤدي إضافة NULLS NOT DISTINCT إلى اعتبار قيم الوالد NULL متساوية عند التحقق من المفتاح المركب. ويظل بإمكان كل والد غير NULL أن يكون له موضعه 1. PostgreSQL: القيود الفريدة

تنشأ عن إعادة الترتيب مشكلة توقيت منفصلة. إذا كان المقال 1 يشغل الموضع 1 وكان المقال 2 يشغل الموضع 2، فإن نقل المقال 2 إلى الموضع 1 يكرر مؤقتًا موضع المقال 1. يتحقق القيد الفريد غير القابل للتأجيل من كل صف مُضاف أو معدّل فورًا. أما القيد القابل للتأجيل فيمكنه الانتظار حتى نهاية العبارة، أو حتى انتهاء المعاملة عند تأجيله صراحةً. PostgreSQL: CREATE TABLE

عند تنفيذ إعادة الترتيب عبر عدة تحديثات، تكون الخاصيتان مهمتين: التفرد الذي يراعي قيم NULL يحدد المجموعات الصحيحة، وتأجيل التحقق يسمح بحدوث تعارضات مؤقتة بين التحديثات.

ما التعريف الذي ينبغي استخدامه وفق إصدار PostgreSQL لديك؟

قدّم PostgreSQL 15 الخيار NULLS NOT DISTINCT للقيود والفهارس الفريدة. تتطلب الأمثلة الأساسية أدناه إصدار الخادم 15 أو أحدث؛ أما PostgreSQL 14 فيحتاج إلى البديل الموضّح لاحقًا. تحقّق من إصدار الخادم المتصل به بدلًا من استنتاجه من إصدار عميل سطر الأوامر المثبّت. ملاحظات إصدار PostgreSQL 15

الحالة النهج المدعوم
PostgreSQL 15 أو أحدث، جدول جديد قيد مسمّى من نوع UNIQUE NULLS NOT DISTINCT مع DEFERRABLE INITIALLY IMMEDIATE
قيد فريد عادي موجود استبدل تعريفه؛ لا يمكن لـ ALTER CONSTRAINT تغيير قابلية تأجيل قيد فريد
فهرس فريد تعبيري أو جزئي موجود استبدل آلية الفرض؛ لا يمكن إرفاقه كقيد فريد عادي
PostgreSQL 14 استخدم أعمدة تجميع مُنشأة مع قيد فريد قابل للتأجيل

توضح حدود الكائنات الموجودة في ALTER TABLE أن تعديل سمات القيود بهذه الطريقة يقتصر على المفاتيح الخارجية، وأن الفهرس المرفق كقيد فريد لا يمكن أن يحتوي على تعبيرات أو أن يكون جزئيًا. كما أن CREATE UNIQUE INDEX مستقلًا لا يتضمن عبارة DEFERRABLE. 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. وبعد التحديث الثاني، تصبح مواضعهما فريدة مجددًا. يؤدي تغيير القيد إلى IMMEDIATE إلى التحقق من التغييرات المعلّقة في تلك اللحظة. إذا بقي تكرار، يفشل هذا الأمر؛ وإلا فيمكن اعتماد المعاملة. يؤثر التأجيل في توقيت التحقق. PostgreSQL: SET CONSTRAINTS

الترتيب النهائي المتوقع للجذور هو:

الموضع معرّف المقال العنوان
1 2 المقال ب
2 1 المقال أ
3 3 المقال ج

تحتفظ المقالات التابعة بمواضعها لأن أيًا من التحديثين لا يحددها. إذا واجهت معاملة تفاعلية خطأً، فأصدر ROLLBACK; قبل إعادة المحاولة. تجمع المعاملة العبارات المرتبطة حتى COMMIT أو ROLLBACK. PostgreSQL: BEGIN

كيف تعيد ترتيب مجموعة أشقاء كاملة باستخدام node-postgres؟

استخدم عميلًا واحدًا مستعارًا طوال المعاملة. يتطلب node-postgres ذلك صراحةً: استدعاءات pool.query() المنفصلة غير مناسبة للمعاملة لأنها قد تعمل على اتصالات مختلفة. node-postgres: المعاملات

يقبل البرنامج أدناه قائمة مرتبة كاملة بمعرّفات الأشقاء. وقاعدة القرار الإضافية بسيطة: يجب أن تكون القائمة المقدمة تبديلًا لمجموعة الأشقاء الحالية. يمنع رفض التكرارات والعناصر الناقصة والمعرّفات التابعة لمجموعات أخرى إعادة ترتيب جزئية تبدو ناجحة.

في مشروع Node.js محلي، ثبّت pg، واحفظ البرنامج الكامل التالي باسم reorder.cjs، ثم شغّله. يفترض البرنامج أن إعدادات الاتصال المحلية الموجودة متاحة عبر متغيرات بيئة PostgreSQL. يدعم node-postgres متغيرات بيئة الاتصال نفسها التي يدعمها 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 تصفية الوالد صالحة للقيم NULL وغير NULL. أما المقارنة العادية بعلامة المساواة مع مُعامل NULL فلا تحدد الجذور. لإعادة ترتيب المقالات التابعة في المثال بدلًا من ذلك، غيّر parentId إلى 1 وorderedIds إلى [5, 4]. PostgreSQL: دوال وعوامل المقارنة

تنقل العناصر النائبة بصيغة $1 القيم منفصلةً عن نص SQL. ويحدد تحويل النوع الصحيح الصريح النوع المقصود أيضًا عندما يكون مُعامل الوالد NULL. node-postgres: الاستعلامات

لماذا ينبغي الحصول على القفل قبل قراءة الأشقاء؟

لا تتضمن التجربة التوضيحية المؤقتة جلسات متنافسة. ويوضح قفل الجدول نهجًا واسعًا متعمدًا لجدول صغير مشترك: يتعارض SHARE ROW EXCLUSIVE مع عمليات الكتابة العادية على الجدول ومع نفسه، مع السماح بالقراءات العادية. ويُبقي القفل، طوال المعاملة، عضوية الأشقاء ثابتة أثناء التحقق والتحديثات. لكنه يمنع أيضًا الكتابة إلى مجموعات آباء غير ذات صلة، لذا قيّم هذه المفاضلة قبل استخدامه على جدول كثير الاستخدام. PostgreSQL: القفل الصريح

يحدد البرنامج صراحةً مستوى العزل READ COMMITTED. في هذا المستوى، يرى استعلام الأشقاء التغييرات المعتمدة حتى بداية الاستعلام، بما فيها التغييرات التي اعتُمدت قبل انتهاء انتظار قفل سابق. PostgreSQL: BEGIN، PostgreSQL: عزل المعاملات

جرى التحقق من JavaScript باستخدام تبديل صحيح، ومعرّفات مكررة وناقصة وتابعة لمجموعة أخرى. واجتازت هذه الفحوص الحتمية. روجعت أمثلة قاعدة البيانات وفق الوثائق، لكنها لم تُنفّذ على PostgreSQL في بيئة البحث.

ما البديل المدعوم في PostgreSQL 14؟

في PostgreSQL 14، أنشئ تعبير التجميع على هيئة أعمدة مولّدة، ثم طبّق قيدًا فريدًا قابلًا للتأجيل على هذه الأعمدة. تُحسب الأعمدة المولّدة المخزنة عند كتابة الصفوف، ولا يمكن تجاوز قيمها بقيم عادية. PostgreSQL 14: الأعمدة المولّدة

يستخدم مثال SQL المستقل هذا قيمتين مولّدتين لتمثيل مجموعة الوالد. وتميّز القيمة المنطقية بين الجذور ومعرّف والد فعلي قيمته 0، لذا لا يتطلب البديل COALESCE حجز معرّف.

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;

تكون قيمتا التجميع المولّدتان للجذر (true, 0)؛ وللوالد 0 تكونان (false, 0). ويحتفظ كل والد غير NULL بقيمته الصحيحة الخاصة. لذلك يعبّر القيد عن التجميع المقصود من دون الاعتماد على طريقة PostgreSQL 15 في التعامل مع NULL. يدعم PostgreSQL 14 القيود الفريدة القابلة للتأجيل على الأعمدة المسماة. PostgreSQL 14: CREATE TABLE

ما الذي ينبغي التحقق منه قبل تكييف جدول موجود؟

يمكن لفهرس تعبيري على COALESCE(parent_id, 0) فرض التفرد التعبيري، لكن لا يمكن ببساطة جعله قابلًا للتأجيل أو إرفاقه كقيد فريد عادي. وللفهرس الجزئي المخصص لصفوف الجذور القيد نفسه. وقد يظل أي منهما، إذا تُرك كما هو، مانعًا للتبديل بعد إضافة قيد قابل للتأجيل. PostgreSQL: الفهارس على التعبيرات، PostgreSQL: ALTER TABLE

قبل استبدال تعريف موجود، افحص أزواج الوالد والموضع المكررة، ولا سيما الجذور التي سمح التفرد العادي القابل لـ NULL بوجودها سابقًا. يتحقق القيد الفريد من الصفوف الموجودة عند إضافته؛ ولا يتوفر NOT VALID للقيود الفريدة. عالج المواضع الملتبسة عن قصد قبل محاولة التغيير. PostgreSQL: ALTER TABLE

افصل بين ما يضمنه القيد وقاعدة الترتيب التي يطبقها التطبيق. فالتفرد وحده يسمح بوجود فجوات مثل المواضع 1 و4 و9. ويعيّن برنامج القائمة الكاملة عمدًا مواضع متتالية بدءًا من 1. والتحقق من العضوية هو ما يضمن مشاركة كل شقيق.

لهذه المهمة، اختر القيد الذي يمثّل مجموعات الآباء القابلة لأن تكون قيمها NULL، وأجّل التحقق منه قبل أول تحديث يسبب تعارضًا، وتحقق من مجموعة الأشقاء المطلوبة كاملةً، ونفّذ كل التحديثات على عميل واحد قبل الاعتماد. يدعم هذا الجمع إعادة ترتيب الجذور والأبناء مع الإبقاء على موضع نهائي فريد لكل شقيق.

المصادر