PostgreSQL ilg'or cheklovlar: EXCLUDE, DEFERRABLE UNIQUE, NOT VALID va generated ustunlar

Qisqacha: EXCLUDE — "bunday ikki qator bo'lmasin" qoidasi istalgan operator bilan: kesishadigan vaqt, registrga befarq matn. DEFERRABLE — tekshiruvni buyruq yoki tranzaksiya oxiriga suradi, shunda qiymatlarni almashtirish mumkin. NOT VALID + VALIDATE — katta jadvalga cheklovni saytni to'xtatmasdan qo'shadi. Generated ustun qiymatini baza o'zi formuladan hisoblaydi (PostgreSQL 18 da sukut — VIRTUAL).

Bu darsda

• EXCLUDE ning ikki ko'rinishini o'qiysiz: GiST bilan vaqt oralig'i va B-tree bilan registrga befarq noyoblik (vazifalar dagi haqiqiy cheklov).
• Cheklov qachon tekshirilishini bilasiz: har qator, buyruq oxiri yoki COMMIT.
• DEFERRABLE UNIQUE bilan tartib raqamlarini almashtirasiz va uning ON CONFLICT bilan chegarasini bilasiz.
• NOT VALID va VALIDATE qanday qulf olishini o'z ko'zingiz bilan ko'rasiz.
• Generated ustunning STORED va VIRTUAL turlarini, cheklovlarini ajratasiz.

Oldin bilishingiz kerak: Massivlar, range, domain va maxsus turlar, Foreign key va referential actions, ALTER TABLE va sxemani o'zgartirish, Qulflar: row, table va advisory.

1. Nega bu kerak?

O'tgan darsda bron jadvali ikki kesishgan bronni rad etdi — EXCLUDE cheklovi bilan. Bu dars — cheklovlar haqida qolgan savollar.

Uch vaziyat. Birinchi: ro'yxatda vazifalarni sudrab tartiblash. Aziz 1-vazifani 3-o'ringa, 3-ni 1-o'ringa qo'ydi. UPDATE ikki qatorni o'zgartiradi — lekin bir lahzaga ikkala vazifada tartib = 3 bo'ladi. Oddiy UNIQUE shu lahzada xato beradi, garchi oxirgi natija to'g'ri bo'lsa ham.

Ikkinchi: prodakshnda 5 million qatorli jadval. Yangi qoida qo'shish kerak: "matn bo'sh bo'lmasin". Oddiy ADD CONSTRAINT butun jadvalni tekshiradi va shu vaqt davomida jadvalga yozish ham, o'qish ham to'xtaydi (ALTER TABLE darsidagi qulf jadvali). Bir necha daqiqa sayt "qotadi".

Uchinchi: "bajarilish vaqti = bajarilgan − yaratilgan" kabi qiymat. Ilova har safar hisoblaydimi yoki bazada tayyor turadimi?

O'xshatish: bank kassasi. Kassir har bir pul o'tkazmasini darhol tekshirsa (oddiy cheklov), "Alidan Valiga, Validan Aliga" degan almashtiruvda birinchi qadamdan keyin Ali hisobida pul yetmay qolishi mumkin. Shuning uchun bank kun oxirida balansni tekshiradi (kechiktirilgan cheklov) — oraliq holat muhim emas, natija muhim.

Bu dars ham "faqat tajriba" darsi: misollar alohida jadvallarda. Lekin bitta istisno bor — vazifalar da 26-qismdan beri ishlab turgan EXCLUDE … DEFERRABLE cheklovi. Undan boshlaymiz.

2. EXCLUDE: vazifalar dagi haqiqiy misol

2.1 Cheklovni o'qish

Deadlock va serialization failure darsida faol vazifa matnining noyobligi UNIQUE INDEX dan EXCLUDE ga almashgan edi (sql/014). Endi uni to'liq o'qiymiz. Bu darsdagi so'rovlarni holat 01 bazasida, o'z PostgreSQL 17.6 klasterimizda ishga tushirdik (kanon PostgreSQL 18.6 bilan farq bo'lsa — aytamiz):

SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'vazifalar_egasi_id_matn_excl';

                                          pg_get_constraintdef
--------------------------------------------------------------------------------------------------------
 EXCLUDE USING btree (egasi_id WITH =, lower(matn) WITH =) WHERE ((ochirilgan_vaqt IS NULL)) DEFERRABLE
(1 row)

Bo'laklab:

Hamma operator = bo'lsa, EXCLUDE aslida UNIQUE ning o'zi. Nega UNIQUE emas? Uchta sabab bir joyda: ifoda (lower(matn)), shart (WHERE) va DEFERRABLE. UNIQUE cheklovi ifoda va shartni qo'llamaydi; noyob indeks qo'llaydi, lekin u DEFERRABLE bo'la olmaydi (hujjat bo'yicha, "CREATE TABLE"). EXCLUDE uchalasini bir qilib beradi.

2.2 Amalda

Azizning vazifalari orasida ikkita bir xil matn bor:

SELECT id, matn, ochirilgan_vaqt IS NOT NULL AS savatda
FROM vazifalar
WHERE egasi_id = 1 AND matn LIKE 'Bozordan%'
ORDER BY id;

 id |                   matn                   | savatda
----+------------------------------------------+---------
  8 | Bozordan kartoshka va piyoz olish #bozor | t
 48 | Bozordan kartoshka va piyoz olish #bozor | f
(2 rows)

8-vazifa Savatda, 48 — faol. Shart (WHERE ochirilgan_vaqt IS NULL) tufayli ular to'qnashmaydi. Endi uchta urinish:

BEGIN;
INSERT INTO vazifalar (egasi_id, matn)
VALUES (1, 'BOZORDAN kartoshka va piyoz olish #bozor');
ROLLBACK;
BEGIN;
UPDATE vazifalar SET ochirilgan_vaqt = NULL WHERE id = 8;
ROLLBACK;

BEGIN
ERROR:  conflicting key value violates exclusion constraint "vazifalar_egasi_id_matn_excl"
DETAIL:  Key (egasi_id, lower(matn))=(1, bozordan kartoshka va piyoz olish #bozor) conflicts with existing key (egasi_id, lower(matn))=(1, bozordan kartoshka va piyoz olish #bozor).
ROLLBACK
BEGIN
ERROR:  conflicting key value violates exclusion constraint "vazifalar_egasi_id_matn_excl"
DETAIL:  Key (egasi_id, lower(matn))=(1, bozordan kartoshka va piyoz olish #bozor) conflicts with existing key (egasi_id, lower(matn))=(1, bozordan kartoshka va piyoz olish #bozor).
ROLLBACK

1. Katta harfli "BOZORDAN…" — rad: lower() dan keyin bir xil.
2. 8-vazifani Savatdan qaytarish — rad: endi u ham faol bo'ladi va 48 bilan to'qnashadi. Ilova bu holatda "Bunday vazifa allaqachon bor" deyishi kerak — API xato kodi 23P01 ni 409 ga aylantiradi (Parametrli so'rovlar va 26-qism API qismi).

Uchinchi urinish — DEFERRABLE nima uchun kerakligini ko'rsatadi. Ikki vazifaning matnini bitta UPDATE bilan almashtiramiz:

BEGIN;
UPDATE vazifalar
SET matn = CASE id WHEN 43 THEN 'Qoralama yozish'
                   WHEN 44 THEN 'Blog uchun mavzu tanlash' END
WHERE id IN (43, 44)
RETURNING id, matn;
ROLLBACK;

BEGIN
 id |           matn
----+--------------------------
 43 | Qoralama yozish
 44 | Blog uchun mavzu tanlash
(2 rows)

UPDATE 2
ROLLBACK

O'tdi. 43-vazifa yangilangan lahzada ikkita "Qoralama yozish" bor edi (43 va hali o'zgarmagan 44). Lekin cheklov DEFERRABLE — u buyruq oxirida tekshirildi, o'shanda takror yo'q edi. API'dagi PUT (butun ro'yxatni saqlash) aynan shunga tayanadi — 014 izohida shunday yozilgan.

3. Cheklov qachon tekshiriladi

3.1 Uch rejim

flowchart TD
  A["UNIQUE / PK / EXCLUDE / FK"] --> B{"DEFERRABLE yozilganmi?"}
  B -- "yo'q" --> C["UNIQUE, PK, EXCLUDE: har qator yozilganda"]
  B -- "ha" --> D{"INITIALLY ..."}
  D -- "IMMEDIATE" --> E["Buyruq oxirida"]
  D -- "DEFERRED" --> F["COMMIT da"]
  E -. "SET CONSTRAINTS ... DEFERRED" .-> F

Nimaga qarang: chap tarmoq — Postgres'ning o'ziga xosligi. SQL standarti bo'yicha noyoblik buyruq oxirida tekshirilishi kerak, Postgres esa kechiktirilmaydigan UNIQUE ni har qator yozilganda tekshiradi (hujjat bo'yicha, "CREATE TABLE", Compatibility bo'limi). Tashqi kalit (FK) sukut bo'yicha buyruq oxirida (Foreign key). CHECK va NOT NULL doim darhol — ular kechiktirilmaydi.

3.2 Oddiy UNIQUE bilan almashtirish yiqiladi

Sudrab tartiblash jadvali (kanon tajribasidagi bilan bir xil):

CREATE TABLE t_tartib (
  id int PRIMARY KEY, egasi int, tartib int,
  UNIQUE (egasi, tartib)
);
INSERT INTO t_tartib VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3);
UPDATE t_tartib
SET tartib = CASE id WHEN 1 THEN 3 WHEN 3 THEN 1 END
WHERE id IN (1, 3);
UPDATE t_tartib SET tartib = tartib + 1;

CREATE TABLE
INSERT 0 3
ERROR:  duplicate key value violates unique constraint "t_tartib_egasi_tartib_key"
DETAIL:  Key (egasi, tartib)=(1, 3) already exists.
ERROR:  duplicate key value violates unique constraint "t_tartib_egasi_tartib_key"
DETAIL:  Key (egasi, tartib)=(1, 2) already exists.

Ikkalasi yiqildi. Birinchisi — 1-qatorga tartib = 3 yozilgan lahzada 3-qator hali 3 da edi. Ikkinchisi — "hammasini bittaga surish": 1-qator 2 ga o'tgan lahzada 2-qator hali 2 da. Ikkala buyruq tugaganda takror bo'lmasdi — lekin tekshiruv oraliq holatda bo'ldi. Ikkinchi buyruq qaysi qatordan boshlashi — jadvaldagi fizik tartibga bog'liq; ORDER BY qo'yib bo'lmaydi.

3.3 DEFERRABLE INITIALLY IMMEDIATE

Cheklovni kechiktiriladigan qilib qayta yaratamiz:

ALTER TABLE t_tartib
  DROP CONSTRAINT t_tartib_egasi_tartib_key,
  ADD CONSTRAINT t_tartib_egasi_tartib_key
    UNIQUE (egasi, tartib) DEFERRABLE INITIALLY IMMEDIATE;
UPDATE t_tartib
SET tartib = CASE id WHEN 1 THEN 3 WHEN 3 THEN 1 END
WHERE id IN (1, 3);
UPDATE t_tartib SET tartib = tartib + 1;
SELECT * FROM t_tartib ORDER BY tartib;

ALTER TABLE
UPDATE 2
UPDATE 3
 id | egasi | tartib
----+-------+--------
  3 |     1 |      2
  2 |     1 |      3
  1 |     1 |      4
(3 rows)

Ikkalasi o'tdi. INITIALLY IMMEDIATE — "sukut bo'yicha buyruq oxirida". Natijada 1 va 3 almashdi, keyin hammasi bittaga surildi: 3-vazifa birinchi o'rinda (2), qolganlari ortidan.

3.4 DEFERRED: tranzaksiya oxirigacha

Ba'zan almashtirish ikki alohida buyruqda bo'ladi (masalan, ilova har qatorni alohida yangilaydi). Unda tekshiruvni COMMIT gacha surish kerak:

BEGIN;
SET CONSTRAINTS t_tartib_egasi_tartib_key DEFERRED;
UPDATE t_tartib SET tartib = 2 WHERE id = 1;
SELECT 'oraliqda takror' AS holat, count(*)
FROM t_tartib WHERE tartib = 2;
COMMIT;

BEGIN
SET CONSTRAINTS
UPDATE 1
      holat      | count
-----------------+-------
 oraliqda takror |     2
(1 row)

ERROR:  duplicate key value violates unique constraint "t_tartib_egasi_tartib_key"
DETAIL:  Key (egasi, tartib)=(1, 2) already exists.

SET CONSTRAINTS … DEFERRED — shu tranzaksiyada tekshiruv COMMIT ga suriladi. Oraliqda ikkita tartib = 2 bor — ruxsat. Lekin ikkinchi qatorni tuzatmadik, va COMMIT paytida tekshiruv yiqildi. Diqqat: COMMIT buyrug'i xato berdi — butun tranzaksiya bekor. Ilova COMMIT dan ham xato kutishi kerak; ko'p kod faqat UPDATE ning xatosini ushlaydi.

SET CONSTRAINTS faqat DEFERRABLE cheklovga ishlaydi. Oddiy UNIQUE ni shunday qilib bo'lmaydi.

Tekshirib ko'ring: Nega hamma UNIQUE ni DEFERRABLE qilmaymiz — xavfsizroq-ku?

3.5 Narxi: ON CONFLICT bilan ishlamaydi

INSERT INTO t_tartib VALUES (9, 1, 2)
ON CONFLICT (egasi, tartib) DO NOTHING;

ERROR:  ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters

"ON CONFLICT kechiktiriladigan noyoblik yoki istisno cheklovini hakam (arbiter) sifatida qo'llamaydi". UPSERT darsidagi naqsh bu ustunda endi ishlamaydi. Shuning uchun vazifalar dagi 014 cheklovi bilan API ON CONFLICT ishlatmaydi: takrorni xato kodi (23P01) orqali ushlaydi.

Kanon eslatmasi: vazifalar da (egasi_id, tartib) uchun UNIQUE umuman yo'q — 013 fayli izohida yozilganidek, parallel qo'shishda takror tartib mumkin. Kechiktirilgan noyoblik — shu teshikni yopish varianti; uni 3-mashqda sinaysiz.

4. NOT VALID va VALIDATE: qulfni kuzatib

4.1 Takrorlash va yangi savol

ALTER TABLE darsida NOT VALID ni ko'rdik: eski qatorlar tekshirilmaydi, yangilari — darhol; keyin VALIDATE eskilarini tekshiradi. U yerda ochiq qolgan savol: bu qulf jihatidan nima beradi? Endi buni pg_locks jadvalidan o'zimiz ko'ramiz (Qulflar darsidagi qulf turlari).

Tajriba jadvali — 1 000 qator va bitta eski "yomon" (bo'sh matnli) qator:

CREATE TABLE g_matn (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  matn text NOT NULL
);
INSERT INTO g_matn (matn)
SELECT 'Vazifa ' || g FROM generate_series(1, 1000) g;
INSERT INTO g_matn (matn) VALUES ('');
ALTER TABLE g_matn ADD CONSTRAINT g_bosh_emas
  CHECK (matn <> '');

CREATE TABLE
INSERT 0 1000
INSERT 0 1
ERROR:  check constraint "g_bosh_emas" of relation "g_matn" is violated by some row

Oddiy ADD CONSTRAINT butun jadvalni tekshirdi va eski qatorni topdi — rad. Ma'lumot katta bo'lsa, bu tekshiruv uzoq davom etadi va shu vaqt qulf ushlanadi.

4.2 Qaysi qulf olinadi

Har buyruqni tranzaksiya ichida bajarib, shu sessiya ushlab turgan qulfni so'raymiz (pg_backend_pid() — joriy ulanish jarayoni):

BEGIN;
ALTER TABLE g_matn ADD CONSTRAINT g_bosh_emas
  CHECK (matn <> '') NOT VALID;
SELECT mode FROM pg_locks
WHERE relation = 'g_matn'::regclass
  AND pid = pg_backend_pid();
COMMIT;
INSERT INTO g_matn (matn) VALUES ('');

BEGIN
ALTER TABLE
        mode
---------------------
 AccessExclusiveLock
(1 row)

COMMIT
ERROR:  new row for relation "g_matn" violates check constraint "g_bosh_emas"
DETAIL:  Failing row contains (1002, ).

Ikki narsa:

1. NOT VALID bilan ham AccessExclusiveLock — eng og'ir qulf: hatto SELECT ham kutadi. Lekin u juda qisqa: jadval tekshirilmaydi, faqat katalogga yozuv qo'shiladi. Millisekundlar.
2. Yangi bo'sh matn darhol rad etildi — qoida yangi qatorlar uchun ishlayapti. DETAIL dagi (1002, ) — rad etilgan qatorning id'si (identity raqami xato bo'lsa ham sarflanadi: 1001 — eski yomon qator) va bo'sh matn.

Endi eski ma'lumotni tekshirish:

ALTER TABLE g_matn VALIDATE CONSTRAINT g_bosh_emas;
DELETE FROM g_matn WHERE matn = '';
BEGIN;
ALTER TABLE g_matn VALIDATE CONSTRAINT g_bosh_emas;
SELECT mode FROM pg_locks
WHERE relation = 'g_matn'::regclass
  AND pid = pg_backend_pid();
COMMIT;
SELECT conname, convalidated FROM pg_constraint
WHERE conname = 'g_bosh_emas';

ERROR:  check constraint "g_bosh_emas" of relation "g_matn" is violated by some row
DELETE 1
BEGIN
ALTER TABLE
           mode
--------------------------
 ShareUpdateExclusiveLock
(1 row)

COMMIT
   conname   | convalidated
-------------+--------------
 g_bosh_emas | t
(1 row)

Birinchi VALIDATE eski yomon qatorni topdi — NOT VALID buzilgan ma'lumotni "sehrli" tuzatmaydi. Tozalagandan keyin o'tdi. Asosiysi — qulf: ShareUpdateExclusiveLock. U SELECT, INSERT, UPDATE, DELETE ni to'smaydi (faqat boshqa sxema o'zgarishlari va VACUUM bilan to'qnashadi — hujjat bo'yicha, "Explicit Locking"). Ya'ni uzoq tekshiruv vaqtida sayt ishlab turadi. convalidated = t — cheklov endi to'liq.

Prodakshn retsepti:

1. ADD CONSTRAINT … NOT VALID — qisqa og'ir qulf (yaxshisi SET lock_timeout bilan, ALTER TABLE).
2. Eski ma'lumotni topib tuzatish.
3. VALIDATE CONSTRAINT — uzoq, lekin yengil qulf.

Sayt nuqtai nazaridan uch qadam shunday ko'rinadi (qulflar mosligi — hujjatdagi jadval bo'yicha; yuqorida biz faqat qulf turini o'lchadik, ikki sessiyada kutishni emas):

Shu retsept tashqi kalitga ham ishlaydi. Migratsiya vositalari bilan xavfli o'zgarishlarni Xavfli migratsiyalar va qulflar darsida batafsil ko'ramiz.

4.3 NOT NULL uchun

PostgreSQL 18 da yangilik: NOT NULL ham NOT VALID bo'la oladi. Kanon chiqishi (PG 18.6; bizning 17.6 da bu sintaksis yo'q — syntax error at or near "NOT"):

ALTER TABLE g_vazifa ADD CONSTRAINT g_bajarilgan_nn
  NOT NULL bajarilgan NOT VALID;
SELECT conname, contype, convalidated
FROM pg_constraint WHERE conname = 'g_bajarilgan_nn';

ALTER TABLE
     conname     | contype | convalidated 
-----------------+---------+--------------
 g_bajarilgan_nn | n       | f
(1 row)

contype n — NOT NULL cheklovi, convalidated f — hali tekshirilmagan. PostgreSQL 17 va eskida xuddi shu natijaga boshqa yo'l bilan erishiladi: avval CHECK (ustun IS NOT NULL) NOT VALID, keyin VALIDATE, keyin ALTER COLUMN … SET NOT NULL. Oxirgi qadam to'g'ri CHECK borligini ko'rib, jadvalni qayta tekshirmaydi (PostgreSQL 12 dan, hujjat bo'yicha, "ALTER TABLE"). Bu yo'lni Xavfli migratsiyalar va qulflar darsida yana uchratasiz.

5. Generated ustunlar

5.1 STORED: hisoblab saqlanadi

Denormalizatsiya darsida STORED generated ustunni ko'rdik: qiymat formuladan hisoblanadi va diskka yoziladi. Bir nechta yangi jihat:

\pset null '(null)'
CREATE TABLE g_vazifa (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  matn text NOT NULL,
  yaratilgan timestamptz NOT NULL,
  bajarilgan timestamptz,
  bajarish_vaqti interval
    GENERATED ALWAYS AS (bajarilgan - yaratilgan) STORED,
  kichik_matn text GENERATED ALWAYS AS (lower(matn)) STORED
);
INSERT INTO g_vazifa (matn, yaratilgan, bajarilgan) VALUES
  ('Non OLISH', '2026-10-20 09:00+05', '2026-10-20 11:30+05'),
  ('Hisobot', '2026-10-20 10:00+05', NULL);
SELECT id, matn, bajarish_vaqti, kichik_matn
FROM g_vazifa ORDER BY id;

Null display is "(null)".
CREATE TABLE
INSERT 0 2
 id |   matn    | bajarish_vaqti | kichik_matn
----+-----------+----------------+-------------
  1 | Non OLISH | 02:30:00       | non olish
  2 | Hisobot   | (null)         | hisobot
(2 rows)

bajarish_vaqti — 2.5 soat. Ikkinchi vazifa bajarilmagan: NULL - vaqt = NULL, formula ham NULL berdi. kichik_matn — qidiruv uchun tayyor kichik harfli nusxa.

Uchta qoida:

INSERT INTO g_vazifa (matn, yaratilgan, kichik_matn)
VALUES ('x', '2026-10-20 09:00+05', 'x');
ALTER TABLE g_vazifa ADD COLUMN otganmi boolean
  GENERATED ALWAYS AS (yaratilgan < now()) STORED;
CREATE INDEX g_vazifa_kichik_idx ON g_vazifa (kichik_matn);

ERROR:  cannot insert a non-DEFAULT value into column "kichik_matn"
DETAIL:  Column "kichik_matn" is a generated column.
ERROR:  generation expression is not immutable
CREATE INDEX

1. Qo'lda yozib bo'lmaydi — "generated ustunga DEFAULT bo'lmagan qiymat yozib bo'lmaydi".
2. Formula IMMUTABLE bo'lishi shart — "generation expression is not immutable". now() har safar boshqacha. Shuning uchun "muddati o'tganmi?" generated ustun bo'la olmaydi — u o'qish paytida hisoblanadi (so'rovda yoki keyingi darsdagi view'da). Funksiya turlari (IMMUTABLE, STABLE, VOLATILE) — Funksiyalar va PL/pgSQL darsida.
3. STORED ustunga indeks qo'yish mumkin — u oddiy ustundek diskda.

Yana bir cheklov (hujjat bo'yicha, "Generated Columns"): formula faqat o'sha qatordagi ustunlarni ishlatadi — boshqa jadvalga, boshqa generated ustunga qarab bo'lmaydi.

5.2 VIRTUAL: PostgreSQL 18 ning sukuti

PostgreSQL 18 da yangi tur — VIRTUAL: qiymat diskda saqlanmaydi, har o'qishda hisoblanadi. Va u sukut bo'ldi: GENERATED ALWAYS AS (…) ni STORED siz yozsangiz — virtual. Bizning 17.6 da bu yo'q (STORED siz yozish — sintaksis xatosi), shuning uchun kanon chiqishini olamiz (kanon tajribasi (IlmHamroh, 2026-10), PG 18.6; qatorlar bo'lingan, kanonda bu buyruqlar orasidagi INSERT va SELECT tushirib qoldirilgan):

CREATE TABLE g_vazifa (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  matn text NOT NULL,
  yaratilgan timestamptz NOT NULL,
  bajarilgan timestamptz,
  matn_uzunligi int GENERATED ALWAYS AS (char_length(matn)),
  bajarish_vaqti interval
    GENERATED ALWAYS AS (bajarilgan - yaratilgan) VIRTUAL,
  kichik_matn text GENERATED ALWAYS AS (lower(matn)) STORED
);
SELECT attname, attgenerated FROM pg_attribute
WHERE attrelid = 'g_vazifa'::regclass AND attgenerated <> ''
ORDER BY attnum;
CREATE INDEX ON g_vazifa (matn_uzunligi);

CREATE TABLE
    attname     | attgenerated 
----------------+--------------
 matn_uzunligi  | v
 bajarish_vaqti | v
 kichik_matn    | s
(3 rows)

ERROR:  indexes on virtual generated columns are not supported

attgenerated: v — virtual, s — stored. matn_uzunligi hech narsa yozmasak ham virtual bo'ldi. Virtual ustunga indeks qo'yib bo'lmaydi — "virtual generated ustunlarda indeks qo'llab-quvvatlanmaydi".

Halol o'lchov: kanon 200 000 qatorli jadvalda virtual ustunlar bilan va hammasi diskda bo'lgan nusxani solishtirdi — ikkalasi 16 MB. Kichik ustunlar (int, interval) qator ichidagi bo'sh joylarga (alignment) sig'ib ketgan; bu o'lchov virtual'ning hajm foydasini ko'rsatmadi. Foyda katta, kam o'qiladigan hosila ustunlarda va katta jadvalga ustun qo'shish tezligida — hujjat bo'yicha, sinalmagan.

Tekshirib ko'ring: PG 18 da kichik_matn bo'yicha qidiruv tez bo'lishi kerak. Uni STORED siz yozsa nima bo'ladi?

Tajribani yakunlaymiz:

DROP TABLE t_tartib, g_matn, g_vazifa;

DROP TABLE

6. Ko'p uchraydigan xatolar

1. Almashtirish uchun "vaqtinchalik qiymat" hiylasi. tartib = -1 qilib, keyin almashtirish — uch buyruq va poyga holati. To'g'risi: DEFERRABLE INITIALLY IMMEDIATE va bitta UPDATE.

2. COMMIT xatosini kutmaslik. DEFERRED cheklovda xato COMMIT da chiqadi. Ilova COMMIT natijasini ham tekshirsin.

3. Deferrable cheklov bilan ON CONFLICT. ON CONFLICT does not support deferrable unique constraints…. To'g'risi: xato kodini ushlash yoki oddiy noyob indeks.

4. Katta jadvalga oddiy ADD CONSTRAINT. Butun tekshiruv davomida AccessExclusiveLock. To'g'risi: NOT VALID → tozalash → VALIDATE (ShareUpdateExclusiveLock).

5. NOT VALID ni "eski ma'lumotga ruxsat" deb tushunish. Eski yomon qatorni keyin UPDATE qilsangiz, yangi nusxa tekshiriladi va rad etiladi.

6. Generated formulada now() yoki boshqa jadval. generation expression is not immutable. Vaqtga bog'liq qiymat — o'qishda hisoblanadi.

7. PG 18 da STORED siz yozib, keyin indeks. Virtual ustunga indeks qo'yilmaydi. Indeks kerak bo'lsa — STORED.

7. Mashqlar

Mashqlar kurs/mashqlar/27/03-cheklovlar/ da. Holat 01 bazasida, hammasi BEGIN … ROLLBACK ichida. Yechimlar tekshirilgan (biz ishga tushirdik, PostgreSQL 17.6).

1-mashq (oson)

vazifalar ga muddat_kuni date generated ustunini qo'shing: muddatning Toshkent vaqtidagi sanasi (STORED). Azizning (egasi_id = 1) muddatli vazifalarini id, muddat, muddat_kuni bilan chiqaring. Ishora: muddat AT TIME ZONE 'Asia/Tashkent' — vaqtni shu zonadagi soatga aylantiradi (Sana va vaqt zonalari), ::date — sanasini oladi; zona nomi aniq yozilgani uchun ifoda IMMUTABLE.

BEGIN;
ALTER TABLE vazifalar ADD COLUMN muddat_kuni date
  GENERATED ALWAYS AS
    ((muddat AT TIME ZONE 'Asia/Tashkent')::date) STORED;
SELECT id, muddat, muddat_kuni FROM vazifalar
WHERE egasi_id = 1 AND muddat IS NOT NULL ORDER BY id;
ROLLBACK;

BEGIN
ALTER TABLE
 id |         muddat         | muddat_kuni
----+------------------------+-------------
  1 | 2026-10-31 18:59:00+00 | 2026-10-31
 17 | 2026-11-15 13:00:00+00 | 2026-11-15
 23 | 2026-10-20 13:00:00+00 | 2026-10-20
 28 | 2026-10-10 18:59:00+00 | 2026-10-10
 34 | 2026-10-25 16:00:00+00 | 2026-10-25
(5 rows)

ROLLBACK

2-mashq (o'rta)

Yangi qoida: har vazifa matnida kamida bitta teg (#) bo'lsin. Cheklov nomi — vazifalar_teg_bor, sharti — position('#' in matn) > 0. Uni avval oddiy usulda qo'shing (xatoni ko'ring), keyin NOT VALID bilan. So'ng qoidaga zid vazifalar sonini chiqaring, ularning matniga #boshqa qo'shing, VALIDATE qiling va convalidated ni ko'rsating. Ishora: «NOT VALID va VALIDATE» bo'limi; xatodan keyin davom etish — SAVEPOINT (o'tgan dars mashqlari).

BEGIN;
SAVEPOINT s;
ALTER TABLE vazifalar ADD CONSTRAINT vazifalar_teg_bor
  CHECK (position('#' in matn) > 0);
ROLLBACK TO SAVEPOINT s;
ALTER TABLE vazifalar ADD CONSTRAINT vazifalar_teg_bor
  CHECK (position('#' in matn) > 0) NOT VALID;
SELECT count(*) AS tegsiz FROM vazifalar
WHERE position('#' in matn) = 0;
UPDATE vazifalar SET matn = matn || ' #boshqa'
WHERE position('#' in matn) = 0;
ALTER TABLE vazifalar VALIDATE CONSTRAINT vazifalar_teg_bor;
SELECT convalidated FROM pg_constraint
WHERE conname = 'vazifalar_teg_bor';
ROLLBACK;

BEGIN
SAVEPOINT
ERROR:  check constraint "vazifalar_teg_bor" of relation "vazifalar" is violated by some row
ROLLBACK
ALTER TABLE
 tegsiz
--------
      7
(1 row)

UPDATE 7
ALTER TABLE
 convalidated
--------------
 t
(1 row)

ROLLBACK

3-mashq (qiyin)

Dilnozaning (egasi_id = 2) vazifalari tartib 1–6 da. (egasi_id, tartib) uchun oddiy UNIQUE qo'shing va 29-vazifani (tartib 5) bitta UPDATE bilan birinchi o'ringa ko'chiring: u 1 bo'ladi, tartibi 1–4 bo'lganlar bittaga suriladi. Xatoni ko'ring, keyin cheklovni DEFERRABLE INITIALLY IMMEDIATE ga almashtirib qayta urinib ko'ring va Dilnozaning ro'yxatini (id, tartib) tartib bo'yicha chiqaring. Ishora: CASE WHEN id = 29 THEN 1 ELSE tartib + 1 END, shart tartib <= 5; «DEFERRABLE INITIALLY IMMEDIATE» bo'limi.

BEGIN;
ALTER TABLE vazifalar ADD CONSTRAINT vazifalar_tartib_key
  UNIQUE (egasi_id, tartib);
SAVEPOINT s;
UPDATE vazifalar
SET tartib = CASE WHEN id = 29 THEN 1 ELSE tartib + 1 END
WHERE egasi_id = 2 AND tartib <= 5;
ROLLBACK TO SAVEPOINT s;
ALTER TABLE vazifalar
  DROP CONSTRAINT vazifalar_tartib_key,
  ADD CONSTRAINT vazifalar_tartib_key
    UNIQUE (egasi_id, tartib) DEFERRABLE INITIALLY IMMEDIATE;
UPDATE vazifalar
SET tartib = CASE WHEN id = 29 THEN 1 ELSE tartib + 1 END
WHERE egasi_id = 2 AND tartib <= 5;
SELECT id, tartib FROM vazifalar
WHERE egasi_id = 2 ORDER BY tartib;
ROLLBACK;

BEGIN
ALTER TABLE
SAVEPOINT
ERROR:  duplicate key value violates unique constraint "vazifalar_tartib_key"
DETAIL:  Key (egasi_id, tartib)=(2, 2) already exists.
ROLLBACK
ALTER TABLE
UPDATE 5
 id | tartib
----+--------
 29 |      1
  5 |      2
 14 |      3
 16 |      4
 26 |      5
 36 |      6
(6 rows)

ROLLBACK

8. Loyiha qadami

Bu darsda vazifalar o'zgarmaydi — commit yo'q. Kanon qarorlari:

Kechiktirilgan tartib noyobligini qo'shish — sizning qaroringiz: 3-mashq yechimi tayyor migratsiya. Agar qo'shsangiz — yangi fayl (masalan sql/1NN-tartib-noyob.sql), mavjud 013 ni tahrirlamasdan.

9. Real ishda

• Sudrab tartiblash (Trello, Notion, todo ilovalar) — tartib ustuni va uni almashtirish; kechiktirilgan noyoblik yoki kasr tartib raqamlari (1, 1.5, 2) bilan.
• Katta prodakshn jadvallari — NOT VALID + VALIDATE — cheklov va tashqi kalit qo'shishning standart usuli; GitLab, Shopify kabi kompaniyalarning ochiq migratsiya qo'llanmalarida shunday (ularning hujjatlari bo'yicha, sinalmagan).
• Generated ustunlar — qidiruv uchun normallashtirilgan matn (kichik harf, keyinroq full-text search dagi tsvector), JSONB'dan chiqarilgan maydon, pul summasining hisoblangan qismi.
• ORM'lar — Prisma generated ustunni sxemada to'liq ifodalay olmaydi va EXCLUDE ni bilmaydi; bunday cheklovlar xom SQL migratsiyasida yoziladi (Prisma Migrate chuqur).
• Intervyuda: "Million qatorli jadvalga cheklovni qanday qo'shasiz?", "Ikki qatorning noyob qiymatini qanday almashtirasiz?" — NOT VALID/VALIDATE va DEFERRABLE.

Xulosa

• EXCLUDE — istalgan operator bilan noyoblik: GiST + && (vaqt), B-tree + = (ifoda, shart va DEFERRABLE bilan).
• Kechiktirilmaydigan UNIQUE Postgres'da har qatorda tekshiriladi; DEFERRABLE INITIALLY IMMEDIATE — buyruq oxirida, DEFERRED — COMMIT da.
• Kechiktiriladigan noyoblik ON CONFLICT hakami bo'la olmaydi; DEFERRED xatosi COMMIT da chiqadi.
• NOT VALID — qisqa AccessExclusiveLock; VALIDATE — uzoq, lekin o'qish-yozishni to'smaydigan ShareUpdateExclusiveLock.
• Generated: formula IMMUTABLE va bir qator ichida; STORED — diskda, indeksli; PG 18 sukuti VIRTUAL — indekssiz.

Keyingi dars: View va materialized view — murakkab so'rovni nom bilan saqlash, view orqali yozish, security_barrier va foydalanuvchi statistikasi uchun materialized view (holat 04).

Manbalar

• PostgreSQL 18 hujjati — Constraints (exclusion, deferrable): https://www.postgresql.org/docs/18/ddl-constraints.html
• PostgreSQL 18 hujjati — CREATE TABLE (Compatibility: non-deferred uniqueness; DEFERRABLE): https://www.postgresql.org/docs/18/sql-createtable.html
• PostgreSQL 18 hujjati — ALTER TABLE (NOT VALID, VALIDATE, SET NOT NULL va CHECK): https://www.postgresql.org/docs/18/sql-altertable.html
• PostgreSQL 18 hujjati — Explicit Locking (qulf turlari): https://www.postgresql.org/docs/18/explicit-locking.html
• PostgreSQL 18 hujjati — Generated Columns (VIRTUAL, STORED): https://www.postgresql.org/docs/18/ddl-generated-columns.html
• IlmHamroh kanon tajribasi (PostgreSQL 18.6, Docker, 2026-10-24); darsdagi so'rovlar — biz ishga tushirdik (PostgreSQL 17.6, Windows 11), 2026-10-26.