SQL avancé pour le transactionnel

Module S60

Dalibo SCOP

25.09

5 septembre 2025

Sur ce document

Formation Module S60
Titre SQL avancé pour le transactionnel
Révision 25.09
PDF https://dali.bo/s60_pdf
EPUB https://dali.bo/s60_epub
HTML https://dali.bo/s60_html
Slides https://dali.bo/s60_slides
TP https://dali.bo/s60_tp
TP (solutions) https://dali.bo/s60_solutions

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

SQL avancé pour le transactionnel

Préambule

  • SQL et PostgreSQL proposent de nombreuses fonctionnalités avancées
    • normes SQL:99, 2003, 2008 et 2011
    • parfois, extensions propres à PostgreSQL
  • LIMIT/OFFSET
  • Jointures LATERAL
  • UPSERT : INSERT ou UPDATE
  • Common Table Expressions
  • Serializable Snapshot Isolation

Objectifs

  • Aller au-delà de SQL:92
  • Concevoir des requêtes simples pour résoudre des problèmes complexes

LIMIT

  • Clause LIMIT
  • ou syntaxe en norme SQL : FETCH FIRST xx ROWS
  • Utilisation :
    • limite le nombre de lignes du résultat

LIMIT : exemple

SELECT *
  FROM employes
LIMIT 2;
 matricule |   nom    | service  | salaire
-----------+----------+----------+----------
 00000001  | Dupuis   |          | 10000.00
 00000004  | Fantasio | Courrier |  4500.00
(2 lignes)

OFFSET

  • Clause OFFSET
    • à utiliser avec LIMIT
  • Utilité :
    • pagination de résultat
    • sauter les n premières lignes avant d’afficher le résultat

OFFSET : exemple (1/2)

  • Sans offset :
SELECT *
  FROM employes
 LIMIT 2
 ORDER BY matricule;
 matricule |   nom    | service  | salaire
-----------+----------+----------+----------
 00000001  | Dupuis   |          | 10000.00
 00000004  | Fantasio | Courrier |  4500.00

OFFSET : exemple (2/2)

  • En sautant les deux premières lignes :
SELECT *
  FROM employes
 ORDER BY matricule
 LIMIT 2
 OFFSET 2;
 matricule |   nom    |   service   | salaire
-----------+----------+-------------+---------
 00000006  | Prunelle | Publication | 4000.00
 00000020  | Lagaffe  | Courrier    | 3000.00

OFFSET : problèmes

  • OFFSET est problématique
    • beaucoup de données lues
    • temps de réponse dégradés
  • Alternative possible
    • utilisation d’un index sur le critère de tri
    • critère de filtrage sur la page précédente
  • https://use-the-index-luke.com/fr/no-offset

RETURNING

  • Clause RETURNING
  • Utilité :
    • récupérer les enregistrements modifiés
    • avec INSERT
    • avec UPDATE
    • avec DELETE

RETURNING : exemple

CREATE TABLE test_returning (id serial primary key, val integer);
-- Insertion de la valeur val seulement
INSERT INTO test_returning (val)
  VALUES (10)
RETURNING id, val;
 id | val
----+-----
  1 |  10
(1 ligne)

UPSERT (INSERT … ON CONFLICT)

  • INSERT ou UPDATE ?
    • INSERT … ON CONFLICT DO { NOTHING | UPDATE }
  • Utilité :
    • mettre à jour en cas de conflit sur un INSERT
    • ne rien faire en cas de conflit sur un INSERT
  • Plutôt préférer MERGE

UPSERT : problème à résoudre

  • Insérer une ligne déjà existante provoque une erreur :
INSERT INTO employes (matricule, nom, service, salaire)
 VALUES ('00000001', 'Marsupilami', 'Direction', 50000.00);
ERROR:  duplicate key value violates unique constraint
        "employes_pkey"
DETAIL:  Key (matricule)=(00000001) already exists.

ON CONFLICT DO NOTHING

  • La clause ON CONFLICT DO NOTHING évite d’insérer une ligne existante :
INSERT INTO employes (matricule, nom, service, salaire)
   VALUES ('00000001', 'Marsupilami', 'Direction', 50000.00)
   ON CONFLICT DO NOTHING;
INSERT 0 0

ON CONFLICT DO NOTHING : syntaxe

INSERT …
ON CONFLICT
  DO NOTHING;

ON CONFLICT DO UPDATE

INSERT INTO employes (matricule, nom, service, salaire)
VALUES ('00000001', 'M. Pirate', 'Direction', 0.00)
ON CONFLICT (matricule)
   DO UPDATE SET salaire = employes.salaire,
                 nom = excluded.nom
RETURNING *;
 matricule |    nom    |   service   | salaire
-----------+-----------+-------------+----------
 00000001  | M. Pirate | Direction   | 50000.00

ON CONFLICT DO UPDATE

  • Avec plusieurs lignes insérées :
INSERT INTO employes (matricule, nom, service, salaire)
VALUES ('00000002', 'Moizelle Jeanne', 'Publication', 3000.00),
       ('00000040', 'Lebrac', 'Publication', 3100.00)
ON CONFLICT (matricule)
   DO UPDATE SET salaire = employes.salaire,
                 nom = excluded.nom
RETURNING *;
 matricule |       nom       |   service   | salaire
-----------+-----------------+-------------+----------
 00000002  | Moizelle Jeanne | Publication |  3000.00
 00000040  | Lebrac          | Publication |  3000.00

ON CONFLICT DO UPDATE : syntaxe

  • Colonne(s) portant(s) une contrainte d’unicité
  • Pseudo-table excluded
INSERT ….
ON CONFLICT (<colonne clé>)
  DO UPDATE
        SET colonne_a_modifier = excluded.colonne,
            autre_colonne_a_modifier = excluded.autre_colonne,
            …;

MERGE

MERGE INTO mesures_capteurs c
USING import_mesures_capteurs i
ON c.id = i .id
WHEN NOT MATCHED THEN
  INSERT (id, top_mesure, derniere_mesure, derniere_maj)
  VALUES (i.id, i.mesure, i.mesure, current_timestamp)
WHEN MATCHED AND ( c.derniere_maj + INTERVAL '10 days' <= current_timestamp ) THEN
  DELETE
WHEN MATCHED AND ( c.top_mesure > i.mesure ) THEN
  UPDATE
  SET derniere_mesure = i.mesure, derniere_maj = current_timestamp
WHEN MATCHED THEN
  UPDATE SET top_mesure = i.mesure, derniere_mesure = i.mesure, derniere_maj = current_timestamp
;

LATERAL

  • Jointures LATERAL
    • SQL:99
    • équivalent d’une boucle foreach

LATERAL : utilité

  • Utilité :
    • Top-N à partir de plusieurs tables
    • jointure avec une fonction retournant un ensemble
    • exemple : afficher les 5 derniers messages des 5 derniers sujets actifs d’un forum

LATERAL : exemple

SELECT titre,
       top_5_messages.date_publication,
       top_5_messages.extrait
  FROM sujets,
  LATERAL(SELECT date_publication,
                 substr(message, 0, 100) AS extrait
          FROM messages
         WHERE sujets.sujet_id = messages.sujet_id
         ORDER BY date_publication DESC
         LIMIT 5) top_5_messages
 ORDER BY sujets.date_modification DESC,
          top_5_messages.date_publication DESC
 LIMIT 25;

LATERAL : principe

LATERAL : avec une fonction

  • Utilisation avec une fonction retournant un ensemble
    • clause LATERAL optionnelle
  • Utilité :
    • extraire les données d’un tableau ou d’une structure JSON sous forme tabulaire
    • utiliser une fonction métier qui retourne un ensemble X selon un ensemble Y fourni

LATERAL : exemple avec une fonction

SELECT titre,
       top_5_messages.date_publication,
       top_5_messages.extrait
  FROM sujets
       -- cette fonction peut retourner plusieurs lignes
       get_top_5_messages(sujet_id) AS top_5_messages
 ORDER BY sujets.date_modification DESC
 LIMIT 25;

Common Table Expressions (CTE)

  • Common Table Expressions
    • clauses WITH et WITH RECURSIVE
  • Utilité :
    • factoriser des sous-requêtes

CTE et SELECT

  • Utilité
    • factoriser des sous-requêtes
    • améliorer la lisibilité d’une requête

CTE et SELECT : exemple

WITH resultat AS (
   /* requête complexe */
)
SELECT *
  FROM resultat
  WHERE nb < 5;

CTE et SELECT : syntaxe

WITH nom_vue1 AS (
 <requête pour générer la vue 1>
), nom_vue2 AS (
 <requête pour générer la vue 2, pouvant utiliser la vue 1>
)
<requête principale utilisant vue 1 et/ou vue2> ;

CTE, MATERIALIZED, et barrière d’optimisation

  • Clause MATERIALIZED pour forcer une barrière d’optimisation
  • Normalement automatique, et pour le meilleur
  • Parfois un souci

CTE en écriture

  • CTE avec des requêtes en modification
    • avec INSERT/UPDATE/DELETE
    • et éventuellement RETURNING
  • Exemples d’utilisation :
    • requête complexe
    • écriture dans plusieurs tables
    • déplacement de données (archivage…)

CTE en écriture : exemple

WITH donnees_a_archiver AS (
DELETE FROM donnes_courantes
 WHERE date < '2015-01-01'
 RETURNING *
)
INSERT INTO donnes_archivees
SELECT * FROM donnees_a_archiver ;

CTE récursive

  • SQL permet d’exprimer des récursions
    • WITH RECURSIVE
  • Utilité :
    • récupérer une arborescence de menu hiérarchique
    • parcourir des graphes (réseaux sociaux, etc.)

CTE récursive : exemple (1/2)

WITH RECURSIVE suite AS (
SELECT 1 AS valeur
UNION ALL
SELECT valeur + 1
  FROM suite
 WHERE valeur < 10
)
SELECT * FROM suite;

CTE récursive : principe

  • 1ère étape : initialisation de la récursion

CTE récursive : principe

  • récursion : la requête s’appelle elle-même

CTE récursive : exemple (2/2)

WITH RECURSIVE parcours_menu AS (
SELECT menu_id, libelle, parent_id,
       libelle AS arborescence
  FROM entrees_menu
 WHERE libelle = 'Terminal'
   AND parent_id IS NULL
UNION ALL
SELECT menu.menu_id, menu.libelle, menu.parent_id,
       arborescence || '/' || menu.libelle
  FROM entrees_menu menu
  JOIN parcours_menu parent
    ON (menu.parent_id = parent.menu_id)
)
SELECT * FROM parcours_menu;

Concurrence d’accès

  • Problèmes pouvant se poser :
    • UPDATE perdu
    • lecture non répétable
  • Plusieurs solutions possibles
    • versionnement des lignes
    • SELECT FOR UPDATE
    • SERIALIZABLE

SELECT FOR UPDATE

  • SELECT FOR UPDATE
  • Utilité :
    • « réserver » des lignes en vue de leur mise à jour
    • éviter les problèmes de concurrence d’accès

SKIP LOCKED

  • SELECT FOR UPDATE SKIP LOCKED
  • Utilité :
    • files d’attentes parallélisables

Serializable Snapshot Isolation

SSI : Serializable Snapshot Isolation

  • Chaque transaction est seule sur la base
  • Si on ne peut maintenir l’illusion
    • une des transactions en cours est annulée
  • Sans blocage
  • On doit être capable de rejouer la transaction
  • Toutes les transactions impliquées doivent être serializable
  • default_transaction_isolation=serializable dans la configuration

Conclusion

  • SQL est un langage très riche
  • Connaître les nouveautés des versions de la norme depuis 20 ans permet de
    • gagner énormément de temps de développemment
    • mais aussi de performance

Travaux pratiques

Travaux pratiques (solutions)