Module S5

SQL avancé pour le transactionnel

DALIBO
L'expertise PostgreSQL

24.09
Table des matières

Sur ce document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Chers lectrices & lecteurs, . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
À propos de DALIBO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Remerciements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Forme de ce manuel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Licence Creative Commons CC‑BY‑NC‑SA . . . . . . . . . . . . . . . . . . . . . . . . . 2
Marques déposées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Versions de PostgreSQL couvertes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1/ SQL avancé pour le transactionnel 5

1.0.1 Préambule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.0.2 Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.0.3 Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.1 LIMIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.1.1 LIMIT : exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.1.2 OFFSET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.1.3 OFFSET : exemple (1/2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.1.4 OFFSET : exemple (2/2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.1.5 OFFSET : problèmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.2 RETURNING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.2.1 RETURNING : exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.3 UPSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.3.1 UPSERT : problème à résoudre . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.3.2 ON CONFLICT DO NOTHING . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.3.3 ON CONFLICT DO NOTHING : syntaxe . . . . . . . . . . . . . . . . . . . . . . . 16
1.3.4 ON CONFLICT DO UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.3.5 ON CONFLICT DO UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
1.3.6 ON CONFLICT DO UPDATE : syntaxe . . . . . . . . . . . . . . . . . . . . . . . . 19
1.4 LATERAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
1.4.1 LATERAL : avec une sous‑requête . . . . . . . . . . . . . . . . . . . . . . . . . 20
1.4.2 LATERAL : exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.4.3 LATERAL : principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
1.4.4 LATERAL : avec une fonction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
1.4.5 LATERAL : exemple avec une fonction . . . . . . . . . . . . . . . . . . . . . . . 23
1.5 Common Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.5.1 CTE et SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.5.2 CTE et SELECT : exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.5.3 CTE et SELECT : syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.5.4 CTE et barrière d’optimisation . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.5.5 CTE en écriture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
1.5.6 CTE en écriture : exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
1.5.7 CTE récursive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

iii
 DALIBO Formations

1.5.8 CTE récursive : exemple (1/2) . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

1.5.9 CTE récursive : principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
1.5.10 CTE récursive : principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
1.5.11 CTE récursive : exemple (2/2) . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
1.6 Concurrence d’accès . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
1.6.1 SELECT FOR UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
1.6.2 SKIP LOCKED . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
1.7 Serializable Snapshot Isolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
1.8 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
1.9 Travaux pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
1.10 Travaux pratiques (solutions) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Les formations Dalibo 55

Cursus des formations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Les livres blancs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Téléchargement gratuit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

iv SQL avancé pour le transactionnel

 DALIBO Formations

Sur ce document

Formation Module S5
Titre SQL avancé pour le transactionnel
Révision 24.09
PDF [Link]
EPUB [Link]
HTML [Link]
Slides [Link]
TP [Link]
TP (solutions) [Link]

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éati‑
vité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puis‑
sance 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 [email protected] !

À 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 [Link]
1
[Link]

SQL avancé pour le transactionnel 1

 DALIBO 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‑SA2 . 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 ti‑
tulaire 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 [Link]
/fr/legalcode
2
[Link]

2 SQL avancé pour le transactionnel

 DALIBO Formations

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ées3 par PostgreSQL Community As‑
sociation 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.

3
[Link]

SQL avancé pour le transactionnel 3

1/ SQL avancé pour le transactionnel

1.0.1 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 appli‑
cation transactionnelle.

1.0.2 Menu

ʦ – LIMIT / OFFSET
– Jointures LATERAL
– UPSERT : INSERT ou UPDATE
– Common Table Expressions
– Serializable Snapshot Isolation

1.0.3 Objectifs

ʦ – Aller au‑delà de SQL:92

– Concevoir des requêtes simples pour résoudre des problèmes complexes

5
 DALIBO Formations

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

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

6 SQL avancé pour le transactionnel

 DALIBO Formations

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

1.1.2 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

SQL avancé pour le transactionnel 7

 DALIBO Formations

00000040 | Lebrac | Publication | 3000.00

(5 lignes)

1.1.3 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

1.1.4 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

8 SQL avancé pour le transactionnel

 DALIBO Formations

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

Cependant, sur un jeu de données conséquent et une pagination importante, ce principe de fonc‑
tionnement 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 [Link] à laquelle on ajoute un
index sur (id_article_id, id_post) ) :

\d posts

Table « [Link] »
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écution2 suivant :

QUERY PLAN
------------------------------------------------------------------------------
Limit (cost=0.43..18.26 rows=10 width=115)

2
[Link]

SQL avancé pour le transactionnel 9

 DALIBO Formations

(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 si‑
tuation devient problématique :
SELECT *
FROM posts
WHERE id_article = 12
ORDER BY id_post
LIMIT 10
OFFSET 900 ;

Le plan3 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 article4 .
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

3
[Link]
4
[Link]

10 SQL avancé pour le transactionnel

 DALIBO Formations

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

SQL avancé pour le transactionnel 11

 DALIBO Formations

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

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

12 SQL avancé pour le transactionnel

 DALIBO Formations

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)

SQL avancé pour le transactionnel 13

 DALIBO Formations

1.3 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 depesz5 . 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 sou‑
haitable. 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 PostgreSQL6 . 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 "[Link]"
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)
5
[Link]
6
La solution actuelle semble techniquement meilleure et la solution actuelle a donc été choisie. Le wiki du projet Post‑
greSQL 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 : [Link]
PSERT#MERGE_disadvantages.

14 SQL avancé pour le transactionnel

 DALIBO Formations

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

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

1.3.2 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

SQL avancé pour le transactionnel 15

 DALIBO Formations

00000001 | Dupuis | Direction | 10000.00

(5 rows)

La transaction est toujours valide.

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

16 SQL avancé pour le transactionnel

 DALIBO Formations

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

1.3.4 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 = [Link],
nom = [Link]
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 = [Link];

SQL avancé pour le transactionnel 17

 DALIBO Formations

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)

1.3.5 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 = [Link],
nom = [Link]
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 dou‑
blons :
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)

18 SQL avancé pour le transactionnel

 DALIBO Formations

À noter que la clause SET salaire = [Link] est inutile, c’est ce que fait PostgreSQL im‑
plicitement.

1.3.6 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 = [Link],
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 = [Link],
autre_colonne = excluded.autre_colonne,
...

En alternative, il est possible d’indiquer un nom de contrainte plutôt que le nom d’une colonne por‑
tant une contrainte d’unicité :
INSERT ....
ON CONFLICT ON CONSTRAINT nom_contrainte
DO UPDATE
SET colonne_a_modifier = [Link],
autre_colonne_a_modifier = excluded.autre_colonne,
...;

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

7
[Link]

SQL avancé pour le transactionnel 19

 DALIBO Formations

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

1.4.1 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 fo‑
rum

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.

20 SQL avancé pour le transactionnel

 DALIBO Formations

1.4.2 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

b 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 beau‑
coup 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.

SQL avancé pour le transactionnel 21

 DALIBO Formations

Un index sur les colonnes sujet_id et date_publication permettra de minimiser

b les accès pour cette requête :

CREATE INDEX ON messages (sujet_id, date_publication DESC);

1.4.3 LATERAL : principe

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

22 SQL avancé pour le transactionnel

 DALIBO Formations

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

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

SQL avancé pour le transactionnel 23

 DALIBO Formations

1.5 COMMON TABLE EXPRESSIONS

ʦ – Common Table Expressions

– clauses WITH et WITH RECURSIVE

– Utilité :
– factoriser des sous‑requêtes

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

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

24 SQL avancé pour le transactionnel

 DALIBO Formations

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 princi‑
pale :
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)

SQL avancé pour le transactionnel 25

 DALIBO Formations

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.

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

1.5.4 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

26 SQL avancé pour le transactionnel

 DALIBO Formations

Il faut néanmoins être vigilant car l’optimiseur n’inclut pas la définition des CTE dans la requête prin‑
cipale 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

SQL avancé pour le transactionnel 27

 DALIBO Formations

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)

28 SQL avancé pour le transactionnel

 DALIBO Formations

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

1.5.5 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

SQL avancé pour le transactionnel 29

 DALIBO Formations

1.5.6 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 princi‑
pale 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 re‑
quê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éa‑
liser 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.

30 SQL avancé pour le transactionnel

 DALIBO Formations

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.

1.5.7 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 arbores‑
cences 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 limi‑
té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 explo‑
ré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 so‑
ciaux 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 Post‑
greSQL 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.

SQL avancé pour le transactionnel 31

 DALIBO Formations

1.5.8 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 WHE
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.

32 SQL avancé pour le transactionnel

 DALIBO Formations

1.5.9 CTE récursive : principe

ʦ – 1ère étape : initialisation de la récursion

1.5.10 CTE récursive : principe

ʦ – récursion : la requête s’appelle elle‑même

SQL avancé pour le transactionnel 33

 DALIBO Formations

1.5.11 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, [Link], menu.parent_id,
arborescence || '/' || [Link]
FROM entrees_menu menu
JOIN parcours_menu parent
ON (menu.parent_id = parent.menu_id)
)
SELECT * FROM parcours_menu;

Cet exemple suivant porte sur le parcours d’une arborescence de menu hiérarchique.
Une table entrees_menu est créée :

CREATE TABLE entrees_menu (menu_id serial primary key, libelle text not null,
parent_id integer);

Elle dispose du contenu suivant :

SELECT * FROM entrees_menu;

menu_id | libelle | parent_id

---------+----------------------------+-----------
1 | Fichier |
2 | Edition |
3 | Affichage |
4 | Terminal |
5 | Onglets |
6 | Ouvrir un onglet | 1
7 | Ouvrir un terminal | 1
8 | Fermer l'onglet | 1
9 | Fermer la fenêtre | 1
10 | Copier | 2
11 | Coller | 2
12 | Préférences | 2
13 | Général | 12
14 | Apparence | 12
15 | Titre | 13
16 | Commande | 13
17 | Police | 14
18 | Couleur | 14
19 | Afficher la barre d'outils | 3
20 | Plein écran | 3
21 | Modifier le titre | 4
22 | Définir l'encodage | 4

34 SQL avancé pour le transactionnel

 DALIBO Formations

23 | Réinitialiser | 4
24 | UTF-8 | 22
25 | Europe occidentale | 22
26 | Europe centrale | 22
27 | ISO-8859-1 | 25
28 | ISO-8859-15 | 25
29 | WINDOWS-1252 | 25
30 | ISO-8859-2 | 26
31 | ISO-8859-3 | 26
32 | WINDOWS-1250 | 26
33 | Onglet précédent | 5
34 | Onglet suivant | 5
(34 rows)

Nous allons définir une CTE récursive qui va afficher l’arborescence du menu Terminal. La récursion
va donc commencer par chercher la ligne correspondant à cette entrée de menu dans la table
entrees_menu . Une colonne calculée arborescence est créée, elle servira plus tard dans la
récursion :

SELECT menu_id, libelle, parent_id, libelle AS arborescence

FROM entrees_menu
WHERE libelle = 'Terminal'
AND parent_id IS NULL

La requête qui réalisera la récursion est une jointure entre le résultat de l’itération précédente, obtenu
par la vue parcours_menu de la CTE, qui réalisera une jointure avec la table entrees_menu sur la co‑
lonne entrees_menu.parent_id qui sera jointe à la colonne menu_id de l’itération précédente.

La condition d’arrêt de la récursion n’a pas besoin d’être exprimée. En effet, les entrées terminales
des menus ne peuvent pas être jointes avec de nouvelles entrées de menu, car il n’y a pas d’autre
correspondance avec parent_id ).

On obtient ainsi la requête CTE récursive présentée ci‑dessus.

À titre d’exemple, voici l’implémentation du jeu des six degrés de Kevin Bacon en utilisant pgRou‑
ting :

WITH dijkstra AS (
SELECT seq, id1 AS node, id2 AS edge, cost
FROM pgr_dijkstra('
SELECT f.film_id AS id,
f.actor_id::integer AS source,
f2.actor_id::integer AS target,
1.0::float8 AS cost
FROM film_actor f
JOIN film_actor f2
ON (f.film_id = f2.film_id and f.actor_id <> f2.actor_id)'
, 29539, 29726, false, false)
)
SELECT *
FROM actors
JOIN dijkstra
on ([Link] = actors.actor_id) ;

SQL avancé pour le transactionnel 35

 DALIBO Formations

actor_id | actor_name | seq | node | edge | cost

----------+----------------+-----+-------+------+------
29539 | Kevin Bacon | 0 | 29539 | 1330 | 1
29625 | Robert De Niro | 1 | 29625 | 53 | 1
29726 | Al Pacino | 2 | 29726 | -1 | 0
(3 lignes)

36 SQL avancé pour le transactionnel

 DALIBO Formations

1.6 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

Plusieurs problèmes de concurrences d’accès peuvent se poser quand plusieurs transactions modi‑
fient les mêmes données en même temps.
Tout d’abord, des UPDATE peuvent être perdus, dans le cas où plusieurs transactions lisent la même
ligne, puis la mettent à jour sans concertation. Par exemple, si la transaction 1 ouvre une transaction
et effectue une lecture d’une ligne donnée :
BEGIN TRANSACTION;
SELECT * FROM employes WHERE matricule = '00000004';

La transaction 2 effectue les mêmes traitements :

BEGIN TRANSACTION;
SELECT * FROM employes WHERE matricule = '00000004';

Après un traitement applicatif, la transaction 1 met les données à jour pour noter l’augmentation de
5 % du salarié. La transaction est validée dans la foulée avec COMMIT :

UPDATE employes
SET salaire = <valeur récupérée préalablement * 1.05>
WHERE matricule = '00000004';
COMMIT;

Après un traitement applicatif, la transaction 2 met également les données à jour pour noter une aug‑
mentation exceptionnelle de 100 € :
UPDATE employes
SET salaire = <valeur récupérée préalablement + 100>
WHERE matricule = '00000004';
COMMIT;

Le salarié a normalement droit à son augmentation de 100 € ET l’augmentation de 5 %, or

l’augmentation de 5 % a été perdue car écrasée par la transaction n°2. Ce problème aurait pu être
évité de trois façons différentes :

– en effectuant un UPDATE utilisant la valeur lue par l’ordre UPDATE ,

SQL avancé pour le transactionnel 37

 DALIBO Formations

– en verrouillant les données lues avec SELECT FOR UPDATE ,

– en utilisant le niveau d’isolation SERIALIZABLE .

La première solution n’est pas toujours envisageable, il faut donc se tourner vers les deux autres solu‑
tions.
Le problème des lectures sales (dirty reads) ne peut pas se poser car PostgreSQL n’implémente pas
le niveau d’isolation READ UNCOMMITTED . Si ce niveau d’isolation est sélectionné, PostgreSQL utilise
alors le niveau READ COMMITTED .

1.6.1 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

L’ordre SELECT FOR UPDATE permet de lire des lignes tout en les réservant en posant un verrou des‑
sus en vue d’une future mise à jour. Le verrou permettra une lecture parallèle, mais mettra toute mise
à jour en attente.
Reprenons l’exemple précédent et utilisons SELECT FOR UPDATE pour voir si le problème de concur‑
rence d’accès peut être résolu.
session 1
BEGIN TRANSACTION;
SELECT * FROM employes WHERE matricule = '00000004' FOR UPDATE;

matricule | nom | service | salaire

-----------+----------+----------+---------
00000004 | Fantasio | Courrier | 4500.00
(1 row)

La requête SELECT a retourné les données souhaitées.

session 2
BEGIN TRANSACTION;
SELECT * FROM employes WHERE matricule = '00000004' FOR UPDATE;

La requête SELECT ne rend pas la main, elle est mise en attente.

session 3
Une troisième session effectue une lecture, sans poser de verrou explicite :
SELECT * FROM employes WHERE matricule = '00000004';

38 SQL avancé pour le transactionnel

 DALIBO Formations

matricule | nom | service | salaire

-----------+----------+----------+---------
00000004 | Fantasio | Courrier | 4500.00
(1 row)

Le SELECT n’a pas été bloqué par la session 1. Seule la session 2 est bloquée car elle tente d’obtenir
le même verrou.
session 1
L’application a effectué ses calculs et met à jour les données en appliquant l’augmentation de 5 % :
UPDATE employes
SET salaire = 4725
WHERE matricule = '00000004';

Les données sont vérifiées :

SELECT * FROM employes WHERE matricule = '00000004';

matricule | nom | service | salaire

-----------+----------+----------+---------
00000004 | Fantasio | Courrier | 4725.00
(1 row)

Enfin, la transaction est validée :

COMMIT;

session 2
La session 2 a rendu la main, le temps d’attente a été important pour réaliser ces calculs complexes :
matricule | nom | service | salaire
-----------+----------+----------+---------
00000004 | Fantasio | Courrier | 4725.00
(1 row)

Time: 128127,105 ms

Le salaire obtenu est bien le salaire mis à jour par la session 1. Sur cette base, l’application applique
l’augmentation de 100 € :
UPDATE employes
SET salaire = 4825.00
WHERE matricule = '00000004';

SELECT * FROM employes WHERE matricule = '00000004';

matricule | nom | service | salaire

-----------+----------+----------+---------
00000004 | Fantasio | Courrier | 4825.00

La transaction est validée :

COMMIT;

SQL avancé pour le transactionnel 39

 DALIBO Formations

Les deux transactions ont donc été effectuée de manière sérialisée, l’augmentation de 100 € ET
l’augmentation de 5 % ont été accordées à Fantasio. En contre‑partie, l’une des deux transactions
concurrentes a été mise en attente afin de pouvoir sérialiser les transactions. Cela implique de penser
les traitements en verrouillant les ressources auxquelles on souhaite accéder.
L’ordre SELECT FOR UPDATE dispose également d’une option NOWAIT qui permet d’annuler la tran‑
saction courante si un verrou ne pouvait être acquis. Si l’on reprend les premières étapes de l’exemple
précédent :
session 1

BEGIN TRANSACTION;
SELECT * FROM employes WHERE matricule = '00000004' FOR UPDATE NOWAIT;

matricule | nom | service | salaire

-----------+----------+----------+---------
00000004 | Fantasio | Courrier | 4500.00
(1 row)

Aucun verrou préalable n’avait été posé, la requête SELECT a retourné les données souhaitées.
session 2
On effectue la même chose sur la session n°2 :

BEGIN TRANSACTION;
SELECT * FROM employes WHERE matricule = '00000004' FOR UPDATE NOWAIT;

ERROR: could not obtain lock on row in relation "employes"

Comme la session n°1 possède déjà un verrou sur la ligne qui nous intéresse, l’option NOWAIT sur le
SELECT a annulé la transaction.

Il faut maintenant effectuer un ROLLBACK explicite pour pouvoir recommencer les traitements au
risque d’obtenir le message suivant :

ERROR: current transaction is aborted, commands ignored until

end of transaction block

1.6.2 SKIP LOCKED

ʦ – SELECT FOR UPDATE SKIP LOCKED

– PostgreSQL 9.5
– Utilité :
– implémente des files d’attentes parallélisables

40 SQL avancé pour le transactionnel

 DALIBO Formations

Une dernière fonctionnalité intéressante de SELECT FOR UPDATE , apparue avec PostgreSQL 9.5, per‑
met de mettre en oeuvre différents workers qui consomment des données issues d’une table repré‑
sentant une file d’attente. Il s’agit de la clause SKIP LOCKED , dont le principe de fonctionnement est
identique à son équivalent sous Oracle.
En prenant une table représentant la file d’attente suivante, peuplée avec des données générées :
CREATE TABLE test_skiplocked (id serial primary key, val text);
INSERT INTO test_skiplocked (val) SELECT md5(i::text)
FROM generate_series(1, 1000) i;

Une première transaction est ouverte et tente d’obtenir un verrou sur les 10 premières lignes :
BEGIN TRANSACTION;

SELECT *
FROM test_skiplocked
LIMIT 10
FOR UPDATE SKIP LOCKED;

id | val
----+----------------------------------
1 | c4ca4238a0b923820dcc509a6f75849b
2 | c81e728d9d4c2f636f067f89cc14862c
3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
4 | a87ff679a2f3e71d9181a67b7542122c
5 | e4da3b7fbbce2345d7772b0674a318d5
6 | 1679091c5a880faf6fb5e6087eb1b2dc
7 | 8f14e45fceea167a5a36dedd4bea2543
8 | c9f0f895fb98ab9159f51fd0297e236d
9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
10 | d3d9446802a44259755d38e6d163e820
(10 rows)

Si on démarre une seconde transaction en parallèle, avec la première transaction toujours ouverte,
le fait d’exécuter la requête SELECT FOR UPDATE sans la clause SKIP LOCKED aurait pour effet de la
mettre en attente. L’ordre SELECT rendra la main lorsque la transaction #1 se terminera.
Avec la clause SKIP LOCKED , les 10 premières verrouillées par la transaction n°1 seront passées et ce
sont les 10 lignes suivantes qui seront verrouillées et retournées par l’ordre SELECT :

BEGIN TRANSACTION;

SELECT *
FROM test_skiplocked
LIMIT 10
FOR UPDATE SKIP LOCKED;

id | val
----+----------------------------------
11 | 6512bd43d9caa6e02c990b0a82652dca
12 | c20ad4d76fe97759aa27a0c99bff6710
13 | c51ce410c124a10e0db5e4b97fc2af39
14 | aab3238922bcc25a6f606eb525ffdc56
15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3

SQL avancé pour le transactionnel 41

 DALIBO Formations

16 | c74d97b01eae257e44aa9d5bade97baf
17 | 70efdf2ec9b086079795c442636b55fb
18 | 6f4922f45568161a8cdf4ad2299f6d23
19 | 1f0e3dad99908345f7439f8ffabdffc4
20 | 98f13708210194c475687be6106a3b84
(10 rows)

Ensuite, la première transaction supprime les lignes verrouillées et valide la transaction :

DELETE FROM test_skiplocked
WHERE id IN (...);
COMMIT;

De même pour la seconde transaction, qui aura traité d’autres lignes en parallèle de la transaction
#1.

42 SQL avancé pour le transactionnel

 DALIBO Formations

1.7 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

PostgreSQL fournit depuis la version 9.1 un mode d’isolation appelé SERIALIZABLE . Dans ce mode,
toutes les transactions déclarées comme telles s’exécutent comme si elles étaient seules sur la base.
Dès que cette garantie ne peut plus être apportée, une des transactions est annulée.
Toute transaction non déclarée comme SERIALIZABLE peut en théorie s’exécuter n’importe quand,
ce qui rend inutile le mode SERIALIZABLE sur les autres. C’est donc un mode qui doit être mis en
place globalement.
Voici un exemple.
Dans cet exemple, il y a des enregistrements avec une colonne couleur contenant ‘blanc’ ou ‘rouge’.
Deux utilisateurs essayent simultanément de convertir tous les enregistrements vers une couleur
unique, mais chacun dans une direction opposée. Un utilisateur veut passer tous les blancs en rouge,
et l’autre tous les rouges en blanc.
L’exemple peut être mis en place avec ces ordres :

create table points

(
id int not null primary key,
couleur text not null
);
insert into points
with x(id) as (select generate_series(1,10))
select id, case when id % 2 = 1 then 'rouge'
else 'blanc' end from x;

Session 1 :

set default_transaction_isolation = 'serializable';

begin;
update points set couleur = 'rouge'
where couleur = 'blanc';

Session 2 :

SQL avancé pour le transactionnel 43

 DALIBO Formations

set default_transaction_isolation = 'serializable';

begin;
update points set couleur = 'blanc'
where couleur = 'rouge';

À ce moment, une des deux transaction est condamnée à mourir.

Session 2 :
commit;

Le premier à valider gagne.

select * from points order by id;

id | couleur
----+-------
1 | blanc
2 | blanc
3 | blanc
4 | blanc
5 | blanc
6 | blanc
7 | blanc
8 | blanc
9 | blanc
10 | blanc
(10 rows)

Session 1 :
Celle‑ci s’est exécutée comme si elle était seule.
commit;

ERROR: could not serialize access

due to read/write dependencies
among transactions
DETAIL: Cancelled on identification
as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

Une erreur de sérialisation. On annule et on réessaye.

rollback;
begin;
update points set couleur = 'rouge'
where couleur = 'blanc';
commit;

Il n’y a pas de transaction concurrente pour gêner.

select * from points order by id;

id | couleur
----+-------
1 | rouge
2 | rouge

44 SQL avancé pour le transactionnel

 DALIBO Formations

3 | rouge
4 | rouge
5 | rouge
6 | rouge
7 | rouge
8 | rouge
9 | rouge
10 | rouge
(10 rows)

La transaction s’est exécutée seule, après l’autre.

Le mode SERIALIZABLE permet de s’affranchir des SELECT FOR UPDATE qu’on écrit habituellement,
dans les applications en mode READ COMMITTED . Toutefois, il fait bien plus que ça, puisqu’il réalise
du verrouillage de prédicats. Un enregistrement qui « apparaît » ultérieurement suite à une mise à
jour réalisée par une transaction concurrente déclenchera aussi une erreur de sérialisation. Il permet
aussi de gérer les problèmes ci‑dessus avec plus de deux sessions.
Pour des exemples plus complets, le mieux est de consulter la documentation officielle8 .

8
[Link]

SQL avancé pour le transactionnel 45

 DALIBO Formations

1.8 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

46 SQL avancé pour le transactionnel

 DALIBO Formations

1.9 TRAVAUX PRATIQUES

Jointure latérale
Cette série de question utilise la base de TP magasin. La base magasin (dump de 96 Mo, pour 667 Mo
sur le disque au final) peut être téléchargée et restaurée comme suit dans une nouvelle base maga‑
sin :
createdb magasin
curl -kL [Link] -o /tmp/[Link]
pg_restore -d magasin /tmp/[Link]
# le message sur public préexistant est normal
rm -- /tmp/[Link]

Toutes les données sont dans deux schémas nommés magasin et facturation.

Afficher les 10 derniers articles commandés.

Pour chacune des 10 dernières commandes passées, afficher le premier article commandé.

CTE récursive
La table genealogie peut être téléchargée depuis [Link] et restaurée à l’aide
de pg_restore :

curl -kL [Link] -o [Link]

createdb genealogie
pg_restore -O -d genealogie [Link]
# le message d'erreur sur le schéma public est normal

Voici la description de la table genealogie qui sera utilisée :

\d genealogie
Table "[Link]"
Column | Type | Modifiers
----------------+---------+---------------------------------------
id | integer | not null default +
| | nextval('genealogie_id_seq'::regclass)
nom | text |
prenom | text |
date_naissance | date |
pere | integer |
mere | integer |
Indexes:
"genealogie_pkey" PRIMARY KEY, btree (id)

À partir de la table genealogie , déterminer qui sont les descendants de Fernand DEVAUX.

À l’inverse, déterminer qui sont les ancètres de Adèle TAILLANDIER

SQL avancé pour le transactionnel 47

 DALIBO Formations

Réseau social
La table socialnet peut être téléchargée et restaurée ainsi :

curl -kL [Link] -o /tmp/[Link]

createdb socialnet
pg_restore -O -d socialnet /tmp/[Link]
# le message d'erreur sur le schéma public est normal

Cet exercice est assez similaire au précédent et propose de manipuler des arborescences.

Les tableaux et la fonction unnest() peuvent être utiles pour résoudre plus facilement
b ce problème.

La table personnes contient la liste de toutes les personnes d’un réseau social.

Table "[Link]"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
id | integer | not null default nextval('personnes_id_seq'::regclass)
nom | text | not null
prenom | text | not null
Indexes:
"personnes_pkey" PRIMARY KEY, btree (id)

La table relation contient les connexions entre ces personnes.

Table "[Link]"
Column | Type | Modifiers
--------+---------+-----------
gauche | integer | not null
droite | integer | not null
Indexes:
"relation_droite_idx" btree (droite)
"relation_gauche_idx" btree (gauche)

Déterminer le niveau de connexions entre Sadry Luettgen et Yelsi Kerluke et afficher le chemin
de relation le plus court qui permet de les connecter ensemble.

Dépendance de vues
Les dépendances entre objets est un problème classique dans les bases de données :

– dans quel ordre charger des tables selon les clés étrangères ?
– dans quel ordre recréer des vues ?
– etc.

Le catalogue de PostgreSQL décrit l’ensemble des objets de la base de données. Deux tables vont nous
intéresser pour mener à bien cet exercice :

– pg_depend liste les dépendances entre objets

48 SQL avancé pour le transactionnel

 DALIBO Formations

– pg_rewrite stocke les définitions des règles de réécritures des vues (RULES)
– pg_class liste les objets que l’on peut interroger comme une table, hormis les fonctions re‑
tournant des ensembles

La définition d’une vue peut être obtenue à l’aide de la fonction pg_get_viewdef .

Pour plus d’informations sur ces tables, se référer à la documentation :

– Catalogue pg_depend9
– Catalogue pg_rewrite10
– Catalogue pg_class11
– Fonction d’information du catalogue système12

L’objectif de se TP consiste à récupérer l’ordre de suppression et de recréation des vues de la base

brno2015 en fonction du niveau de dépendances entre chacune des vues. Brno est une ville de Tché‑
quie, dans la région de Moravie‑du‑Sud. Le circuit Brno‑Masaryk est situé au nord‑ouest de la ville. Le
Grand Prix moto de Tchéquie s’y déroule chaque année.
La table brno2015 peut être téléchargée et restaurée ainsi :

curl -kL [Link] -o /tmp/[Link]

createdb brno2015
pg_restore -O -d brno2015 /tmp/[Link]
# une erreur sur l'existence du schéma public est normale

Retrouver les dépendances de la vue pilotes_brno . Déduisez également l’ordre de suppres‑

sion et de recréation des vues.

9
[Link]
10
[Link]
11
[Link]
12
[Link]

SQL avancé pour le transactionnel 49

 DALIBO Formations

1.10 TRAVAUX PRATIQUES (SOLUTIONS)

Jointure latérale
Afficher les 10 derniers articles commandés.

Tout d’abord, nous positionnons le search_path pour chercher les objets du schéma magasin :

SET search_path = magasin;

On commence par afficher les 10 dernières commandes :

SELECT *
FROM commandes
ORDER BY numero_commande DESC
LIMIT 10;

Une simple jointure nous permet de retrouver les 10 derniers articles commandés :

SELECT lc.produit_id, [Link]

FROM commandes c
JOIN lignes_commandes lc
ON (c.numero_commande = lc.numero_commande)
JOIN produits p
ON (lc.produit_id = p.produit_id)
ORDER BY c.numero_commande DESC, numero_ligne_commande DESC
LIMIT 10;

Pour chacune des 10 dernières commandes passées, afficher le premier article commandé.

La requête précédente peut être dérivée pour répondre à la question demandée. Ici, pour chacune
des dix dernières commandes, nous voulons récupérer le nom du dernier article commandé, ce qui
sera transcrit sous la forme d’une jointure latérale :

SELECT numero_commande, produit_id, nom

FROM commandes c,
LATERAL (SELECT p.produit_id, [Link]
FROM lignes_commandes lc
JOIN produits p
ON (lc.produit_id = p.produit_id)
WHERE (c.numero_commande = lc.numero_commande)
ORDER BY numero_ligne_commande ASC
LIMIT 1
) premier_article_par_commande
ORDER BY c.numero_commande DESC
LIMIT 10;

CTE récursive

À partir de la table genealogie , déterminer qui sont les descendants de Fernand DEVAUX.

50 SQL avancé pour le transactionnel

 DALIBO Formations

WITH RECURSIVE arbre_genealogique AS (

SELECT id, nom, prenom, date_naissance, pere, mere
FROM genealogie
WHERE nom = 'DEVAUX'
AND prenom = 'Fernand'
UNION ALL
SELECT g.*
FROM arbre_genealogique ancetre
JOIN genealogie g
ON ([Link] = [Link] OR [Link] = [Link])
)
SELECT id, nom, prenom, date_naissance
FROM arbre_genealogique;

À l’inverse, déterminer qui sont les ancètres de Adèle TAILLANDIER

WITH RECURSIVE arbre_genealogique AS (

SELECT id, nom, prenom, date_naissance, pere, mere
FROM genealogie
WHERE nom = 'TAILLANDIER'
AND prenom = 'Adèle'
UNION ALL
SELECT [Link], [Link], [Link], ancetre.date_naissance,
[Link], [Link]
FROM arbre_genealogique descendant
JOIN genealogie ancetre
ON ([Link] = [Link] OR [Link] = [Link])
)
SELECT id, nom, prenom, date_naissance
FROM arbre_genealogique;

Réseau social
Déterminer le niveau de connexions entre Sadry Luettgen et Yelsi Kerluke et afficher le chemin
de relation le plus court qui permet de les connecter ensemble.

La requête suivante permet de répondre à cette question :

WITH RECURSIVE connexions AS (
SELECT gauche, droite, ARRAY[gauche] AS personnes_connectees,0::integer AS level
FROM relation
WHERE gauche = 1
UNION ALL
SELECT [Link], [Link], personnes_connectees || [Link], level + 1 AS level
FROM connexions c
JOIN relation p ON ([Link] = [Link])
WHERE level < 4
AND [Link] <> ANY (personnes_connectees)
), plus_courte_connexion AS (
SELECT *
FROM connexions
WHERE gauche = (
SELECT id FROM personnes WHERE nom = 'Kerluke' AND prenom = 'Yelsi'
)
ORDER BY level ASC

SQL avancé pour le transactionnel 51

 DALIBO Formations

LIMIT 1
)
SELECT [Link], [Link], [Link], [Link] - 1 AS level
FROM plus_courte_connexion,
unnest(personnes_connectees) WITH ORDINALITY AS list(id, level)
JOIN personnes p on ([Link] = [Link])
ORDER BY [Link];

Cet exemple fonctionne sur une faible volumétrie, mais les limites des bases relation‑
Á nelles sont rapidement atteintes sur de telles requêtes.
Une solution consisterait à implémenter un algorithme de parcours de graphe avec
pgRouting13 , mais cela nécessitera de présenter les données sous une forme particu‑
lière.
Pour les problématiques de traitement de graphe, notamment sur de grosses volumé‑
tries, une base de données orientée graphe comme Neo4J sera probablement plus
adaptée.

Dépendance de vues

Retrouver les dépendances de la vue pilotes_brno . Déduisez également l’ordre de suppres‑

sion et de recréation des vues.

Tout d’abord, nous positionnons le search_path pour chercher les objets du schéma brno2015 :

SET search_path = brno2015;

Si la jointure entre pg_depend et pg_rewrite est possible pour l’objet de départ, alors il s’agit pro‑
bablement d’une vue. En discriminant sur les objets qui référencent la vue pilotes_brno , nous arri‑
vons à la requête de départ suivante :
SELECT DISTINCT pg_rewrite.ev_class as objid, refobjid as refobjid, 0 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
WHERE refobjid = 'pilotes_brno'::regclass;

La présence de doublons nous oblige à utiliser la clause DISTINCT.

Nous pouvons donc créer un graphe de dépendances à partir de cette requête de départ, transformée
en requête récursive :
WITH RECURSIVE graph AS (
SELECT distinct pg_rewrite.ev_class as objid, refobjid as refobjid, 0 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
WHERE refobjid = 'pilotes_brno'::regclass
UNION ALL
SELECT distinct pg_rewrite.ev_class as objid, pg_depend.refobjid as refobjid,
depth + 1 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid

52 SQL avancé pour le transactionnel

 DALIBO Formations

JOIN graph on pg_depend.refobjid = [Link]

WHERE pg_rewrite.ev_class != [Link]
)
SELECT * FROM graph;

Il faut maintenant résoudre les OID pour déterminer les noms des vues et leur schéma. Pour cela, nous
ajoutons une vue resolved telle que :

WITH RECURSIVE graph AS (

SELECT distinct pg_rewrite.ev_class as objid, refobjid as refobjid, 0 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
WHERE refobjid = 'pilotes_brno'::regclass
UNION ALL
SELECT distinct pg_rewrite.ev_class as objid, pg_depend.refobjid as refobjid,
depth + 1 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN graph on pg_depend.refobjid = [Link]
WHERE pg_rewrite.ev_class != [Link]
),
resolved AS (
SELECT [Link] AS dependent_schema, [Link] as dependent,
[Link] AS dependee_schema, [Link] as dependee,
depth
FROM graph
JOIN pg_class d ON [Link] = objid
JOIN pg_namespace n ON [Link] = [Link]
JOIN pg_class d2 ON [Link] = refobjid
JOIN pg_namespace n2 ON [Link] = [Link]
)
SELECT * FROM resolved;

Nous pouvons maintenant présenter les ordres de suppression et de recréation des vues, dans le bon
ordre. Les vues doivent être supprimées selon le numéro d’ordre décroissant et recrées selon le nu‑
méro d’ordre croissant :
WITH RECURSIVE graph AS (
SELECT distinct pg_rewrite.ev_class as objid, refobjid as refobjid, 0 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
WHERE refobjid = 'pilotes_brno'::regclass
UNION ALL
SELECT distinct pg_rewrite.ev_class as objid, pg_depend.refobjid as refobjid,
depth + 1 as depth
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN graph on pg_depend.refobjid = [Link]
WHERE pg_rewrite.ev_class != [Link]
),
resolved AS (
SELECT [Link] AS dependent_schema, [Link] as dependent,
[Link] AS dependee_schema, [Link] as dependee,
[Link] as dependent_oid,
depth

SQL avancé pour le transactionnel 53

 DALIBO Formations

FROM graph
JOIN pg_class d ON [Link] = objid
JOIN pg_namespace n ON [Link] = [Link]
JOIN pg_class d2 ON [Link] = refobjid
JOIN pg_namespace n2 ON [Link] = [Link]
)
(SELECT 'DROP VIEW ' || dependent_schema || '.' || dependent || ';'
FROM resolved
GROUP BY dependent_schema, dependent
ORDER BY max(depth) DESC)
UNION ALL
(SELECT 'CREATE OR REPLACE VIEW ' || dependent_schema || '.' || dependent ||
' AS ' || pg_get_viewdef(dependent_oid)
FROM resolved
GROUP BY dependent_schema, dependent, dependent_oid
ORDER BY max(depth));

54 SQL avancé pour le transactionnel

Les formations Dalibo

Retrouvez nos formations et le calendrier sur [Link]

Pour toute information ou question, n’hésitez pas à nous écrire sur contact@[Link].

Cursus des formations

55
 DALIBO Formations

Retrouvez nos formations dans leur dernière version :

– DBA1 : Administration PostgreSQL

[Link]
– DBA2 : Administration PostgreSQL avancé
[Link]
– DBA3 : Sauvegarde et réplication avec PostgreSQL
[Link]
– DEVPG : Développer avec PostgreSQL
[Link]
– PERF1 : PostgreSQL Performances
[Link]
– PERF2 : Indexation et SQL avancés
[Link]
– MIGORPG : Migrer d’Oracle à PostgreSQL
[Link]
– HAPAT : Haute disponibilité avec PostgreSQL
[Link]

Les livres blancs

– Migrer d’Oracle à PostgreSQL

[Link]
– Industrialiser PostgreSQL
[Link]
– Bonnes pratiques de modélisation avec PostgreSQL
[Link]
– Bonnes pratiques de développement avec PostgreSQL
[Link]

Téléchargement gratuit

Les versions électroniques de nos publications sont disponibles gratuitement sous licence open
source ou sous licence Creative Commons.

56 SQL avancé pour le transactionnel