Welcher Mechanismus ermöglicht es **PostgreSQL**-Triggern, auf die vollständige Menge von durch eine einzige **DML**-Anweisung modifizierten Zeilen zuzugreifen, ohne pro Zeile ausgelöst zu werden?

Antwort auf die Frage

Übergangstabellen, die in PostgreSQL 10 eingeführt wurden, bieten diese Fähigkeit über die REFERENCING-Klausel in Triggern auf Anweisungsebene. Dieses Mechanismus gibt das gesamte Ergebnis einer Modifikation als temporäre Tabellenstrukturen frei – NEW TABLE für INSERT/UPDATE-Operationen und OLD TABLE für DELETE/UPDATE-Operationen – wodurch die Triggerfunktion setzbasierte Verarbeitung durchführen kann. Statt einzeln durch Zeilen zu iterieren, können Sie eine einzige SQL-Anweisung ausführen, die auf alle betroffenen Zeilen gleichzeitig wirkt.

CREATE TRIGGER bulk_audit_trigger
AFTER UPDATE ON inventory
REFERENCING NEW TABLE AS updated_items OLD TABLE AS previous_items
FOR EACH STATEMENT
EXECUTE FUNCTION log_inventory_changes();

Innerhalb von log_inventory_changes() gibt eine Abfrage an updated_items alle von der auslösenden UPDATE-Anweisung berührten Zeilen zurück und ermöglicht effiziente Bulk-Operationen wie INSERT INTO audit_log SELECT * FROM updated_items;.

Situation aus dem Leben

Eine E-Commerce-Plattform erlitt während nächtlicher Bulkpreisänderungen für 100.000 Produktdatensätze erhebliche Leistungseinbußen. Die bestehende Architektur verwendete einen zeilenbasierten AFTER-Trigger, um Prüfdatensätze zu schreiben, was zu 100.000 sequenziellen INSERT-Ausführungen in die Tabelle price_history führte und die Ressourcen des Verbindungs-Pools erschöpfte.

Lösung 1: Batch-Verarbeitung auf Anwendungsebene Das Team erwog, den Trigger zu entfernen und die Prüfungen innerhalb der Java-Anwendung unter Verwendung von JDBC-Batch-Insertions zu behandeln. Obwohl dies die CPU-Belastung der Datenbank reduzieren würde, führte es zu kritischen Konsistenzrisiken: Wenn die Anwendung während des Batches abstürzte, würden bestätigte Preisupdates dauerhaft ohne entsprechende Prüfdaten bleiben, was die SOX-Einhaltung verletzen würde. Darüber hinaus erforderte dieser Ansatz ein komplexes Management verteilter Transaktionen zwischen dem Anwendungsserver und PostgreSQL.

Lösung 2: Asynchrone Nachrichtenwarteschlange Ein weiterer Vorschlag bestand darin, Zeilenidentifikatoren während des Updates in einen Redis-Stream zu schreiben und die Prüfungen über einen Hintergrundarbeiter zu verarbeiten. Dies entkoppelte die Schreibpfade, opferte jedoch die unmittelbare transaktionale Konsistenz. Der asynchrone Arbeiter könnte während hoher Auslastung hinterherhinken, was temporäre Lücken im Prüfpfad schaffen würde, die von Regulierungsprüfern markiert würden. Darüber hinaus fügte die Gewährleistung einer genau einmaligen Semantik zwischen PostgreSQL und Redis erhebliche infrastrukturelle Komplexität hinzu.

Lösung 3: Trigger auf Anweisungsebene mit Übergangstabellen Der gewählte Ansatz ersetzte den Zeilentriggers durch einen Trigger auf Anweisungsebene, der REFERENCING NEW TABLE AS new_prices nutzt. Die Triggerfunktion führte eine einzige setzbasierte Operation durch: INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. Dies gewährleistete strikte ACID-Einhaltung innerhalb derselben Transaktion, während alle Modifikationen in einem Durchlauf verarbeitet wurden.

Ergebnis: Die Abschlusszeit des nächtlichen Batches sank von 45 Sekunden auf 300 Millisekunden. Die WAL (Write-Ahead Log)-Generierung fiel um 90 % und das System beseitigte die zuvor durch massive Trigger-Rekursion verursachten Lock-Contention-Spitzen.

Was Kandidaten oft übersehen

Wie interagieren Übergangstabellen mit BEFORE-Triggern und INSTEAD OF-Triggern auf Sichten?

Übergangstabellen sind ausschließlich in AFTER-Triggern auf normalen Tabellen verfügbar. BEFORE-Trigger arbeiten an einzelnen Zeilen, bevor das endgültige Ergebnis der Anweisung materialisiert wird, sodass die vollständige Sammlung modifizierter Zeilen noch nicht existiert. INSTEAD OF-Trigger auf Sichten können keine Übergangstabellen verwenden, da sie alternative Ausführungspfade definieren, anstatt das tatsächliche Ergebnis der DML-Operationen auf den zugrunde liegenden Basistabellen zu beobachten.

Kann die Triggerfunktion Daten innerhalb der NEW TABLE oder OLD TABLE ändern, um das endgültige Ergebnis zu verändern?

Nein, Übergangstabellen sind schreibgeschützte Snapshots, die nur während der Triggerausführung zugänglich sind. Sie stellen unveränderliche Ansichten der von der Anweisung betroffenen Zeilen dar und können nicht mit UPDATE, DELETE oder INSERT-Operationen geändert werden. Um Werte vor ihrer Bestätigung zu ändern, müssen Sie zeilenbasierte BEFORE-Trigger verwenden, die die NEW-Datensatzvariable direkt manipulieren, oder separate DML-Operationen gegen permanente Tabellen ausführen.

Warum bevölkern TRUNCATE-Operationen nicht die OLD TABLE-Übergangstabelle?

Obwohl PostgreSQL Trigger auf TRUNCATE auf Anweisungsebene unterstützt, entfernt dieser DDL-Befehl alle Zeilen, ohne individuelle Zeilenübergangszustände durch das MVCC-System zu erzeugen. TRUNCATE umgeht den Tupel-Versionierungsmechanismus, der verwendet wird, um Übergangsrelationen zu erstellen, sodass er OLD TABLE nicht mit den gelöschten Zeilen befüllen kann. Die Prüfung von TRUNCATE-Operationen erfordert alternative Mechanismen wie Ereignistrigger oder logische Dekodierungsströme.