Forums PostgreSQL.fr

trainvapeur

Membre

faut-il une transaction ?

Notre base de données recueille tous les quarts d'heure un grand nombre d'informations provenant d'automatismes divers. Nous gardons les détails pendant plusieurs mois mais pour les mois anciens nous calculons des statistiques sur ce mois que nous insérons dans la même table que les détails puis nous effaçons les lignes de détail correspondantes. Nous avions une application qui durait 1 heure, nous voulons la remplacer par une seule requête SQL qui dans nos tests ne met que 57 secondes.
Dans le WITH de cette requète, nous calculons une table temporaire, effectuons le DELETE des lignes, ce DELETE ayant un RETURNING *, nous faisons les statistiques sur ce retour. C'est parfait.
Nous n'avons pas de problème d'accès concurrentiel.
Mais que se passerait-il si le serveur tombait en panne durant la requête ? Faut-il entourer la requête par une transaction ou existe-t-il une sorte de transaction automatique pour chaque requete ? Nous nous posons la question parce que l'application faisait les statistiques avant d'effacer les lignes et là la requête efface les lignes avant de faire les statistiques.

La requete en question ressemble à ceci :

WITH -- recherche les triplets equipement_id,statistique_id,jour à aggréger
lignes_a_utiliser AS
(
    SELECT equipement_id, statistique_id, horodatage::date AS jour FROM statistique_resultat
    GROUP BY 1,2,3
    HAVING count(*)>1
),
lignesaggregat AS
(   -- supprime toutes les vieilles lignes correspondant aux triplets trouvés
    DELETE FROM statistique_resultat sr WHERE sr.horodatage<'2016-06-07' AND
    EXISTS(SELECT * FROM lignes_a_utiliser lu WHERE
        lu.equipement_id=sr.equipement_id AND
        lu.statistique_id=sr.statistique_id AND
        lu.jour=sr.horodatage::date)
    RETURNING *
)
-- Enfin, crée un enregistrement unique pour le triplet en calculant moyennes, sommes, max...
INSERT INTO statistique_resultat
SELECT
  nextval('statistique_result_seq') AS id,
  equipement_id,
  statistique_id,
  avg(valeur) AS valeur,
  ...
  sum(nb_un) AS nb_un
FROM lignesaggregat
GROUP BY equipement_id,statistique_id,horodatage::date;

Qu'en pensez-vous ?
Jean

dverite

Membre

Re : faut-il une transaction ?

Oui en l'absence de transaction explicite, il y a une transaction implicite par requête. De ce fait, un bloc BEGIN/COMMIT entourant une seule requête est inutile.

En cas de plantage pendant la requête, le "morceau" de requête ayant été exécuté avant le plantage ne laissera aucun résultat dans la base de données, c'est comme si la requête n'avait pas eu lieu, à part pour les appels à nextval() qui ne sont pas transactionnés.

---

@DanielVerite
http://blog-postgresql.verite.pro/

trainvapeur

Membre

Re : faut-il une transaction ?

merci beaucoup.

Jean