welk mechanisme maakt het mogelijk dat **PostgreSQL** triggers de complete set van rijen die door een enkele **DML**-instructie zijn gewijzigd, kunnen benaderen zonder per rij te worden geactiveerd?

Antwoord op de vraag

Overgangstabellen, geïntroduceerd in PostgreSQL 10, bieden deze mogelijkheid via de REFERENCING clausule in statement-level triggers. Dit mechanisme stelt de gehele resultaatset van een wijziging bloot als tijdelijke tabelstructuren—NEW TABLE voor INSERT/UPDATE operaties en OLD TABLE voor DELETE/UPDATE operaties—waardoor de triggerfunctie set-gebaseerde verwerking kan uitvoeren. In plaats van door rijen individueel te itereren, kun je een enkele SQL-instructie uitvoeren die op alle aangetaste rijen tegelijk werkt.

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();

Binnen log_inventory_changes() retourneert het opvragen van updated_items alle rijen die zijn aangeraakt door de triggerende UPDATE, wat efficiënte bulkbewerkingen mogelijk maakt, zoals INSERT INTO audit_log SELECT * FROM updated_items;.

Situatie uit het leven

Een e-commerceplatform ondervond ernstige prestatieproblemen tijdens nachtelijke bulkprijsaanpassingen die 100.000 productrecords betroffen. De bestaande architectuur gebruikte een rij-niveau AFTER trigger om auditentries te schrijven, wat resulteerde in 100.000 opeenvolgende INSERT-uitvoeringen in de price_history tabel en het uitputten van de bronnen van de verbindingpool.

Oplossing 1: Batchverwerking aan de applicatiekant Het team overwoog de trigger te verwijderen en audits binnen de Java-applicatie met behulp van JDBC batch-insertmanagement af te handelen. Hoewel dit de CPU-belasting op de database zou verminderen, introduceerde het kritieke consistentierisico's: als de applicatie mid-batch crasht, zouden de gecommitete prijsupdates permanent ontbreken met bijbehorende auditrecords, wat in strijd zou zijn met SOX-compliance. Bovendien vereiste deze aanpak complexe gedistribueerde transactiebeheer tussen de appserver en PostgreSQL.

Oplossing 2: Asynchrone berichtenqueue Een ander voorstel bestond uit het schrijven van rij-identifiers naar een Redis-stroom tijdens de update, en vervolgens audits via een achtergrondwerknemer te verwerken. Dit ontkoppelde de schrijfprocessen maar offerde onmiddellijke transactionele consistentie op. De asynchrone werker kon vertraagd raken tijdens hoge belasting, waardoor tijdelijke gaten in het auditspoor ontstonden die door regelgevende auditors zouden worden gemarkeerd. Bovendien voegde het waarborgen van exact-een-semantiek tussen PostgreSQL en Redis aanzienlijke infrastructuurcomplexiteit toe.

Oplossing 3: Statement-level trigger met overgangstabellen De gekozen aanpak verving de rijtrigger door een statement-level trigger die REFERENCING NEW TABLE AS new_prices gebruikte. De triggerfunctie voerde een enkele set-gebaseerde operatie uit: INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. Dit handhaafde strikte ACID-compliance binnen dezelfde transactie terwijl alle wijzigingen in één keer werden verwerkt.

Resultaat: De voltooitijd van de nachtelijke batch daalde van 45 seconden naar 300 milliseconden. WAL (Write-Ahead Log) generatie daalde met 90%, en het systeem elimineerde de vergrendelingsconcurrentiepieken die eerder werden veroorzaakt door enorme triggerrecursie.

Wat kandidaten vaak missen

Hoe interageren overgangstabellen met BEFORE triggers en INSTEAD OF triggers op weergaven?

Overgangstabellen zijn uitsluitend beschikbaar in AFTER triggers op gewone tabellen. BEFORE triggers werken op individuele rijen voordat de uiteindelijke resultaatset van de instructie wordt gematerialiseerd, dus de complete verzameling van gewijzigde rijen bestaat nog niet. INSTEAD OF triggers op weergaven kunnen geen overgangstabellen gebruiken omdat ze alternatieve uitvoeringspaden definiëren in plaats van het werkelijke resultaat van DML-bewerkingen op de onderliggende basistabellen te observeren.

Kan de triggerfunctie gegevens binnen de NEW TABLE of OLD TABLE wijzigen om het eindresultaat te veranderen?

Nee, overgangstabellen zijn alleen-lezen snapshots die alleen toegankelijk zijn tijdens de triggeruitvoering. Ze vertegenwoordigen onveranderlijke weergaven van de rijen die door de instructie zijn aangetast en kunnen niet worden gewijzigd met behulp van UPDATE, DELETE of INSERT-bewerkingen. Om waarden te wijzigen voordat ze worden gecommit, moet je rij-niveau BEFORE triggers gebruiken die de NEW recordvariabele rechtstreeks manipuleren, of aparte DML-bewerkingen uitvoeren tegen permanente tabellen.

Waarom vullen TRUNCATE-bewerkingen de OLD TABLE overgangstabel niet?

Hoewel PostgreSQL triggers op TRUNCATE op statementniveau ondersteunt, verwijdert deze DDL-opdracht alle rijen zonder individuele rijovergangstoestanden door het MVCC-systeem te genereren. TRUNCATE omzeilt het tupleversiebeheer dat wordt gebruikt om overgangsrelaties op te bouwen, zodat het OLD TABLE niet kan vullen met de verwijderde rijen. Het auditen van TRUNCATE-bewerkingen vereist alternatieve mechanismen zoals evenemententriggers of logische decoderingsstromen.