تجمع عبارة CTE المعدِّلة للبيانات في PostgreSQL العمليات في عبارة واحدة ذات لقطة مشتركة. تُنفَّذ العبارات المنفصلة ضمن معاملة بالتتابع، ويمكنها قراءة الكتابات السابقة. اختر وفقًا للطريقة التي تحتاج بها كل عملية إلى تلقي مدخلاتها.

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

ما الذي يتغير عند جمع العمليات في CTE؟

تتضمن CTE المعدِّلة للبيانات عبارة INSERT أو UPDATE أو DELETE أو، في إصدارات PostgreSQL الحالية، MERGE داخل WITH. تتشارك مكوناتها لقطة واحدة؛ أما الترتيب الفعلي للتعديلات فغير محدد. مرّر الصفوف المتغيرة عبر RETURNING عندما يحتاج إليها مكوّن آخر. كتابة عبارات CTE من الأعلى إلى الأسفل لا تحدد ترتيبًا إجرائيًا للتنفيذ. PostgreSQL: استعلامات WITH

تتضمن عبارات SQL المنفصلة داخل BEGIN وCOMMIT حدودًا بين العبارات. عند مستوى العزل الافتراضي Read Committed في PostgreSQL، تأخذ كل عبارة لقطة جديدة، ويمكنها رؤية التغييرات السابقة التي أجرتها معاملتها نفسها. وقد تصبح عمليات الإيداع التي تجريها معاملات أخرى مرئية أيضًا بين العبارات. PostgreSQL: عزل المعاملات

يمكن لكلا النهجين توفير عملية تنجح بالكامل أو تفشل بالكامل. ينفّذ PostgreSQL العبارة الواحدة ضمن معاملة ضمنية عند عدم وجود كتلة معاملة صريحة؛ أما المعاملة الصريحة فتجمع عدة عبارات. ويمكن أيضًا أن تكون CTE المعدِّلة للبيانات عبارة واحدة ضمن معاملة أكبر. PostgreSQL: المعاملات

المتطلب نقطة بداية مناسبة
تمرير الصفوف المعدَّلة مباشرةً إلى جدول آخر CTE تستخدم RETURNING
قراءة جدول بعد تغييره بعبارة سابقة عبارات متتابعة ضمن معاملة
تغيير الصف نفسه على مرحلتين مقصودتين عبارات متتابعة، أو دمج الحساب النهائي في تحديث واحد
فحص نتيجة وسيطة قبل اختيار الاستعلام التالي عبارات متتابعة مع منطق في التطبيق
تقليل وقت الانتظار بين الطلبات المستقلة تقييم خيارات النقل بمعزل عن دلالات SQL

تستند أداة اتخاذ القرار هذه إلى قواعد إمكانية الاطّلاع الموثّقة. ابدأ بوصف التبعية: هل تحتاج العملية التالية إلى علاقة معادة، أم إلى قراءة حديثة للجدول، أم إلى قرار تتخذه JavaScript؟ هذا السؤال أنفع من عدّ عدد العمليات التي تتسع لها WITH.

كيف يمكنك إعادة إنتاج الفرق في إمكانية الاطّلاع محليًا؟

استخدم اتصال PostgreSQL 18 موجودًا في psql أو محرر SQL على جهاز Mac. شغّل النص البرمجي الكامل التالي في جلسة واحدة. ينشئ جدولًا مؤقتًا فقط، ويستخدم مقالة اصطناعية واحدة، وينتهي بـ ROLLBACK. القيم المتوقعة مستنتجة من الوثائق؛ ولم تُنفّذ أمثلة قاعدة البيانات أثناء الإعداد.

BEGIN ISOLATION LEVEL READ COMMITTED;

CREATE TEMP TABLE cte_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO cte_demo_articles (id, title, stage)
VALUES (1, 'Sample article', 'draft');

WITH edited AS (
    UPDATE cte_demo_articles
    SET stage = 'edited'
    WHERE id = 1
    RETURNING id, stage
)
SELECT a.stage AS table_stage,
       e.stage AS returned_stage
FROM cte_demo_articles AS a
JOIN edited AS e ON e.id = a.id;
-- Expected: table_stage = draft, returned_stage = edited

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: edited, because this is a subsequent statement.

UPDATE cte_demo_articles SET stage = 'draft' WHERE id = 1;

UPDATE cte_demo_articles SET stage = 'edited' WHERE id = 1;
UPDATE cte_demo_articles
SET stage = 'ready'
WHERE id = 1 AND stage = 'edited';

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: ready

ROLLBACK;

في الاستعلام الأول، تقرأ عملية فحص الجدول القيمة draft، بينما يحتوي خرج CTE على edited. تقرأ العبارة التالية القيمة edited من الجدول. يوضح ذلك الفرق الموثّق بين قراءة الجدول المستهدف داخل العبارة المعدِّلة وقراءة الصفوف التي أعادتها CTE. PostgreSQL: استعلامات WITH

يغيّر التحديثان الأخيران الصف نفسه عمدًا في عبارتين منفصلتين. لا تدعم PostgreSQL دمجهما في عبارات CTE شقيقة معدِّلة: فهي لا تضمن تنفيذ تعديلين على الصف نفسه ضمن عبارة واحدة. ولا يؤدي الرجوع إلى CTE سابقة إلى إزالة هذا القيد. PostgreSQL: SELECT

متى تكون CTE المعدِّلة للبيانات خيارًا مناسبًا؟

يُعد نقل صفوف المقالات المحددة إلى أرشيف مهمة علائقية مفيدة: إذ يحدد الحذف الصفوف المطلوبة بدقة، وتوفّر علاقته المعادة مدخلات الإدراج. باستخدام DELETE، تكشف RETURNING محتويات الصف المحذوف؛ وباستخدام UPDATE، يحتوي خرجها الافتراضي على القيم المحدَّثة. PostgreSQL: إرجاع البيانات من الصفوف المعدَّلة

يوفّر هذا المثال المستقل جداوله ومدخلاته. شغّله في جلسة واحدة بعد اكتمال المثال السابق.

BEGIN;

CREATE TEMP TABLE move_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

CREATE TEMP TABLE move_demo_archive (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO move_demo_articles (id, title, stage)
VALUES
    (1, 'Sample article A', 'ready'),
    (2, 'Sample article B', 'draft');

WITH moved AS (
    DELETE FROM move_demo_articles
    WHERE stage = 'ready'
    RETURNING id, title, stage
)
INSERT INTO move_demo_archive (id, title, stage)
SELECT id, title, stage
FROM moved
RETURNING id, title, stage;
-- Expected: article 1, with stage ready.

SELECT id, stage FROM move_demo_articles ORDER BY id;
-- Expected: article 2, with stage draft.

ROLLBACK;

يستهلك الإدراج moved مباشرةً. يجب إرفاق WITH المعدِّلة بالعبارة الأساسية. تُنفّذ CTE المعدِّلة حتى الاكتمال مرة واحدة، حتى إن لم يُستخدم خرجها؛ وحذف RETURNING يزيل علاقة النتائج القابلة للاستخدام، لا التعديل نفسه. PostgreSQL: استعلامات WITH

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

كيف ينبغي لـ node-postgres تنفيذ العبارات التابعة؟

لسير عمل يفحص نتيجة قبل إرسال الاستعلام التالي، استخدم استدعاءات await متتابعة على عميل واحد. يتطلب node-postgres استخدام العميل نفسه لكل عبارة ضمن المعاملة؛ ولا يضمن إرسال خطوات المعاملة عبر استدعاءات pool.query() منفصلة استمرارية الاتصال. node-postgres: المعاملات

المتطلبات المسبقة هي Node.js وnpm واتصال محلي موجود بقاعدة بيانات PostgreSQL 18. في مجلد مشروع Node ضمن Terminal، شغّل npm install pg. احفظ البرنامج الكامل أدناه باسم article-transaction.mjs، ثم شغّل node article-transaction.mjs. يستخدم new Client() متغيرات بيئة اتصال PostgreSQL المعتادة؛ اضبطها لقاعدة بياناتك الموجودة إذا لم تتطابق القيم الافتراضية معها. node-postgres: الاتصال

import pg from 'pg';

const { Client } = pg;
const client = new Client();
await client.connect();

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

  await client.query(`
    CREATE TEMP TABLE articles (
      id integer PRIMARY KEY,
      title text NOT NULL,
      stage text NOT NULL
    ) ON COMMIT DROP
  `);

  await client.query(
    'INSERT INTO articles VALUES ($1, $2, $3)',
    [1, 'Sample article', 'draft']
  );

  const first = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 RETURNING id, stage`,
    ['edited', 1]
  );
  if (first.rowCount !== 1) {
    throw new Error('Expected one article');
  }

  const second = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 AND stage = $3
     RETURNING id, stage`,
    ['ready', 1, 'edited']
  );
  if (second.rowCount !== 1) {
    throw new Error('Expected one edited article');
  }

  await client.query('COMMIT');
  console.log(second.rows);
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  await client.end();
}

يحتوي الصف المتوقع طباعته على id: 1 وstage: 'ready'. يُحذف الجدول المؤقت عند الإيداع. جرى التحقق من صياغة JavaScript باستخدام Node.js؛ ولم يُنفّذ البرنامج على قاعدة بيانات.

تمرّر استدعاءات الاستعلام القيم بشكل منفصل باستخدام $1 و$2 و$3، وفق واجهة الاستعلامات ذات المعاملات في node-postgres. يحتوي كل استدعاء على عبارة SQL واحدة. node-postgres: الاستعلامات

يجعل التحقق من rowCount التحديث غير المتوقع الذي لا يصيب أي صف فشلًا صريحًا قبل الإيداع. تعرض rowCount عدد الصفوف التي عالجها الأمر، بينما تحتوي rows على السجلات المعادة؛ وقد يؤثر تحديث لا يتضمن RETURNING في صفوف مع إرجاع مصفوفة rows فارغة. node-postgres: pg.Result

هل يؤدي تجميع العبارات إلى تغيير دلالات SQL؟

تصف الدفعة طريقة إرسال العمل. لكنها لا تحوّل عدة عبارات SQL إلى CTE واحدة معدِّلة للبيانات.

يقبل بروتوكول الاستعلام البسيط في PostgreSQL عبارات مفصولة بفواصل منقوطة. وبغياب تحكم صريح بالمعاملة، تُنفّذ العبارات الواردة في رسالة Query بسيطة ضمن معاملة ضمنية. يوقف الخطأ تنفيذ العبارات المتبقية. ولا تقبل رسالة Parse في البروتوكول الموسّع إلا عبارة واحدة، لذا فإن الدفعة المفصولة بفواصل منقوطة ليست بديلًا عامًا للاستعلامات ذات المعاملات المنفصلة. PostgreSQL: تدفق الرسائل

تحتاج الدفعة الصريحة أيضًا إلى معالجة الأخطاء خارجها: فإذا وقع خطأ قبل ROLLBACK الختامية، فلن يُنفّذ التراجع. أرسل طلب تراجع منفصلًا لإنهاء المعاملة الفاشلة. PostgreSQL: عبارات متعددة في استعلام بسيط

تصف وثائق node-postgres الحالية أيضًا تنفيذ المسار الأنبوبي الاختياري. فهو يرسل الطلبات دون انتظار كل استجابة، بينما يعالجها PostgreSQL بالترتيب. وتوصي الوثائق باستدعاءات await متتابعة عندما يعتمد الاستعلام التالي في المعاملة على نتيجة سابقة. يغيّر التنفيذ الأنبوبي توقيت الطلبات؛ ولا ينشئ لقطة CTE مشتركة. node-postgres: التنفيذ الأنبوبي

ما قيود الإصدارات والعزل التي ينبغي أن توجه الاختيار؟

تحدد وثائق PostgreSQL الحالية التي جُمعت لهذه المقالة الإصدار 18. يسمح PostgreSQL 16 باستخدام INSERT وUPDATE وDELETE كأجسام CTE معدِّلة، لكنه يستثني MERGE منها. تتضمن وثائق الإصدار 17 MERGE، وتوثّق ملاحظات إصداره إضافة MERGE RETURNING. في سير عمل PostgreSQL 16، استخدم العبارات المعدِّلة المدعومة أو العبارات المنفصلة بدلًا من نسخ صياغة CTE الأحدث. PostgreSQL 16: استعلامات WITH، PostgreSQL 17: استعلامات WITH، ملاحظات إصدار PostgreSQL 17

لا يجعل مستوى Repeatable Read العبارات المتتابعة تتصرف كأنها عبارات CTE شقيقة. فهو يحتفظ بلقطة المعاملة التي أُنشئت بواسطة أول عبارة ليست للتحكم في المعاملة، لكن العبارات اللاحقة تظل ترى كتابات المعاملة السابقة نفسها. ويجب أيضًا أن تكون التطبيقات التي تستخدم مستوى العزل هذا مستعدة لإعادة محاولة المعاملات بعد إخفاقات التسلسل. PostgreSQL: مستوى عزل Repeatable Read

لا توفّر MATERIALIZED وNOT MATERIALIZED حلًا بديلًا لمشكلة إمكانية الاطّلاع على CTE المعدِّلة. تتجاهل PostgreSQL الخيار NOT MATERIALIZED مع CTE غير الخالية من الآثار الجانبية. ولا تستطيع عناصر التحكم في المخطط هذه فرض تسلسل للكتابات على الصف نفسه. PostgreSQL: SELECT

اختر CTE معدِّلة عندما تشكّل الصفوف المعادة مدخلات العملية التالية بطبيعتها. واختر العبارات المتتابعة عندما يتطلب العمل اللاحق تغييرات سابقة على الجدول أو قرارًا من التطبيق. حافظ على هذه التبعية عند تحسين توقيت الطلبات، واستخدم معاملة صريحة كلما وجب إتمام عدة عبارات معًا.

المصادر