قید EXCLUDE: جلوگیری از هم‌پوشانی رزرو دستگاه بافندگی

قیدی که هیچ دیتابیس رایج دیگری ندارد

برنامه‌ریزی تولید در یک کارخانه‌ی فرش ماشینی: هر سفارش باید در یک بازه‌ی زمانی روی یک دستگاه بافندگی بافته شود و یک دستگاه نمی‌تواند هم‌زمان دو کار داشته باشد. UNIQUE این را تضمین نمی‌کند، چون بازه‌ها لازم نیست برابر باشند تا تداخل کنند؛ کافی است هم‌پوشانی داشته باشند. کد برنامه هم در بار هم‌زمان قابل اعتماد نیست: دو برنامه‌ریز هم‌زمان SELECT می‌زنند، هر دو دستگاه را خالی می‌بینند و هر دو رزرو می‌کنند.

قید EXCLUDE تعمیم UNIQUE است: «هیچ دو ردیفی نباشند که برای همه‌ی این جفت‌ستون‌ها، این عملگرها true برگردانند».

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE loom_bookings (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  loom_id  int NOT NULL REFERENCES looms (id),
  order_id bigint NOT NULL REFERENCES orders (id),
  during   tstzrange NOT NULL CHECK (NOT isempty(during)),
  status   text NOT NULL DEFAULT 'planned',
  EXCLUDE USING gist (loom_id WITH =, during WITH &&) WHERE (status <> 'cancelled')
);

ترجمه: هیچ دو رزرو غیرلغوشده‌ای نباشند که loom_id آن‌ها برابر و بازه‌شان هم‌پوشان باشد. اکستنشن btree_gist لازم است چون ایندکس GiST به‌طور پیش‌فرض عملگر = را برای int بلد نیست.

INSERT INTO loom_bookings (loom_id, order_id, during)
VALUES (3, 501, '[2026-09-28 08:00+03:30, 2026-09-30 20:00+03:30)');

INSERT INTO loom_bookings (loom_id, order_id, during)
VALUES (3, 502, '[2026-09-30 18:00+03:30, 2026-10-02 08:00+03:30)');
-- ERROR:  conflicting key value violates exclusion constraint "loom_bookings_loom_id_during_excl"

INSERT INTO loom_bookings (loom_id, order_id, during)
VALUES (3, 502, '[2026-09-30 20:00+03:30, 2026-10-02 08:00+03:30)');   -- مجاز: بازه‌ها فقط مجاورند

پرسیدن از همان ایندکس

-- الان روی دستگاه ۳ چه چیزی بافته می‌شود؟
SELECT order_id FROM loom_bookings
WHERE loom_id = 3 AND during @> now() AND status <> 'cancelled';

-- مجموع ساعت‌های رزروشده‌ی هر دستگاه در مهر
SELECT loom_id,
       sum(upper(r) - lower(r)) AS busy
FROM loom_bookings,
     LATERAL (SELECT during * tstzrange('2026-09-23 00:00+03:30','2026-10-23 00:00+03:30') AS r) x
WHERE status <> 'cancelled' AND NOT isempty(r)
GROUP BY loom_id;

عملگر * روی range، اشتراک دو بازه است؛ همین باعث می‌شود رزروی که از شهریور شروع شده فقط سهم مهرش را حساب کند.

نکته‌هایی که کمتر کسی می‌داند

• کد خطای نقض EXCLUDE 23P01 (exclusion_violation) است، متفاوت با 23505 برای UNIQUE؛ در برنامه هر دو را جدا بگیرید تا پیام مناسب («دستگاه در این بازه رزرو است») نشان دهید.
• قید EXCLUDE فقط با ON CONFLICT DO NOTHING کار می‌کند، نه DO UPDATE.
• نوشتن بازه با [) (نیمه‌باز) کلید کار است: پایان یک شیفت دقیقاً شروع شیفت بعدی است و تداخل حساب نمی‌شود. با [] دو شیفت پشت‌سرهم با هم تعارض پیدا می‌کنند.
• برای رزرو با واحد روز (اتاق مهمان‌سرا یا سالن نمایشگاه) از daterange استفاده کنید؛ daterange خودش کران‌ها را به شکل canonical [) درمی‌آورد.
• نسخه‌ی 18 کلید اصلی زمانی را اضافه کرده است: PRIMARY KEY (loom_id, during WITHOUT OVERLAPS) که همین قید را با نحو استاندارد SQL:2011 می‌سازد.