Verwenden Sie PostgreSQL 15 oder höher eine als UNIQUE NULLS NOT DISTINCT deklarierte DEFERRABLE und verschieben Sie ihre Prüfung anschließend innerhalb der Umordnungs-Transaktion. So können Artikelzeilen ihre Positionen tauschen, während eine eindeutige endgültige Reihenfolge gewährleistet bleibt – auch für Zeilen, deren Elternwert NULL ist. PostgreSQL-Constraint-Syntax

Dieses Tutorial richtet sich an Entwickler, die im Mac-Terminal synthetische Artikelzeilen innerhalb einer Elterngruppe neu ordnen müssen. Es enthält eine SQL-Demonstration, ein vollständiges node-postgres-Programm, das die angeforderte Reihenfolge validiert, sowie eine Alternative für PostgreSQL 14. Das Verschieben von Artikeln in eine andere Elterngruppe ist nicht Gegenstand dieses Beispiels.

Warum erschwert ein NULL-fähiger Elternwert die Eindeutigkeit von Positionen?

Angenommen, jeder Artikel hat eine id, eine NULL-fähige parent_id und eine positive Ganzzahl position. Artikel mit demselben Elternwert bilden eine geordnete Geschwistergruppe. In diesem Modell gehören alle Artikel mit einem NULL-Elternwert zu einer einzigen Stammgruppe.

Ein einfaches UNIQUE (parent_id, position) setzt diese Regel für die Stammgruppe nicht durch. PostgreSQL behandelt NULL-Werte bei der Eindeutigkeitsprüfung standardmäßig als verschieden, sodass zwei Stammartikel beide die Position 1 belegen können. NULLS NOT DISTINCT bewirkt, dass diese NULL-Elternwerte beim Prüfen des zusammengesetzten Schlüssels als gleich gelten. Verschiedene Nicht-NULL-Elternwerte können weiterhin jeweils ihre eigene Position 1 haben. PostgreSQL: Eindeutige Constraints

Beim Neuordnen entsteht ein separates Timing-Problem. Wenn Artikel 1 die Position 1 und Artikel 2 die Position 2 belegt, wird durch das Verschieben von Artikel 2 auf Position 1 vorübergehend die Position von Artikel 1 doppelt belegt. Eine nicht aufschiebbare Eindeutigkeits-Constraint prüft jede eingefügte oder geänderte Zeile sofort. Eine aufschiebbare Constraint kann die Prüfung bis zum Ende der Anweisung oder – wenn sie ausdrücklich aufgeschoben wird – bis zum Ende der Transaktion warten lassen. PostgreSQL: CREATE TABLE

Bei einer Neuordnung, die mit mehreren Updates umgesetzt wird, sind beide Eigenschaften wichtig: Die NULL-bewusste Eindeutigkeitsprüfung definiert die richtigen Gruppen, und die aufgeschobene Prüfung erlaubt vorübergehende Kollisionen zwischen den Updates.

Welche Definition sollten Sie für Ihre PostgreSQL-Version verwenden?

PostgreSQL 15 führte NULLS NOT DISTINCT für eindeutige Constraints und Indizes ein. Die Hauptbeispiele unten erfordern Serverversion 15 oder höher; für PostgreSQL 14 ist weiter unten eine Alternative angegeben. Prüfen Sie die Version des verbundenen Servers, statt sie aus der installierten Befehlszeilen-Clientversion abzuleiten. PostgreSQL-15-Versionshinweise

Situation Unterstützter Ansatz
PostgreSQL 15 oder höher, neue Tabelle Benannte UNIQUE NULLS NOT DISTINCT-Constraint mit DEFERRABLE INITIALLY IMMEDIATE
Vorhandene gewöhnliche Eindeutigkeits-Constraint Definition ersetzen; ALTER CONSTRAINT kann die Aufschiebbarkeit einer Eindeutigkeits-Constraint nicht ändern
Vorhandener Ausdrucks- oder partieller eindeutiger Index Durchsetzungsmethode ersetzen; er lässt sich nicht als gewöhnliche Eindeutigkeits-Constraint anhängen
PostgreSQL 14 Generierte Gruppierungsspalten mit einer aufschiebbaren Eindeutigkeits-Constraint verwenden

Die Einschränkungen für vorhandene Objekte sind in ALTER TABLE dokumentiert: Das Ändern von Constraint-Attributen auf diese Weise ist auf Fremdschlüssel beschränkt, und ein als Eindeutigkeits-Constraint angehängter Index darf keine Ausdrücke enthalten und nicht partiell sein. Ein eigenständiges CREATE UNIQUE INDEX besitzt außerdem keine DEFERRABLE-Klausel. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Wie tauschen Sie zwei Stammartikel in SQL?

Voraussetzung ist eine vorhandene lokale PostgreSQL-Datenbank ab Version 15 sowie eine funktionierende psql-Verbindung. Öffnen Sie das Mac-Terminal und starten Sie den Client mit Ihren konfigurierten Verbindungseinstellungen. Der folgende Befehl überspringt Startanpassungen und beendet die Skriptverarbeitung bei einem Fehler. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Fügen Sie die vollständige SQL-Demonstration unten in diese Sitzung ein. Sie erstellt eine temporäre Tabelle, die ausschließlich synthetische Artikel enthält; temporäre Tabellen verschwinden, wenn die Sitzung endet. Der Tabellenname muss in dieser Sitzung unbenutzt sein. PostgreSQL: Temporäre Tabellen

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;

Nach dem ersten Update enthält die Transaktion vorübergehend zwei Stammartikel an Position 1. Nach dem zweiten Update sind ihre Positionen wieder eindeutig. Wenn Sie die Constraint wieder auf IMMEDIATE setzen, werden zu diesem Zeitpunkt noch nicht geprüfte Änderungen validiert. Falls ein Duplikat bestehen bleibt, schlägt dieser Befehl fehl; andernfalls kann die Transaktion festgeschrieben werden. Das Aufschieben ändert den Zeitpunkt der Validierung. PostgreSQL: SET CONSTRAINTS

Die erwartete endgültige Reihenfolge der Stammartikel ist:

Position Artikel-ID Titel
1 2 Artikel B
2 1 Artikel A
3 3 Artikel C

Die Positionen der untergeordneten Artikel bleiben unverändert, weil sie von keinem der beiden Updates ausgewählt werden. Wenn in einer interaktiven Transaktion ein Fehler auftritt, führen Sie vor einem erneuten Versuch ROLLBACK; aus. Eine Transaktion fasst die zusammengehörigen Anweisungen bis zu COMMIT oder ROLLBACK zusammen. PostgreSQL: BEGIN

Wie ordnen Sie mit node-postgres eine vollständige Geschwistergruppe neu?

Verwenden Sie für die gesamte Transaktion einen einzigen ausgecheckten Client. node-postgres verlangt dies ausdrücklich: Separate Aufrufe von pool.query() eignen sich nicht für eine Transaktion, weil sie auf unterschiedlichen Verbindungen ausgeführt werden können. node-postgres: Transaktionen

Das folgende Programm nimmt eine vollständige geordnete Liste von Geschwister-IDs entgegen. Die zusätzliche Regel ist einfach: Die übergebene Liste muss eine Permutation der aktuellen Geschwistermenge sein. Durch das Zurückweisen von Duplikaten, ausgelassenen IDs und IDs aus anderen Gruppen wird verhindert, dass eine scheinbar erfolgreiche, aber unvollständige Neuordnung erfolgt.

Installieren Sie in einem lokalen Node.js-Projekt pg, speichern Sie das folgende vollständige Programm als reorder.cjs und führen Sie es aus. Das Programm setzt voraus, dass Ihre vorhandenen lokalen Verbindungseinstellungen über PostgreSQL-Umgebungsvariablen verfügbar sind. node-postgres unterstützt dieselben Verbindungsumgebungsvariablen wie psql. node-postgres: Verbindung herstellen

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 sorgt dafür, dass der Filter für den Elternwert sowohl bei NULL als auch bei Nicht-NULL funktioniert. Ein gewöhnlicher Vergleich mit einem NULL-Parameter wählt die Stammartikel nicht aus. Um stattdessen die Beispiel-Unterartikel neu zu ordnen, ändern Sie parentId in 1 und orderedIds in [5, 4]. PostgreSQL: Vergleichsfunktionen und -operatoren

Die Platzhalter im Format $1 übergeben Werte getrennt vom SQL-Text. Der explizite Integer-Cast gibt außerdem den beabsichtigten Typ an, wenn der Elternparameter NULL ist. node-postgres: Abfragen

Warum sollte die Sperre vor dem Lesen der Geschwister gesetzt werden?

Die temporäre Demonstration hat keine konkurrierenden Sitzungen. Die Tabellensperre veranschaulicht einen bewusst weit gefassten Ansatz für eine kleine gemeinsam genutzte Tabelle: SHARE ROW EXCLUSIVE steht im Konflikt mit normalen Tabellenschreibvorgängen und mit sich selbst, lässt gewöhnliche Lesevorgänge aber zu. Wenn die Sperre während der gesamten Transaktion gehalten wird, bleibt die Geschwisterzugehörigkeit während der Validierung und der Updates stabil. Sie blockiert auch Schreibvorgänge für andere Elterngruppen. Prüfen Sie diesen Kompromiss, bevor Sie die Sperre auf einer stark ausgelasteten Tabelle einsetzen. PostgreSQL: Explizite Sperrung

Das Programm wählt ausdrücklich READ COMMITTED. Bei dieser Isolationsstufe sieht die Geschwisterabfrage die festgeschriebenen Änderungen zum Startzeitpunkt der Abfrage, einschließlich Änderungen, die festgeschrieben wurden, bevor eine vorangehende Wartezeit auf eine Sperre endet. PostgreSQL: BEGIN, PostgreSQL: Transaktionsisolation

Die JavaScript-Validierung wurde mit einer gültigen Permutation sowie mit doppelten, fehlenden und zu einer fremden Gruppe gehörenden IDs geprüft. Diese deterministischen Prüfungen waren erfolgreich. Die Datenbankbeispiele wurden anhand der Dokumentation geprüft, aber in der Rechercheumgebung nicht auf PostgreSQL ausgeführt.

Welche unterstützte Alternative gibt es für PostgreSQL 14?

Verwenden Sie in PostgreSQL 14 generierte Spalten, um den Gruppierungsausdruck abzubilden, und wenden Sie anschließend auf diese Spalten eine aufschiebbare Eindeutigkeits-Constraint an. Gespeicherte generierte Spalten werden beim Schreiben von Zeilen berechnet und können nicht mit gewöhnlichen Werten überschrieben werden. PostgreSQL 14: Generierte Spalten

Dieses eigenständige SQL-Beispiel verwendet zwei generierte Werte, um die Elterngruppe darzustellen. Der boolesche Wert unterscheidet Stammartikel von einer tatsächlichen Eltern-ID 0, sodass für den Fallback COALESCE keine ID reserviert werden muss.

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;

Für einen Stammartikel lauten die generierten Gruppierungswerte (true, 0); für den Elternwert 0 lauten sie (false, 0). Jeder Nicht-NULL-Elternwert behält seinen eigenen Integerwert. Die Constraint bildet somit die beabsichtigte Gruppierung ab, ohne von der NULL-Behandlung in PostgreSQL 15 abhängig zu sein. PostgreSQL 14 unterstützt aufschiebbare Eindeutigkeits-Constraints für benannte Spalten. PostgreSQL 14: CREATE TABLE

Was sollten Sie prüfen, bevor Sie eine vorhandene Tabelle anpassen?

Ein Ausdrucksindex auf COALESCE(parent_id, 0) kann eine ausdrucksbasierte Eindeutigkeit erzwingen, lässt sich aber nicht einfach aufschiebbar machen oder als gewöhnliche Eindeutigkeits-Constraint anhängen. Für einen partiellen Index auf Stammzeilen gilt dieselbe Einschränkung. Wenn eine der beiden sofort prüfenden Durchsetzungsmethoden bestehen bleibt, kann sie einen Tausch auch nach dem Hinzufügen einer aufschiebbaren Constraint weiterhin verhindern. PostgreSQL: Indizes auf Ausdrücken, PostgreSQL: ALTER TABLE

Prüfen Sie vor dem Ersetzen einer vorhandenen Definition auf doppelte Elternwert-Positions-Paare, insbesondere bei Stammartikeln, die aufgrund der gewöhnlichen NULL-fähigen Eindeutigkeitsprüfung bisher zulässig waren. Beim Hinzufügen einer Eindeutigkeits-Constraint werden vorhandene Zeilen validiert; NOT VALID ist für Eindeutigkeits-Constraints nicht verfügbar. Bereinigen Sie mehrdeutige Positionen gezielt, bevor Sie die Änderung versuchen. PostgreSQL: ALTER TABLE

Trennen Sie das Versprechen der Constraint von der Ordnungsregel der Anwendung. Eindeutigkeit allein lässt Lücken zu, etwa die Positionen 1, 4 und 9. Das Programm mit der vollständigen Liste weist bewusst fortlaufende Positionen ab 1 zu. Durch die Mitgliedschaftsprüfung wird sichergestellt, dass alle Geschwister berücksichtigt werden.

Wählen Sie für diese Aufgabe eine Constraint, die NULL-fähige Elterngruppen abbildet, schieben Sie ihre Prüfung vor dem ersten kollidierenden Update auf, validieren Sie die vollständige angeforderte Geschwistermenge und führen Sie alle Updates auf einem Client aus, bevor Sie die Transaktion festschreiben. Diese Kombination ermöglicht Neuordnungen von Stamm- und Unterartikeln und gewährleistet zugleich eine eindeutige endgültige Position für jedes Geschwister.

Quellen