Elimina genitore se non è referenziato da nessun altro bambino

Ho una situazione di esempio: tabella parent ha una colonna di nome id , a cui fa riferimento nella tabella child come una chiave esterna.

Quando si elimina una riga figlia, come eliminare anche il genitore se non viene fatto riferimento da nessun altro bambino?

In PostgreSQL 9.1 o versioni successive è ansible eseguire questa operazione con una singola istruzione utilizzando un CTE che modifica i dati . Questo è generalmente meno sobject a errori. Riduce al minimo l’intervallo di tempo tra i due DELETE in cui le condizioni di gara possono portare a risultati sorprendenti con operazioni simultanee:

 WITH del_child AS ( DELETE FROM child WHERE child_id = 1 RETURNING parent_id, child_id ) DELETE FROM parent p USING del_child x WHERE p.parent_id = x.parent_id AND NOT EXISTS ( SELECT 1 FROM child c WHERE c.parent_id = x.parent_id AND c.child_id <> x.child_id -- ! ); 

SQL Fiddle.

Il bambino viene cancellato in ogni caso. Cito il manuale :

Le istruzioni di modifica dei dati in WITH vengono eseguite esattamente una volta, e sempre fino al completamento , indipendentemente dal fatto che la query primaria legga tutto (o effettivamente qualsiasi) del loro output. Si noti che questo è diverso dalla regola per SELECT in WITH : come indicato nella sezione precedente, l’esecuzione di un SELECT viene eseguita solo fino a quando la query primaria richiede il suo output.

Il genitore viene eliminato solo se non ha altri figli.
Nota l’ultima condizione. Contrariamente a quanto ci si potrebbe aspettare, questo è necessario, dal momento che:

Le istruzioni secondarie in WITH vengono eseguite simultaneamente l’una con l’altra e con la query principale. Pertanto, quando si utilizzano le istruzioni di modifica dei dati in WITH , l’ordine in cui si verificano effettivamente gli aggiornamenti specificati è imprevedibile. Tutte le istruzioni sono eseguite con la stessa istantanea (vedere il Capitolo 13), in modo che non possano “vedere” gli effetti degli altri sulle tabelle di destinazione.

Grassetto enfasi mio.
Ho usato il nome della colonna parent_id al posto parent_id non descrittivo.

Elimina le condizioni di gara

Per eliminare le possibili condizioni di gara che ho menzionato sopra completamente , blocca prima la riga genitore. Naturalmente, tutte le operazioni simili devono seguire la stessa procedura per farlo funzionare.

 WITH lock_parent AS ( SELECT p.parent_id, c.child_id FROM child c JOIN parent p ON p.parent_id = c.parent_id WHERE c.child_id = 12 -- provide child_id here once FOR NO KEY UPDATE -- locks parent row. ) , del_child AS ( DELETE FROM child c USING lock_parent l WHERE c.child_id = l.child_id ) DELETE FROM parent p USING lock_parent l WHERE p.parent_id = l.parent_id AND NOT EXISTS ( SELECT 1 FROM child c WHERE c.parent_id = l.parent_id AND c.child_id <> l.child_id -- ! ); 

In questo modo solo una transazione alla volta può bloccare lo stesso genitore. Quindi non può succedere che più transazioni cancellino figli dello stesso genitore, vedano ancora altri bambini e risparmiano il genitore, mentre tutti i bambini sono andati dopo. (Gli aggiornamenti sulle colonne non chiave sono ancora consentiti con FOR NO KEY UPDATE .)

Se tali casi non si verificano mai o si vive con esso (quasi mai), la prima domanda è più economica. Altrimenti, questo è il percorso sicuro.

FOR NO KEY UPDATE stato introdotto con Postgres 9.4. Dettagli nel manuale. Nelle versioni precedenti utilizzare invece il blocco più forte FOR UPDATE .

 delete from child where parent_id = 1 

Dopo eliminato nel bambino fallo nel genitore:

 delete from parent where id = 1 and not exists ( select 1 from child where parent_id = 1 ) 

La condizione not exists si assicurerà che venga cancellata solo se non esiste nel bambino. Puoi racchiudere entrambi i comandi di cancellazione in una transazione:

 begin; first_delete; second_delete; commit;