Les points de sauvegarde et l'état de transaction avortée dans PostgreSQL

Ce que c'est

Un bloc de transaction (BEGIN ... COMMIT) est atomique : toutes ses modifications sont conservées, ou aucune. Le tutoriel PostgreSQL indique qu'après une erreur, le bloc passe dans un état avorté, et que ROLLBACK TO vers un point de sauvegarde est le seul moyen de reprendre le contrôle sans annuler toute la transaction. Les commandes émises dans cet état échouent avec le SQLSTATE 25P02, in_failed_sql_transaction dans l'annexe des codes d'erreur, dont le message est « current transaction is aborted, commands ignored until end of transaction block ». SAVEPOINT name marque un point ; ROLLBACK TO SAVEPOINT name annule les modifications effectuées après ce point et laisse la transaction ouverte ; RELEASE SAVEPOINT name supprime la marque et conserve les modifications. Les points de sauvegarde peuvent être imbriqués.

Pourquoi c'est important

Un code applicatif qui intercepte une exception de base de données et continue d'émettre des instructions sur la même connexion échoue sur chacune d'elles, souvent avec l'erreur déroutante 25P02, loin de sa cause d'origine. Les traitements par lots qui veulent « ignorer la ligne fautive, conserver le reste » ont besoin de points de sauvegarde ou de transactions par ligne. Les pools de connexions qui rendent une connexion restée dans l'état avorté empoisonnent le prochain utilisateur de cette connexion.

Comment l'appliquer

• Poser un point de sauvegarde immédiatement avant une instruction susceptible d'échouer légitimement (une violation de contrainte que le code entend gérer), y effectuer un rollback dans le gestionnaire d'exception, puis poursuivre ; le libérer en cas de succès.
• Pour les imports ligne par ligne, soit envelopper chaque ligne dans un point de sauvegarde et n'annuler que les lignes en échec, soit supprimer plutôt la source de l'erreur : valider d'abord dans une table de préparation (staging), ou utiliser INSERT ... ON CONFLICT DO NOTHING.
• En PL/pgSQL, un bloc BEGIN ... EXCEPTION ... END intercepte les erreurs qui, sinon, avorteraient la fonction et la transaction englobante ; la documentation note qu'un tel bloc est nettement plus coûteux à l'entrée et à la sortie qu'un bloc qui n'en a pas, donc n'en envelopper que l'instruction qui en a besoin.
• Garder les transactions courtes : une transaction longue avec de nombreux points de sauvegarde retient ses verrous et maintient en vie d'anciennes versions de lignes pendant toute sa durée.
• Vérifier ce que fait le pilote (driver) : s'il ouvre les transactions implicitement, et s'il signale distinctement le 25P02, de sorte que le gestionnaire effectue un rollback plutôt que de relancer l'instruction.

Pièges

ROLLBACK TO ne met pas fin à la transaction ; les modifications antérieures au point de sauvegarde restent non validées et invisibles aux autres sessions jusqu'au COMMIT. Certains pilotes et ORM peuvent envelopper chaque instruction dans un point de sauvegarde ; chaque point de sauvegarde ouvre une sous-transaction avec sa propre comptabilité interne, donc tenir ce mode à l'écart des chemins critiques (hot paths). Le tutoriel note que libérer un point de sauvegarde ou y effectuer un rollback libère tous les points de sauvegarde définis après lui, et qu'un point de sauvegarde reste défini après un rollback vers celui-ci, ce qui permet de le réutiliser.