Storia della domanda
Quest'ambiguità è emersa con l'introduzione della funzionalità nativa UPSERT in PostgreSQL 9.5 tramite la clausola ON CONFLICT. Prima di questo rilascio, gli sviluppatori implementavano inserimenti idempotenti utilizzando complessi loop PL/pgSQL o logica a livello di applicazione soggetta a errori. La clausola RETURNING è stata a lungo essenziale per recuperare UUID o ID seriali, ma la sua interazione con il modello di esecuzione a doppio percorso di UPSERT—dove l'istruzione può risultare in un INSERT o un UPDATE—ha creato un sottile divario semantico che confonde anche gli ingegneri senior riguardo a quale versione della riga sia effettivamente restituita.
Il problema
Quando un'istruzione INSERT ... ON CONFLICT ... DO UPDATE incontra una violazione di unicità, essa passa ad aggiornare la riga esistente. La clausola RETURNING fa quindi riferimento allo stato finale persistente di quella riga. Tuttavia, se la logica della tua applicazione dipende da valori generati per il tentativo di inserimento—come timestamp created_at, espressioni predefinite o valori calcolati dall'applicazione—l'istruzione restituirà invece i dati obsoleti della riga preesistente. Questa sostituzione silenziosa causa dissincronizzazione della cache, corruzione della traccia di revisione e sottili condizioni di competizione in cui i sistemi downstream ricevono metadati temporalmente inconsistenti.
La soluzione
La pseudo-tabella EXCLUDED fornisce una finestra sui valori di inserimento proposti che hanno innescato il conflitto. Facendo esplicitamente riferimento a EXCLUDED.column_name all'interno della tua clausola RETURNING o nell'elenco di impostazione dell'UPDATE, garantisci l'accesso ai nuovi dati previsti indipendentemente dal percorso di esecuzione intrapreso.
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;
In questo schema, EXCLUDED.last_seen e EXCLUDED.session_token garantiscono che l'applicazione riceva i valori aggiornati dal tentativo di inserimento, anche quando il database esegue invece un aggiornamento.
Accrual di punti fedeltà concorrenti
Una piattaforma fintech che elabora micro-transazioni ad alta frequenza ha incontrato calcoli fantasma dei premi. Quando due richieste parallele tentavano di accreditare punti allo stesso account utente contemporaneamente, il database PostgreSQL manteneva correttamente l'atomicità, ma il livello di cache Redis riceveva timestamp updated_at obsoleti dalla clausola RETURNING. Questo ha causato il rifiuto da parte della cache di legittimi incrementi di punti come obsoleti, portando a perdite di entrate e reclami dei clienti per premi mancanti.
Soluzione A: Blocco distribuito con Redis
Il team di ingegneria ha inizialmente proposto di acquisire blocchi distribuiti in Redis prima di eseguire la transazione del database. Questo approccio avrebbe serializzato le operazioni in conflitto e garantito la coerenza sequenziale. Tuttavia, ha introdotto un singolo punto di guasto, aggiungendo 12-18ms di latenza di rete per richiesta e creando scenari complessi di stallo quando le transazioni venivano annullate dopo aver acquisito i blocchi. Il carico operativo della gestione dei blocchi e il potenziale di guasti a cascata hanno reso questa architettura insostenibile su larga scala.
Soluzione B: Lettura-modifica-scrittura a livello di applicazione
Un'altra proposta ha riguardato la query sulla esistenza del record prima con un SELECT, quindi decidendo tra INSERT o UPDATE nel codice dell'applicazione. Anche se concettualmente semplice, questo schema fallisce catastroficamente sotto carico concorrente a causa dell'isolamento READ COMMITTED che consente letture non ripetibili tra il controllo e la scrittura. Implementare l'isolamento SERIALIZABLE per prevenire condizioni di competizione avrebbe causato eccessive ripetizioni di fallimenti e tempeste di retry, mentre il locking esplicito delle tabelle avrebbe rallentato il throughput a livelli inaccettabili.
Soluzione C: Uso corretto di EXCLUDED
L'approccio scelto ha ristrutturato la query per sfruttare EXCLUDED per tutti i valori mutabili nella clausola RETURNING. Facendo riferimento a EXCLUDED.points e EXCLUDED.calculated_at, l'applicazione ha ricevuto costantemente i metadati previsti dal tentativo di inserimento, indipendentemente dal fatto che l'operazione avesse generato una nuova riga o un aggiornamento.
Soluzione scelta e risultato
Il team ha implementato la Soluzione C in tutto il microservizio dei premi. Questo ha eliminato i problemi di inconsistenza della cache senza aggiungere salti di rete o compromettere i livelli di isolamento. L'accuratezza dell'accumulo di punti è migliorata al 99,99%, l'utilizzo della CPU del database è diminuito del 35% grazie alla riduzione dei round-trip delle query e il sistema ha gestito con successo i picchi di traffico del Black Friday senza interventi manuali.
Come fa PostgreSQL a determinare quale indice unico utilizzare per la rilevazione dei conflitti quando esistono più indici su una tabella?
PostgreSQL richiede specifiche arbitrarie esplicite nella clausola ON CONFLICT. Quando scrivi ON CONFLICT (column_list), il pianificatore seleziona l'indice unico i cui colonne indicizzate corrispondono esattamente all'elenco fornito in ordine. Se esistono più indici su colonne identiche, sceglie quello creato per primo. Per indici unici parziali (quelli con clausole WHERE) o indici di espressione, devi usare la sintassi ON CONFLICT ON CONSTRAINT constraint_name; altrimenti, il motore genera un errore che indica che non può dedurre l'indice arbitro. I candidati assumono erroneamente che il database selezioni automaticamente l'indice "più selettivo" o ignorano che gli indici funzionali richiedono una denominazione esplicita dei vincoli.
Perché potrebbe un'istruzione UPSERT perdere silenziosamente aggiornamenti quando più transazioni si scontrano sulla stessa chiave sotto isolamento READ COMMITTED?
Questo avviene a causa del comportamento di riesame della clausola UPDATE. Quando la Transazione A inserisce una riga e si impegna, la Transazione B—che aspetta il blocco della riga—riesegue il suo predicato UPDATE sulla nuova riga visibile. Se la logica dell'UPDATE utilizza un'assegnazione assoluta (ad esempio, SET balance = 100) piuttosto che un'aritmetica relativa che fa riferimento a EXCLUDED (ad esempio, SET balance = account.balance + EXCLUDED.amount), la Transazione B sovrascrive completamente le modifiche della Transazione A. Molti candidati presumono erroneamente che UPSERT implicasse una fusione o un accumulo automatico, senza riconoscere che la clausola DO UPDATE richiede una gestione esplicita dei valori EXCLUDED per ottenere una semantica di accumulo idempotente.
Qual è la differenza precisa tra controllare xmax = 0 rispetto a xmax IS NULL per determinare se un UPSERT ha eseguito un'inserzione, e perché questa distinzione è importante per gli aggiornamenti HOT?
In PostgreSQL, xmax memorizza l'ID della transazione del processo di eliminazione o aggiornamento. Per le righe appena inserite, xmax è inizializzato a 0, mai NULL. I candidati spesso controllano erroneamente xmax IS NULL per rilevare le inserzioni, il che restituisce sempre falso. Il controllo xmax = 0 identifica in modo affidabile le inserzioni rispetto agli aggiornamenti. Questa distinzione diventa critica con gli aggiornamenti HOT (Heap Only Tuple), dove PostgreSQL ottimizza le prestazioni aggiornando le righe in loco sulla stessa pagina senza modificare gli indici. Mentre xmax indica correttamente che la riga è stata toccata, comprendere che 0 significa "nessun precedente aggiornamento" mentre un valore diverso da zero indica una versioning previene errori logici nel calcolo dei numeri di generazione delle righe o nell'implementazione della logica di cattura dei dati che deve distinguere tra nascite e mutazioni.