Common Table Expressions und rekursive Abfragen mit WITH

Worum es geht

WITH name AS (SELECT ...) definiert ein temporäres, benanntes Ergebnis für die Hauptabfrage; eine Anweisung kann mehrere definieren, wobei jede sich auf vorangehende beziehen kann. Die PostgreSQL-Dokumentation beschreibt die rekursive Form: WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) wertet den nicht-rekursiven Term aus, legt dessen Zeilen in eine Arbeitstabelle, und wertet dann wiederholt den rekursiven Term aus, wobei die Arbeitstabelle den Selbstbezug ersetzt, bis keine Zeilen mehr zurückkommen. UNION verwirft doppelte Zeilen, UNION ALL behält sie. Dieselbe Seite dokumentiert SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col, um die Ausgabe zu ordnen, sowie CYCLE col SET is_cycle USING path, um bei Schleifen zu stoppen, und hält fest, dass eine nicht-rekursive, nebenwirkungsfreie WITH-Abfrage, die einmal referenziert wird, standardmässig in die übergeordnete Abfrage eingefaltet wird, während MATERIALIZED eine separate Auswertung erzwingt und NOT MATERIALIZED das Einfalten erzwingt.

Warum es wichtig ist

Hierarchien (Kategorien, Organigramme, verschachtelte Kommentare, Stücklistenauflösungen) passen nicht in eine feste Anzahl Joins. Ein rekursiver CTE bewältigt beliebige Tiefe in einer einzigen Anweisung; nicht-rekursive CTEs zerlegen eine lange Abfrage in benannte Schritte, die sich einzeln lesen und testen lassen.

So wird es angewendet

• Vorfahrenkette: bei dem Knoten beginnen (SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1) und den rekursiven Term über parent_id verknüpfen; Nachkommen laufen in die andere Richtung. depth mitführen und begrenzen (WHERE depth < 50) als Schutz gegen unerwartete Schleifen.
• Bei Graphen, die Zyklen enthalten können, UNION oder die CYCLE-Klausel verwenden; UNION ALL terminiert bei einem Zyklus ohne Begrenzung nie.
• SEARCH DEPTH FIRST BY name SET ord zusammen mit ORDER BY ord verwenden, um einen Baum in verschachtelter Reihenfolge auszugeben.
• Datenverändernde CTEs verschieben Zeilen in einer einzigen Anweisung: WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• Ein CTE, der mehr als einmal referenziert wird, wird standardmässig materialisiert; MATERIALIZED bei einem nur einmal verwendeten CTE nur schreiben, um eine separate Auswertung zu erzwingen (eine teure Funktion, die einmal berechnet wird), und NOT MATERIALIZED bei einem mehrfach referenzierten, wenn jede Verwendung nur einen kleinen, indexfreundlichen Ausschnitt benötigt, wie das big_table-Beispiel der Dokumentation zeigt.

Stolpersteine

Ein materialisierter CTE wird so ausgewertet, wie er geschrieben ist, daher lässt sich das äussere WHERE nicht in ihn hineinschieben, und Indizes der Basistabelle bedienen das äussere Prädikat nicht. Jede Zeile, die ein rekursiver Term erzeugt, bleibt im Ergebnis, daher sind breite Zeilen in grossen Bäumen teuer. Die Dokumentation bezeichnet die Breitensuche-Ausgabereihenfolge als Implementierungsdetail, auf das man sich nicht verlassen sollte, und die Reihenfolge innerhalb einer Ebene als undefiniert; SEARCH oder ein explizites ORDER BY verwenden.