Data-Modifying-CTEs

Das Grundpattern: Archivieren

Klassisches Beispiel: alte Bestellungen aus orders löschen und in orders_archive schieben — atomar.

WITH archived AS (
    DELETE FROM orders
    WHERE created_at < now() - interval '2 years'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

Was passiert:

1. Der DELETE löscht die alten Zeilen aus orders und gibt sie via RETURNING * zurück.
2. Der INSERT schreibt sie in orders_archive.
3. Beides läuft in derselben Transaktion — bei einem Fehler werden beide zurückgerollt.

Das ist MOVE in einem Statement, ohne dass eine andere Transaktion zwischendurch die Daten in einem inkonsistenten Zustand sieht.

Audit-Log mit UPDATE

Wer Änderungen mit-protokollieren will:

WITH updated AS (
    UPDATE orders
    SET status = 'shipped',
        shipped_at = now()
    WHERE status = 'paid'
      AND created_at < now() - interval '1 day'
    RETURNING id, customer_id, total, now() AS event_at
)
INSERT INTO order_events (order_id, customer_id, event_type, event_at)
SELECT id, customer_id, 'shipped', event_at
FROM updated;

Erst UPDATE mit RETURNING, dann mit den zurückgegebenen Daten direkt einen Audit-Eintrag pro betroffene Zeile schreiben.

Mehrere Schreib-CTEs

Auch mehrere Schreib-Operationen sind erlaubt:

WITH
cancelled AS (
    UPDATE orders
    SET status = 'cancelled', cancelled_at = now()
    WHERE id = ANY($1::bigint[])
    RETURNING id, customer_id, total
),
refund_items AS (
    INSERT INTO refunds (order_id, amount, requested_at)
    SELECT id, total, now()
    FROM cancelled
    RETURNING id AS refund_id, order_id
)
INSERT INTO customer_notifications (customer_id, message, created_at)
SELECT c.customer_id,
       'Bestellung ' || c.id || ' wurde storniert. Refund-ID: ' || r.refund_id,
       now()
FROM cancelled c
INNER JOIN refund_items r ON r.order_id = c.id;

Drei Schritte in einem Statement: Bestellungen stornieren, Refunds anlegen, Kunden benachrichtigen. Alles atomar.

Sichtbarkeit: alle Schreib-CTEs sehen denselben Snapshot

Wichtigste Eigenheit: alle Schreib-CTEs in einer Query sehen denselben konsistenten Snapshot der Daten — den Zustand bevor das Statement begann. Auch wenn ein CTE Zeilen ändert, sehen die anderen CTEs sie noch im Original-Zustand.

-- Annahme: products(id=1, price=100)
WITH price_update AS (
    UPDATE products SET price = 200 WHERE id = 1
    RETURNING id, price
)
SELECT
    (SELECT price FROM products WHERE id = 1) AS direct_read,
    (SELECT price FROM price_update)          AS from_cte;

Output:

 direct_read | from_cte
-------------+----------
         100 |      200

Der direct_read aus products sieht den alten Wert (100) — obwohl der UPDATE schon im selben Statement passiert ist. Erst der RETURNING-Pfad zeigt den neuen Wert.

Konsequenz: wer auf den geänderten Zustand zugreifen will, muss über RETURNING und den CTE-Namen gehen — nicht direkt aus der Tabelle nochmal lesen.

Reihenfolge ist nicht garantiert

Postgres garantiert nicht, in welcher Reihenfolge die Schreib-CTEs ausgeführt werden — nur dass sie alle vor dem Final-Statement laufen und atomar sind.

WITH
deleted AS (DELETE FROM tasks WHERE done RETURNING id),
inserted AS (INSERT INTO tasks_archive SELECT * FROM tasks_old)
SELECT * FROM tasks WHERE id NOT IN (SELECT id FROM deleted);

Das funktioniert, aber wer sich darauf verlässt, dass deleted definitiv vor dem äußeren SELECT läuft, hat Glück — Postgres macht's so, garantiert ist's aber durch die Snapshot-Logik (siehe oben). Auf Sichtbarkeits-Reihenfolge nie verlassen.

Schreib-CTEs sind immer materialisiert

Anders als lesende CTEs werden Schreib-CTEs immer materialisiert — Postgres kann sie nicht inlinen, da sie Side-Effects haben:

myapp=> EXPLAIN
        WITH del AS (DELETE FROM tmp WHERE x < 10 RETURNING *)
        INSERT INTO tmp_archive SELECT * FROM del;
                          QUERY PLAN
----------------------------------------------------------
 Insert on tmp_archive
   CTE del
     ->  Delete on tmp
           ->  Seq Scan on tmp
                 Filter: (x < 10)
   ->  CTE Scan on del

Der Plan zeigt klar: CTE del ist ein eigener Knoten, der einmal ausgeführt wird.

Constraints und Trigger pro CTE

Trigger und Constraints feuern pro CTE, in der Reihenfolge der Tabellen-Modifikationen. Das kann unerwartete Wechselwirkungen geben:

-- AFTER INSERT trigger auf orders schreibt in audit_log
-- AFTER DELETE trigger auf orders schreibt auch in audit_log

WITH
del AS (DELETE FROM orders WHERE status = 'expired' RETURNING *),
ins AS (INSERT INTO orders_archive SELECT * FROM del)
SELECT count(*) FROM del;

Resultat: Der DELETE-Trigger feuert für jede gelöschte Zeile (= Anzahl Zeilen in del). Aber INSERT INTO orders_archive löst keinen Trigger auf orders aus — es ist eine andere Tabelle. Wenn man die audit_log-Logik durchgehend will, braucht es Trigger auf orders_archive — oder einen direkten INSERT in audit_log als weiteren CTE.

Pattern: UPSERT mit Logging

WITH upserted AS (
    INSERT INTO products (sku, name, price)
    VALUES ('SKU-100', 'Widget', 29.99)
    ON CONFLICT (sku) DO UPDATE
        SET name = EXCLUDED.name,
            price = EXCLUDED.price,
            updated_at = now()
    RETURNING id, sku, (xmax = 0) AS was_inserted
)
INSERT INTO product_log (product_id, action, logged_at)
SELECT id,
       CASE WHEN was_inserted THEN 'insert' ELSE 'update' END,
       now()
FROM upserted;

Der Trick mit xmax = 0: bei einem INSERT ist die xmax-Systemspalte 0, beim UPDATE (also Konflikt-Fall) wird sie gesetzt. So unterscheidet man im Log, was wirklich passiert ist.

Weiterführende Ressourcen

Externe Quellen

• WITH Queries – Data-Modifying Statements
• INSERT … ON CONFLICT
• RETURNING-Klausel

Verwandte Artikel

• CTE-Grundlagen
• RETURNING-Klausel
• UPSERT mit ON CONFLICT
• MERGE-Statement