Verwenden Sie eine begrenzte rekursive Abfrage in einem AFTER STATEMENT-Trigger, um Kategorien abzulehnen, die tiefer als das zulässige Limit liegen. Verlangen Sie serialisierbare Schreibtransaktionen und wiederholen Sie Transaktionen nach Serialisierungsfehlern, damit durch gleichzeitige Änderungen keine ungültige Hierarchie entsteht.

Dieses Tutorial richtet sich an Entwickler, die auf einem Mac im Terminal mit einer lokalen PostgreSQL-Datenbank arbeiten. Die konkrete Aufgabe besteht darin, maximal drei Kategorieebenen zu erzwingen, auch wenn ein bestehender Teilbaum verschoben wird. Alle Beispiele verwenden synthetische Artikelkategorien.

Genau festlegen, was das Tiefenlimit bedeutet

Hier hat eine Wurzelkategorie die Tiefe 1. Ihr Kind hat die Tiefe 2, und ihr Enkel die Tiefe 3. Eine vierte Ebene ist ungültig. Wenn Ihre Anwendung Wurzeln als Tiefe null zählt, rechnen Sie diese Konvention um, bevor Sie das Limit festlegen.

Die Tabelle verwendet eine Adjazenzliste: Jede Kategorie speichert eine optionale parent_id, die auf eine andere Kategorie verweist. Ein Fremdschlüssel stellt sicher, dass ein referenzierter Elternknoten existiert, aber für die Tiefenregel müssen mehrere Zeilen untersucht werden. PostgreSQL unterstützt ausdrücklich keine CHECK-Einschränkung, die von anderen Zeilen abhängt und als fortlaufend aufrechterhaltene Garantie dient. Die Traversierung in eine von CHECK aufgerufene Funktion zu legen, hebt diese Einschränkung nicht auf. Einschränkungen.

Die folgende Implementierung zielt auf PostgreSQL 18, die zum Zeitpunkt der Erstellung dieses Artikels abgerufene aktuelle stabile Dokumentation. Verwenden Sie einen installierten PostgreSQL-18-Server und -Client sowie Node.js 24 und npm für das JavaScript-Beispiel. Für frühere PostgreSQL-Versionen wird in diesem Tutorial keine Kompatibilität zugesichert. Die node-postgres-Dokumentation führt Node.js 24 unter den kompatiblen Versionen auf. PostgreSQL-18-Dokumentation, Kompatibilität von node-postgres.

Warum die gesamte Hierarchie nach einer Anweisung prüfen?

Nur die Vorfahren des neuen Elternknotens zu prüfen, reicht bei einer Teilbaumverschiebung nicht aus. Angenommen, Articles → Guides → Editing umfasst bereits drei Ebenen. Wird Articles unter eine andere Wurzel verschoben, wird Editing zu einer Kategorie der vierten Ebene, obwohl die verschobene Zeile selbst nur Tiefe zwei erreicht.

Die Entscheidungsregel für eine geplante Verschiebung lautet: Neue Tiefe der Teilbaumwurzel + Höhe des Teilbaums darf das Maximum nicht überschreiten, wobei die Höhe die Kanten von der Teilbaumwurzel bis zu ihrem tiefsten Nachfahren zählt. Ergibt beispielsweise eine neue Wurzeltiefe von 2 plus eine Teilbaumhöhe von 2 eine tiefste Tiefe von 4.

Anstatt separate Berechnungen für Vorfahren und Nachfahren zu pflegen, prüft diese Implementierung nach jeder ändernden Anweisung jede Kategorie. PostgreSQL dokumentiert, dass ein AFTER-Trigger die durch die Operation vorgenommenen Änderungen sieht und ein Statement-Trigger einmal pro Anweisung ausgeführt wird. Das unterstützt auch mehrzeilige Einfügungen und Aktualisierungen, ohne sich auf die Verarbeitungsreihenfolge der Zeilen zu verlassen. CREATE TRIGGER.

Die begrenzte Traversierung beginnt bei jeder Kategorie und folgt den Verweisen auf Elternknoten. Werden in einem Aufwärtslauf vier Knoten gefunden, ist damit bewiesen, dass die Drei-Ebenen-Regel verletzt wird. Auch ein Zyklus erzeugt einen Lauf mit vier Knoten, weil er keine Wurzel erreicht. Dieselbe Begrenzung weist daher übermäßige Tiefe und Zyklen zurück, ohne eine unbegrenzte rekursive Suche auszuführen. Dieses Design leitet sich aus der dokumentierten Auswertung rekursiver CTEs in PostgreSQL ab. WITH-Abfragen.

Tabelle und Tiefen-Trigger erstellen

Speichern Sie die folgende vollständige Einrichtung in Ihrem Mac-Texteditor als category-depth.sql. Sie erstellt ein neues Schema. Führen Sie sie daher einmal in einer lokalen Entwicklungsdatenbank aus, in der dieses Schema noch nicht existiert. Vorhandene Tabellen werden weder gelöscht noch ersetzt.

Die Triggerfunktion gibt trigger zurück, wie es für einen PL/pgSQL-Trigger zur Datenänderung erforderlich ist. Sie verwendet RAISE EXCEPTION mit expliziten Fehlercodes, um eine ungültige Hierarchie von einer falschen Transaktionseinstellung zu unterscheiden. Triggerfunktionen, Fehler und Meldungen.

BEGIN ISOLATION LEVEL SERIALIZABLE;

CREATE SCHEMA category_depth_demo;

CREATE TABLE category_depth_demo.category (
    id integer PRIMARY KEY,
    name text NOT NULL,
    parent_id integer REFERENCES category_depth_demo.category(id),
    CONSTRAINT category_not_own_parent
        CHECK (parent_id IS NULL OR parent_id <> id)
);

CREATE INDEX category_parent_idx
    ON category_depth_demo.category(parent_id);

CREATE FUNCTION category_depth_demo.enforce_depth()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    max_levels CONSTANT integer := 3;
    bad_id integer;
BEGIN
    IF current_setting('transaction_isolation') <> 'serializable' THEN
        RAISE EXCEPTION
            'Category writes require a SERIALIZABLE transaction'
            USING ERRCODE = '25000';
    END IF;

    WITH RECURSIVE walk(origin_id, parent_id, levels) AS (
        SELECT id, parent_id, 1
        FROM category_depth_demo.category

        UNION ALL

        SELECT w.origin_id, p.parent_id, w.levels + 1
        FROM walk AS w
        JOIN category_depth_demo.category AS p
          ON p.id = w.parent_id
        WHERE w.levels <= max_levels
    )
    SELECT origin_id INTO bad_id
    FROM walk
    WHERE levels = max_levels + 1
    LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION
            'Category % exceeds % levels or belongs to a cycle',
            bad_id, max_levels
            USING ERRCODE = '23514';
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER category_depth_guard
AFTER INSERT OR UPDATE OR DELETE
ON category_depth_demo.category
FOR EACH STATEMENT
EXECUTE FUNCTION category_depth_demo.enforce_depth();

INSERT INTO category_depth_demo.category(id, name, parent_id)
VALUES
    (1, 'Articles', NULL),
    (2, 'Guides', 1),
    (3, 'Editing', 2),
    (4, 'Reference', NULL),
    (10, 'Notes', NULL),
    (11, 'Examples', NULL);

COMMIT;

Die Funktion liest die Isolationsstufe der Transaktion mithilfe der dokumentierten PostgreSQL-Funktion current_setting aus. Die einzelne Konstante max_levels steuert sowohl die Rekursionsgrenze als auch den Ablehnungsschwellenwert. Funktionen für Konfigurationseinstellungen.

Öffnen Sie Terminal, wechseln Sie in das Verzeichnis mit der Datei und führen Sie den folgenden Befehl aus. Er setzt voraus, dass Ihre vorhandenen lokalen Verbindungseinstellungen eine Verbindung zur Datenbank postgres auf localhost herstellen können. Die Option -X überspringt persönliche psql-Startdateien; ON_ERROR_STOP beendet die Dateiverarbeitung bei einem Fehler. psql.

psql -X -h localhost -d postgres -v ON_ERROR_STOP=1 -f category-depth.sql

Der Fremdschlüssel behält sein standardmäßiges Löschverhalten bei: Das Löschen einer Kategorie, die noch übergeordneter Knoten für Kinder ist, wird abgelehnt, anstatt ihren Teilbaum stillschweigend zu löschen. Der Elternindex unterstützt Suchvorgänge nach parent_id; er macht die vollständige Tabellenprüfung des Triggers nicht überflüssig. PostgreSQL erstellt nicht automatisch einen Index auf der referenzierenden Seite eines Fremdschlüssels. Fremdschlüssel.

Einfügungen, Teilbaumverschiebungen und Zyklen prüfen

Bevor Sie eine Anwendung verbinden, prüfen Sie anhand der vorbereiteten Daten die Grenzfälle. Jede der folgenden Operationen setzt voraus, dass die ursprüngliche Einrichtung unverändert geblieben ist.

Operation Erwartetes Ergebnis Grund
Kategorie 5 unter Kategorie 3 einfügen Ablehnen Die neue Kategorie läge auf Ebene 4.
Kategorie 1 unter Kategorie 4 verschieben Ablehnen Kategorie 3 würde auf Ebene 4 verschoben.
Kategorie 1 unter Kategorie 3 verschieben Ablehnen Die Verschiebung erzeugt einen Zyklus.
Kategorie 3 unter Kategorie 4 verschieben Akzeptieren Kategorie 3 wird zu einer Kategorie auf Ebene 2.
Einen Namen in einer Standardtransaktion mit Read Committed aktualisieren Ablehnen Diese Implementierung verlangt serialisierbare Transaktionen für jeden Kategorieschreibvorgang.

Starten Sie für einen konkreten Teilbaumtest eine interaktive Sitzung mit psql -X -h localhost -d postgres und führen Sie anschließend diesen Block aus. Die Aktualisierung sollte den Tiefenfehler auslösen. ROLLBACK beendet die fehlgeschlagene Transaktion und lässt die ursprüngliche Hierarchie intakt.

BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE category_depth_demo.category
SET parent_id = 4
WHERE id = 1;

ROLLBACK;

SELECT id, name, parent_id
FROM category_depth_demo.category
ORDER BY id;

Die Traversierung meldet übermäßige Tiefe und Zyklen bewusst gemeinsam. Ihre Aufgabe ist es, die Invariante durchzusetzen; sie ermittelt nicht den genauen Eintrittspunkt eines Zyklus. Eine Diagnoseabfrage könnte besuchte IDs separat nachverfolgen, doch für diese begrenzte Annahme-oder-Ablehnung-Entscheidung ist das unnötig.

Gleichzeitige Schreibvorgänge derselben Regel unterwerfen

Ein rekursiver Trigger allein löst das Nebenläufigkeitsproblem nicht. Zwei Transaktionen können jeweils eine Hierarchie prüfen, ohne die nicht bestätigte Änderung der jeweils anderen Transaktion zu sehen. Die Dokumentation zur Konsistenz in PostgreSQL beschreibt dieses Problem und empfiehlt serialisierbare Transaktionen als eine unterstützte Methode, Regeln durchzusetzen, die mehrere Zeilen betreffen. Konsistenzprüfungen auf Anwendungsebene.

Dieses Design verlangt deshalb für jeden Schreibvorgang an Kategorien die serialisierbare Isolationsstufe. Erfolgreiche serialisierbare Transaktionen verhalten sich so, als wären sie nacheinander ausgeführt worden. PostgreSQL kann eine Transaktion ablehnen, wenn gleichzeitige Aktivitäten eine solche Reihenfolge verhindern würden. Eine erfolgreiche Aktualisierung ist daher erst nach dem Commit ihrer Transaktion endgültig. Transaktionsisolation.

Sie können das Problem anhand der beiden synthetischen Wurzeln 10 und 11 demonstrieren. Führen Sie in zwei interaktiven psql-Sitzungen die Aktualisierung jeder Sitzung aus, bevor Sie in einer der beiden Sitzungen einen Commit ausführen.

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 11
WHERE id = 10;

-- Session B, in a separate psql session
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 10
WHERE id = 11;

Führen Sie anschließend in jeder Sitzung COMMIT aus. Die beiden gegensätzlichen Änderungen der Elternknoten dürfen nicht beide bestätigt werden: Zusammen bilden sie einen Zyklus. Ein Serialisierungsfehler kann während einer Anweisung oder beim Commit auftreten. Wird eine Änderung bestätigt, bevor die andere Sitzung ihre Aktualisierung ausführt, kann der Trigger den nun sichtbaren Zyklus stattdessen direkt ablehnen. Dies sind erwartete Folgen der Kombination aus der Validierungsabfrage und serialisierbarer Ausführung. Transaktionsisolation.

Eine Kategorie mit node-postgres verschieben

Führen Sie in einem Projektverzeichnis npm init -y aus, falls Sie noch keine Paketdatei haben, und installieren Sie anschließend den Treiber mit npm install pg. Speichern Sie das vollständige Programm unten als move-category.mjs. Das dokumentierte Installationspaket des Treibers heißt pg. node-postgres.

Das Programm stellt eine Verbindung zu derselben lokalen Datenbank her wie der psql-Befehl. Die übrigen Verbindungsfelder verwenden die umgebungsbasierten Standardwerte des Treibers. Alle Transaktionsanweisungen verwenden denselben ausgecheckten Client, wie es node-postgres verlangt; Transaktionsanweisungen dürfen nicht auf mehrere separate pool.query-Aufrufe verteilt werden. Verbindung herstellen, Transaktionen.

import pg from 'pg';
import { setTimeout as delay } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({
  host: 'localhost',
  database: 'postgres',
  connectionTimeoutMillis: 5000
});

pool.on('error', (error) => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

async function moveCategory(id, parentId) {
  const maxAttempts = 5;

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    const client = await pool.connect();
    let discard = false;
    let retry = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

      const result = await client.query(
        `UPDATE category_depth_demo.category
         SET parent_id = $1
         WHERE id = $2
         RETURNING id, name, parent_id`,
        [parentId, id]
      );

      if (result.rowCount !== 1) {
        throw new Error(`Category ${id} does not exist`);
      }

      await client.query('COMMIT');
      return result.rows[0];
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch {
        discard = true;
      }

      retry = !discard && error.code === '40001'
        && attempt < maxAttempts;

      if (!retry) throw error;
    } finally {
      client.release(discard);
    }

    if (retry) await delay(25 * attempt);
  }
}

try {
  const moved = await moveCategory(3, 4);
  console.log(moved);
} catch (error) {
  console.error(error.code ?? 'APP_ERROR', error.message);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Führen Sie das Programm mit node move-category.mjs aus. Mit den ursprünglichen Ausgangsdaten wird Kategorie 3 unter Kategorie 4 verschoben, und die zurückgegebene Zeile enthält parent_id: 4. Die Abfrage übergibt Werte mit $1 und $2 entsprechend der dokumentierten Parameterschnittstelle des Treibers. Abfragen.

Das Programm gibt den Client in finally zurück, verwirft ihn, falls der Rollback fehlschlägt, und schließt den Pool, sobald das Skript beendet ist. Diese Lebenszyklusoperationen entsprechen der dokumentierten Pool-API. pg.Pool.

Wiederholen Sie bei SQLSTATE 40001 die gesamte Transaktion, einschließlich aller Lesevorgänge, die die Verschiebung bestimmen. Den Tiefenfehler 23514 des Triggers dürfen Sie nicht automatisch wiederholen: Die geplante Änderung muss korrigiert werden. Das Limit von fünf Versuchen und die kurze, schrittweise verlängerte Verzögerung sind beispielhafte Entscheidungen für die Anwendung. Sie sind weder Anforderungen noch Garantien für einen letztlich erfolgreichen Abschluss durch PostgreSQL. Behandlung von Serialisierungsfehlern.

Vor der Anpassung des Beispiels die Grenzen verstehen

Diese Implementierung prüft die Hierarchie nach jeder Anweisung, auch nach Aktualisierungen, die nur Namen ändern. Eine Transaktion kann nicht in einer Anweisung vorübergehend eine übermäßige Tiefe erzeugen und diese in einer späteren Anweisung korrigieren. Planen Sie jede Anweisung so, dass sie eine gültige Hierarchie hinterlässt. PostgreSQL unterstützt aufschiebbare Constraint-Trigger, sie müssen jedoch AFTER ROW-Trigger sein; dieser Statement-Trigger lässt sich nicht einfach als aufschiebbar markieren. CREATE TRIGGER.

Der Kompromiss sind vorhersehbare Prüfkosten: Bei einem Limit von drei enthält die rekursive Relation höchstens vier Laufzeilen pro Kategorie. Die tatsächlichen Ausführungskosten hängen jedoch vom Abfrageplan und den Datenbankinhalten ab. Bei großen Tabellen oder häufigen kleinen Schreibvorgängen sollten Sie die Kosten messen, bevor Sie einen Scan der gesamten Tabelle einsetzen. Wenn Sie ihn durch eine Prüfung des betroffenen Teilbaums ersetzen, müssen Sie weiterhin die Prüfung der Nachfahren, die Ablehnung von Zyklen und die Argumentation zur Nebenläufigkeit gewährleisten.

Bei einer bestehenden Tabelle prüft das Einrichten eines Triggers die gespeicherten Zeilen nicht automatisch. Halten Sie gewöhnliche Schreibvorgänge an und verwenden Sie die rekursive Abfrage aus der Funktion als eigenständige Prüfung. Ersetzen Sie dabei max_levels durch 3 und wählen Sie jede passende origin_id aus. Beheben Sie gemeldete Pfade und prüfen Sie separat die Verweise auf Elternknoten, bevor Sie den Schreibpfad aktivieren.

Die nützliche Invariante ist einfach: Jede Kategorie muss innerhalb von drei Knoten eine Wurzel erreichen. Die begrenzte Traversierung prüft diese Bedingung bei Teilbaumverschiebungen und Zyklen; serialisierbare Transaktionen und vollständige Wiederholungen erhalten sie auch bei gleichzeitigen Aktualisierungen. Behalten Sie diese Komponenten gemeinsam bei, wenn Sie das Beispiel an Ihre Kategorientabelle anpassen.

Quellen