Pada PostgreSQL 15 atau lebih baharu, gunakan kekangan UNIQUE NULLS NOT DISTINCT yang diisytiharkan DEFERRABLE, kemudian tangguhkannya dalam transaksi penyusunan semula. Ini membolehkan baris artikel bertukar kedudukan sambil menguatkuasakan susunan akhir yang unik, termasuk baris yang induknya NULL. Sintaks kekangan PostgreSQL

Tutorial ini ditujukan kepada pembangun yang bekerja dalam Terminal Mac dan perlu menyusun semula baris artikel sintetik dalam satu kumpulan induk. Tutorial ini merangkumi demonstrasi SQL, program node-postgres lengkap yang mengesahkan susunan yang diminta, dan alternatif PostgreSQL 14. Menukar induk artikel di luar skop contoh ini.

Mengapakah induk boleh null menyukarkan keunikan kedudukan?

Andaikan setiap artikel mempunyai id, parent_id yang boleh null, dan position integer positif. Artikel dengan induk yang sama membentuk satu kumpulan adik-beradik tersusun. Dalam model ini, semua artikel yang induknya null tergolong dalam satu kumpulan akar.

UNIQUE (parent_id, position) biasa tidak menguatkuasakan peraturan kumpulan akar itu. PostgreSQL biasanya menganggap nilai null sebagai berbeza dalam semakan keunikan, jadi dua artikel akar boleh berada pada kedudukan 1 yang sama. Menambah NULLS NOT DISTINCT menyebabkan nilai induk null dianggap sama ketika kunci komposit diperiksa. Induk bukan null yang berbeza masih boleh masing-masing mempunyai kedudukan 1 sendiri. PostgreSQL: Kekangan Unik

Penyusunan semula menimbulkan masalah masa yang berasingan. Jika artikel 1 berada pada kedudukan 1 dan artikel 2 berada pada kedudukan 2, memindahkan artikel 2 ke kedudukan 1 buat sementara waktu menduplikasi kedudukan artikel 1. Kekangan unik yang tidak boleh ditangguhkan menyemak setiap baris yang dimasukkan atau diubah serta-merta. Kekangan yang boleh ditangguhkan boleh menunggu sehingga pernyataan berakhir, atau sehingga transaksi selesai apabila ditangguhkan secara jelas. PostgreSQL: CREATE TABLE

Bagi penyusunan semula yang dilaksanakan melalui beberapa kemas kini, kedua-dua sifat ini penting: keunikan yang mengambil kira null mentakrifkan kumpulan yang betul, dan semakan tertangguh membenarkan perlanggaran sementara antara kemas kini.

Takrif manakah yang patut digunakan untuk versi PostgreSQL anda?

PostgreSQL 15 memperkenalkan NULLS NOT DISTINCT untuk kekangan dan indeks unik. Contoh utama di bawah memerlukan versi pelayan 15 atau lebih baharu; PostgreSQL 14 memerlukan alternatif yang ditunjukkan kemudian. Semak pelayan yang disambungkan, bukan membuat andaian tentang versinya berdasarkan klien baris perintah yang dipasang. Nota keluaran PostgreSQL 15

Situasi Pendekatan yang disokong
PostgreSQL 15 atau lebih baharu, jadual baharu Kekangan UNIQUE NULLS NOT DISTINCT bernama dengan DEFERRABLE INITIALLY IMMEDIATE
Kekangan unik biasa yang sedia ada Gantikan takrifnya; ALTER CONSTRAINT tidak boleh mengubah keboleh­tangguhan kekangan unik
Indeks unik ungkapan atau separa yang sedia ada Gantikan reka bentuk penguatkuasaan; indeks itu tidak boleh dilampirkan sebagai kekangan unik biasa
PostgreSQL 14 Gunakan lajur pengumpulan terjana dengan kekangan unik yang boleh ditangguhkan

Batasan objek sedia ada didokumenkan dalam ALTER TABLE: mengubah atribut kekangan dengan cara ini terhad kepada kunci asing, dan indeks yang dilampirkan sebagai kekangan unik tidak boleh mengandungi ungkapan atau bersifat separa. CREATE UNIQUE INDEX yang berdiri sendiri juga tidak mempunyai klausa DEFERRABLE. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Bagaimanakah anda menukar kedudukan dua artikel akar dalam SQL?

Prasyaratnya ialah pangkalan data PostgreSQL 15 atau lebih baharu yang sedia ada secara setempat dan sambungan psql yang berfungsi. Buka Terminal Mac dan mulakan klien menggunakan tetapan sambungan anda. Perintah berikut melangkau penyesuaian permulaan dan menghentikan pemprosesan skrip jika berlaku ralat. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Tampalkan demonstrasi SQL lengkap di bawah ke dalam sesi itu. Demonstrasi ini mencipta jadual sementara yang hanya mengandungi artikel sintetik; jadual sementara akan hilang apabila sesi berakhir. Nama jadual itu mestilah belum digunakan dalam sesi tersebut. PostgreSQL: Jadual Sementara

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;

Selepas kemas kini pertama, transaksi itu buat sementara waktu mengandungi dua akar pada kedudukan 1. Selepas kemas kini kedua, kedudukan mereka kembali unik. Menukar kekangan kepada IMMEDIATE menyemak perubahan tertunggak pada ketika itu. Jika pendua masih wujud, perintah itu gagal; jika tidak, transaksi boleh dikomit. Penangguhan mengubah masa pengesahan dilakukan. PostgreSQL: SET CONSTRAINTS

Susunan akhir akar yang dijangka ialah:

Kedudukan ID Artikel Tajuk
1 2 Artikel B
2 1 Artikel A
3 3 Artikel C

Artikel anak mengekalkan kedudukannya kerana kedua-dua kemas kini tidak memilihnya. Jika transaksi interaktif mengalami ralat, keluarkan ROLLBACK; sebelum mencuba semula. Transaksi mengumpulkan pernyataan berkaitan sehingga COMMIT atau ROLLBACK. PostgreSQL: BEGIN

Bagaimanakah anda menyusun semula seluruh kumpulan adik-beradik dengan node-postgres?

Gunakan satu klien yang telah diperoleh untuk keseluruhan transaksi. node-postgres menetapkan keperluan ini dengan jelas: panggilan pool.query() yang berasingan tidak sesuai untuk transaksi kerana panggilan itu mungkin berjalan pada sambungan yang berbeza. node-postgres: Transaksi

Program di bawah menerima senarai ID adik-beradik lengkap yang tersusun. Peraturan tambahannya mudah: senarai yang dibekalkan mestilah permutasi set adik-beradik semasa. Menolak pendua, ID yang tertinggal dan ID daripada kumpulan lain menghalang penyusunan semula separa yang kelihatan berjaya.

Dalam projek Node.js setempat, pasang pg, simpan program lengkap berikut sebagai reorder.cjs, dan jalankannya. Program ini mengandaikan tetapan sambungan setempat sedia ada anda tersedia melalui pemboleh ubah persekitaran PostgreSQL. node-postgres menyokong pemboleh ubah persekitaran sambungan yang sama seperti psql. node-postgres: Menyambung

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 membolehkan penapis induk berfungsi untuk nilai null dan bukan null. Kesamaan biasa dengan parameter null tidak memilih akar. Untuk menyusun semula artikel sampel, tukar parentId kepada 1 dan orderedIds kepada [5, 4]. PostgreSQL: Fungsi dan Operator Perbandingan

Pemegang tempat gaya $1 menghantar nilai secara berasingan daripada teks SQL. Tuangan integer yang jelas juga menyatakan jenis yang dimaksudkan apabila parameter induk ialah null. node-postgres: Pertanyaan

Mengapakah kunci perlu diperoleh sebelum membaca adik-beradik?

Demonstrasi sementara ini tidak mempunyai sesi yang bersaing. Kunci jadual menunjukkan pendekatan yang sengaja dibuat luas untuk jadual kongsi yang kecil: SHARE ROW EXCLUSIVE bercanggah dengan penulisan jadual biasa dan kunci seumpamanya, tetapi membenarkan pembacaan biasa. Memegangnya sepanjang transaksi memastikan keahlian kumpulan adik-beradik kekal stabil semasa pengesahan dan kemas kini. Kunci ini juga menyekat penulisan kepada kumpulan induk yang tidak berkaitan, jadi pertimbangkan pertukaran itu sebelum menggunakannya pada jadual yang sibuk. PostgreSQL: Penguncian Eksplisit

Program ini memilih READ COMMITTED secara jelas. Pada tahap pengasingan itu, pertanyaan adik-beradik melihat perubahan yang telah dikomit pada permulaan pertanyaan, termasuk perubahan yang dikomit sebelum penantian kunci terdahulu selesai. PostgreSQL: BEGIN, PostgreSQL: Pengasingan Transaksi

Pengesahan JavaScript diperiksa menggunakan permutasi yang sah serta ID pendua, tertinggal dan daripada kumpulan lain. Semakan deterministik tersebut berjaya. Contoh pangkalan data disemak berdasarkan dokumentasi tetapi tidak dilaksanakan pada PostgreSQL dalam persekitaran penyelidikan.

Apakah alternatif yang disokong pada PostgreSQL 14?

Pada PostgreSQL 14, wujudkan ungkapan pengumpulan sebagai lajur terjana, kemudian gunakan kekangan unik yang boleh ditangguhkan pada lajur tersebut. Lajur terjana tersimpan dikira apabila baris ditulis dan tidak boleh ditindih dengan nilai biasa. PostgreSQL 14: Lajur Terjana

Contoh SQL kendiri ini menggunakan dua nilai terjana untuk mewakili kumpulan induk. Nilai Boolean membezakan akar daripada ID induk sebenar 0, jadi sandaran COALESCE tidak memerlukan ID yang dikhaskan.

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;

Bagi akar, nilai pengumpulan terjana ialah (true, 0); bagi induk 0, nilainya ialah (false, 0). Setiap induk bukan null mengekalkan nilai integernya sendiri. Oleh itu, kekangan ini menyatakan pengumpulan yang dimaksudkan tanpa bergantung pada pengendalian null PostgreSQL 15. PostgreSQL 14 menyokong kekangan unik yang boleh ditangguhkan pada lajur bernama. PostgreSQL 14: CREATE TABLE

Apakah yang patut anda semak sebelum menyesuaikan jadual sedia ada?

Indeks ungkapan pada COALESCE(parent_id, 0) boleh menguatkuasakan keunikan berasaskan ungkapan, tetapi indeks itu tidak boleh terus dijadikan boleh ditangguhkan atau dilampirkan sebagai kekangan unik biasa. Indeks separa untuk baris akar mempunyai batasan pelampiran yang sama. Membiarkan salah satu objek penguatkuasaan serta-merta itu kekal boleh terus menghalang pertukaran selepas kekangan tertangguh ditambah. PostgreSQL: Indeks pada Ungkapan, PostgreSQL: ALTER TABLE

Sebelum menggantikan takrif sedia ada, periksa pasangan induk-kedudukan yang pendua, terutamanya akar yang sebelum ini dibenarkan oleh keunikan nullable biasa. Penambahan kekangan unik mengesahkan baris sedia ada; NOT VALID tidak tersedia untuk kekangan unik. Selesaikan kedudukan yang samar dengan teliti sebelum mencuba perubahan itu. PostgreSQL: ALTER TABLE

Asingkan jaminan kekangan daripada peraturan susunan aplikasi. Keunikan sahaja membenarkan jurang seperti kedudukan 1, 4 dan 9. Program senarai lengkap sengaja menetapkan kedudukan berturutan bermula pada 1. Semakan keahliannya memastikan setiap adik-beradik disertakan.

Untuk tugas ini, pilih kekangan yang mewakili kumpulan induk nullable, tangguhkannya sebelum kemas kini pertama yang berlanggar, sahkan keseluruhan set adik-beradik yang diminta, dan selesaikan semua kemas kini pada satu klien sebelum mengomit. Gabungan ini menyokong penyusunan semula akar dan anak sambil mengekalkan kedudukan akhir yang unik bagi setiap adik-beradik.

Sumber