SQL avancé pour le transactionnel

17 avril 2024

Dalibo SCOP

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

Vous trouverez en ligne les différentes versions complètes de ce document.


Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

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

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

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.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

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

La norme SQL a continué d’évoluer et a bénéficié d’un grand nombre d’améliorations. Beaucoup de requêtes qu’il était difficile d’exprimer avec les premières incarnations de la norme sont maintenant faciles à réaliser avec les dernières évolutions.

Ce module a pour objectif de voir les fonctionnalités pouvant être utiles pour développer une application transactionnelle.


  • 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

La clause LIMIT, ou sa déclinaison normalisée par le comité ISO FETCH FIRST xx ROWS, permet de limiter le nombre de lignes résultant d’une requête SQL. La syntaxe normalisée vient de DB2 d’IBM et va être amenée à apparaître sur la plupart des bases de données. La syntaxe LIMIT reste néanmoins disponible sur de nombreux SGBD et est plus concise.


LIMIT : exemple

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

L’exemple ci-dessous s’appuie sur le jeu d’essai suivant :

SELECT *
  FROM employes ;
 matricule |   nom    |   service   | salaire
-----------+----------+-------------+----------
 00000001  | Dupuis   |             | 10000.00
 00000004  | Fantasio | Courrier    |  4500.00
 00000006  | Prunelle | Publication |  4000.00
 00000020  | Lagaffe  | Courrier    |  3000.00
 00000040  | Lebrac   | Publication |  3000.00
(5 lignes)

Il faut faire attention au fait que ces fonctions ne permettent pas d’obtenir des résultats stables si les données ne sont pas triées explicitement. En effet, le standard SQL ne garantit en aucune façon l’ordre des résultats à moins d’employer la clause ORDER BY, et que l’ensemble des champs sur lequel on trie soit unique et non null.

Si une ligne était modifiée, changeant sa position physique dans la table, le résultat de la requête ne serait pas le même. Par exemple, en réalisant une mise à jour fictive de la ligne correspondant au matricule 00000001 :

UPDATE employes
   SET nom = nom
 WHERE matricule = '00000001';

L’ordre du résultat n’est pas garanti :

SELECT *
  FROM employes
 LIMIT 2;
 matricule |   nom    |   service   | salaire
-----------+----------+-------------+---------
 00000004  | Fantasio | Courrier    | 4500.00
 00000006  | Prunelle | Publication | 4000.00
(2 lignes)

L’application d’un critère de tri explicite permet d’obtenir la sortie souhaitée :

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

OFFSET

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

Ainsi, en reprenant le jeu d’essai utilisé précédemment :

SELECT * FROM employes ;
 matricule |   nom    |   service   | salaire
-----------+----------+-------------+----------
 00000001  | Dupuis   |             | 10000.00
 00000004  | Fantasio | Courrier    |  4500.00
 00000006  | Prunelle | Publication |  4000.00
 00000020  | Lagaffe  | Courrier    |  3000.00
 00000040  | Lebrac   | Publication |  3000.00
(5 lignes)

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

Cependant, sur un jeu de données conséquent et une pagination importante, ce principe de fonctionnement peut devenir contre-performant. En effet, la base de données devra lire malgré tout les enregistrements qui n’apparaîtront pas dans le résultat de la requête, simplement dans le but de les compter.

Soit la table posts suivante (téléchargeable sur https://dali.bo/tp_posts, à laquelle on ajoute un index sur (id_article_id, id_post)) :

\d posts
                              Table « public.posts »
  Colonne   |           Type           | Collationnement | NULL-able | Par défaut
------------+--------------------------+-----------------+-----------+------------
 id_article | integer                  |                 |           |
 id_post    | integer                  |                 |           |
 ts         | timestamp with time zone |                 |           |
 message    | text                     |                 |           |
Index :
    "posts_id_article_id_post" btree (id_article, id_post)
    "posts_ts_idx" btree (ts)

Si l’on souhaite récupérer les 10 premiers enregistrements :

SELECT  *
FROM    posts
WHERE   id_article =12
ORDER BY id_post
LIMIT   10 ;

On obtient le plan d’exécution suivant :

                                  QUERY PLAN
------------------------------------------------------------------------------
 Limit  (cost=0.43..18.26 rows=10 width=115)
        (actual time=0.043..0.053 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                  (cost=0.43..1745.88 rows=979 width=115)
               (actual time=0.042..0.051 rows=10 loops=1)
         Index Cond: (id_article = 12)
 Planning Time: 0.204 ms
 Execution Time: 0.066 ms

La requête est rapide car elle profite d’un index bien trié et elle ne lit que peu de données, ce qui est bien.

En revanche, si l’on saute un nombre conséquent d’enregistrements grâce à la clause OFFSET, la situation devient problématique :

SELECT  *
FROM    posts
WHERE   id_article = 12
ORDER BY id_post
LIMIT   10
OFFSET  900 ;

Le plan n’est plus le même :

Limit  (cost=1605.04..1622.86 rows=10 width=115)
        (actual time=0.216..0.221 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                   (cost=0.43..1745.88 rows=979 width=115)
                   (actual time=0.018..0.194 rows=910 loops=1)
         Index Cond: (id_article = 12)
 Planning Time: 0.062 ms
 Execution Time: 0.243 ms

Pour répondre à la requête, PostgreSQL choisit la lecture de l’ensemble des résultats, puis leur tri, pour enfin appliquer la limite. En effet, LIMIT et OFFSET ne peuvent s’opèrer que sur le résultat trié : il faut lire les 910 posts avant de pouvoir choisir les 10 derniers.

Le problème de ce plan est que, plus le jeu de données sera important, plus les temps de réponse seront importants. Ils seront encore plus importants si le tri n’est pas utilisable dans un index, ou si l’on déclenche un tri sur disque. Il faut donc trouver une solution pour les minimiser.

Les problèmes de l’utilisation de la clause OFFSET sont parfaitement expliqués dans cet article.

Dans notre cas, le principe est d’abord de créer un index qui contient le critère ainsi que le champ qui fixe la pagination (l’index existant convient). Puis on mémorise à quel post_id la page précédente s’est arrêtée, pour le donner comme critère de filtrage (ici 12900). Il suffit donc de récupérer les 10 articles pour lesquels id_article = 12 et id_post > 12900 :

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   id_article = 12
AND     id_post> 12900
ORDER BY id_post
LIMIT   10 ;
                            QUERY PLAN
----------------------------------------------------------------
 Limit  (cost=0.43..18.29 rows=10 width=115)
        (actual time=0.018..0.024 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                         (cost=0.43..1743.02 rows=976 width=115)
                         (actual time=0.016..0.020 rows=10 loops=1)
         Index Cond: ((id_article = 12) AND (id_post > 12900))
 Planning Time: 0.111 ms
 Execution Time: 0.039 ms

RETURNING

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

La clause RETURNING permet de récupérer les valeurs modifiées par un ordre DML. Ainsi, la clause RETURNING associée à l’ordre INSERT permet d’obtenir une ou plusieurs colonnes des lignes insérées.


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)

Cela permet par exemple de récupérer la valeur de colonnes portant une valeur par défaut, comme la valeur affectée par une séquence, comme sur l’exemple ci-dessus.

La clause RETURNING permet également de récupérer les valeurs des colonnes mises à jour :

UPDATE test_returning
   SET val = val + 10
 WHERE id = 1
 RETURNING id, val;
id | val
----+-----
 1 |  20
(1 ligne)

Associée à l’ordre DELETE, il est possible d’obtenir les lignes supprimées :

DELETE FROM test_returning
 WHERE val < 30
 RETURNING id, val;
id | val
----+-----
 1 |  20
(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

L’implémentation de l’UPSERT peut poser des questions sur la concurrence d’accès. L’implémentation de PostgreSQL de ON CONFLICT DO UPDATE est une opération atomique, c’est-à-dire que PostgreSQL garantit qu’il n’y aura pas de conditions d’exécution qui pourront amener à des erreurs. L’utilisation d’une contrainte d’unicité n’est pas étrangère à cela, elle permet en effet de pouvoir vérifier que la ligne n’existe pas, et si elle existe déjà, de verrouiller la ligne à mettre à jour de façon atomique.

En comparaison, plusieurs approches naïves présentent des problèmes de concurrences d’accès. Les différentes approches sont décrites dans cet article de depesz. Elle présente toutes des problèmes de race conditions qui peuvent entraîner des erreurs. Une autre possibilité aurait été d’utiliser une CTE en écriture, mais elle présente également les problèmes de concurrence d’accès décrits dans l’article.

Sur des traitements d’intégration de données, il s’agit d’un comportement qui n’est pas toujours souhaitable. La norme SQL propose l’ordre MERGE pour palier à des problèmes de ce type, mais il est peu probable de le voir rapidement implémenté dans PostgreSQL1. L’ordre INSERT s’est toutefois vu étendu avec PostgreSQL 9.5 pour gérer les conflits à l’insertion.

Les exemples suivants s’appuient sur le jeu de données suivant :

\d employes
       Table "public.employes"
  Column   |     Type     | Modifiers
-----------+--------------+-----------
 matricule | character(8) | not null
 nom       | text         | not null
 service   | text         |
 salaire   | numeric(7,2) |
Indexes:
    "employes_pkey" PRIMARY KEY, btree (matricule)
SELECT * FROM employes ;
 matricule |   nom    |   service   | salaire
-----------+----------+-------------+----------
 00000001  | Dupuis   | Direction   | 10000.00
 00000004  | Fantasio | Courrier    |  4500.00
 00000006  | Prunelle | Publication |  4000.00
 00000020  | Lagaffe  | Courrier    |  3000.00
 00000040  | Lebrac   | Publication |  3000.00

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.

Si l’on souhaite insérer une ligne contenant un matricule déjà existant, une erreur de clé dupliquée est levée et toute la transaction est annulée.


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

Les données n’ont pas été modifiées :

SELECT * FROM employes ;
 matricule |   nom    |   service   | salaire
-----------+----------+-------------+----------
 00000004  | Fantasio | Courrier    |  4500.00
 00000006  | Prunelle | Publication |  4000.00
 00000020  | Lagaffe  | Courrier    |  3000.00
 00000040  | Lebrac   | Publication |  3000.00
 00000001  | Dupuis   | Direction   | 10000.00
(5 rows)

La transaction est toujours valide.


ON CONFLICT DO NOTHING : syntaxe

INSERT ....
ON CONFLICT
  DO NOTHING;

Il suffit d’indiquer à PostgreSQL de ne rien faire en cas de conflit sur une valeur dupliquée avec la clause ON CONFLICT DO NOTHING placée à la fin de l’ordre INSERT qui peut poser problème.

Dans ce cas, si une rupture d’unicité est détectée, alors PostgreSQL ignorera l’erreur, silencieusement. En revanche, si une erreur apparaît sur une autre contrainte, l’erreur sera levée.

En prenant l’exemple suivant :

CREATE TABLE test_upsert (
  i serial PRIMARY KEY,
  v text UNIQUE,
  x integer CHECK (x > 0)
);

INSERT INTO test_upsert (v, x) VALUES ('x', 1);

L’insertion d’une valeur dupliquée provoque bien une erreur d’unicité :

INSERT INTO test_upsert (v, x) VALUES ('x', 1);
ERROR:  duplicate key value violates unique constraint "test_upsert_v_key"

L’erreur d’unicité est bien ignorée si la ligne existe déjà, le résultat est INSERT 0 0 qui indique qu’aucune ligne n’a été insérée :

INSERT INTO test_upsert (v, x)
VALUES ('x', 1)
 ON CONFLICT DO NOTHING;
INSERT 0 0

L’insertion est aussi ignorée si l’on tente d’insérer des lignes rompant la contrainte d’unicité mais ne comportant pas les mêmes valeurs pour d’autres colonnes :

INSERT INTO test_upsert (v, x)
VALUES ('x', 4)
 ON CONFLICT DO NOTHING;
INSERT 0 0

Si l’on insère une valeur interdite par la contrainte CHECK, une erreur est bien levée :

INSERT INTO test_upsert (v, x)
VALUES ('x', 0)
 ON CONFLICT DO NOTHING;
ERROR:  new row for relation "test_upsert" violates check constraint
        "test_upsert_x_check"
DETAIL:  Failing row contains (4, x, 0).

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

La clause ON CONFLICT permet de déterminer une colonne sur laquelle le conflit peut arriver. Cette colonne ou ces colonnes doivent porter une contrainte d’unicité ou une contrainte d’exclusion, c’est à dire une contrainte portée par un index. La clause DO UPDATE associée fait référence aux valeurs rejetées par le conflit à l’aide de la pseudo-table excluded. Les valeurs courantes sont accessibles en préfixant les colonnes avec le nom de la table. L’exemple montre cela.

Avec la requête de l’exemple, on voit que le salaire du directeur n’a pas été modifié, mais son nom l’a été :

SELECT * FROM employes ;
 matricule |    nom    |   service   | salaire
-----------+-----------+-------------+----------
 00000004  | Fantasio  | Courrier    |  4500.00
 00000006  | Prunelle  | Publication |  4000.00
 00000020  | Lagaffe   | Courrier    |  3000.00
 00000040  | Lebrac    | Publication |  3000.00
 00000001  | M. Pirate | Direction   | 10000.00
(5 rows)

La clause ON CONFLICT permet également de définir une contrainte d’intégrité sur laquelle on réagit en cas de conflit :

INSERT INTO employes (matricule, nom, service, salaire)
VALUES ('00000001', 'Marsupilami', 'Direction', 50000.00)
ON CONFLICT ON CONSTRAINT employes_pkey
   DO UPDATE SET salaire = excluded.salaire;

On remarque que seul le salaire du directeur a changé :

SELECT * FROM employes ;
 matricule |   nom     |   service   | salaire
-----------+----------+-------------+----------
 00000004  | Fantasio  | Courrier    |  4500.00
 00000006  | Prunelle  | Publication |  4000.00
 00000020  | Lagaffe   | Courrier    |  3000.00
 00000040  | Lebrac    | Publication |  3000.00
 00000001  | M. Pirate | Direction   | 50000.00
(5 rows)

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

Bien sûr, on peut insérer plusieurs lignes, INSERT ON CONFLICT réagira uniquement sur les doublons :

La nouvelle employée, Moizelle Jeanne a été intégrée dans la table employes, et Lebrac a été traité comme un doublon, en appliquant la règle de mise à jour vue plus haut : seul le nom est mis à jour et le salaire est inchangé.

SELECT * FROM employes ;
 matricule |       nom       |   service   | salaire
-----------+-----------------+-------------+----------
 00000004  | Fantasio        | Courrier    |  4500.00
 00000006  | Prunelle        | Publication |  4000.00
 00000020  | Lagaffe         | Courrier    |  3000.00
 00000001  | M. Pirate       | Direction   | 50000.00
 00000002  | Moizelle Jeanne | Publication |  3000.00
 00000040  | Lebrac          | Publication |  3000.00
(6 rows)

À noter que la clause SET salaire = employes.salaire est inutile, c’est ce que fait PostgreSQL implicitement.


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

Si l’on choisit de réaliser une mise à jour plutôt que de générer une erreur, on utilisera la clause ON CONFLICT DO UPDATE. Il faudra dans ce cas préciser là ou les colonnes qui portent une contrainte d’unicité. Cette contrainte d’unicité permettra de détecter la duplication de valeur, PostgreSQL pourra alors appliquer la règle de mise à jour édictée.

La règle de mise à jour permet de définir très finement les colonnes à mettre à jour et les colonnes à ne pas mettre à jour. Dans ce contexte, la pseudo-table excluded représente l’ensemble rejeté par l’INSERT. Il faudra explicitement indiquer les colonnes dont la valeur sera mise à jour à partir des valeurs que l’on tente d’insérer, reprise de la pseudo-table excluded :

ON CONFLICT (...)
  DO UPDATE
  SET colonne = excluded.colonne,
      autre_colonne = excluded.autre_colonne,
      ...

En alternative, il est possible d’indiquer un nom de contrainte plutôt que le nom d’une colonne portant une contrainte d’unicité :

INSERT ....
ON CONFLICT ON CONSTRAINT nom_contrainte
  DO UPDATE
        SET colonne_a_modifier = excluded.colonne,
            autre_colonne_a_modifier = excluded.autre_colonne,
            ...;

De plus amples informations quant à la syntaxe sont disponibles dans la documentation.


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 apparaît dans la révision de la norme SQL de 1999. Elle permet d’appliquer une requête ou une fonction sur le résultat d’une table.


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

La clause LATERAL existe dans la norme SQL depuis plusieurs années. L’implémentation de cette clause dans la plupart des SGBD reste cependant relativement récente.

Elle permet d’utiliser les données de la requête principale dans une sous-requête. La sous-requête sera appliquée à chaque enregistrement retourné par la requête principale.


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;

L’exemple ci-dessus montre comment afficher les 5 derniers messages postés sur les 5 derniers sujets actifs d’un forum avec la clause LATERAL.

Une autre forme d’écriture emploie le mot clé JOIN, inutile dans cet exemple. Il peut avoir son intérêt si l’on utilise une jointure externe (LEFT JOIN par exemple si un sujet n’impliquait pas forcément la présence d’un message) :

SELECT titre, top_5_messages.date_publication, top_5_messages.extrait
  FROM sujets
  JOIN 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
    ON (true) -- condition de jointure toujours vraie
 ORDER BY sujets.date_modification DESC, top_5_messages.date_publication DESC
 LIMIT 25;

Il aurait été possible de réaliser cette requête par d’autres moyens, mais LATERAL permet d’obtenir la requête la plus performante. Une autre approche quasiment aussi performante aurait été de faire appel à une fonction retournant les 5 enregistrements souhaités.

À noter qu’une colonne date_modification a été ajouté à la table sujets afin de déterminer rapidement les derniers sujets modifiés. Sans cela, il faudrait parcourir l’ensemble des sujets, récupérer la date de publication des derniers messages avec une jointure LATERAL et récupérer les 5 derniers sujets actifs. Cela nécessite de lire beaucoup de données. Un trigger positionné sur la table messages permettra d’entretenir la colonne date_modification sur la table sujets sans difficulté. Il s’agit donc ici d’une entorse aux règles de modélisation en vue d’optimiser les traitements.

Un index sur les colonnes sujet_id et date_publication permettra de minimiser les accès pour cette requête :

CREATE INDEX ON messages (sujet_id, date_publication DESC);

LATERAL : principe

Principe LATERAL

Si nous n’avions pas la clause LATERAL, nous pourrions être tentés d’écrire la requête suivante :

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

Cependant, la norme SQL interdit une telle construction, il n’est pas possible de référencer la table principale dans une sous-requête. Mais avec la clause LATERAL, la sous-requête peut faire appel à la table principale.


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

L’exemple ci-dessous montre qu’il est possible d’utiliser une fonction retournant un ensemble (SRF pour Set Returning Functions).


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;

La fonction get_top_5_messages est la suivante :

CREATE OR REPLACE FUNCTION get_top_5_messages (p_sujet_id integer)
RETURNS TABLE (date_publication timestamp, extrait text)
AS $PROC$
BEGIN
  RETURN QUERY SELECT date_publication, substr(message, 0, 100) AS extrait
    FROM messages
   WHERE messages.sujet_id = p_sujet_id
   ORDER BY date_publication DESC
   LIMIT 5;
END;
$PROC$ LANGUAGE plpgsql;

La clause LATERAL n’est pas obligatoire, mais elle s’utiliserait ainsi :

SELECT titre, top_5_messages.date_publication, top_5_messages.extrait
  FROM sujets, LATERAL 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

Les Common Table Expressions ou CTE permettent de factoriser la définition d’une sous-requête qui pourrait être appelée plusieurs fois.

Une CTE est exprimée avec la clause WITH. Cette clause permet de définir des vues éphémères qui seront utilisées les unes après les autres et au final utilisées dans la requête principale.

Avant la version 12, une CTE était forcément matérialisée. À partir de la version 12, ce n’est plus le cas. Le seul moyen de s’en assurer revient à ajouter la clause MATERIALIZED.


CTE et SELECT : exemple

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

On utilise principalement une CTE pour factoriser la définition d’une sous-requête commune, comme dans l’exemple ci-dessus.

Un autre exemple un peu plus complexe :

WITH resume_commandes AS (
SELECT c.numero_commande, c.client_id, quantite*prix_unitaire AS montant
  FROM commandes c
  JOIN lignes_commandes l
    ON (c.numero_commande = l.numero_commande)
 WHERE date_commande BETWEEN '2014-01-01' AND '2014-12-31'
)
SELECT type_client, NULL AS pays, SUM(montant) AS montant_total_commande
  FROM resume_commandes
  JOIN clients
    ON (resume_commandes.client_id = clients.client_id)
 GROUP BY type_client
UNION ALL
SELECT NULL, code_pays AS pays, SUM(montant)
  FROM resume_commandes r
  JOIN clients cl
    ON (r.client_id = cl.client_id)
  JOIN contacts co
    ON (cl.contact_id = co.contact_id)
 GROUP BY code_pays;

Le plan d’exécution de la requête montre que la vue resume_commandes est exécutée une seule fois et son résultat est utilisé par les deux opérations de regroupements définies dans la requête principale :

                                QUERY PLAN
-------------------------------------------------------------------------------
 Append  (cost=244618.50..323855.66 rows=12 width=67)
   CTE resume_commandes
     ->  Hash Join  (cost=31886.90..174241.18 rows=1216034 width=26)
           Hash Cond: (l.numero_commande = c.numero_commande)
           ->  Seq Scan on lignes_commandes l
                    (cost=0.00..73621.47 rows=3141947 width=18)
           ->  Hash  (cost=25159.00..25159.00 rows=387032 width=16)
                 ->  Seq Scan on commandes c
                        (cost=0.00..25159.00 rows=387032 width=16)
                       Filter: ((date_commande >= '2014-01-01'::date)
                                AND (date_commande <= '2014-12-31'::date))
   ->  HashAggregate  (cost=70377.32..70377.36 rows=3 width=34)
         Group Key: clients.type_client
         ->  Hash Join  (cost=3765.00..64297.15 rows=1216034 width=34)
               Hash Cond: (resume_commandes.client_id = clients.client_id)
               ->  CTE Scan on resume_commandes
                        (cost=0.00..24320.68 rows=1216034 width=40)
               ->  Hash  (cost=2026.00..2026.00 rows=100000 width=10)
                     ->  Seq Scan on clients
                            (cost=0.00..2026.00 rows=100000 width=10)
   ->  HashAggregate  (cost=79236.89..79237.00 rows=9 width=35)
         Group Key: co.code_pays
         ->  Hash Join  (cost=12624.57..73156.72 rows=1216034 width=35)
               Hash Cond: (r.client_id = cl.client_id)
               ->  CTE Scan on resume_commandes r
                        (cost=0.00..24320.68 rows=1216034 width=40)
               ->  Hash  (cost=10885.57..10885.57 rows=100000 width=11)
                     ->  Hash Join
                                (cost=3765.00..10885.57 rows=100000 width=11)
                           Hash Cond: (co.contact_id = cl.contact_id)
                           ->  Seq Scan on contacts co
                                    (cost=0.00..4143.05 rows=110005 width=11)
                           ->  Hash (cost=2026.00..2026.00 rows=100000 width=16)
                                 ->  Seq Scan on clients cl
                                       (cost=0.00..2026.00 rows=100000 width=16)

Si la requête avait été écrite sans CTE, donc en exprimant deux fois la même sous-requête, le coût d’exécution aurait été multiplié par deux car il aurait fallu exécuter la sous-requête deux fois au lieu d’une.

On utilise également les CTE pour améliorer la lisibilité des requêtes complexes, mais cela peut poser des problèmes d’optimisations, comme cela sera discuté plus bas.


CTE et SELECT : syntaxe

WITH nom_vue1 AS [ [ NOT ] MATERIALIZED ] (
 <requête pour générer la vue 1>
)
SELECT *
  FROM nom_vue1;

La syntaxe de définition d’une vue est donnée ci-dessus.

On peut néanmoins enchaîner plusieurs vues les unes à la suite des autres :

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

Il faut néanmoins être vigilant car l’optimiseur n’inclut pas la définition des CTE dans la requête principale quand il réalise les différentes passes d’optimisations.

Par exemple, sans CTE, si un prédicat appliqué dans la requête principale peut être remonté au niveau d’une sous-requête, l’optimiseur de PostgreSQL le réalisera :

EXPLAIN
SELECT MAX(date_embauche)
  FROM (SELECT * FROM employes WHERE num_service = 4) e
 WHERE e.date_embauche < '2006-01-01';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Aggregate  (cost=1.21..1.22 rows=1 width=4)
   ->  Seq Scan on employes  (cost=0.00..1.21 rows=2 width=4)
         Filter: ((date_embauche < '2006-01-01'::date) AND (num_service = 4))
(3 lignes)

Les deux prédicats num_service = 4 et date_embauche < '2006-01-01' ont été appliqués en même temps, réduisant ainsi le jeu de données à considérer dès le départ. En anglais, on parle de predicate push-down.

Une requête équivalente basée sur une CTE ne permet pas d’appliquer le filtre au plus tôt : ici le filtre inclus dans la CTE est appliqué, pas le second.

EXPLAIN
WITH e AS
  (SELECT * FROM employes WHERE num_service = 4)
SELECT MAX(date_embauche)
 FROM e
WHERE e.date_embauche < '2006-01-01';
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate  (cost=1.29..1.30 rows=1 width=4)
   CTE e
     ->  Seq Scan on employes  (cost=0.00..1.18 rows=5 width=43)
           Filter: (num_service = 4)
   ->  CTE Scan on e  (cost=0.00..0.11 rows=2 width=4)
         Filter: (date_embauche < '2006-01-01'::date)

On peut se faire piéger également en voulant calculer trop de choses dans les CTE. Dans cet autre exemple, on cherche à afficher les 7 commandes d’un client donné, le cumul des valeurs des lignes par commande étant réalisé dans un CTE :

EXPLAIN ANALYZE
WITH nos_commandes AS
(
  SELECT c.numero_commande, c.client_id, SUM(quantite*prix_unitaire) AS montant
  FROM   commandes c
  JOIN   lignes_commandes l
  ON     (c.numero_commande = l.numero_commande)
  GROUP BY 1,2
)
SELECT clients.client_id, type_client, nos_commandes.*
FROM   nos_commandes
INNER JOIN clients
ON     (nos_commandes.client_id = clients.client_id)
WHERE clients.client_id = 6845;
                           QUERY PLAN
-----------------------------------------------------------------
Nested Loop  (cost=154567.68..177117.90 rows=5000 width=58)
           (actual time=7.757..5526.148 rows=7 loops=1)
   CTE nos_commandes
     ->  GroupAggregate  (cost=3.51..154567.39 rows=1000000 width=48)
                  (actual time=0.043..5076.121 rows=1000000 loops=1)
           Group Key: c.numero_commande
           ->  Merge Join  (cost=3.51..110641.89 rows=3142550 width=26)
                           (actual time=0.017..2511.385 rows=3142632 loops=1)
                 Merge Cond: (c.numero_commande = l.numero_commande)
                 ->  Index Scan using commandes_pkey on commandes c
                           (cost=0.42..16290.72 rows=1000000 width=16)
                           (actual time=0.008..317.547 rows=1000000 loops=1)
                 ->  Index Scan using lignes_commandes_pkey on lignes_commandes l
                           (cost=0.43..52570.08 rows=3142550 width=18)
                           (actual time=0.006..1030.420 rows=3142632 loops=1)
   ->  Index Scan using clients_pkey on clients
                          (cost=0.29..0.51 rows=1 width=10)
                          (actual time=0.009..0.009 rows=1 loops=1)
         Index Cond: (client_id = 6845)
   ->  CTE Scan on nos_commandes  (cost=0.00..22500.00 rows=5000 width=48)
                                  (actual time=7.746..5526.128 rows=7 loops=1)
         Filter: (client_id = 6845)
         Rows Removed by Filter: 999993

Notez que la construction de la CTE fait un calcul sur l’intégralité des 5000 commandes et brasse un million de lignes. Puis, une fois connu le client_id, PostgreSQL parcourt cette CTE pour en récupérer une seule ligne. C’est évidemment extrêmement coûteux et dure plusieurs secondes.

Alors que sans la CTE, l’optimiseur se permet de faire la jointure avec les tables, donc à filtrer sur le client demandé, et ne fait la somme des lignes qu’après, en quelques millisecondes.

EXPLAIN ANALYZE
SELECT clients.client_id, type_client, nos_commandes.*
FROM
(
  SELECT c.numero_commande, c.client_id, SUM(quantite*prix_unitaire) AS montant
  FROM   commandes c
  JOIN   lignes_commandes l
  ON     (c.numero_commande = l.numero_commande)
  GROUP BY 1,2
) AS nos_commandes
INNER JOIN clients
ON    (nos_commandes.client_id = clients.client_id)
WHERE clients.client_id = 6845;
                           QUERY PLAN
-----------------------------------------------------------------
 Nested Loop  (cost=12.83..13.40 rows=11 width=58)
              (actual time=0.113..0.117 rows=7 loops=1)
   ->  Index Scan using clients_pkey on clients  (cost=0.29..0.51 rows=1 width=10)
                                        (actual time=0.007..0.007 rows=1 loops=1)
         Index Cond: (client_id = 6845)
   ->  HashAggregate  (cost=12.54..12.67 rows=11 width=48)
                      (actual time=0.106..0.108 rows=7 loops=1)
         Group Key: c.numero_commande
         ->  Nested Loop  (cost=0.85..12.19 rows=35 width=26)
                          (actual time=0.028..0.087 rows=23 loops=1)
               ->  Index Scan using commandes_clients_fkey on commandes c
                                         (cost=0.42..1.82 rows=11 width=16)
                                         (actual time=0.022..0.028 rows=7 loops=1)
                     Index Cond: (client_id = 6845)
               ->  Index Scan using lignes_commandes_pkey on lignes_commandes l
                                         (cost=0.43..0.89 rows=5 width=18)
                                         (actual time=0.006..0.007 rows=3 loops=7)
                     Index Cond: (numero_commande = c.numero_commande)

En plus d’améliorer la lisibilité et d’éviter la duplication de code, le mécanisme des CTE est aussi un moyen contourner certaines limitations de l’optimiseur de PostgreSQL en vue de contrôler précisément le plan d’exécution d’une requête.

Ce principe de fonctionnement a changé avec la version 12 de PostgreSQL. Par défaut, il n’y a pas de matérialisation mais celle-ci peut être forcée avec l’option MATERIALIZED.


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;

La requête d’exemple permet d’archiver des données dans une table dédiée à l’archivage en utilisant une CTE en écriture. L’emploi de la clause RETURNING permet de récupérer les lignes purgées.

Le même principe s’applique pour une table que l’on vient de partitionner. Les enregistrements se trouvent initialement dans la table mère, il faut les répartir sur les différentes partitions. On utilisera une requête reposant sur le même principe que la précédente. L’ordre INSERT visera la table principale si l’on souhaite utiliser le trigger de partition pour répartir les données. Il pourra également viser une partition donnée afin d’éviter le surcoût du trigger de partition.

En plus de ce cas d’usage simple, il est possible d’utiliser cette fonctionnalité pour débugger une requête complexe.

WITH sous-requete1 AS (

),
debug_sous-requete1 AS (
INSERT INTO debug_sousrequete1
SELECT * FROM sous-requete1
), sous-requete2 AS (
SELECT ...
  FROM sous-requete1
  JOIN ....
 WHERE ....
 GROUP BY ...
),
debug_sous-requete2 AS (
INSERT INTO debug_sousrequete2
SELECT * FROM sous-requete2
)
SELECT *
  FROM sous-requete2;

On peut également envisager une requête CTE en écriture pour émuler une requête MERGE pour réaliser une intégration de données complexe, là où l’UPSERT ne serait pas suffisant. Il faut toutefois avoir à l’esprit qu’une telle requête présente des problèmes de concurrences d’accès, pouvant entraîner des résultats inattendus si elle est employée alors que d’autres sessions modifient les données. On se contentera d’utiliser une telle requête dans des traitements batchs.

Il est important de noter que sur PostgreSQL, chaque sous-partie d’une CTE qui exécute une opération de mise à jour sera exécutée, même si elle n’est pas explicitement appelée. Par exemple :

WITH del AS (DELETE FROM nom_table),
fonction_en_ecriture AS (SELECT * FROM fonction_en_ecriture())
SELECT 1;

supprimera l’intégralité des données de la table nom_table, mais n’appellera pas la fonction fonction_en_ecriture(), même si celle-ci effectue des écritures.


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

Le langage SQL permet de réaliser des récursions avec des CTE récursives. Son principal intérêt est de pouvoir parcourir des arborescences, comme par exemple des arbres généalogiques, des arborescences de service ou des entrées de menus hiérarchiques.

Il permet également de réaliser des parcours de graphes, mais les possibilités en SQL sont plus limitées de ce côté-là. En effet, SQL utilise un algorithme de type Breadth First (parcours en largeur) où PostgreSQL produit tout le niveau courant, et approfondit ensuite la récursion. Ce fonctionnement est à l’opposé d’un algorithme Depth First (parcours en profondeur) où chaque branche est explorée à fond individuellement avant de passer à la branche suivante. Ce principe de fonctionnement de l’implémentation dans SQL peut poser des problèmes sur des recherches de types réseaux sociaux où des bases de données orientées graphes, tel que Neo4J. À noter que l’extension pgRouting implémente des algorithmes de parcours de graphes plus efficace. Cela permet de rester dans PostgreSQL mais nécessite un certain formalisme et il faut avoir conscience que pgRouting n’est pas l’outil le plus efficace car il génère un graphe en mémoire à chaque requête à résoudre, qui est perdu après l’appel.


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;

Voici le résultat de cette requête :

valeur
--------
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10

L’exécution de cette requête commence avec le SELECT 1 AS valeur (la requête avant le UNION ALL), d’où la première ligne avec la valeur 1. Puis PostgreSQL exécute le SELECT valeur + 1 FROM suite WHERE valeur < 10 tant que cette requête renvoie des lignes. À la première exécution, il additionne 1 avec la valeur précédente (1), ce qui fait qu’il renvoie 2. A la deuxième exécution, il additionne 1 avec la valeur précédente (2), ce qui fait qu’il renvoie 3. Etc. La récursivité s’arrête quand la requête ne renvoie plus de ligne, autrement dit quand la colonne vaut 10.

Cet exemple n’a aucun autre intérêt que de présenter la syntaxe permettant de réaliser une récursion en langage SQL.


CTE récursive : principe

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