Fiche de révision : Procédures PostgreSQL et transactions

📋 Plan du Cours

1. Création de la procédure ciblée
2. Transactions et tests contrôlés
3. Jeux de tests et cas limites
4. Risques du filtre OR
5. Témoins et preuve de non-régression
6. Lignes ciblées et modifications réelles
7. Mise à jour ensembliste ou boucle

📖 1. Création de la procédure ciblée

🔑 Notions clés & Définitions

• marquer_mesures_controlees : Crée ou remplace une procédure PL/pgSQL appelée avec CALL pour mettre à jour la qualité des mesures d'une station et d'un jour donnés.

★ À maîtriser

• La procédure filtre les lignes avec station_id = p_station_id et date_heure::date = p_jour, affecte qualite_validation à 'controlee', récupère le nombre de lignes avec GET DIAGNOSTICS puis affiche ce nombre avec RAISE NOTICE.

Compléments

📌 Le cast date_heure::date permet de comparer la date extraite d'un timestamp avec le paramètre p_jour de type date.

• RAISE NOTICE affiche un message informatif sans interrompre l'exécution, et ses symboles % sont remplacés dans l'ordre par les arguments fournis.

💡 Astuce mémo

Filtrer, modifier, compter, tracer

📖 2. Transactions et tests contrôlés

★ À maîtriser

• Un test transactionnel ouvre une transaction avec BEGIN, appelle la procédure avec CALL, vérifie les lignes avec SELECT, puis annule les changements avec ROLLBACK.

Compléments

• Après ROLLBACK, les valeurs modifiées redeviennent 'brute' si elles avaient cette valeur avant la transaction.

💡 Astuce mémo

BEGIN → CALL → SELECT → ROLLBACK

📖 3. Jeux de tests et cas limites

★ À maîtriser

• 🔄 Les trois cas à tester sont:

  1. station existante avec mesures
  2. jour sans mesure
  3. station inexistante

• Un jour sans mesure et une station inexistante doivent tous deux produire 0 ligne modifiée sans générer d'erreur.

Compléments

• Pour une station existante avec des mesures, le nombre de lignes modifiées doit être supérieur à zéro.

💡 Astuce mémo

Mesures présentes contre absence de mesure

📖 4. Risques du filtre OR

★ À maîtriser

📌 Le filtre station_id = p_station_id AND date_heure::date = p_jour cible uniquement les mesures de la station et du jour demandés.

• Avec OR, toutes les mesures de la station cible à n'importe quelle date et toutes les mesures de n'importe quelle station au jour demandé sont modifiées.

Compléments

• Un filtre OR provoque une sur-modification et produit un ROW_COUNT plus élevé que le filtre AND attendu.

💡 Astuce mémo

AND = station ET jour ; OR = station OU jour

📖 5. Témoins et preuve de non-régression

📝 Points essentiels

• Les trois mesures témoins sont insérées dans une transaction avec des identifiants libres, puis la procédure est appelée sur la station 101 et le 1er janvier 2030 avant vérification par SELECT.

• Les trois témoins représentent:

  • la station cible au jour cible
  • une autre station au même jour
  • la station cible au jour suivant

• Avec le filtre correct, une seule ligne témoin devient 'controlee' et les deux autres restent 'brute'.

💡 Astuce mémo

Trois témoins : cible, même jour, autre station ; autre jour, même station

📖 6. Lignes ciblées et modifications réelles

★ À maîtriser

📌 Pour ne cibler que les lignes dont la qualité est différente de 'controlee', il faut ajouter AND qualite_validation IS DISTINCT FROM 'controlee' au filtre.

📌 En PostgreSQL, ROW_COUNT après UPDATE compte les lignes qui satisfont le WHERE, même si la valeur affectée est déjà identique.

Compléments

• Lors d'un second appel sur la même station et le même jour, ROW_COUNT vaut encore 1, la trace reste identique et la donnée demeure 'controlee'.

• Avec le filtre IS DISTINCT FROM, le second appel produit ROW_COUNT = 0 parce qu'aucune ligne ne satisfait encore la condition de différence.

💡 Astuce mémo

ROW_COUNT compte les lignes ciblées, pas forcément les valeurs changées

📖 7. Mise à jour ensembliste ou boucle

★ À maîtriser

📌 La version ensembliste effectue un seul UPDATE et utilise le ROW_COUNT natif, tandis que la version en boucle effectue un UPDATE par ligne et incrémente un compteur manuel.

Compléments

• La procédure en boucle sélectionne le ctid de chaque ligne correspondant aux mêmes filtres de station et de jour, puis met à jour chaque ligne à partir de ce ctid.

📌 Sur trois lignes, les versions ensembliste et en boucle ont une performance identique dans cet exemple, mais leurs contrats diffèrent entre approche déclarative et approche impérative.

💡 Astuce mémo

Un UPDATE déclaratif contre N UPDATE impératifs

📊 Tableaux de synthèse

AND contre OR

Contrats de mise à jour