В PostgreSQL 15 или новее используйте ограничение UNIQUE NULLS NOT DISTINCT, объявленное как DEFERRABLE, а затем отложите его проверку внутри транзакции переупорядочивания. Это позволяет строкам статей обмениваться позициями, сохраняя уникальность итогового порядка, в том числе для строк, у которых родитель равен NULL. Синтаксис ограничений PostgreSQL

Это руководство предназначено для разработчика, работающего в Mac Terminal и которому нужно изменить порядок искусственных строк статей внутри одной родительской группы. В нём приведены демонстрация SQL, полная программа на node-postgres, проверяющая запрошенный порядок, и альтернатива для PostgreSQL 14. Перемещение статей к другому родителю выходит за рамки этого примера.

Почему nullable-родитель усложняет обеспечение уникальности позиций?

Предположим, что у каждой статьи есть id, допускающий NULL parent_id и положительное целое число position. Статьи с одним и тем же родителем образуют упорядоченную группу соседних элементов. В этой модели все статьи с родителем NULL принадлежат одной корневой группе.

Обычное ограничение UNIQUE (parent_id, position) не обеспечивает это правило для корневой группы. Обычно PostgreSQL считает NULL-значения различными при проверке уникальности, поэтому две корневые статьи могут занимать позицию 1. Добавление NULLS NOT DISTINCT заставляет считать эти значения родителя NULL одинаковыми при проверке составного ключа. У каждого другого ненулевого родителя при этом может быть своя позиция 1. PostgreSQL: ограничения UNIQUE

При переупорядочивании возникает и проблема времени проверки. Если статья 1 занимает позицию 1, а статья 2 — позицию 2, перемещение статьи 2 на позицию 1 временно дублирует позицию статьи 1. Невідкладываемое ограничение уникальности проверяет каждую добавленную или изменённую строку немедленно. Отложимое ограничение может дождаться конца оператора или, если проверка явно отложена, завершения транзакции. PostgreSQL: CREATE TABLE

При переупорядочивании, реализованном несколькими обновлениями, важны оба свойства: проверка с учётом NULL задаёт правильные группы, а отложенная проверка позволяет временно допускать совпадения между обновлениями.

Какое определение использовать в вашей версии PostgreSQL?

В PostgreSQL 15 появилась возможность использовать NULLS NOT DISTINCT в ограничениях и индексах уникальности. Основные примеры ниже требуют версии сервера 15 или новее; для PostgreSQL 14 далее приведён альтернативный вариант. Проверяйте версию подключённого сервера, а не делайте вывод по установленному клиенту командной строки. Примечания к выпуску PostgreSQL 15

Ситуация Поддерживаемый способ
PostgreSQL 15 или новее, новая таблица Именованное ограничение UNIQUE NULLS NOT DISTINCT с DEFERRABLE INITIALLY IMMEDIATE
Существующее обычное ограничение уникальности Замените его определение: ALTER CONSTRAINT не позволяет изменить свойство отложенной проверки у ограничения уникальности
Существующий индекс уникальности с выражением или условием Замените способ обеспечения уникальности: такой индекс нельзя присоединить как обычное ограничение уникальности
PostgreSQL 14 Используйте генерируемые столбцы для группировки с отложимым ограничением уникальности

Ограничения для существующих объектов описаны в ALTER TABLE: таким способом можно менять атрибуты только внешних ключей, а индекс, присоединённый как ограничение уникальности, не может содержать выражения или быть частичным. В отдельной команде CREATE UNIQUE INDEX также нет предложения DEFERRABLE. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Как поменять местами две корневые статьи в SQL?

Понадобится существующая локальная база данных PostgreSQL версии 15 или новее и рабочее подключение psql. Откройте Mac Terminal и запустите клиент с настроенными параметрами подключения. Следующая команда пропускает пользовательские настройки запуска и останавливает выполнение скрипта при ошибке. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Вставьте в эту сессию приведённую ниже полную демонстрацию SQL. Она создаёт временную таблицу только с искусственными статьями; временные таблицы удаляются после завершения сессии. Имя таблицы должно быть свободно в этой сессии. PostgreSQL: временные таблицы

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;

После первого обновления в транзакции временно окажутся два корневых элемента на позиции 1. После второго обновления их позиции снова станут уникальными. Переключение ограничения обратно на IMMEDIATE проверяет ожидающие изменения в этот момент. Если дубликат останется, команда завершится ошибкой; иначе транзакцию можно зафиксировать. Отложенная проверка меняет момент выполнения проверки. PostgreSQL: SET CONSTRAINTS

Ожидаемый итоговый порядок корневых элементов:

Позиция ID статьи Заголовок
1 2 Статья B
2 1 Статья A
3 3 Статья C

Дочерние статьи сохранят свои позиции, поскольку ни одно обновление их не выбирает. Если в интерактивной транзакции возникнет ошибка, перед повторной попыткой выполните ROLLBACK;. Транзакция объединяет связанные операторы до COMMIT или ROLLBACK. PostgreSQL: BEGIN

Как переупорядочить всю группу соседних элементов с помощью node-postgres?

Для всей транзакции используйте один выделенный клиент. node-postgres требует именно этого: отдельные вызовы pool.query() не подходят для транзакции, поскольку могут выполняться через разные подключения. node-postgres: транзакции

Программа ниже принимает полный упорядоченный список ID соседних элементов. Дополнительное правило простое: переданный список должен быть перестановкой текущего набора соседних элементов. Отклонение дубликатов, пропущенных элементов и ID из других групп предотвращает ситуацию, когда частичное переупорядочивание выглядит успешным.

В локальном проекте Node.js установите pg, сохраните приведённую ниже полную программу как reorder.cjs и запустите её. Программа предполагает, что параметры существующего локального подключения доступны через переменные среды PostgreSQL. node-postgres поддерживает те же переменные среды для подключения, что и psql. node-postgres: подключение

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 позволяет фильтровать родителя и при значении NULL, и при ненулевом значении. Обычное сравнение с параметром NULL не выберет корневые элементы. Чтобы вместо них переупорядочить показанные дочерние статьи, измените parentId на 1, а orderedIds на [5, 4]. PostgreSQL: функции и операторы сравнения

Плейсхолдеры вида $1 передают значения отдельно от текста SQL. Явное приведение к целому числу также указывает требуемый тип, когда параметр родителя равен NULL. node-postgres: запросы

Зачем блокировать таблицу до чтения соседних элементов?

Временная демонстрация не предполагает конкурирующих сессий. Блокировка таблицы иллюстрирует намеренно широкий подход для небольшой общей таблицы: SHARE ROW EXCLUSIVE конфликтует с обычными записями в таблицу и с такой же блокировкой, но позволяет выполнять обычное чтение. Удержание блокировки до конца транзакции сохраняет состав группы соседних элементов неизменным во время проверки и обновлений. При этом блокируются записи и в другие родительские группы, поэтому оцените этот компромисс перед использованием подхода на загруженной таблице. PostgreSQL: явные блокировки

В программе явно выбирается уровень изоляции READ COMMITTED. При таком уровне запрос соседних элементов видит изменения, зафиксированные к моменту начала этого запроса, включая изменения, зафиксированные до завершения ожидания предшествующей блокировки. PostgreSQL: BEGIN, PostgreSQL: изоляция транзакций

Проверка JavaScript прошла с корректной перестановкой, а также с дубликатами, пропущенными ID и ID из другой группы. Эти детерминированные проверки завершились успешно. Примеры для базы данных сверялись с документацией, но в исследовательской среде не выполнялись в PostgreSQL.

Какая альтернатива поддерживается в PostgreSQL 14?

В PostgreSQL 14 материализуйте выражение группировки в генерируемых столбцах, а затем примените к этим столбцам отложимое ограничение уникальности. Сохранённые генерируемые столбцы вычисляются при записи строк, и их значения нельзя переопределить обычными данными. PostgreSQL 14: генерируемые столбцы

В этом отдельном примере SQL для представления родительской группы используются два генерируемых значения. Логическое значение отличает корневые элементы от настоящего ID родителя 0, поэтому резервировать ID для значения подстановки COALESCE не требуется.

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;

Для корневого элемента генерируемые значения группировки равны (true, 0); для родителя 0 они равны (false, 0). Каждому ненулевому родителю соответствует собственное целочисленное значение. Таким образом, ограничение задаёт требуемую группировку, не завися от обработки NULL в PostgreSQL 15. PostgreSQL 14 поддерживает отложимые ограничения уникальности для именованных столбцов. PostgreSQL 14: CREATE TABLE

Что проверить перед адаптацией существующей таблицы?

Индекс-выражение по COALESCE(parent_id, 0) может обеспечивать уникальность на основе выражения, но его нельзя просто сделать отложимым или присоединить как обычное ограничение уникальности. Частичный индекс для корневых строк имеет то же ограничение при присоединении. Если оставить любой из этих объектов немедленной проверки, он всё ещё может помешать перестановке после добавления отложимого ограничения. PostgreSQL: индексы по выражениям, PostgreSQL: ALTER TABLE

Перед заменой существующего определения проверьте пары «родитель — позиция» на дубликаты, особенно для корневых элементов, которые ранее допускались из-за обычной обработки NULL в ограничении уникальности. Добавление ограничения уникальности проверяет существующие строки; для ограничений уникальности режим NOT VALID недоступен. Намеренно устраните неоднозначность позиций до внесения изменений. PostgreSQL: ALTER TABLE

Отделяйте гарантию ограничения от правила упорядочивания в приложении. Одна лишь уникальность допускает пропуски, например позиции 1, 4 и 9. Программа, принимающая полный список, намеренно назначает последовательные позиции начиная с 1. Именно проверка состава гарантирует, что участвуют все соседние элементы.

Для этой задачи выберите ограничение, которое представляет группы с nullable-родителями, отложите его проверку до первого обновления с совпадением, проверьте полный набор запрошенных соседних элементов и выполните все обновления через один клиент до фиксации транзакции. Такое сочетание позволяет переупорядочивать корневые и дочерние элементы, сохраняя уникальность итоговой позиции каждого соседнего элемента.

Источники