تمارين Kata في SQL بثلاثة مستويات مع الحلول

SELECT id, name, city, created_at::date AS joined
FROM customers
WHERE city = 'Abha' AND created_at >= '2025-06-01'
ORDER BY created_at DESC
LIMIT 5;

والمخرج سوف يكون كما يلي:

  id  |       name       | city |   joined
------+------------------+------+------------
  300 | Yousef Alghamdi  | Abha | 2025-08-20
 1511 | Ahmed Alshehri   | Abha | 2025-08-19
   50 | Omar Almutairi   | Abha | 2025-08-18
 1663 | Sara Alqahtani   | Abha | 2025-08-17
 1406 | Yousef Alqahtani | Abha | 2025-08-17
(5 rows)

في الاستعلام أعلاه لاحظ التالي:

• رتبنا ب created_at الكامل وليس ب joined، والسبب أن العميلين 1663 و1406 في نفس اليوم، والترتيب بالتاريخ وحده يجعل ترتيبهما عشوائياً بين مرة وأخرى.
• النص '2025-06-01' يتحول إلى بداية اليوم في المنطقة الزمنية للجلسة، وهي UTC في هذا ال Container، فإذا كان سيرفرك بتوقيت آخر فقد يختلف أول يوم بثلاث ساعات.
• بدون LIMIT سوف يعيد الاستعلام كل العملاء المطابقين، وفي التطبيق الحقيقي ترسل الصفحة التالية بشرط على آخر قيمة رأيتها وليس ب OFFSET.

للتفصيل: الترقيم بالمفتاح بدل OFFSET في دليل الجداول الضخمة، والتاريخ والوقت والمناطق الزمنية.

SELECT status, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY status
ORDER BY orders DESC;

والمخرج سوف يكون كما يلي:

  status   | orders |   revenue
-----------+--------+--------------
 paid      |  85723 | 135674081.00
 shipped   |  57062 |  90196414.00
 cancelled |  28681 |  45562331.00
 pending   |  28534 |  44754043.00
(4 rows)

أربعة صفوف لأن القيد CHECK يسمح بأربع قيم فقط، ومجموع العمود الأول 200000 وهو عدد كل الطلبات. ولاحظ أن الإيراد هنا يشمل الطلبات الملغاة والمعلقة، فإذا سألك المدير عن «الإيراد» فاسأله أولاً أي الحالات يقصد، وهذا سؤال أهم من الاستعلام نفسه. ويمكن أن تكتب ORDER BY 2 DESC برقم العمود، ولكن الاسم أوضح لمن يقرأ بعدك.

للتفصيل: الدوال التجميعية في توثيق PostgreSQL، والقيود Constraints في دليل تصميم قواعد البيانات.

SELECT customer_id, count(*) AS orders, sum(total) AS spent
FROM orders
WHERE status IN ('paid','shipped')
  AND created_at >= '2026-01-01' AND created_at < '2026-10-01'
GROUP BY customer_id
HAVING sum(total) > 95000
ORDER BY spent DESC;

والمخرج سوف يكون كما يلي:

 customer_id | orders |   spent
-------------+--------+-----------
        1226 |     43 | 105910.00
         557 |     50 | 100597.00
        1742 |     40 |  99206.00
         871 |     40 |  95063.00
(4 rows)

وقد يتساءل البعض: لماذا لا نكتب WHERE sum(total) > 95000؟ والإجابة أن WHERE تنفذ على كل صف قبل أن توجد المجموعات، فلا يوجد مجموع لتقارن به، وسوف يرفض PostgreSQL الاستعلام بالخطأ aggregate functions are not allowed in WHERE. ولاحظ أننا كتبنا نهاية الفترة < '2026-10-01' وليس <= '2026-09-30'، والسبب أن الثانية تعني منتصف ليل 30 سبتمبر فتسقط كل طلبات ذلك اليوم.

للتفصيل: GROUP BY وHAVING في توثيق PostgreSQL.

SELECT * FROM order_items WHERE order_id = 5000;

   id   | order_id | product_id | qty | unit_price
--------+----------+------------+-----+------------
  10068 |     5000 |         16 |   3 |      45.00
 399943 |     5000 |            |   1 |      99.00
(2 rows)

للطلب سطران، والثاني منتجه NULL لأن المنتج حذف من الكتالوج والمفتاح الأجنبي معرف ب ON DELETE SET NULL. والربط العادي INNER JOIN مع products يبقي فقط الصفوف التي تجد منتجاً مطابقاً، فاختفى السطر الثاني بقيمة 99، و135 + 99 = 234. والإصلاح أن تربط المنتجات ب LEFT JOIN، فيبقى السطر وتأتي أعمدة المنتج NULL:

SELECT o.id AS order_id, p.name AS product, i.qty, i.unit_price, i.qty * i.unit_price AS line_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id
WHERE o.id = 5000;

والمخرج سوف يكون كما يلي:

 order_id |   product    | qty | unit_price | line_total
----------+--------------+-----+------------+------------
     5000 | Water Bottle |   3 |      45.00 |     135.00
     5000 |              |   1 |      99.00 |      99.00
(2 rows)

وفي الفاتورة الحقيقية تكتب coalesce(p.name, '(discontinued product)') حتى لا يظهر للعميل سطر بلا اسم، والأفضل من ذلك أن تحفظ اسم المنتج في سطر الطلب وقت البيع كما حفظنا السعر، لأن الفاتورة وثيقة تاريخية لا يجب أن تتغير إذا تغير الكتالوج.

للتفصيل: المفتاح الأجنبي Foreign Key ومتى تكسر التطبيع عن قصد في دليل تصميم قواعد البيانات.

والمخرج سوف يكون كما يلي:

 count
-------
     0
(1 row)

 count
-------
   303
(1 row)

 all_rows | with_phone
----------+------------
     2003 |       1700
(1 row)

المقارنة phone = NULL نتيجتها NULL لكل صف، حتى للصفوف التي فيها NULL، وWHERE تقبل فقط ما نتيجته true، فخرج صفر صف دون أي رسالة خطأ. والطريقة الصحيحة هي IS NULL كما في توثيق المقارنة، فخرج 303. والاستعلام الثالث يؤكد الرقم بطريقة أخرى: 2003 صفاً منها 1700 فيها جوال، والفرق 303. وهذه الطريقة الثالثة مفيدة في التقارير لأنها تعطيك العددين في سطر واحد.

للتفصيل: القيود NOT NULL ومتى تسمح بالقيمة الفارغة في دليل تصميم قواعد البيانات.

التجميع بالعمود كما هو لا يجد شيئاً، لأن النسخة الثانية تبدأ بحرف كبير، فالحل أن تجمع بالقيمة بعد توحيد حالة الأحرف:

SELECT lower(email) AS email, count(*) AS copies, array_agg(id ORDER BY id) AS ids
FROM customers
GROUP BY lower(email)
HAVING count(*) > 1;

والمخرج سوف يكون كما يلي:

              email               | copies |     ids
----------------------------------+--------+-------------
 [email protected] |      2 | {1404,2003}
 [email protected]   |      2 | {230,2002}
 [email protected]      |      2 | {17,2001}
(3 rows)

وarray_agg يجمع أرقام العملاء في قائمة واحدة لتعرف أي الحسابين تدمج. والقيد الذي يمنع التكرار هو فهرس فريد على القيمة الموحدة CREATE UNIQUE INDEX ON customers (lower(email));، ولكنه سوف يفشل اليوم لأن التكرار موجود:

ERROR:  could not create unique index "customers_lower_idx"
DETAIL:  Key (lower(email))=([email protected]) is duplicated.

فعليك أن تدمج الحسابات الثلاثة أولاً ثم تنشئ الفهرس. ولا تقم بالاعتماد على فحص التكرار في كود التطبيق وحده، والسبب أن طلبين في نفس اللحظة يمران من الفحص معاً.

للتفصيل: القيود Constraints في دليل تصميم قواعد البيانات، وما المشكلة التي يحلها المعرف الفريد في مقال المعرفات.

العميل الذي ليس له طلبات يخرج من LEFT JOIN بصف واحد أعمدة o فيه كلها NULL، ثم يأتي شرط WHERE على o.created_at فتكون نتيجته NULL فيحذف الصف، فتحول LEFT JOIN عملياً إلى JOIN عادي. والمخرج:

 id  |       name       | sept_orders
-----+------------------+-------------
   5 | Lama Alghamdi    |           1
 189 | Yousef Almutairi |           1
 201 | Yousef Aldosari  |           1
 379 | Sara Alqahtani   |           1
 435 | Khalid Alshehri  |           1
(5 rows)

 rows_returned
---------------
           334
(1 row)

فاختفى عشرة عملاء دون أي تحذير. والإصلاح أن ينتقل شرط الجدول الأيمن إلى ON، فيصبح جزءاً من قرار الربط وليس فلتراً بعده، ويبقى شرط الجدول الأيسر في WHERE:

SELECT c.id, c.name, count(o.id) AS sept_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
                  AND o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01'
WHERE c.city = 'Abha'
GROUP BY c.id, c.name
ORDER BY sept_orders, c.id
LIMIT 5;

والمخرج سوف يكون كما يلي:

  id  |       name       | sept_orders
------+------------------+-------------
  461 | Yousef Almutairi |           0
  716 | Sara Alqahtani   |           0
 1959 | Majed Alghamdi   |           0
 1966 | Sara Alshehri    |           0
 1971 | Faisal Almutairi |           0
(5 rows)

 rows_returned
---------------
           344
(1 row)

ولاحظ أننا عددنا count(o.id) وليس count(*)، والسبب أن count(*) يعد الصف الذي أضافه LEFT JOIN بأعمدة NULL فيعطي العميل الذي لم يطلب الرقم 1 بدل 0. والسطر rows_returned من نفس الاستعلام داخل SELECT count(*) FROM (...) x دون LIMIT.

للتفصيل: أنواع الربط وشرط ON في توثيق PostgreSQL.

الاستعلام الفرعي يعيد كل قيم product_id ومنها NULL من أسطر المنتج المحذوف، والمقارنة 22 <> NULL نتيجتها NULL، فيصبح الشرط كله NULL لكل منتج ولا يمر أحد، وهذا موثق في توثيق NOT IN: إذا لم توجد قيمة مساوية وكانت قيمة واحدة على الأقل NULL فالنتيجة NULL وليست true. والحل الأفضل هو NOT EXISTS لأنه لا يتأثر ب NULL، أو أن تستبعد NULL صراحة:

SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM order_items);
SELECT name FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.product_id = p.id);
SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL);

والمخرج سوف يكون كما يلي:

 name
------
(0 rows)

    name
------------
 Prayer Mat
(1 row)

    name
------------
 Prayer Mat
(1 row)

واجعل NOT EXISTS عادتك، والسبب أن العمود الذي لا يحمل NULL اليوم قد يحمله غداً بعد تغيير بسيط في التصميم مثل الذي حدث هنا، فينكسر التقرير بصمت.

للتفصيل: الحذف المنطقي أم الحذف الفعلي في دليل تصميم قواعد البيانات.

WITH revenue AS (
    SELECT p.category, p.name, sum(i.qty * i.unit_price) AS revenue
    FROM order_items i
    JOIN orders   o ON o.id = i.order_id
    JOIN products p ON p.id = i.product_id
    WHERE o.status IN ('paid','shipped')
      AND o.created_at >= '2026-01-01' AND o.created_at < '2026-10-01'
    GROUP BY p.category, p.name
), ranked AS (
    SELECT *, row_number() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM revenue
)
SELECT category, rn, name, revenue
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

والمخرج سوف يكون كما يلي:

  category   | rn |                 name                  |   revenue
-------------+----+---------------------------------------+-------------
 Books       |  1 | Site Reliability Engineering          |  2505808.00
 Books       |  2 | Designing Data-Intensive Applications |  2343600.00
 Electronics |  1 | 27" Monitor                           | 15006684.00
 Electronics |  2 | Noise-Cancelling Headphones           | 11092761.00
 Grocery     |  1 | Honey 500g                            |  1476000.00
 Grocery     |  2 | Dates Box 1kg                         |  1095768.00
 Home        |  1 | Standing Desk                         | 25265100.00
 Home        |  2 | Office Chair                          | 17955350.00
(8 rows)

في الاستعلام أعلاه لاحظ التالي:

• دوال النوافذ تحسب بعد WHERE وGROUP BY، لذلك احتجنا طبقة ثانية ranked لنصفي بالرقم rn.
• row_number يعطي رقماً مختلفاً لكل صف حتى عند التعادل، فإذا كان لمنتجين نفس الإيراد فسوف يختار واحداً، وإذا أردت أن يظهر الاثنان فاستخدم rank() أو dense_rank().
• الربط مع products هنا JOIN عادي عن قصد، لأن أسطر المنتج المحذوف لا فئة لها ولا تدخل في الترتيب.

للتفصيل: دوال النوافذ وقائمتها في توثيق PostgreSQL، وعمليات التطبيق أم التحليل في دليل تصميم قواعد البيانات.

SELECT created_at::date AS day,
       sum(total) AS day_revenue,
       sum(sum(total)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
WHERE status IN ('paid','shipped')
  AND created_at >= '2026-09-01' AND created_at < '2026-09-08'
GROUP BY created_at::date
ORDER BY day;

والمخرج سوف يكون كما يلي:

    day     | day_revenue | running_total
------------+-------------+---------------
 2026-09-01 |   370340.00 |     370340.00
 2026-09-02 |   383439.00 |     753779.00
 2026-09-03 |   363911.00 |    1117690.00
 2026-09-04 |   334793.00 |    1452483.00
 2026-09-05 |   360094.00 |    1812577.00
 2026-09-06 |   363040.00 |    2175617.00
 2026-09-07 |   384768.00 |    2560385.00
(7 rows)

والتعبير sum(sum(total)) OVER (...) يبدو غريباً أول مرة: الداخلية دالة تجميع تعطي إيراد اليوم بعد GROUP BY، والخارجية دالة نافذة تجمع هذه القيم من أول صف حتى الصف الحالي بترتيب اليوم. وتحقق بيدك: 370340 + 383439 = 753779. ولاحظ أن اليوم يحسب بتوقيت الجلسة UTC، فالطلب الذي جاء الساعة الواحدة فجراً بتوقيت الرياض يحسب في اليوم السابق، وهذا يهم في تقرير يقرؤه فريق في السعودية.

للتفصيل: دوال النوافذ في توثيق PostgreSQL، والتاريخ والوقت والمناطق الزمنية.

الربط يعطي صفاً لكل سطر طلب، فالطلب الذي فيه ثلاثة أسطر يظهر ثلاث مرات، ويتكرر معه total ثلاث مرات، فالعدد يعد الأسطر وليس الطلبات، والإيراد مضروب في عدد الأسطر، بينما القطع وحدها صحيحة لأن qty من الجدول الأيمن نفسه. وهذه المشكلة تسمى انتشار الصفوف Fan-out. والإصلاح أن تجمع الأسطر إلى مستوى الطلب قبل الربط:

SELECT count(*) AS orders, sum(o.total) AS revenue, sum(u.units) AS units
FROM orders o
JOIN (SELECT order_id, sum(qty) AS units FROM order_items GROUP BY order_id) u ON u.order_id = o.id
WHERE o.status IN ('paid','shipped')
  AND o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01';

والمخرج قبل الإصلاح وبعده:

 orders |   revenue   | units
--------+-------------+-------
  13599 | 25402471.00 | 27348
(1 row)

 orders |   revenue   | units
--------+-------------+-------
   6817 | 10936061.00 | 27348
(1 row)

وتحققنا بطريقة ثالثة دون ربط أصلاً، بحساب القطع في استعلام فرعي مستقل، فأعطت نفس الأرقام 6817 و10936061.00 و27348. وقد يتساءل البعض: هل نستخدم count(DISTINCT o.id) ونكتفي؟ والإجابة أنها تصلح العدد فقط، أما sum(DISTINCT o.total) فخطأ أسوأ، لأن طلبين بنفس المبلغ يحسبان مرة واحدة.

للتفصيل: الاستعلامات الفرعية وWITH في توثيق PostgreSQL، والعلاقات بين الجداول في دليل تصميم قواعد البيانات.

الأمر لا يربط c.id = o.customer_id، فشرط c.city = 'Abha' يصبح «يوجد عميل واحد على الأقل من أبها»، وهذا صحيح دائماً، فيلغي كل الطلبات المعلقة لكل المدن. وشغلناه داخل معاملة ثم تراجعنا:

BEGIN;
UPDATE orders o
SET status = 'cancelled'
FROM customers c
WHERE c.city = 'Abha'
  AND o.status = 'pending'
  AND o.created_at < '2026-09-01';
ROLLBACK;

BEGIN
UPDATE 27182
ROLLBACK

والطريقة الآمنة أن تعد الصفوف بنفس الشروط أولاً، ثم تنفذ داخل معاملة، ثم تقارن الرقم الذي يطبعه UPDATE بالعدد:

BEGIN;
SELECT count(*) AS will_change
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Abha' AND o.status = 'pending' AND o.created_at < '2026-09-01';

UPDATE orders o
SET status = 'cancelled'
FROM customers c
WHERE c.id = o.customer_id
  AND c.city = 'Abha'
  AND o.status = 'pending'
  AND o.created_at < '2026-09-01';
-- the two numbers match: COMMIT; otherwise: ROLLBACK;
ROLLBACK;

والمخرج سوف يكون كما يلي:

BEGIN
 will_change
-------------
        4678
(1 row)

UPDATE 4678
ROLLBACK

الرقمان متطابقان، ففي الإنتاج تكتب COMMIT، وفي المختبر كتبنا ROLLBACK حتى تبقى البيانات كما هي لبقية التمارين. وقارن 4678 بالرقم 27182 الذي كاد يغير ستة أضعاف الطلبات المقصودة. ولاحظ أن المعاملة المفتوحة تقفل الصفوف التي غيرتها، فلا تتركها مفتوحة وأنت تفكر، وهذا موضوع التمرين 15. وتوثيق UPDATE يحذر من نفس الأمر: الصف المستهدف يجب أن يرتبط بصف واحد على الأكثر من الجدول الآخر.

للتفصيل: الشرط الذي تنساه في استعلام واحد في دليل تعدد المستأجرين، والتحديث والحذف على دفعات في دليل الجداول الضخمة.

بدون فهرس، يقرأ PostgreSQL الجدول كله بثلاث عمليات متوازية، ويرمي كل الصفوف إلا طلبات العميل، ثم يرتبها:

 Limit  (cost=5441.60..5442.76 rows=10 width=28) (actual time=33.407..38.604 rows=10.00 loops=1)
   Buffers: shared hit=3475
   ->  Gather Merge  (cost=5441.60..5453.36 rows=101 width=28) (actual time=33.404..38.598 rows=10.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=3475
         ->  Sort  (cost=4441.57..4441.68 rows=42 width=28) (actual time=28.166..28.169 rows=7.67 loops=3)
               Sort Key: created_at DESC
               Sort Method: top-N heapsort  Memory: 26kB
               Buffers: shared hit=3475
               Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
               ->  Parallel Seq Scan on orders  (cost=0.00..4440.67 rows=42 width=28) (actual time=11.577..27.996 rows=39.67 loops=3)
                     Filter: (customer_id = 1226)
                     Rows Removed by Filter: 66627
                     Buffers: shared hit=3399
 Planning Time: 0.603 ms
 Execution Time: 38.728 ms

والسطر Rows Removed by Filter: 66627 مع loops=3 يعني أنه قرأ قرابة 200 ألف صف ليجد 119، و3475 صفحة Buffers. ثم جربنا الفهرس على العمود وحده:

CREATE INDEX orders_customer_idx ON orders (customer_id);
EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total, created_at FROM orders WHERE customer_id = 1226 ORDER BY created_at DESC LIMIT 10;

 Limit  (cost=360.33..360.36 rows=10 width=28) (actual time=0.314..0.317 rows=10.00 loops=1)
   Buffers: shared hit=120 read=2
   ->  Sort  (cost=360.33..360.59 rows=102 width=28) (actual time=0.312..0.313 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=120 read=2
         ->  Bitmap Heap Scan on orders  (cost=5.09..358.13 rows=102 width=28) (actual time=0.067..0.274 rows=119.00 loops=1)
               Recheck Cond: (customer_id = 1226)
               Heap Blocks: exact=117
               Buffers: shared hit=120 read=2
               ->  Bitmap Index Scan on orders_customer_idx  (cost=0.00..5.06 rows=102 width=0) (actual time=0.047..0.047 rows=119.00 loops=1)
                     Index Cond: (customer_id = 1226)
                     Index Searches: 1
                     Buffers: shared hit=3 read=2
 Planning Time: 0.307 ms
 Execution Time: 0.339 ms

أسرع بمئة مرة، ولكنه ما زال يقرأ كل طلبات العميل الـ 119 من 117 صفحة مختلفة ثم يرتبها ليأخذ عشرة. ثم الفهرس المركب Composite Index:

DROP INDEX orders_customer_idx;
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total, created_at FROM orders WHERE customer_id = 1226 ORDER BY created_at DESC LIMIT 10;

 Limit  (cost=0.42..40.59 rows=10 width=28) (actual time=0.059..0.078 rows=10.00 loops=1)
   Buffers: shared hit=10 read=3
   ->  Index Scan using orders_customer_created_idx on orders  (cost=0.42..410.20 rows=102 width=28) (actual time=0.058..0.075 rows=10.00 loops=1)
         Index Cond: (customer_id = 1226)
         Index Searches: 1
         Buffers: shared hit=10 read=3
 Planning Time: 0.275 ms
 Execution Time: 0.097 ms

في الخطط أعلاه لاحظ التالي:

• اختفت عقدة Sort تماماً، لأن الفهرس يخزن طلبات كل عميل مرتبة بالتاريخ، فيقرأ أول عشرة ويتوقف، وrows=10.00 في Index Scan تؤكد أنه لم يقرأ أكثر مما يحتاج.
• الصفحات نزلت من 3475 إلى 122 إلى 13، والوقت من 38.7 إلى 0.34 إلى 0.097 ملي ثانية. والأرقام هنا للمقارنة على نفس الجهاز ونفس البيانات، وتختلف عندك في القيمة وليس في الاتجاه.
• ترتيب الأعمدة مهم: عمود المساواة أولاً ثم عمود الترتيب، ولو كان الفهرس (created_at, customer_id) لما استفاد منه هذا الاستعلام بنفس الطريقة.
• الفهرس المركب يخدم أيضاً أي استعلام بشرط على customer_id وحده، لذلك لا تحتاج الفهرس الأول بجانبه، وحجمه في المختبر 6184 kB لجدول حجمه 27 MB.

للتفصيل: ال EXPLAIN (ANALYZE, BUFFERS) وفهرس B-tree مركب: ترتيب الأعمدة مهم في دليل الجداول الضخمة، والفهارس وORDER BY في توثيق PostgreSQL.

هذه مشكلة N+1: استعلام واحد يجلب 50 طلباً، ثم حلقة في الكود تجلب العميل لكل طلب باستعلام منفصل، فيصبح المجموع 51 ذهاباً وعودة Round Trip إلى قاعدة البيانات. وكل استعلام عميل يأخذ قرابة 0.01 ملي ثانية داخل PostgreSQL فلا يظهر في أي سجل بطء، ولكن كل ذهاب وعودة يدفع زمن الشبكة وزمن المكتبة في التطبيق، فإذا كان الذهاب والعودة يأخذ ملي ثانية واحدة بين سيرفر التطبيق وسيرفر القاعدة فقد أضفت 50 ملي ثانية إلى الصفحة، ومع 200 طلب في الصفحة تصبح الثواني. وأنتجنا القائمة في المختبر بمحاكاة الحلقة داخل psql:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS reset;
\o /dev/null
SELECT id, customer_id, total FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50;
SELECT format('SELECT name, city FROM customers WHERE id = %s', customer_id)
FROM (SELECT customer_id FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50) x \gexec
\o
SELECT calls, rows, round(total_exec_time::numeric, 2) AS total_ms, left(query, 70) AS query
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%' AND query NOT ILIKE 'SELECT format%'
ORDER BY calls DESC
LIMIT 5;

والأمر \gexec ينفذ كل صف من النتيجة كاستعلام مستقل، تماماً كما تفعل الحلقة في الكود. ولاحظ أن pg_stat_statements وحد الخمسين استعلاماً في سطر واحد بالرمز $1، لذلك تبحث عن العمود calls الكبير وليس عن total_ms. والإصلاح استعلام واحد بالربط:

SELECT pg_stat_statements_reset() IS NOT NULL AS reset;
\o /dev/null
SELECT o.id, o.total, c.name, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;
\o
SELECT calls, rows, round(total_exec_time::numeric, 2) AS total_ms, left(query, 70) AS query
FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' ORDER BY calls DESC;

والمخرج سوف يكون كما يلي:

 calls | rows | total_ms |                query
-------+------+----------+--------------------------------------
     1 |   50 |    94.31 | SELECT o.id, o.total, c.name, c.city+
       |      |          | FROM orders o                       +
       |      |          | JOIN customers c ON
(1 row)

ذهاب وعودة مرة واحدة بدل 51. ولاحظ أن الاستعلام نفسه يأخذ 70 إلى 94 ملي ثانية لأنه يقرأ كل الطلبات المدفوعة ليرتبها، وهذه مشكلة ثانية منفصلة حلها فهرس على (status, created_at) كما في التمرين 13، فلا تخلط بين المشكلتين، فالأولى عدد مرات الذهاب والعودة والثانية كلفة الاستعلام الواحد. وفي ORM تحل الأولى بالتحميل المسبق Eager Loading مثل Include في Entity Framework أو select_related في Django.

للتفصيل: صفحة N+1 مقابل JOIN في دليل الطلب البطيء، ومشكلة N+1 في دليل التصميم، وال pg_stat_statements في دليل الجداول الضخمة.

الجلسة 358 من لوحة الإدارة تنتظر قفلاً من نوع transactionid، وpg_blocking_pids يقول إن من يمنعها هو 351. والجلسة 351 عامل الدفع checkout-worker نفذ UPDATE على نفس المنتج داخل معاملة، ثم توقف عن إرسال أي شيء، فحالتها idle in transaction وهي تنتظر الكلاينت ClientRead، والقفل على الصف يبقى حتى تنتهي المعاملة، والسبب الأرجح كود يفتح معاملة ثم يستدعي خدمة خارجية بطيئة أو ينتظر شيئاً قبل COMMIT. والسجل كتب نفس القصة لأننا فعلنا log_lock_waits:

LOG:  process 358 still waiting for ShareLock on transaction 891 after 1000.207 ms
DETAIL:  Process holding the lock: 351. Wait queue: 358.
CONTEXT:  while updating tuple (0,3) in relation "products"
STATEMENT:  UPDATE products SET price = 359.00 WHERE id = 3

وأنتجنا الحالة بجلستين، الأولى تفتح المعاملة وتبقى صامتة، والثانية تحاول التعديل:

(echo "BEGIN; UPDATE products SET stock = stock - 1 WHERE id = 3;"; sleep 40) | docker exec -i -e PGAPPNAME=checkout-worker kata-pg psql -U postgres -d shop &
docker exec -e PGAPPNAME=admin-panel kata-pg psql -U postgres -d shop -c "UPDATE products SET price = 359.00 WHERE id = 3" &

والحل الفوري إنهاء الجلسة التي تقفل، وليس التي تنتظر:

SELECT pg_terminate_backend(351);
SELECT id, name, price, stock FROM products WHERE id = 3;

 pg_terminate_backend
----------------------
 t
(1 row)

UPDATE 1
 id |        name         | price  | stock
----+---------------------+--------+-------
  3 | Mechanical Keyboard | 359.00 |     80
(1 row)

فاكتمل تعديل السعر فوراً، والسطر UPDATE 1 هنا من جلسة لوحة الإدارة، والمخزون بقي 80 لأن إنهاء الجلسة 351 تراجع عن معاملتها كلها، فإذا كان الطلب قد دفع فعلاً فعلى فريق الدفع أن يعرف ذلك. والإعداد الذي يمنع التكرار هو idle_in_transaction_session_timeout، وجربناه:

SET idle_in_transaction_session_timeout = '3s';
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 3;
\! sleep 5
SELECT stock FROM products WHERE id = 3;

SET
BEGIN
UPDATE 1
FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
connection to server was lost

ولكن الإصلاح الحقيقي في الكود: لا تقم باستدعاء خدمة خارجية أو انتظار المستخدم داخل معاملة مفتوحة، والسبب أن كل ثانية فيها قفل على صفوف واتصال محجوز من المجمع Pool لا يستطيع أحد غيرك استخدامه.

للتفصيل: طلب ينتظر قفلاً في دليل الطلب البطيء، وما الذي ينكسر في وضع transaction في مقال تجميع الاتصالات، وpg_stat_activity وpg_blocking_pids في التوثيق.

الصورة التالية تبين ما حدث:

الخطوة الأولى: checkout (430) قفل المنتج 1، وrestock (437) قفل المنتج 2. الخطوة الثانية: checkout طلب المنتج 2 فانتظر 437، وrestock طلب المنتج 1 فانتظر 430. وبعد deadlock_timeout وهي ثانية في المختبر بحث PostgreSQL عن دائرة انتظار فوجدها، فألغى معاملة واحدة هي 430 checkout بالكامل، وأكملت 437. وأنتجنا الحالة بجلستين متزامنتين، كل منها تنام 3 ثوان بين التحديثين:

== A (checkout)
BEGIN
UPDATE 1
 pg_sleep
----------

(1 row)

ERROR:  deadlock detected
DETAIL:  Process 430 waits for ShareLock on transaction 898; blocked by process 437.
Process 437 waits for ShareLock on transaction 897; blocked by process 430.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,29) in relation "products"
ROLLBACK
== B (restock)
BEGIN
UPDATE 1
 pg_sleep
----------

(1 row)

UPDATE 1
COMMIT

ولاحظ أن ROLLBACK في الجلسة A هو أمر COMMIT الذي أرسلته، فالمعاملة الملغاة لا يمكن أن تنجح، وpsql يطبع ROLLBACK بدل COMMIT. والإصلاح أن يقفل الجميع الصفوف بنفس الترتيب، وأسهل طريقة أن تبدأ المعاملة بقفل كل الصفوف التي سوف تحتاجها مرتبة بالمفتاح:

BEGIN;
SELECT id FROM products WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE products SET stock = stock + 10 WHERE id = 2;
UPDATE products SET stock = stock + 10 WHERE id = 1;
COMMIT;

وأعدنا نفس التجربة والجلستان تبدآن بهذا السطر، فانتظرت الثانية حتى انتهت الأولى ثم أكملت، ولم يظهر أي خطأ:

== A
UPDATE 1
UPDATE 1
COMMIT
== B
UPDATE 1
UPDATE 1
COMMIT

وفي الكود الحقيقي رتب قائمة المنتجات في الطلب بالرقم قبل أن تحدثها، وأضف إعادة المحاولة Retry عند الخطأ 40P01، لأن الجمود قد يحدث من مسار لم تفكر فيه، والمعاملة الملغاة آمنة للإعادة.

للتفصيل: الأقفال والجمود وdeadlock_timeout في توثيق PostgreSQL، وطلب ينتظر قفلاً في دليل الطلب البطيء.

شغلنا جلستين بفارق ثانية، كل واحدة تقرأ المخزون وتحفظ stock - 1 في متغير ب \gset كما يفعل التطبيق، ثم تنام 5 ثوان، ثم تكتب القيمة:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT stock, to_char(clock_timestamp(), 'HH24:MI:SS') AS at FROM products WHERE id = 20;
SELECT stock - 1 AS new_stock FROM products WHERE id = 20 \gset
SELECT pg_sleep(5);
UPDATE products SET stock = :new_stock WHERE id = 20;
COMMIT;

والمخرج سوف يكون كما يلي، مع حذف أسطر pg_sleep الفارغة وأسطر عدد الصفوف:

== session A
BEGIN
 stock |    at
-------+----------
    70 | 08:28:36
UPDATE 1
COMMIT
== session B
BEGIN
 stock |    at
-------+----------
    70 | 08:28:39
UPDATE 1
COMMIT
 id |    name    | stock
----+------------+-------
 20 | Honey 500g |    69
(1 row)

الجلستان قرأتا 70 قبل أن تحفظ الأولى، وكلتاهما كتبت 69، فضاع نقص واحد دون أي خطأ، وهذا هو التحديث المفقود Lost Update. ثم نفس التجربة في REPEATABLE READ:

== session A
BEGIN
 stock |    at
-------+----------
    70 | 08:27:48
ERROR:  could not serialize access due to concurrent update
ROLLBACK
== session B
BEGIN
 stock |    at
-------+----------
    70 | 08:27:47
UPDATE 1
COMMIT
 id |    name    | stock
----+------------+-------
 20 | Honey 500g |    69
(1 row)

هنا بدأت B قبل A بثانية فحفظت أولاً، وعندما حاولت A أن تعدل صفاً تغير بعد بداية معاملتها رفض PostgreSQL ذلك بالخطأ could not serialize access، فالمخزون 69 صحيح لأن البيع الثاني لم يتم، وعلى التطبيق أن يعيد المعاملة من البداية فيقرأ 69 ويكتب 68. وهذا السلوك موثق في مستويات العزل.

وأبسط إصلاح ألا يحسب التطبيق القيمة أصلاً، وأن يطلب من قاعدة البيانات أن تنقص، لأن UPDATE في READ COMMITTED يعيد قراءة الصف بعد انتظار القفل:

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 20 RETURNING stock;
COMMIT;

 stock
-------
    69
(1 row)
 stock
-------
    68
(1 row)

وإذا كان المنطق أعقد من عملية حسابية، كأن تتحقق من شروط قبل البيع، فاقرأ الصف ب SELECT ... FOR UPDATE حتى ينتظر الطلب الثاني، أو استخدم REPEATABLE READ مع إعادة المحاولة. وأضف القيد CHECK (stock >= 0) حتى لا يبيع النظام ما ليس عنده مهما حدث في الكود.

للتفصيل: القيود Constraints في دليل تصميم قواعد البيانات، والمعاملة وSET LOCAL في دليل تعدد المستأجرين، والأقفال على مستوى الصف في التوثيق.