SQL avancé pour le transactionnel

Module S5

Dalibo SCOP

24.04

17 avril 2024

Sur ce document

Formation Module S5
Titre SQL avancé pour le transactionnel
Révision 24.04
PDF https://dali.bo/s5_pdf
EPUB https://dali.bo/s5_epub
HTML https://dali.bo/s5_html
Slides https://dali.bo/s5_slides
TP https://dali.bo/s5_tp
TP (solutions) https://dali.bo/s5_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 12 à 16.

SQL avancé pour le transactionnel

Préambule

  • SQL et PostgreSQL proposent de nombreuses possibilité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
  • Article sur le sujet

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);

INSERT INTO test_returning (val)
  VALUES (10)
RETURNING id, val;

 id | val
----+-----
  1 |  10
(1 ligne)

UPSERT

  • INSERT ou UPDATE ?
    • INSERT ... ON CONFLICT DO { NOTHING | UPDATE }
    • à partir de la version 9.5
  • Utilité :
    • mettre à jour en cas de conflit sur un INSERT
    • ne rien faire en cas de conflit sur un INSERT

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,
            ...;

LATERAL

  • Jointures LATERAL
    • SQL:99
    • PostgreSQL 9.3
    • équivalent d’une boucle foreach
  • Utilisations
    • top-N à partir de plusieurs tables
    • jointure avec une fonction retournant un ensemble

LATERAL : avec une sous-requête

  • Jointure LATERAL
    • équivalent de foreach
  • Utilité :
    • Top-N à partir de plusieurs tables
    • 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

Principe LATERAL

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 la 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,
       get_top_5_messages(sujet_id) AS top_5_messages
 ORDER BY sujets.date_modification DESC
 LIMIT 25;

Common Table Expressions

  • 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 [ [ NOT ] MATERIALIZED ] (
 <requête pour générer la vue 1>
)
SELECT *
  FROM nom_vue1;

CTE et barrière d’optimisation

  • Attention, une CTE est une barrière d’optimisation !
    • pas de transformations
    • pas de propagation des prédicats
  • Sauf à partir de la version 12
    • clause MATERIALIZED pour obtenir cette barrière

CTE en écriture

  • CTE avec des requêtes en modification
    • avec INSERT/UPDATE/DELETE
    • et éventuellement RETURNING
    • obligatoirement exécuté sur PostgreSQL
  • Exemple d’utilisation :
    • archiver des données
    • partitionner les données d’une table
    • débugger une requête complexe

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

Principe CTE recursive - 1

CTE récursive : principe

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

Principe CTE recursive - 2

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
    • PostgreSQL 9.5
  • Utilité :
    • implémente des files d’attentes parallélisables

Serializable Snapshot Isolation

SSI : Serializable Snapshot Isolation (9.1+)

  • 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)

  1. La solution actuelle semble techniquement meilleure et la solution actuelle a donc été choisie. Le wiki du projet PostgreSQL montre que l’ordre MERGE a été étudié et qu’un certain nombre d’aspects cruciaux n’ont pas été spécifiés, amenant le projet PostgreSQL à utiliser sa propre version. Voir la documentation : https://wiki.postgresql.org/wiki/UPSERT#MERGE_disadvantages.↩︎