SELEZIONA o INSERISCI in una funzione soggetta a condizioni di gara?

Ho scritto una funzione per creare post per un semplice motore di blogging:

CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[]) RETURNS INTEGER AS $$ DECLARE InsertedPostId INTEGER; TagName VARCHAR; BEGIN INSERT INTO Posts (Title, Body) VALUES ($1, $2) RETURNING Id INTO InsertedPostId; FOREACH TagName IN ARRAY $3 LOOP DECLARE InsertedTagId INTEGER; BEGIN -- I am concerned about this part. BEGIN INSERT INTO Tags (Name) VALUES (TagName) RETURNING Id INTO InsertedTagId; EXCEPTION WHEN UNIQUE_VIOLATION THEN SELECT INTO InsertedTagId Id FROM Tags WHERE Name = TagName FETCH FIRST ROW ONLY; END; INSERT INTO Taggings (PostId, TagId) VALUES (InsertedPostId, InsertedTagId); END; END LOOP; RETURN InsertedPostId; END; $$ LANGUAGE 'plpgsql'; 

È sobject a condizioni di gara quando più utenti eliminano tag e creano post contemporaneamente?
Nello specifico, le transazioni (e quindi le funzioni) impediscono che tali condizioni di gara si verifichino?
Sto usando PostgreSQL 9.2.3.

È il problema ricorrente di SELECT o INSERT in un eventuale carico di scrittura simultaneo, correlato a (ma diverso da) UPSERT (che è INSERT o UPDATE ).

Per Postgres 9.5 o successivo

Utilizzando la nuova implementazione di UPSERT, INSERT ... ON CONFLICT .. DO UPDATE , possiamo in gran parte semplificare. Funzione PL / pgSQL per INSERT o SELECT una singola riga (tag):

 CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS $func$ BEGIN SELECT tag_id -- only if row existed before FROM tag WHERE tag = _tag INTO _tag_id; IF NOT FOUND THEN INSERT INTO tag AS t (tag) VALUES (_tag) ON CONFLICT (tag) DO NOTHING RETURNING t.tag_id INTO _tag_id; END IF; END $func$ LANGUAGE plpgsql; 

C’è ancora una piccola finestra per una condizione di gara. Per essere assolutamente sicuro di ottenere un ID:

 CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS $func$ BEGIN LOOP SELECT tag_id FROM tag WHERE tag = _tag INTO _tag_id; EXIT WHEN FOUND; INSERT INTO tag AS t (tag) VALUES (_tag) ON CONFLICT (tag) DO NOTHING RETURNING t.tag_id INTO _tag_id; EXIT WHEN FOUND; END LOOP; END $func$ LANGUAGE plpgsql; 

Ciò continua a fare il ciclo finché INSERT o SELECT riesce. Chiamata:

 SELECT f_tag_id('possibly_new_tag'); 

Se i comandi successivi nella stessa transazione si basano sull’esistenza della riga ed è ansible che altre transazioni si aggiornino o eliminino contemporaneamente, è ansible bloccare una riga esistente nell’istruzione SELECT con FOR SHARE .
Se invece viene inserita la riga, viene comunque bloccata fino alla fine della transazione.

Se una nuova riga viene inserita la maggior parte del tempo, inizia con INSERT per renderla più veloce.

Relazionato:

  • Ottieni l’ID da un INSERT condizionale
  • Come includere le righe escluse in RESTITUZIONE da INSERT … ON CONFLICT

Soluzione (pura SQL) correlata per INSERT o SELECT più righe (un set) contemporaneamente:

  • Come usare RETURNING con ON CONFLICT in PostgreSQL?

Cosa c’è di sbagliato in questa soluzione SQL pura?

In precedenza avevo anche suggerito questa funzione SQL:

 CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS $func$ WITH ins AS ( INSERT INTO tag AS t (tag) VALUES (_tag) ON CONFLICT (tag) DO NOTHING RETURNING t.tag_id ) SELECT tag_id FROM ins UNION ALL SELECT tag_id FROM tag WHERE tag = _tag LIMIT 1 $func$ LANGUAGE sql; 

Che non è del tutto sbagliato, ma non riesce a sigillare una scappatoia, come @FunctorSalad ha funzionato nella sua risposta aggiunta . La funzione può generare un risultato vuoto se una transazione concorrente tenta di fare lo stesso allo stesso tempo. Tutte le istruzioni in una query con CTE sono virtualmente eseguite nello stesso momento. Il manuale:

Tutte le istruzioni sono eseguite con la stessa istantanea

Se una transazione concorrente inserisce lo stesso nuovo tag un momento prima, ma non ha ancora eseguito il commit:

  • La parte UPSERT appare vuota, dopo aver atteso che la transazione concorrente finisca. (Se la transazione concorrente deve eseguire il rollback, inserisce ancora il nuovo tag e restituisce un nuovo ID).

  • Anche la parte SELECT risulta vuota, poiché si basa sulla stessa istantanea, in cui il nuovo tag dalla transazione concorrente (non ancora eseguita) non è visibile.

Non otteniamo nulla . Non come previsto. Questa è una logica ingenua e intuitiva (e sono stato catturato lì), ma è così che funziona il modello MVCC di Postgres.

Quindi non usare questo se più transazioni possono provare a inserire lo stesso tag nello stesso momento. O loop fino a quando non si ottiene effettivamente una riga. Il ciclo non verrà quasi mai triggersto nei carichi di lavoro comuni.

Risposta originale (Postgres 9.4 o precedente)

Data questa tabella (leggermente semplificata):

 CREATE table tag ( tag_id serial PRIMARY KEY , tag text UNIQUE ); 

… una funzione praticamente sicura al 100% per inserire un nuovo tag / selezionarne uno esistente, potrebbe assomigliare a questo.
Perché non al 100%? Considera le note nel manuale per l’esempio UPSERT correlato :

 CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int) AS $func$ BEGIN LOOP BEGIN WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE) , ins AS (INSERT INTO tag(tag) SELECT _tag WHERE NOT EXISTS (SELECT 1 FROM sel) -- only if not found RETURNING tag.tag_id) -- qualified so no conflict with param SELECT sel.tag_id FROM sel UNION ALL SELECT ins.tag_id FROM ins INTO tag_id; EXCEPTION WHEN UNIQUE_VIOLATION THEN -- insert in concurrent session? RAISE NOTICE 'It actually happened!'; -- hardly ever happens END; EXIT WHEN tag_id IS NOT NULL; -- else keep looping END LOOP; END $func$ LANGUAGE plpgsql; 

SQL Fiddle.

Spiegazione

  • Prova prima SELECT . In tal modo si evita l’eccezione notevolmente più costosa gestendo il 99,99% delle volte.

  • Utilizzare un CTE per ridurre al minimo la (già minuscola) fascia oraria per le condizioni della gara.

  • La finestra temporale tra SELECT e INSERT all’interno di una query è super piccola. Se non si dispone di un carico concorrente pesante o se si può vivere con un’eccezione una volta all’anno, si può semplicemente ignorare il caso e utilizzare l’istruzione SQL, che è più veloce.

  • Nessuna necessità di FETCH FIRST ROW ONLY (= LIMIT 1 ). Il nome del tag è ovviamente UNIQUE .

  • Rimuovi FOR SHARE nel mio esempio se di solito non hai DELETE o UPDATE simultaneo sul tag table. Costa un po ‘di prestazioni.

  • Non citare mai il nome della lingua: “plpgsql” . plpgsql è un identificatore . Citando può causare problemi ed è tollerato solo per la retrocompatibilità.

  • Non utilizzare nomi di colonna non descrittivi come id o name . Quando si uniscono un paio di tabelle ( che è ciò che si fa in un DB relazionale) si finisce con più nomi identici e si devono usare alias.

Costruito nella tua funzione

Usando questa funzione potresti in gran parte semplificare il tuo FOREACH LOOP a:

 ... FOREACH TagName IN ARRAY $3 LOOP INSERT INTO taggings (PostId, TagId) VALUES (InsertedPostId, f_tag_id(TagName)); END LOOP; ... 

Più veloce, però, come una singola istruzione SQL con unnest() :

 INSERT INTO taggings (PostId, TagId) SELECT InsertedPostId, f_tag_id(tag) FROM unnest($3) tag; 

Sostituisce l’intero ciclo.

Soluzione alternativa

Questa variante si basa sul comportamento di UNION ALL con una clausola LIMIT : non appena vengono trovate abbastanza righe, il resto non viene mai eseguito:

  • Modo per provare più SELECT fino a quando un risultato è disponibile?

Partendo da questo, possiamo esternalizzare l’ INSERT in una funzione separata. Solo lì abbiamo bisogno di una gestione delle eccezioni. Sicuro come la prima soluzione.

 CREATE OR REPLACE FUNCTION f_insert_tag(_tag text, OUT tag_id int) RETURNS int AS $func$ BEGIN INSERT INTO tag(tag) VALUES (_tag) RETURNING tag.tag_id INTO tag_id; EXCEPTION WHEN UNIQUE_VIOLATION THEN -- catch exception, NULL is returned END $func$ LANGUAGE plpgsql; 

Che viene utilizzato nella funzione principale:

 CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS $func$ BEGIN LOOP SELECT tag_id FROM tag WHERE tag = _tag UNION ALL SELECT f_insert_tag(_tag) -- only executed if tag not found LIMIT 1 -- not strictly necessary, just to be clear INTO _tag_id; EXIT WHEN _tag_id IS NOT NULL; -- else keep looping END LOOP; END $func$ LANGUAGE plpgsql; 
  • Questo è un po ‘più economico se la maggior parte delle chiamate richiede solo SELECT , perché il blocco più costoso con INSERT contenente la clausola EXCEPTION viene inserito raramente. La query è anche più semplice.

  • FOR SHARE non è ansible qui (non consentito nella query UNION ).

  • LIMIT 1 non sarebbe necessario (testato in pg 9.4). Postgres deriva il LIMIT 1 da INTO _tag_id e viene eseguito solo fino alla prima riga trovata.

C’è ancora qualcosa a cui prestare attenzione anche quando si utilizza la clausola ON CONFLICT introdotta in Postgres 9.5. Usando la stessa funzione e tabella di esempio della risposta di @Erwin Brandstetter, se lo facciamo:

 Session 1: begin; Session 2: begin; Session 1: select f_tag_id('a'); f_tag_id ---------- 11 (1 row) Session 2: select f_tag_id('a'); [Session 2 blocks] Session 1: commit; [Session 2 returns:] f_tag_id ---------- NULL (1 row) 

Quindi f_tag_id restituito NULL nella sessione 2, il che sarebbe imansible in un mondo a thread singolo!

Se eleviamo il livello di isolamento della transazione a repeatable read (o serializable più forte), la sessione 2 lancia ERROR: could not serialize access due to concurrent update . Quindi non ci sono risultati “impossibili”, ma sfortunatamente ora dobbiamo essere pronti a riprovare la transazione.

Modifica: con repeatable read o serializable , se la sessione 1 inserisce tag a , quindi la sessione 2 inserisce b , quindi la sessione 1 tenta di inserire b e la sessione 2 tenta di inserire a , una sessione rileva un deadlock:

 ERROR: deadlock detected DETAIL: Process 14377 waits for ShareLock on transaction 1795501; blocked by process 14363. Process 14363 waits for ShareLock on transaction 1795503; blocked by process 14377. HINT: See server log for query details. CONTEXT: while inserting index tuple (0,3) in relation "tag" SQL function "f_tag_id" statement 1 

Dopo che la sessione che ha ricevuto l’errore di stallo torna indietro, l’altra sessione continua. Quindi immagino che dovremmo trattare deadlock come serialization_failure e riprovare, in una situazione come questa?

In alternativa, inserisci i tag in un ordine coerente, ma questo non è facile se non vengono tutti aggiunti in un unico posto.

Penso ci sia una leggera possibilità che quando il tag esistesse già potrebbe essere cancellato da un’altra transazione dopo che la transazione l’ha trovata. L’utilizzo di SELECT FOR UPDATE dovrebbe risolverlo.