PostgreSQL 15 veya sonraki sürümlerde, DEFERRABLE olarak tanımlanmış bir UNIQUE NULLS NOT DISTINCT kısıtlaması kullanın ve ardından bunu yeniden sıralama işlemi içindeki transaction’da erteleyin. Bu, NULL üst değerine sahip kayıtlar da dahil olmak üzere benzersiz bir son sıralamayı korurken makale satırlarının konumlarını değiştirmesine olanak tanır. PostgreSQL kısıtlama sözdizimi

Bu eğitim, tek bir üst grup içindeki sentetik makale satırlarını yeniden sıralaması gereken Mac Terminal kullanan geliştiriciler içindir. SQL gösterimi, istenen sıralamayı doğrulayan eksiksiz bir node-postgres programı ve PostgreSQL 14 alternatifi içerir. Makalelerin başka bir üst gruba taşınması bu örneğin kapsamı dışındadır.

NULL olabilen bir üst değer, konum benzersizliğini neden zorlaştırır?

Her makalenin bir id, NULL olabilen bir parent_id ve pozitif bir tamsayı position olduğunu varsayın. Aynı üst değere sahip makaleler sıralı bir kardeş grubu oluşturur. Bu modelde, üst değeri NULL olan tüm makaleler tek bir kök grubuna aittir.

Düz bir UNIQUE (parent_id, position) bu kök grubu kuralını uygulamaz. PostgreSQL, benzersizlik denetiminde normalde NULL değerleri farklı kabul eder; bu nedenle iki kök makale de 1 konumunda bulunabilir. NULLS NOT DISTINCT eklemek, bileşik anahtar denetlenirken bu NULL üst değerlerin eşit kabul edilmesini sağlar. NULL olmayan farklı üst değerler yine de her biri kendi 1 konumuna sahip olabilir. PostgreSQL: Benzersiz Kısıtlamalar

Yeniden sıralama ayrı bir zamanlama sorunu yaratır. 1 makalesi 1 konumunda, 2 makalesi ise 2 konumunda bulunuyorsa, 2 makalesini 1 konumuna taşımak, geçici olarak 1 makalesinin konumunu yineler. Ertelenemeyen benzersiz bir kısıtlama, eklenen veya değiştirilen her satırı hemen denetler. Ertelenebilir bir kısıtlama denetimi deyim sonuna kadar ya da açıkça ertelendiğinde transaction bitene kadar bekletebilir. PostgreSQL: CREATE TABLE

Birden çok güncelleme ile uygulanan yeniden sıralama için her iki özellik de önemlidir: NULL değerleri dikkate alan benzersizlik doğru grupları tanımlar ve ertelenmiş denetim, güncellemeler arasında geçici çakışmalara izin verir.

PostgreSQL sürümünüzde hangi tanımı kullanmalısınız?

PostgreSQL 15, benzersiz kısıtlamalar ve dizinler için NULLS NOT DISTINCT özelliğini getirdi. Aşağıdaki ana örnekler 15 veya sonraki sunucu sürümlerini gerektirir; PostgreSQL 14 için daha sonra gösterilen alternatifi kullanın. Sürümünü, yüklü komut satırı istemcisinden çıkarsamak yerine bağlı olduğunuz sunucuda denetleyin. PostgreSQL 15 sürüm notları

Durum Desteklenen yaklaşım
PostgreSQL 15 veya sonraki sürüm, yeni tablo DEFERRABLE INITIALLY IMMEDIATE özellikli, adlandırılmış UNIQUE NULLS NOT DISTINCT kısıtlaması
Mevcut sıradan benzersiz kısıtlama Tanımını değiştirin; ALTER CONSTRAINT benzersiz kısıtlamanın ertelenebilirliğini değiştiremez
Mevcut ifade veya kısmi benzersiz dizin Uygulama tasarımını değiştirin; bu dizinler sıradan benzersiz kısıtlama olarak eklenemez
PostgreSQL 14 Ertelenebilir benzersiz kısıtlamayla üretilmiş gruplama sütunları kullanın

Mevcut nesnelere ilişkin sınırlar ALTER TABLE belgesinde açıklanır: kısıtlama özniteliklerini bu şekilde değiştirmek yabancı anahtarlarla sınırlıdır ve benzersiz kısıtlama olarak eklenen bir dizin ifade içeremez veya kısmi olamaz. Tek başına CREATE UNIQUE INDEX komutunun da bir DEFERRABLE yan tümcesi yoktur. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

SQL’de iki kök makalenin yerini nasıl değiştirirsiniz?

Ön koşullar: mevcut bir yerel PostgreSQL 15 veya sonraki sürüm veritabanı ve çalışan bir psql bağlantısı. Mac Terminal’i açın ve yapılandırılmış bağlantı ayarlarınızı kullanarak istemciyi başlatın. Aşağıdaki komut başlangıç özelleştirmelerini atlar ve bir hata oluştuğunda betik işlemeyi durdurur. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Aşağıdaki eksiksiz SQL gösterimini bu oturuma yapıştırın. Gösterim yalnızca sentetik makaleler içeren geçici bir tablo oluşturur; oturum sona erdiğinde geçici tablolar silinir. Tablo adı bu oturumda kullanılmamış olmalıdır. PostgreSQL: Geçici Tablolar

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;

İlk güncellemeden sonra transaction’da geçici olarak 1 konumunda iki kök bulunur. İkinci güncellemeden sonra konumları yeniden benzersiz olur. Kısıtlamayı IMMEDIATE durumuna geri almak, o noktada bekleyen değişiklikleri denetler. Yinelenen bir değer kalmışsa komut başarısız olur; yoksa transaction commit edilebilir. Erteleme, doğrulamanın ne zaman yapılacağını değiştirir. PostgreSQL: SET CONSTRAINTS

Beklenen son kök sıralaması:

Konum Makale kimliği Başlık
1 2 Makale B
2 1 Makale A
3 3 Makale C

Güncellemelerin hiçbiri çocuk makaleleri seçmediğinden, bu makaleler konumlarını korur. Etkileşimli bir transaction hata verirse yeniden denemeden önce ROLLBACK; komutunu çalıştırın. Bir transaction, ilgili deyimleri COMMIT veya ROLLBACK komutuna kadar bir arada tutar. PostgreSQL: BEGIN

node-postgres ile bir kardeş grubunun tamamını nasıl yeniden sıralarsınız?

Transaction’ın tamamı boyunca aynı bağlantı havuzundan alınmış tek bir istemci kullanın. node-postgres bunu açıkça gerektirir: ayrı pool.query() çağrıları, farklı bağlantılarda çalışabilecekleri için transaction içinde uygun değildir. node-postgres: Transaction’lar

Aşağıdaki program, kardeş kimliklerinin sıralı ve eksiksiz bir listesini kabul eder. Ek kuralı basittir: sağlanan liste, mevcut kardeş kümesinin bir permütasyonu olmalıdır. Yinelenenleri, atlananları ve başka gruplara ait kimlikleri reddetmek, görünüşte başarılı olan kısmi yeniden sıralamaları önler.

Yerel bir Node.js projesinde pg paketini yükleyin, aşağıdaki eksiksiz programı reorder.cjs olarak kaydedin ve çalıştırın. Program, mevcut yerel bağlantı ayarlarınızın PostgreSQL ortam değişkenleri üzerinden kullanılabildiğini varsayar. node-postgres, psql ile aynı bağlantı ortam değişkenlerini destekler. node-postgres: Bağlantı Kurma

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, üst değer filtresinin hem NULL hem de NULL olmayan değerlerde çalışmasını sağlar. NULL parametreyle yapılan sıradan eşitlik karşılaştırması kökleri seçmez. Bunun yerine örnek çocukları yeniden sıralamak için parentId değerini 1 ve orderedIds değerini [5, 4] olarak değiştirin. PostgreSQL: Karşılaştırma İşlevleri ve İşleçleri

$1 biçimindeki yer tutucular değerleri SQL metninden ayrı olarak taşır. Açık tamsayı dönüşümü, üst değer parametresi NULL olduğunda da amaçlanan türü belirtir. node-postgres: Sorgular

Kardeşleri okumadan önce neden kilit almalısınız?

Geçici gösterimde eşzamanlı oturum yoktur. Tablo kilidi, küçük ve ortak kullanılan bir tablo için özellikle geniş kapsamlı bir yaklaşımı örnekler: SHARE ROW EXCLUSIVE, normal tablo yazma işlemleriyle ve kendi türündeki kilitlerle çakışırken sıradan okumalara izin verir. Kilidi transaction boyunca tutmak, doğrulama ve güncellemeler sırasında kardeş üyeliğinin sabit kalmasını sağlar. Ayrıca ilgisiz üst gruplarına yazmayı da engeller; bu nedenle yoğun kullanılan bir tabloda uygulamadan önce bu ödünleşimi değerlendirin. PostgreSQL: Açık Kilitleme

Program açıkça READ COMMITTED yalıtım düzeyini seçer. Bu düzeyde kardeş sorgusu, önceki kilit beklemesi sona ermeden önce commit edilmiş değişiklikler dahil olmak üzere, sorgunun başladığı andaki commit edilmiş değişiklikleri görür. PostgreSQL: BEGIN, PostgreSQL: Transaction Yalıtımı

JavaScript doğrulaması geçerli bir permütasyonla ve yinelenen, eksik ve başka gruba ait kimliklerle denetlendi. Bu belirlenimci denetimler başarılı oldu. Veritabanı örnekleri belgelere göre incelendi ancak araştırma ortamında PostgreSQL’e karşı çalıştırılmadı.

PostgreSQL 14’te desteklenen alternatif nedir?

PostgreSQL 14’te gruplama ifadesini üretilmiş sütunlar olarak somutlaştırın ve ardından bu sütunlara ertelenebilir bir benzersiz kısıtlama uygulayın. Saklanan üretilmiş sütunlar satırlar yazılırken hesaplanır ve sıradan değerlerle üzerine yazılamaz. PostgreSQL 14: Üretilmiş Sütunlar

Bu bağımsız SQL örneği, üst grubu temsil etmek için iki üretilmiş değer kullanır. Boolean değer, kökleri gerçek 0 üst kimliğinden ayırır; böylece COALESCE geri dönüş değeri için bir kimlik ayırmak gerekmez.

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;

Bir kök için üretilmiş gruplama değerleri (true, 0); 0 üst değeri içinse (false, 0) olur. NULL olmayan her üst değer kendi tamsayı değerini korur. Böylece kısıtlama, PostgreSQL 15’in NULL işleyişine bağlı kalmadan amaçlanan gruplamayı ifade eder. PostgreSQL 14, adlandırılmış sütunlarda ertelenebilir benzersiz kısıtlamaları destekler. PostgreSQL 14: CREATE TABLE

Mevcut bir tabloyu uyarlamadan önce neleri denetlemelisiniz?

COALESCE(parent_id, 0) üzerinde bir ifade dizini, ifadeye dayalı benzersizliği uygulayabilir ancak ertelenebilir hâle getirilemez veya sıradan bir benzersiz kısıtlama olarak eklenemez. Kök satırlar için kısmi dizin de aynı ekleme sınırlamasına sahiptir. Bu anında denetim yapan nesnelerden birini yerinde bırakmak, ertelenebilir bir kısıtlama ekledikten sonra bile yer değiştirmeyi engelleyebilir. PostgreSQL: İfadeler Üzerinde Dizinler, PostgreSQL: ALTER TABLE

Mevcut bir tanımı değiştirmeden önce, özellikle sıradan NULL kabul eden benzersizlik nedeniyle önceden yinelenmesine izin verilmiş kökler için yinelenen üst-konum çiftlerini inceleyin. Benzersiz kısıtlama eklemek mevcut satırları doğrular; benzersiz kısıtlamalarda NOT VALID kullanılamaz. Değişikliği denemeden önce belirsiz konumları bilinçli biçimde çözün. PostgreSQL: ALTER TABLE

Kısıtlamanın sağladığı garantiyi uygulamanın sıralama kuralından ayrı tutun. Tek başına benzersizlik, 1, 4 ve 9 konumları gibi boşluklara izin verir. Eksiksiz liste programı, özellikle 1 değerinden başlayarak ardışık konumlar atar. Her kardeşin işleme katılmasını sağlayan, listedeki üyelik denetimidir.

Bu görev için NULL olabilen üst gruplarını temsil eden kısıtlamayı seçin, çakışmaya yol açan ilk güncellemeden önce erteleyin, istenen kardeş kümesinin tamamını doğrulayın ve commit etmeden önce tüm güncellemeleri tek bir istemci üzerinden tamamlayın. Bu birleşim, her kardeş için benzersiz bir son konumu korurken kök ve çocukların yeniden sıralanmasını sağlar.

Kaynaklar