Historia pytania
Ta niejasność pojawiła się wraz z wprowadzeniem natywnej funkcjonalności UPSERT w PostgreSQL 9.5 za pomocą klauzuli ON CONFLICT. Przed tą wersją deweloperzy wdrażali idempotentne wstawienia, używając skomplikowanych pętli PL/pgSQL lub błędnej logiki po stronie aplikacji. Klauzula RETURNING od dawna była kluczowa do pobierania UUID lub identyfikatorów szeregowych, ale jej interakcja z modelem wykonania z podwójną ścieżką UPSERT – gdzie polecenie może zakończyć się zarówno INSERT, jak i UPDATE – stworzyła subtelną lukę semantyczną, która myli nawet starszych inżynierów co do tego, która wersja wiersza jest faktycznie zwracana.
Problem
Gdy polecenie INSERT ... ON CONFLICT ... DO UPDATE napotka naruszenie unikalności, przechodzi do aktualizacji istniejącego wiersza. Klauzula RETURNING następnie odnosi się do ostatecznego stanu tego wiersza. Jednak jeśli logika aplikacji zależy od wartości, które zostały wygenerowane dla próbnego wstawienia – takich jak znaczniki czasu created_at, domyślne wyrażenia lub wartości obliczone przez aplikację – polecenie zwraca zamiast tego przestarzałe dane istniejącego wiersza. Ta cicha substytucja powoduje niespójność pamięci podręcznej, uszkodzenie śladów audytowych oraz subtelne warunki wyścigu, w których systemy downstream otrzymują czasowo niespójną metadane.
Rozwiązanie
Pseudo-tabela EXCLUDED zapewnia wgląd w proponowane wartości wstawienia, które wywołały konflikt. Eksplitywnie odwołując się do EXCLUDED.column_name w swojej klauzuli RETURNING lub w liście aktualizacji UPDATE, zapewniasz dostęp do zamierzonych nowych danych, niezależnie od tego, która ścieżka wykonania została podjęta.
INSERT INTO user_sessions (user_id, login_count, last_seen, session_token) VALUES (1001, 1, NOW(), gen_random_uuid()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_sessions.login_count + 1, last_seen = EXCLUDED.last_seen, session_token = EXCLUDED.session_token RETURNING session_id, user_id, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type, session_token, EXCLUDED.last_seen AS intended_timestamp;
W tym wzorze EXCLUDED.last_seen i EXCLUDED.session_token zapewniają, że aplikacja otrzymuje świeże wartości z próbnego wstawienia, nawet gdy baza danych wykonuje aktualizację zamiast tego.
Równoczesne gromadzenie punktów lojalnościowych
Platforma fintech przetwarzająca mikrodokonania o wysokiej częstotliwości napotkała fantomowe obliczenia nagród. Gdy dwa równoległe żądania próbowały jednocześnie przyznać punkty temu samemu kontu użytkownika, baza danych PostgreSQL prawidłowo zachowała atomowość, ale warstwa pamięci podręcznej Redis otrzymała przestarzałe znaczniki czasu updated_at z klauzuli RETURNING. Spowodowało to, że pamięć podręczna odrzucała legalne przyrosty punktów jako przestarzałe, co prowadziło do utraty przychodów i skarg klientów na brak nagród.
Rozwiązanie A: Rozproszone blokady z Redis
Zespół inżynieryjny początkowo zaproponował uzyskiwanie rozproszonych blokad w Redis przed wykonaniem transakcji bazy danych. Podejście to zserializowałoby operacje konfliktowe i zagwarantowało spójność sekwencyjną. Wprowadziło to jednak pojedynczy punkt awarii, dodało 12-18 ms opóźnienia sieciowego na żądanie i stworzyło złożone scenariusze zakleszczenia, gdy transakcje zostały przerwane po uzyskaniu blokad. Operacyjne obciążenie zarządzania blokadami i potencjalne ryzyko awarii kaskadowych sprawiły, że ta architektura stała się nie do utrzymania na dużą skalę.
Rozwiązanie B: Odczyt-modyfikacja-zapis po stronie aplikacji
Inna sugestia polegała na najpierw zapytaniu o istnienie rekordu za pomocą SELECT, a następnie podjęciu decyzji pomiędzy INSERT a UPDATE w kodzie aplikacji. Choć koncepcyjnie prosta, ta metoda katastrofalnie zawodzi pod obciążeniem równoległym z powodu izolacji READ COMMITTED, która pozwala na niepowtarzalne odczyty pomiędzy sprawdzeniem a zapisem. Implementacja izolacji SERIALIZABLE, aby zapobiec warunkom wyścigu, spowodowałaby nadmierne awarie serializacji i burze ponownych prób, podczas gdy jawne blokady tabeli spowolniłyby przepustowość do nieakceptowalnych poziomów.
Rozwiązanie C: Właściwe wykorzystanie EXCLUDED
Wybrane podejście zrekonstruowało zapytanie, aby wykorzystać EXCLUDED dla wszystkich zmiennych wartości w klauzuli RETURNING. Odwołując się do EXCLUDED.points i EXCLUDED.calculated_at, aplikacja konsekwentnie otrzymywała zamierzone metadane z próby wstawienia, niezależnie od tego, czy operacja zakończyła się powstaniem nowego wiersza, czy aktualizacją.
Wybrane rozwiązanie i wynik
Zespół wdrożył rozwiązanie C w mikroserwisie nagród. To wyeliminowało problemy z niespójnością pamięci podręcznej bez dodawania przeskoków sieciowych lub kompromisów w poziomach izolacji. Dokładność gromadzenia punktów wzrosła do 99,99%, wykorzystanie CPU bazy danych spadło o 35% dzięki zmniejszeniu liczby okrążeń zapytań, a system z powodzeniem poradził sobie z szczytami ruchu w Czarny Piątek bez interwencji ręcznej.
Jak PostgreSQL ustala, który unikalny indeks wykorzystać do wykrywania konfliktów, gdy istnieje wiele indeksów na tabeli?
PostgreSQL wymaga jawnej specyfikacji arbitra w klauzuli ON CONFLICT. Gdy piszesz ON CONFLICT (column_list), planista wybiera unikalny indeks, którego indeksowane kolumny dokładnie pasują do podanej listy w kolejności. Jeśli istnieje wiele indeksów na tych samych kolumnach, wybiera ten, który został utworzony jako pierwszy. Dla częściowych unikalnych indeksów (tych z klauzulami WHERE) lub indeksów wyrażeniowych należy użyć składni ON CONFLICT ON CONSTRAINT constraint_name; w przeciwnym razie silnik zgłasza błąd stwierdzający, że nie może wywnioskować indeksu arbitra. Kandydaci często zakładają, że baza danych automatycznie wybiera "najbardziej selektywny" indeks, lub nie zauważają, że funkcjonalne indeksy wymagają jawnego nazwania ograniczenia.
Dlaczego w przypadku konfliktu wielu transakcji na tym samym kluczu pod izolacją READ COMMITTED może dojść do cichej utraty aktualizacji podczas wykonywania polecenia UPSERT?
Dzieje się tak z powodu zachowania ponownej oceny klauzuli UPDATE. Gdy Transakcja A wstawia wiersz i zatwierdza go, Transakcja B – czekająca na blokadę wiersza – ponownie wykonuje swój predykat UPDATE przeciwko nowo widocznemu wierszowi. Jeśli logika UPDATE wykorzystuje absolutne przypisanie (np. SET balance = 100), zamiast odniesienia do obliczeń względnych EXCLUDED (np. SET balance = account.balance + EXCLUDED.amount), Transakcja B całkowicie nadpisuje zmiany Transakcji A. Wielu kandydatów błędnie zakłada, że UPSERT implikuje automatyczne łączenie lub akumulację, nie dostrzegając, że klauzula DO UPDATE wymaga jawnego zarządzania wartościami EXCLUDED, aby osiągnąć semantykę akumulacji idempotentnej.
Jaka jest dokładna różnica między sprawdzaniem xmax = 0 a xmax IS NULL, aby określić, czy UPSERT wykonał wstawienie, i dlaczego ta różnica ma znaczenie dla HOT aktualizacji?
W PostgreSQL, xmax przechowuje identyfikator transakcji usuwającej lub aktualizującej. Dla nowo wstawionych wierszy xmax jest inicjowane na 0, nigdy NULL. Kandydaci często błędnie sprawdzają xmax IS NULL, aby wykryć wstawienia, co zawsze zwraca fałsz. Sprawdzenie xmax = 0 niezawodnie identyfikuje wstawienia w porównaniu do aktualizacji. Ta różnica staje się krytyczna w przypadku aktualizacji HOT (Heap Only Tuple), gdzie PostgreSQL optymalizuje wydajność, aktualizując wiersze in-place na tej samej stronie bez modyfikowania indeksów. Podczas gdy xmax prawidłowo wskazuje, że wiersz został dotknięty, zrozumienie, że 0 oznacza "brak wcześniejszego aktualizatora", podczas gdy wartość różna od zera wskazuje na wersjonowanie, zapobiega błędom logicznym przy obliczaniu numerów generacji wierszy lub wdrażaniu niestandardowej logiki rejestrowania zmian danych, która musi odróżniać narodziny od mutacji.