Tabele przejściowe, wprowadzone w PostgreSQL 10, zapewniają tę możliwość dzięki klauzuli REFERENCING w wyzwalaczach na poziomie instrukcji. Ten mechanizm udostępnia cały zestaw wyników modyfikacji jako struktury tymczasowych tabel – NEW TABLE dla operacji INSERT/UPDATE i OLD TABLE dla operacji DELETE/UPDATE – co pozwala funkcji wyzwalacza na przetwarzanie zbiorowe. Zamiast iterować przez wiersze pojedynczo, można wykonać jedno polecenie SQL, które działa na wszystkich dotkniętych wierszach jednocześnie.
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();
Wewnątrz log_inventory_changes(), zapytanie do updated_items zwraca wszystkie wiersze dotknięte wyzwoleniem UPDATE, co umożliwia wydajne operacje zbiorcze, takie jak INSERT INTO audit_log SELECT * FROM updated_items;.
Platforma e-commerce ucierpiała z powodu znacznego pogorszenia wydajności podczas nocnych masowych dostosowań cenowych, które dotyczyły 100 000 rekordów produktów. Istniejąca architektura używała wyzwalacza na poziomie wiersza AFTER, aby zapisać wpisy audytowe, co powodowało 100 000 sekwencyjnych wywołań INSERT do tabeli price_history i wyczerpywało zasoby puli połączeń.
Rozwiązanie 1: Przetwarzanie zbiorcze po stronie aplikacji Zespół rozważał usunięcie wyzwalacza i obsługę audytów w aplikacji Java przy użyciu zbiorowych wstawień JDBC. Chociaż zmniejszyłoby to obciążenie CPU bazy danych, wprowadzało to krytyczne ryzyko spójności: jeśli aplikacja zawiesiłaby się w trakcie przetwarzania zbiorczego, zaktualizowane ceny na stałe brakowałoby odpowiadających wpisów audytowych, co naruszałoby zgodność z SOX. Dodatkowo, takie podejście wymagało złożonego zarządzania transakcjami rozproszonymi pomiędzy serwerem aplikacji a PostgreSQL.
Rozwiązanie 2: Asynchroniczna kolejka wiadomości Inna propozycja polegała na zapisaniu identyfikatorów wierszy do strumienia Redis podczas aktualizacji, a następnie przetwarzaniu audytów przez pracownika w tle. To odłączyło ścieżki zapisu, ale poświęciło natychmiastową spójność transakcyjną. Asynchroniczny pracownik mógłby mieć opóźnienia podczas dużego obciążenia, tworząc tymczasowe luki w śladzie audytowym, które audytorzy regulacyjni by zgłosili. Ponadto, zapewnienie semantyki dokładnie raz pomiędzy PostgreSQL a Redis dodało znacznej złożoności infrastrukturalnej.
Rozwiązanie 3: Wyzwalacz na poziomie instrukcji z tabelami przejściowymi
Wybrane podejście zastąpiło wyzwalacz na poziomie wiersza wyzwalaczem na poziomie instrukcji wykorzystującym REFERENCING NEW TABLE AS new_prices. Funkcja wyzwalacza wykonała jedną operację zbiorową: INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. To utrzymało surową zgodność ACID w tej samej transakcji, przetwarzając wszystkie modyfikacje na raz.
Wynik: Czas zakończenia nocnej partii zmniejszył się z 45 sekund do 300 milisekund. Generowanie WAL (Write-Ahead Log) spadło o 90%, a system wyeliminował szczyty blokowania, które wcześniej były spowodowane masową rekurencją wyzwalaczy.
Jak tabele przejściowe współdziałają z wyzwalaczami BEFORE oraz INSTEAD OF na widokach?
Tabele przejściowe są dostępne wyłącznie w wyzwalaczach AFTER na zwykłych tabelach. Wyzwalacze BEFORE działają na poszczególnych wierszach przed tym, jak ostateczny zestaw wyników instrukcji zostanie zmaterializowany, więc cały zbiór zmodyfikowanych wierszy jeszcze nie istnieje. Wyzwalacze INSTEAD OF na widokach nie mogą korzystać z tabel przejściowych, ponieważ definiują alternatywne ścieżki wykonania, a nie obserwują rzeczywiste wyniki operacji DML na podstawowych tabelach.
Czy funkcja wyzwalacza może modyfikować dane w NEW TABLE lub OLD TABLE, aby zmienić ostateczny wynik?
Nie, tabele przejściowe są niemodyfikowalnymi migawkami dostępnymi tylko podczas wykonywania wyzwalacza. Reprezentują one niezmienne widoki wierszy dotkniętych instrukcją i nie mogą być modyfikowane za pomocą operacji UPDATE, DELETE ani INSERT. Aby zmienić wartości przed ich zatwierdzeniem, musisz użyć wyzwalaczy BEFORE na poziomie wiersza, które manipulują zmienną rekordu NEW bezpośrednio, lub wykonać oddzielne DML na trwałych tabelach.
Dlaczego operacje TRUNCATE nie wypełniają tabeli przejściowej OLD TABLE?
Chociaż PostgreSQL obsługuje wyzwalacze na TRUNCATE na poziomie instrukcji, to polecenie DDL usuwa wszystkie wiersze bez generowania indywidualnych stanów przejściowych wierszy przez system MVCC. TRUNCATE omija mechanizm wersjonowania krotek używany do tworzenia relacji przejściowych, więc nie może wypełnić OLD TABLE usuniętymi wierszami. Audyt operacji TRUNCATE wymaga alternatywnych mechanizmów, takich jak wyzwalacze zdarzeń lub strumienie dekodowania logicznego.