Formation DEVPG
Dalibo SCOP
24.09
29 août 2024
Formation | Formation DEVPG |
Titre | Développer avec PostgreSQL |
Révision | 24.09 |
ISBN | N/A |
https://dali.bo/devpg_pdf | |
EPUB | https://dali.bo/devpg_epub |
HTML | https://dali.bo/devpg_html |
Slides | https://dali.bo/devpg_slides |
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.
Quelle version utiliser ?
De M.m à M.m+n :
jsonb
pg_stat_progress_copy
, pg_stat_wal
,
pg_lock.waitstart
, query_id
…MERGE
DISTINCT
parallélisablepublic
n’est plus accessible en écriture à tousDISTINCT
…)pg_hba.conf
pg_stat_io
…Entre de nombreux autres :
PostgreSQL n’est que le moteur ! Besoin d’outils pour :
Entre autres, dédiés ou pas :
N’hésitez pas, c’est le moment !
Fonctionnalités du moteur
Objets SQL
Connaître les différentes fonctionnalités et possibilités
Découvrir des exemples concrets
Gestion transactionnelle : la force des bases de données relationnelles :
BEGIN
obligatoire pour grouper des modificationsCOMMIT
pour valider
ROLLBACK
/ perte de la connexion / arrêt (brutal ou
non) du serveurSAVEPOINT
pour sauvegarde des modifications d’une
transaction à un instant t
BEGIN ISOLATION LEVEL xxx;
read commited
(défaut)repeatable read
serializable
pg_dump
, pg_dumpall
,
pg_restore
pg_basebackup
CREATE EXTENSION monextension ;
pg_hba.conf
search_path
pg_catalog
, information_schema
Par défaut, une table est :
int
, float
numeric
, char
,
varchar
, date
, time
,
timestamp
, bool
jsonb
), XMLCHECK
prix > 0
NOT NULL
id_client NOT NULL
id_client UNIQUE
UNIQUE NOT NULL
==>
PRIMARY KEY (id_client)
produit_id REFERENCES produits(id_produit)
EXCLUDE
EXCLUDE USING gist (room WITH =, during WITH &&)
DEFAULT
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
GENERATED ALWAYS AS ( generation_expr ) STORED
SETOF
ou TABLE
pour plusieurs lignesINSERT
, UPDATE
,
DELETE
, TRUNCATE
FOR STATEMENT
)FOR EACH ROW
)N’hésitez pas, c’est le moment !
EXPLAIN
Le modèle vise à minimiser un coût :
ANALYZE
EXPLAIN
QUERY PLAN
---------------------------------------------------------------
Sort (cost=21.64..21.67 rows=9 width=8)
(actual time=0.493..0.498 rows=9 loops=1)
Sort Key: c1
Sort Method: quicksort Memory: 25kB
-> Seq Scan on t1 (cost=0.00..21.50 rows=9 width=8)
(actual time=0.061..0.469 rows=9 loops=1)
Filter: (c2 < 10)
Rows Removed by Filter: 991
Planning Time: 0.239 ms
Execution Time: 0.606 ms
QUERY PLAN
---------------------------------------------------------
Sort (cost=17.64..17.67 rows=9 width=8)
(actual time=0.126..0.127 rows=9 loops=1)
Sort Key: c1
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3 read=5
-> Seq Scan on t1 (cost=0.00..17.50 rows=9 width=8)
(actual time=0.017..0.106 rows=9 loops=1)
Filter: (c2 < 10)
Rows Removed by Filter: 991
Buffers: shared read=5
QUERY PLAN
----------------------------------------------------
Insert on t1 (cost=0.00..10.00 rows=1000 width=8)
(actual time=8.078..8.079 rows=0 loops=1)
WAL: records=2017 fpi=3 bytes=162673
-> Function Scan on generate_series i
(cost=0.00..10.00 rows=1000 width=8)
(actual time=0.222..0.522 rows=1000 loops=1)
Planning Time: 0.076 ms
Execution Time: 8.141 ms
COSTS OFF
TIMING OFF
VERBOSE
SUMMARY
FORMAT
QUERY PLAN
---------------------------------------------------------
Sort (cost=52.14..52.21 rows=27 width=8) (actual time=1.359..1.366 rows=27 loops=1)
…
Buffers: shared hit=3 read=14
I/O Timings: read=0.388
-> Seq Scan on t1 (cost=0.00..51.50 rows=27 width=8) (actual time=0.086..1.233 rows=27 loops=1)
Filter: (c2 < 10)
Rows Removed by Filter: 2973
Buffers: shared read=14
I/O Timings: read=0.388
Planning:
Buffers: shared hit=43 read=14
I/O Timings: read=0.469
Planning Time: 1.387 ms
Execution Time: 1.470 ms
BUFFERS
)track_io_timing
)EXISTS
, IN
et certaines jointures
externes
DISTINCT
)UNION ALL
), Except,
IntersectEXPLAIN
EXPLAIN ANALYZE
EXPLAIN [ANALYZE]
N’hésitez pas, c’est le moment !
Tous les TP se basent sur la configuration par défaut de PostgreSQL, sauf précision contraire.
L’optimisation doit porter sur :
postgresql.conf
& co« 80% des effets sont produits par 20% des causes. » (Principe de Pareto)
Seul un certain nombre de requêtes sont critiques
Quelques profilers :
Le SQL :
Les opérateurs purement relationnels :
SELECT
WHERE
FROM/JOIN
Les autres opérateurs sont non-relationnels :
ORDER BY
GROUP BY/DISTINCT
HAVING
Le volume de données récupéré a un impact sur les performances.
SQL : langage ensembliste
Un Semi Join peut être très efficace (il ne lit pas tout)
EXISTS
(si index
disponible)Ces sous-requêtes sont strictement équivalentes (Semi-join) :
SELECT * FROM t1
WHERE fk IN ( SELECT pk FROM t2 WHERE … )
SELECT * FROM t1
WHERE EXISTS ( SELECT 1 FROM t2 WHERE t2.pk = t1.fk AND … )
SELECT t1.*
FROM t1 LEFT JOIN t2 ON (t1.fk=t2.pk)
WHERE
t2.id IS NULL
(Et Anti-join pour les variantes avec NOT
)
NOT IN
: préférer
NOT EXISTS
Une vue est une requête pré-déclarée en base.
DISTINCT
, GROUP BY
etc.
L’accès aux données est coûteux.
gram.y
de 19000 lignesSe connecter coûte cher :
→ Maintenir les connexions côté applicatif, ou utiliser un pooler.
WITH
)Le schéma est la modélisation des données
Les moteurs SQL sont très efficaces, et évoluent en permanence
CASE
, etc.--
et /* */
Prendre de la distance vis-à-vis des spécifications fonctionnelles (bis) :
COUNT(*)
COMMIT
ROLLBACK
COMMIT
synchronous_commit = off
(…si perte
possible)« Verrous mortels » : comment les éviter ?
Écrire sur plusieurs nœuds ?
UNIQUE
(préférer la contrainte)<
, <=
,
=
, >=
, >
SELECT name FROM ma_table WHERE id = 22
C’est souvent tout à fait normal
VACUUM
fréquentCREATE INDEX … CONCURRENTLY
peut échouerDe nombreuses possibilités d’indexation avancée :
IMMUTABLE
!
ANALYZE
après création d’un index
fonctionnel
WHERE
varchar_pattern_ops
:
EXPLAIN
CALL
)TRUNCATE
et COPY
WHERE
services
: 4 lignesservices_big
: 40 000 lignesemployes
: 14 lignesemployes_big
: ~500 000 lignesservice*
.num_service
(clés primaires)employes*
.matricule
(clés primaires)employes*
.date_embauche
employes_big
.num_service
(clé
étrangère)L’objet de ce module est de comprendre son plan d’exécution :
Hash Join (cost=1.06..2.28 rows=4 width=48)
Hash Cond: (emp.num_service = ser.num_service)
-> Seq Scan on employes emp (cost=0.00..1.14 rows=14 width=35)
-> Hash (cost=1.05..1.05 rows=1 width=21)
-> Seq Scan on services ser (cost=0.00..1.05 rows=1 width=21)
Filter: ((localisation)::text = 'Nantes'::text)
Rappels :
Règle 1 : récupérer le bon résultat
Règle 2 : le plus rapidement possible
critere IN (SELECT ...)
L’optimiseur doit choisir :
DISTINCT
(v15)SET
ANALYZE
pg_class
relpages
: taillereltuples
: lignesNULL
)-[ RECORD 1 ]----------+---------------------------------------------------------
schemaname | public
tablename | employes
attname | date_embauche
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -0.5
most_common_vals | {2006-03-01,2006-09-01,2000-06-01,2005-03-06,2006-01-01}
most_common_freqs | {0.214286,0.214286,0.142857,0.142857,0.142857}
histogram_bounds | {2003-01-01,2006-06-01}
correlation | 1
most_common_elems | ¤
most_common_elem_freqs | ¤
elem_count_histogram | ¤
CREATE STATISTICS
Vues disponibles :
pg_stats_ext
pg_stats_ext_exprs
(pour les expressions, v14)ANALYZE [ VERBOSE ] [ table [ ( colonne [, ...] ) ] [, ...] ]
ANALYZE
mais…
psql
vacuumdb --analyze-only
ANALYZE
: exécution (danger !)BUFFERS
: blocs
read/hit/written/dirtied,
shared/local/tempGENERIC_PLAN
: plan générique (requête préparée,
v16)SETTINGS
: paramètres configurés pour
l’optimisationWAL
: nombre d’enregistrements et nombre d’octets
écrits dans les journauxCOSTS
: par défautTIMING
: par défautVERBOSE
: colonnes considéréesSUMMARY
: temps de planificationFORMAT
: sortie en text, XML, JSON, YAMLseq_page_cost
(défaut : 1)cpu_tuple_cost
&
cpu_operator_cost
enable_seqscan
parallel_tuple_cost
,
min_parallel_table_scan_size
VACUUM
récent
random_page_cost
(4 ou moins ?)cpu_index_tuple_cost
effective_cache_size
(⅔ de la RAM ?)effective_io_concurrency
maintenance_io_concurrency
min_parallel_index_scan_size
enable_indexscan
, enable_indexonlyscan
,
enable_bitmapscan
EXISTS
, IN
et certaines jointures
externes
work_mem
( et hash_mem_multiplier
)seq_page_cost
& random_page_cost
.enable_nestloop
, enable_hashjoin
,
enable_mergejoin
enable_hashagg
work_mem
& hash_mem_multiplier
(v13)DISTINCT
)UNION ALL
), Except,
IntersectLes statistiques sont-elles à jour ?
ANALYZE
join_collapse_limit
(défaut : 8)join_collapse_limit
si nécessaire (12-15)
from_collapse_limit
varchar_pattern_ops
/ text_pattern_ops
,
etc.LIKE '%mot%'
:
pg_trgm
,DELETE
lentDelete (actual time=111.251..111.251 rows=0 loops=1)
-> Hash Join (actual time=1.094..21.402 rows=9347 loops=1)
-> Seq Scan on lot_a30_descr_lot
(actual time=0.007..11.248 rows=34934 loops=1)
-> Hash (actual time=0.501..0.501 rows=561 loops=1)
-> Bitmap Heap Scan on lot_a10_pdl
(actual time=0.121..0.326 rows=561 loops=1)
Recheck Cond: (id_fantoir_commune = 320013)
-> Bitmap Index Scan on...
(actual time=0.101..0.101 rows=561 loops=1)
Index Cond: (id_fantoir_commune = 320013)
Trigger for constraint fk_lotlocal_lota30descrlot:
time=1010.358 calls=9347
Trigger for constraint fk_nonbatia21descrsuf_lota30descrlot:
time=2311695.025 calls=9347
Total runtime: 2312835.032 ms
random_page_cost
effective_cache_size
NOT IN
avec une sous-requête
NOT EXISTS
UNION
entraîne un tri systématique
UNION ALL
SELECT
LATERAL
ALTER DATABASE erp SET auto_explain.log_min_duration = '3s' ;
N’hésitez pas, c’est le moment !
Cette partie présente différents problèmes fréquemment rencontrés et leurs solutions. Elles ont trait aussi bien à des problèmes courants qu’à des mauvaises pratiques.
Contrairement à une idée assez fréquemment répandue, le terme relationnel ne désigne pas le fait que les tables soient liées entre elles. Les « tables » SONT les relations. On fait référence ici à l’algèbre relationnelle, inventée en 1970 par Edgar Frank Codd.
Les bases de données dites relationnelles n’implémentent habituellement pas exactement cet algèbre, mais en sont très proches. Le langage SQL, entre autres, ne respecte pas l’algèbre relationnelle. Le sujet étant vaste et complexe, il ne sera pas abordé ici. Si vous voulez approfondir le sujet, le livre Introduction aux bases de données de Chris J. Date, est un des meilleurs ouvrages sur l’algèbre relationnelle et les déficiences du langage SQL à ce sujet.
Le modèle relationnel est apparu suite à un constat : les bases de données de l’époque (hiérarchiques) reposaient sur la notion de pointeur. Une mise à jour pouvait donc facilement casser le modèle : doublons simples, données pointant sur du « vide », doublons incohérents entre eux, etc.
Le modèle relationnel a donc été proposé pour remédier à tous ces problèmes. Un système relationnel repose sur le concept de relation (table en SQL). Une relation est un ensemble de faits. Chaque fait est identifié par un identifiant (clé naturelle). Le fait lie cet identifiant à un certain nombre d’attributs. Une relation ne peut donc pas avoir de doublon.
La modélisation relationnelle étant un vaste sujet en soi, nous n’allons pas tout détailler ici, mais plutôt rappeler les points les plus importants.
Il existe une définition mathématique précise de chacune des 7 formes normales.
3FN
Une relation (table) est en troisième forme normale si tous les attributs (colonnes) dépendent de la clé (primaire), de toute la clé (pas d’un sous-ensemble de ses colonnes), et de rien d’autre que de la clé (une colonne supplémentaire).
Si vos tables vérifient déjà ces trois points, votre modélisation est probablement assez bonne.
Voir l’article wikipedia présentant l’ensemble des formes normales.
Un attribut (colonne) doit être atomique :
WHERE
Non respect = violation de la première forme normale
L’exemple suivant utilise une table voiture
. Les deux
tables voitures
et voitures_ecv
peuvent être
téléchargées installées comme suit :
createdb voitures
curl -kL https://dali.bo/tp_voitures -o /tmp/voitures.dmp
pg_restore -d voitures /tmp/voitures.dmp
# un message sur le schéma public préexistant est normal
Ne pas oublier d’effectuer un VACUUM ANALYZE
.
Immatriculation | Modèle | Caractéristiques |
---|---|---|
NH-415-DG | twingo | 4 roues motrices,toit ouvrant, climatisation |
EO-538-WR | clio | boite automatique,abs,climatisation |
Cette modélisation viole la première forme normale (atomicité des
attributs). Si on recherche toutes les voitures qui ont l’ABS, on va
devoir utiliser une clause WHERE
de ce type :
ce qui sera évidemment très inefficace.
Par ailleurs, on n’a évidemment aucun contrôle sur ce qui est mis
dans le champ caractéristiques
, ce qui est la garantie de
données incohérentes au bout de quelques jours (heures ?) d’utilisation.
Par exemple, rien n’empêche d’ajouter une ligne avec des
caractéristiques similaires légèrement différentes, comme « ABS »,
« boîte automatique ».
Ce modèle ne permet donc pas d’assurer la cohérence des données.
Column | Type | Description
----------------+---------+------------------------------------
immatriculation | text | Clé primaire
modele | text |
couleur | color | Couleur vehicule (bleu,rouge,vert)
abs | boolean | Option anti-blocage des roues
type_roue | boolean | tole/aluminium
motricite | boolean | 2 roues motrices / 4 roues motrices
Plusieurs valeurs : contrainte CHECK
/enum/table de
référence
Beaucoup de champs : clé/valeur (plusieurs formes possibles)
Une alternative plus fiable est de rajouter des colonnes
boolean quatre_roues_motrices
, boolean abs
,
varchar couleur
. C’est ce qui est à privilégier si le
nombre de caractéristiques est fixe et pas trop important.
Dans le cas où un simple booléen ne suffit pas, un champ avec une contrainte est possible. Il y a plusieurs méthodes :
CREATE TYPE color AS ENUM ('bleu', 'rouge', 'vert') ;
ALTER TABLE voitures ADD COLUMN couleur color ;
(Les énumérations ne sont pas adaptées à des modifications fréquentes
et nécessitent parfois un transtypage vers du text
).
CREATE TABLE couleurs (
couleur_id int PRIMARY KEY,
couleur text
) ;
ALTER TABLE voitures ADD COLUMN couleur_id REFERENCES couleurs ;
Ce modèle facilite les recherches et assure la cohérence. L’indexation est facilitée, et les performances ne sont pas dégradées, bien au contraire.
Dans le cas où le nombre de propriétés n’est pas aussi bien défini qu’ici, ou est grand, même un modèle clé-valeur dans une associée vaut mieux que l’accumulation de propriétés dans un champ texte. Même une simple table des caractéristiques est plus flexible (voir le TP).
Un modèle clé/valeur existe sous plusieurs variantes (table associée,
champs hstore
ou JSON…) et a ses propres inconvénients,
mais il offre au moins plus de flexibilité et de possibilités
d’indexation ou de validation des données. Ce sujet est traité plus
loin.
Les contraintes d’intégrité et notamment les clés étrangères sont parfois absentes des modèles de données. Les problématiques de performance et de flexibilité sont souvent mises en avant, alors que les contraintes sont justement une aide pour l’optimisation de requêtes par le planificateur, mais surtout une garantie contre de très coûteuses corruption de données logiques.
L’absence de contraintes a souvent des conséquences catastrophiques.
LEFT JOIN
CHECK
pour exclure une
partitionDe plus, l’absence de contraintes va également entraîner des problèmes d’intégrité des données. Il est par exemple très compliqué de se prémunir efficacement contre une race condition2 en l’absence de clé étrangère.
Imaginez le scénario suivant :
Ce cas est très facilement gérable pour un moteur de base de donnée si une clé étrangère existe. Redévelopper ces mêmes contrôles dans la couche applicative sera toujours plus coûteux en terme de performance, voire impossible à faire dans certains cas sans passer par la base de donnée elle-même (multiples serveurs applicatifs accédant à la même base de donnée).
Il peut s’ensuivre des calculs d’agrégats faux et des problèmes applicatifs de toute sorte. Souvent, plutôt que de corriger le modèle de données, des fonctions de vérification de la cohérence des données seront mises en place, entraînant ainsi un travail supplémentaire pour trouver et corriger les incohérences.
Lorsque ces problèmes d’intégrité seront détectés, il s’en suivra également la création de procédures de vérification de cohérence des données qui vont aussi alourdir les développements, entraînant ainsi un travail supplémentaire pour trouver et corriger les incohérences. Ce qui a été gagné d’un côté est perdu de l’autre, mais sous une forme différente.
De plus, les contraintes d’intégrité sont des informations qui garantissent non seulement la cohérence des données mais qui vont également influencer l’optimiseur dans ses choix de plans d’exécution.
Parmi les informations utilisées par l’optimiseur, les contraintes
d’unicité permettent de déterminer sans difficulté la répartition des
valeurs stockées dans une colonne : chaque valeur est simplement unique.
L’utilisation des index sur ces colonnes sera donc probablement
favorisée. Les contraintes d’intégrité permettent également à
l’optimiseur de pouvoir éliminer des jointures inutiles avec un
LEFT JOIN
. Enfin, les contraintes CHECK
sur
des tables partitionnées permettent de cibler les lectures sur certaines
partitions seulement, et donc d’exclure les partitions inutiles.
DEFERRABLE
!Parfois, les clés étrangères sont supprimées simplement parce que des transactions sont en erreur car des données sont insérées dans une table fille sans avoir alimenté la table mère. Des identifiants de clés étrangères de la table fille sont absents de la table mère, entraînant l’arrêt en erreur de la transaction. Il est possible de contourner cela en différant la vérification des contraintes d’intégrité à la fin de la transaction
Une contrainte DEFERRABLE
associée à un
SET CONSTRAINT … DEFERRED
n’est vérifiée que lors du
COMMIT
. Elle ne gêne donc pas le développeur, qui peut
insérer les données dans l’ordre qu’il veut ou insérer temporairement
des données incohérentes. Ce qui compte est que la situation soit saine
à la fin de la transaction, quand les données seront enregistrées et
deviendront visibles par les autres sessions.
L’exemple ci-dessous montre l’utilisation de la vérification des contraintes d’intégrité en fin de transaction.
CREATE TABLE mere (id integer, t text);
CREATE TABLE fille (id integer, mere_id integer, t text);
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
ALTER TABLE fille
ADD CONSTRAINT fk_mere_fille
FOREIGN KEY (mere_id)
REFERENCES mere (id)
MATCH FULL
ON UPDATE NO ACTION
ON DELETE CASCADE
DEFERRABLE;
La transaction insère d’abord les données dans la table fille, puis ensuite dans la table mère :
BEGIN ;
SET CONSTRAINTS ALL DEFERRED ;
INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');
INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');
COMMIT;
Sans le SET CONSTRAINTS ALL DEFERRED
, le premier ordre
serait tombé en erreur.
identifiant / nom_attribut / valeur
Le modèle relationnel a été critiqué depuis sa création pour son manque de souplesse pour ajouter de nouveaux attributs ou pour proposer plusieurs attributs sans pour autant nécessiter de redévelopper l’application.
La solution souvent retenue est d’utiliser une table « à tout faire » entité-attribut-valeur qui est associée à une autre table de la base de données. Techniquement, une telle table comporte trois colonnes. La première est un identifiant généré qui permet de référencer la table mère. Les deux autres colonnes stockent le nom de l’attribut représenté et la valeur représentée.
Ainsi, pour reprendre l’exemple des informations de contacts pour un
individu, une table personnes
permet de stocker un
identifiant de personne. Une table personne_attributs
permet d’associer des données à un identifiant de personne. Le type de
données de la colonne est souvent prévu largement pour faire tenir tout
type d’informations, mais sous forme textuelle. Les données ne peuvent
donc pas être validées.
CREATE TABLE personnes (id SERIAL PRIMARY KEY);
CREATE TABLE personne_attributs (
id_pers INTEGER NOT NULL,
nom_attr varchar(20) NOT NULL,
val_attr varchar(100) NOT NULL
);
INSERT INTO personne_attributs (id_pers, nom_attr, val_attr)
VALUES (1, 'nom', 'Prunelle'),
(1, 'prenom', 'Léon');
(...)
Un tel modèle peut sembler souple mais pose plusieurs problèmes. Le
premier concerne l’intégrité des données. Il n’est pas possible de
garantir la présence d’un attribut comme on le ferait avec une
contrainte NOT NULL
. Si l’on souhaite stocker des données
dans un autre format qu’une chaîne de caractère, pour bénéficier des
contrôles de la base de données sur ce type, la seule solution est de
créer autant de colonnes d’attributs qu’il y a de types de données à
représenter. Ces colonnes ne permettront pas d’utiliser des contraintes
CHECK
pour garantir la cohérence des valeurs stockées avec
ce qui est attendu, car les attributs peuvent stocker n’importe quelle
donnée.
Comment lister tous les DBA ?
id_pers |
nom_attr |
val_attr |
---|---|---|
1 | nom | Prunelle |
1 | prenom | Léon |
1 | telephone | 0123456789 |
1 | fonction | dba |
SELECT id, att_nom.val_attr AS nom,
att_prenom.val_attr AS prenom,
att_telephone.val_attr AS tel
FROM personnes p
JOIN personne_attributs AS att_nom
ON (p.id=att_nom.id_pers AND att_nom.nom_attr='nom')
JOIN personne_attributs AS att_prenom
ON (p.id=att_prenom.id_pers AND att_prenom.nom_attr='prenom')
JOIN personne_attributs AS att_telephone
ON (p.id=att_telephone.id_pers AND att_telephone.nom_attr='telephone')
JOIN personne_attributs AS att_fonction
ON (p.id=att_fonction.id_pers AND att_fonction.nom_attr='fonction')
WHERE att_fonction.val_attr='dba';
Les requêtes SQL qui permettent de récupérer les données requises dans l’application sont également particulièrement lourdes à écrire et à maintenir, à moins de récupérer les données attribut par attribut.
Des problèmes de performances vont donc très rapidement se poser. Cette représentation des données entraîne souvent l’effondrement des performances d’une base de données relationnelle. Les requêtes sont difficilement optimisables et nécessitent de réaliser beaucoup d’entrées-sorties disque, car les données sont éparpillées un peu partout dans la table.
hstore
,
jsonb
Lorsque de telles solutions sont déployées pour stocker des données transactionnelles, il vaut mieux revenir à un modèle de données traditionnel qui permet de typer correctement les données, de mettre en place les contraintes d’intégrité adéquates et d’écrire des requêtes SQL efficaces.
Dans d’autres cas où le nombre de champs est vraiment élevé
et variable, il vaut mieux utiliser un type de données de PostgreSQL qui
est approprié, comme hstore
qui permet de stocker des
données sous la forme clé->valeur
. On conserve ainsi
l’intégrité des données (on n’a qu’une ligne par personne), on évite de
très nombreuses jointures source d’erreurs et de ralentissements, et
même de la place disque.
De plus, ce type de données peut être indexé pour garantir de bons temps de réponses des requêtes qui nécessitent des recherches sur certaines clés ou certaines valeurs.
Voici l’exemple précédent revu avec l’extension
hstore
:
CREATE EXTENSION hstore;
CREATE TABLE personnes (id SERIAL PRIMARY KEY, attributs hstore);
INSERT INTO personnes (attributs) VALUES ('nom=>Prunelle, prenom=>Léon');
INSERT INTO personnes (attributs) VALUES ('prenom=>Gaston,nom=>Lagaffe');
INSERT INTO personnes (attributs) VALUES ('nom=>DeMaesmaker');
id | attributs
----+--------------------------------------
1 | "nom"=>"Prunelle", "prenom"=>"Léon"
2 | "nom"=>"Lagaffe", "prenom"=>"Gaston"
3 | "nom"=>"DeMaesmaker"
Le principe du JSON est similaire.
telephone_1
, telephone_2
Dans certains cas, le modèle de données doit être étendu pour pouvoir
stocker des données complémentaires. Un exemple typique est une table
qui stocke les informations pour contacter une personne. Une table
personnes
ou contacts
possède une colonne
telephone
qui permet de stocker le numéro de téléphone
d’une personne. Or, une personne peut disposer de plusieurs numéros. Le
premier réflexe est souvent de créer une seconde colonne
telephone_2
pour stocker un numéro de téléphone
complémentaire. S’en suit une colonne telephone_3
voire
telephone_4
en fonction des besoins.
Dans de tels cas, les requêtes deviennent plus complexes à maintenir et il est difficile de garantir l’unicité des valeurs stockées pour une personne car l’écriture des contraintes d’intégrité devient de plus en plus complexe au fur et à mesure que l’on ajoute une colonne pour stocker un numéro.
La solution la plus pérenne pour gérer ce cas de figure est de créer
une table de dépendance qui est dédiée au stockage des numéros de
téléphone. Ainsi, la table personnes
ne portera plus de
colonnes telephone
, mais une table telephones
portera un identifiant référençant une personne et un numéro de
téléphone. Ainsi, si une personne dispose de trois, quatre… numéros de
téléphone, la table telephones
comportera autant de lignes
qu’il y a de numéros pour une personne.
Les différents numéros de téléphone seront obtenus par jointure entre
la table personnes
et la table telephones
.
L’application se chargera de l’affichage.
Ci-dessous, un exemple d’implémentation du problème où une table
telephones
dans laquelle plusieurs numéros seront stockés
sur plusieurs lignes plutôt que dans plusieurs colonnes.
CREATE TABLE personnes (
per_id SERIAL PRIMARY KEY,
nom VARCHAR(50) NOT NULL,
pnom VARCHAR(50) NOT NULL,
...
);
CREATE TABLE telephones (
per_id INTEGER NOT NULL,
numero VARCHAR(20),
PRIMARY KEY (per_id, numero),
FOREIGN KEY (per_id) REFERENCES personnes (per_id)
);
L’unicité des valeurs sera garantie à l’aide d’une contrainte
d’unicité posée sur l’identifiant per_id
et le numéro de
téléphone.
Une autre solution consiste à utiliser un tableau pour représenter
cette information. D’un point de vue conceptuel, le lien entre une
personne et son ou ses numéros de téléphone est plus une « composition »
qu’une réelle « relation » : le numéro de téléphone ne nous intéresse
pas en tant que tel, mais uniquement en tant que détail d’une personne.
On n’accédera jamais à un numéro de téléphone séparément : la table
telephones
donnée plus haut n’a pas de clé « naturelle »,
un simple rattachement à la table personnes par l’identifiant de la
personne. Sans même parler de partitionnement, on gagnerait donc en
performances en stockant directement les numéros de téléphone dans la
table personnes
, ce qui est parfaitement faisable sous
PostgreSQL :
CREATE TABLE personnes (
per_id SERIAL PRIMARY KEY,
nom VARCHAR(50) NOT NULL,
pnom VARCHAR(50) NOT NULL,
numero VARCHAR(20)[]
);
-- Ajout d'une personne
INSERT INTO personnes (nom, pnom, numero)
VALUES ('Simpson', 'Omer', '{0607080910}');
per_id | nom | pnom | numero
--------+---------+------+--------------
1 | Simpson | Omer | {0607080910}
-- Ajout d'un numéro de téléphone pour une personne donnée :
UPDATE personnes
SET numero = numero || '{0102030420}'
WHERE per_id = 1;
per_id | nom | pnom | numero
--------+---------+------+-------------------------
1 | Simpson | Omer | {0607080910,0102030420}
ARRAY
ou un type
compositeCertaines applications, typiquement celles récupérant des données temporelles, stockent peu de colonnes (parfois juste date, capteur, valeur…) mais énormément de lignes.
Dans le modèle MVCC de PostgreSQL, chaque ligne utilise au bas mot 23
octets pour stocker xmin
, xmax
et les autres
informations de maintenance de la ligne. On peut donc se retrouver avec
un overhead représentant la majorité de la table. Cela peut
avoir un fort impact sur la volumétrie :
CREATE TABLE valeurs_capteur (d timestamp, v smallint);
-- soit 8 + 2 = 10 octets de données utiles par ligne
-- 100 valeurs chaque seconde pendant 100 000 s = 10 millions de lignes
INSERT INTO valeurs_capteur (d, v)
SELECT current_timestamp + (i%100000) * interval '1 s',
(random()*200)::smallint
FROM generate_series (1,10000000) i ;
pg_size_pretty
----------------
422 MB
-- dont seulement 10 octets * 10 Mlignes = 100 Mo de données utiles
Il est parfois possible de regrouper les valeurs sur une même ligne
au sein d’un ARRAY
, ici pour chaque seconde :
CREATE TABLE valeurs_capteur_2 (d timestamp, tv smallint[]);
INSERT INTO valeurs_capteur_2
SELECT current_timestamp+ (i%100000) * interval '1 s' ,
array_agg((random()*200)::smallint)
FROM generate_series (1,10000000) i
GROUP BY 1 ;
pg_size_pretty
----------------
25 MB
-- soit par ligne :
-- 23 octets d'entête + 8 pour la date + 100 * 2 octets de valeurs smallint
Dans cet exemple, on économise la plupart des entêtes de ligne, mais aussi les données redondantes (la date), et le coût de l’alignement des champs. Avec suffisamment de valeurs à stocker, une partie des données peut même se retrouver compressée dans la partie TOAST de la table.
La récupération des données se fait de manière à peine moins simple :
L’indexation des valeurs à l’intérieur du tableau nécessite un index GIN :
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on valeurs_capteur_2 (cost=311.60..1134.20 rows=40000 width=232)
(actual time=8.299..20.460 rows=39792 loops=1)
Recheck Cond: ('{199}'::smallint[] && tv)
Heap Blocks: exact=3226
-> Bitmap Index Scan on tvx (cost=0.00..301.60 rows=40000 width=0)
(actual time=7.723..7.723 rows=39792 loops=1)
Index Cond: ('{199}'::smallint[] && tv)
Planning time: 0.214 ms
Execution time: 22.386 ms
Évidemment cette technique est à réserver aux cas où les données mises en tableau sont insérées et mises à jour ensemble.
Le maniement des tableaux est détaillé dans la documentation officielle.
Tout cela est détaillé et mesuré dans ce billet
de Julien Rouhaud. Il évoque aussi le cas de structures plus
complexes : au lieu d’un hstore
ou d’un ARRAY
,
on peut utiliser un type qui regroupe les différentes valeurs.
Une autre option, complémentaire, est le partitionnement. Il peut être géré manuellement (tables générées par l’applicatif, par date et/ou par source de données…) ou profiter des deux modes de partitionnement de PostgreSQL. Il n’affectera pas la volumétrie totale mais permet de gérer des partitions plus maniables. Il a aussi l’intérêt de ne pas nécessiter de modification du code pour lire les données.
Tables à plusieurs dizaines, voire centaines de colonnes :
Il arrive régulièrement de rencontrer des tables ayant énormément de
colonnes (souvent à NULL
d’ailleurs). Cela signifie qu’on
modélise une entité ayant tous ces attributs (centaines d’attributs). Il
est très possible que cette entité soit en fait composée de
« sous-entités », qu’on pourrait modéliser séparément. On peut
évidemment trouver des cas particuliers contraires, mais une table de ce
type reste un bon indice.
Surtout si vous trouvez dans les dernières colonnes des attributs
comme attribut_supplementaire_1
…
real
ou double
(float
)money
numeric
pour les calculs précis (financiers
notamment)Certaines applications scientifiques se contentent de types flottants
standards, car ils permettent d’encoder des valeurs plus importantes que
les types entiers standards. En pratique, les types
float(x)
correspondent aux types real
ou
double precision
de PostgreSQL.
Néanmoins, les types flottants sont peu précis, notamment pour les applications financières où une erreur d’arrondi n’est pas tolérable. Par exemple :
test=# CREATE TABLE comptes (compte_id serial PRIMARY KEY, solde float);
CREATE TABLE
test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4
test=# SELECT SUM(solde) FROM comptes;
sum
-----------------
100000100010010
Le type numeric
est alors généralement conseillé. Sa
valeur est exacte et les calculs sont justes.
test=# CREATE TABLE comptes (compte_id serial PRIMARY KEY, solde numeric);
CREATE TABLE
test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4
test=# SELECT SUM(solde) FROM comptes;
sum
-------------------
100000100010010.5
numeric
(sans autre indication de précision) autorise
même un calcul exact sans arrondi avec des ordres de grandeur très
différents; comme SELECT 1e9999 + 1e-9999 ;
.
Paradoxalement, le type money
n’est pas adapté aux
montants financiers : sa manipulation implique de convertir en
numeric
pour éviter des erreurs d’arrondis. Autant utiliser
directement numeric
: si l’on ne mentionne pas la
précision, elle est exacte.
Le type numeric
paye sa précision par un stockage
parfois plus important et par des calculs plus lents que ceux des types
natifs comme les intX
et les floatX
.
Pour plus de détails, voir la documentation officielle :
Plus rarement, on rencontre aussi :
varchar
contenant
NULL
On rencontre parfois ce genre de choses :
Immatriculation Camion | Numero de tournee |
---|---|
TP-108-AX | 12 |
TF-112-IR | ANNULÉE |
avec bien sûr une table tournée
décrivant la tournée
elle-même, avec une clé technique numérique.
Cela pose un gros problème de modélisation : la colonne a un type de
contenu qui dépend de l’information qu’elle contient. On va aussi avoir
un problème de performance en joignant cette chaîne à la clé numérique
de la table tournée
. Le moteur n’aura que deux choix :
convertir la chaîne en numérique, avec une exception à la clé en
essayant de convertir « ANNULÉE », ou bien (ce qu’il fera) convertir le
numérique de la table tournee
en chaîne. Cette dernière
méthode rendra l’accès à l’identifiant de tournée par index impossible.
D’où un parcours complet (Seq Scan) de la table
tournée
à chaque accès et des performances qui décroissent
au fur et à mesure que la table grossit.
La solution est une supplémentaire (un booléen
tournee_ok
par exemple).
Un autre classique est le champ date stocké au format texte. Le format correct de cette date ne peut être garanti par la base, ce qui mène systématiquement à des erreurs de conversion si un humain est impliqué. Dans un environnement international où l’on mélange DD-MM-YYYY et MM-DD-YYYY, un rattrapage manuel est même illusoire. Les calculs de date sont évidemment impossibles.
NULL
LIKE
Le langage SQL est généralement méconnu, ce qui amène à l’écriture de requêtes peu performantes, voire peu pérennes.
NULL
signifie habituellement :
NULL
est habituellement signe d’un
problème de modélisation.NOT NULL
recommandéUne table qui contient majoritairement des valeurs NULL
contient bien peu de faits utilisables. La plupart du temps, c’est une
table dans laquelle on stocke beaucoup de choses n’ayant que peu de
rapport entre elles, les champs étant renseignés suivant le type de
chaque « chose ». C’est donc le plus souvent un signe de mauvaise
modélisation. Cette table aurait certainement dû être éclatée en
plusieurs tables, chacune représentant une des relations qu’on veut
modéliser.
Il est donc recommandé que tous les attributs d’une table portent une
contrainte NOT NULL
. Quelques colonnes peuvent ne pas
porter ce type de contraintes, mais elles doivent être une exception. En
effet, le comportement de la base de données est souvent source de
problèmes lorsqu’une valeur NULL
entre en jeu. Par exemple,
la concaténation d’une chaîne de caractères avec une valeur
NULL
retourne une valeur NULL
, car elle est
propagée dans les calculs. D’autres types de problèmes apparaissent
également pour les prédicats.
Il faut avoir à l’esprit cette citation de Chris Date :
« La valeur
NULL
telle qu’elle est implémentée dans SQL peut poser plus de problèmes qu’elle n’en résout. Son comportement est parfois étrange et est source de nombreuses erreurs et de confusions. »
Il ne ne s’agit pas de remplacer ce NULL
par des valeurs
« magiques » (par exemple -1 pour « Non renseigné » , cela ne ferait que
complexifier le code) mais de se demander si NULL
a une
vraie signification.
Le langage SQL permet de s’appuyer sur l’ordre physique des colonnes d’une table. Or, faire confiance à la base de données pour conserver cet ordre physique peut entraîner de graves problèmes applicatifs en cas de changements. Dans le meilleur des cas, l’application ne fonctionnera plus, ce qui permet d’éviter les corruptions de données silencieuses, où une colonne prend des valeurs destinées normalement à être stockées dans une autre colonne. Si l’application continue de fonctionner, elle va générer des résultats faux et des incohérences d’affichage.
Par exemple, l’ordre des colonnes peut changer notamment lorsque
certains ETL sont utilisés pour modifier le type d’une colonne
varchar(10)
en varchar(11)
. Par exemple, pour
la colonne username
, l’ETL Kettle génère les ordres
suivants :
ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username
Il génère des ordres SQL inutiles et consommateurs d’entrées/sorties disques car il doit générer des ordres SQL compris par tous les SGBD du marché. Or, tous les SGBD ne permettent pas de changer le type d’une colonne aussi simplement que dans PostgreSQL. PostgreSQL, lui, ne permet pas de changer l’ordre d’apparition des colonnes.
C’est pourquoi il est préférable de lister explicitement les colonnes
dans les ordres INSERT
et SELECT
, afin de
garder un ordre d’insertion déterministe.
Exemples
Exemple de modification du schéma pouvant entraîner des problèmes d’insertion si les colonnes ne sont pas listées explicitement :
CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);
INSERT INTO insere VALUES (1, 'XX', 10);
ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;
INSERT INTO insere VALUES (2, 'XXX', 10);
L’utilisation de SELECT *
à la place d’une liste
explicite est une erreur similaire. Le nombre de colonnes peut
brutalement varier. De plus, toutes les colonnes sont rarement utilisées
dans un tel cas, ce qui provoque un gaspillage de ressources.
Le problème est similaire à tout autre langage :
Un exemple (sous Oracle) :
SELECT Article.datem AS Article_1_9,
Article.degre_alcool AS Article_1_10,
Article.id AS Article_1_19,
Article.iddf_categor AS Article_1_20,
Article.iddp_clsvtel AS Article_1_21,
Article.iddp_cdelist AS Article_1_22,
Article.iddf_cd_prix AS Article_1_23,
Article.iddp_agreage AS Article_1_24,
Article.iddp_codelec AS Article_1_25,
Article.idda_compo AS Article_1_26,
Article.iddp_comptex AS Article_1_27,
Article.iddp_cmptmat AS Article_1_28,
Article.idda_articleparent AS Article_1_29,
Article.iddp_danger AS Article_1_30,
Article.iddf_fabric AS Article_1_33,
Article.iddp_marqcom AS Article_1_34,
Article.iddp_nomdoua AS Article_1_35,
Article.iddp_pays AS Article_1_37,
Article.iddp_recept AS Article_1_40,
Article.idda_unalvte AS Article_1_42,
Article.iddb_sitecl AS Article_1_43,
Article.lib_caisse AS Article_1_49,
Article.lib_com AS Article_1_50,
Article.maj_en_attente AS Article_1_61,
Article.qte_stk AS Article_1_63,
Article.ref_tech AS Article_1_64,
1 AS Article_1_70,
CASE
WHEN (SELECT COUNT(MA.id)
FROM da_majart MA
join da_majmas MM
ON MM.id = MA.idda_majmas
join gt_tmtprg TMT
ON TMT.id = MM.idgt_tmtprg
join gt_prog PROG
ON PROG.id = TMT.idgt_prog
WHERE idda_article = Article.id
AND TO_DATE(TO_CHAR(PROG.date_lancement, 'DDMMYYYY')
|| TO_CHAR(PROG.heure_lancement, ' HH24:MI:SS'),
'DDMMYYYY HH24:MI:SS') >= SYSDATE) >= 1 THEN 1
ELSE 0
END AS Article_1_74,
Article.iddp_compnat AS Article_2_0,
Article.iddp_modven AS Article_2_1,
Article.iddp_nature AS Article_2_2,
Article.iddp_preclin AS Article_2_3,
Article.iddp_raybala AS Article_2_4,
Article.iddp_sensgrt AS Article_2_5,
Article.iddp_tcdtfl AS Article_2_6,
Article.iddp_unite AS Article_2_8,
Article.idda_untgrat AS Article_2_9,
Article.idda_unpoids AS Article_2_10,
Article.iddp_unilogi AS Article_2_11,
ArticleComplement.datem AS ArticleComplement_5_6,
ArticleComplement.extgar_depl AS ArticleComplement_5_9,
ArticleComplement.extgar_mo AS ArticleComplement_5_10,
ArticleComplement.extgar_piece AS ArticleComplement_5_11,
ArticleComplement.id AS ArticleComplement_5_20,
ArticleComplement.iddf_collect AS ArticleComplement_5_22,
ArticleComplement.iddp_gpdtcul AS ArticleComplement_5_23,
ArticleComplement.iddp_support AS ArticleComplement_5_25,
ArticleComplement.iddp_typcarb AS ArticleComplement_5_27,
ArticleComplement.mt_ext_gar AS ArticleComplement_5_36,
ArticleComplement.pres_cpt AS ArticleComplement_5_44,
GenreProduitCulturel.code AS GenreProduitCulturel_6_0,
Collection.libelle AS Collection_8_1,
Gtin.date_dern_vte AS Gtin_10_0,
Gtin.gtin AS Gtin_10_1,
Gtin.id AS Gtin_10_3,
Fabricant.code AS Fabricant_14_0,
Fabricant.nom AS Fabricant_14_2,
ClassificationVenteLocale.niveau1 AS ClassificationVenteL_16_2,
ClassificationVenteLocale.niveau2 AS ClassificationVenteL_16_3,
ClassificationVenteLocale.niveau3 AS ClassificationVenteL_16_4,
ClassificationVenteLocale.niveau4 AS ClassificationVenteL_16_5,
MarqueCommerciale.code AS MarqueCommerciale_18_0,
MarqueCommerciale.libellelong AS MarqueCommerciale_18_4,
Composition.code AS Composition_20_0,
CompositionTextile.code AS CompositionTextile_21_0,
AssoArticleInterfaceBalance.datem AS AssoArticleInterface_23_0,
AssoArticleInterfaceBalance.lib_envoi AS AssoArticleInterface_23_3,
AssoArticleInterfaceCaisse.datem AS AssoArticleInterface_24_0,
AssoArticleInterfaceCaisse.lib_envoi AS AssoArticleInterface_24_3,
NULL AS TypeTraitement_25_0,
NULL AS TypeTraitement_25_1,
RayonBalance.code AS RayonBalance_31_0,
RayonBalance.max_cde_article AS RayonBalance_31_5,
RayonBalance.min_cde_article AS RayonBalance_31_6,
TypeTare.code AS TypeTare_32_0,
GrilleDePrix.datem AS GrilleDePrix_34_1,
GrilleDePrix.libelle AS GrilleDePrix_34_3,
FicheAgreage.code AS FicheAgreage_38_0,
Codelec.iddp_periact AS Codelec_40_1,
Codelec.libelle AS Codelec_40_2,
Codelec.niveau1 AS Codelec_40_3,
Codelec.niveau2 AS Codelec_40_4,
Codelec.niveau3 AS Codelec_40_5,
Codelec.niveau4 AS Codelec_40_6,
PerimetreActivite.code AS PerimetreActivite_41_0,
DonneesPersonnalisablesCodelec.gestionreftech AS DonneesPersonnalisab_42_0,
ClassificationArticleInterne.id AS ClassificationArticl_43_0,
ClassificationArticleInterne.niveau1 AS ClassificationArticl_43_2,
DossierCommercial.id AS DossierCommercial_52_0,
DossierCommercial.codefourndc AS DossierCommercial_52_1,
DossierCommercial.anneedc AS DossierCommercial_52_3,
DossierCommercial.codeclassdc AS DossierCommercial_52_4,
DossierCommercial.numversiondc AS DossierCommercial_52_5,
DossierCommercial.indice AS DossierCommercial_52_6,
DossierCommercial.code_ss_classement AS DossierCommercial_52_7,
OrigineNegociation.code AS OrigineNegociation_53_0,
MotifBlocageInformation.libellelong AS MotifBlocageInformat_54_3,
ArbreLogistique.id AS ArbreLogistique_63_1,
ArbreLogistique.codesap AS ArbreLogistique_63_5,
Fournisseur.code AS Fournisseur_66_0,
Fournisseur.nom AS Fournisseur_66_2,
Filiere.code AS Filiere_67_0,
Filiere.nom AS Filiere_67_2,
ValorisationAchat.val_ach_patc AS Valorisation_74_3,
LienPrixVente.code AS LienPrixVente_76_0,
LienPrixVente.datem AS LienPrixVente_76_1,
LienGratuite.code AS LienGratuite_78_0,
LienGratuite.datem AS LienGratuite_78_1,
LienCoordonnable.code AS LienCoordonnable_79_0,
LienCoordonnable.datem AS LienCoordonnable_79_1,
LienStatistique.code AS LienStatistique_81_0,
LienStatistique.datem AS LienStatistique_81_1
FROM da_article Article
join (SELECT idarticle,
poids,
ROW_NUMBER()
over (
PARTITION BY RNA.id
ORDER BY INNERSEARCH.poids) RN,
titre,
nom,
prenom
FROM da_article RNA
join (SELECT idarticle,
pkg_db_indexation.CALCULPOIDSMOTS(chaine,
'foire vins%') AS POIDS,
DECODE(index_clerecherche, 'Piste.titre', chaine,
'') AS TITRE,
DECODE(index_clerecherche, 'Artiste.nom_prenom',
SUBSTR(chaine, 0, INSTR(chaine, '_') - 1),
'') AS NOM,
DECODE(index_clerecherche, 'Artiste.nom_prenom',
SUBSTR(chaine, INSTR(chaine, '_') + 1),
'') AS PRENOM
FROM ((SELECT index_idenreg AS IDARTICLE,
C.cde_art AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticle'
join da_article C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT index_idenreg AS IDARTICLE,
C.cde_art AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticle'
join da_article C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL
(SELECT index_idenreg AS IDARTICLE,
C.cde_art_bal AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticleBalance'
join da_article C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT index_idenreg AS IDARTICLE,
C.cde_art_bal AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.codeArticleBalance'
join da_article C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL
(SELECT index_idenreg AS IDARTICLE,
C.lib_com AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.libelleCommercial'
join da_article C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT index_idenreg AS IDARTICLE,
C.lib_com AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Article.libelleCommercial'
join da_article C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL
(SELECT idda_article AS IDARTICLE,
C.gtin AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Gtin.gtin'
join da_gtin C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT idda_article AS IDARTICLE,
C.gtin AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'Gtin.gtin'
join da_gtin C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1)
UNION ALL
(SELECT idda_article AS IDARTICLE,
C.ref_frn AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'ArbreLogistique.referenceFournisseur'
join da_arblogi C
ON id = index_idenreg
WHERE mots_mot = 'foire'
INTERSECT
SELECT idda_article AS IDARTICLE,
C.ref_frn AS CHAINE,
index_clerecherche
FROM cstd_mots M
join cstd_index I
ON I.mots_id = M.mots_id
AND index_clerecherche =
'ArbreLogistique.referenceFournisseur'
join da_arblogi C
ON id = index_idenreg
WHERE mots_mot LIKE 'vins%'
AND 1 = 1))) INNERSEARCH
ON INNERSEARCH.idarticle = RNA.id) SEARCHMC
ON SEARCHMC.idarticle = Article.id
AND 1 = 1
left join da_artcmpl ArticleComplement
ON Article.id = ArticleComplement.idda_article
left join dp_gpdtcul GenreProduitCulturel
ON ArticleComplement.iddp_gpdtcul = GenreProduitCulturel.id
left join df_collect Collection
ON ArticleComplement.iddf_collect = Collection.id
left join da_gtin Gtin
ON Article.id = Gtin.idda_article
AND Gtin.principal = 1
AND Gtin.db_suplog = 0
left join df_fabric Fabricant
ON Article.iddf_fabric = Fabricant.id
left join dp_clsvtel ClassificationVenteLocale
ON Article.iddp_clsvtel = ClassificationVenteLocale.id
left join dp_marqcom MarqueCommerciale
ON Article.iddp_marqcom = MarqueCommerciale.id
left join da_compo Composition
ON Composition.id = Article.idda_compo
left join dp_comptex CompositionTextile
ON CompositionTextile.id = Article.iddp_comptex
left join da_arttrai AssoArticleInterfaceBalance
ON AssoArticleInterfaceBalance.idda_article = Article.id
AND AssoArticleInterfaceBalance.iddp_tinterf = 1
left join da_arttrai AssoArticleInterfaceCaisse
ON AssoArticleInterfaceCaisse.idda_article = Article.id
AND AssoArticleInterfaceCaisse.iddp_tinterf = 4
left join dp_raybala RayonBalance
ON Article.iddp_raybala = RayonBalance.id
left join dp_valdico TypeTare
ON TypeTare.id = RayonBalance.iddp_typtare
left join df_categor Categorie
ON Categorie.id = Article.iddf_categor
left join df_grille GrilleDePrix
ON GrilleDePrix.id = Categorie.iddf_grille
left join dp_agreage FicheAgreage
ON FicheAgreage.id = Article.iddp_agreage
join dp_codelec Codelec
ON Article.iddp_codelec = Codelec.id
left join dp_periact PerimetreActivite
ON PerimetreActivite.id = Codelec.iddp_periact
left join dp_perscod DonneesPersonnalisablesCodelec
ON Codelec.id = DonneesPersonnalisablesCodelec.iddp_codelec
AND DonneesPersonnalisablesCodelec.db_suplog = 0
AND DonneesPersonnalisablesCodelec.iddb_sitecl = 1012124
left join dp_clsart ClassificationArticleInterne
ON DonneesPersonnalisablesCodelec.iddp_clsart =
ClassificationArticleInterne.id
left join da_artdeno ArticleDenormalise
ON Article.id = ArticleDenormalise.idda_article
left join df_clasmnt ClassementFournisseur
ON ArticleDenormalise.iddf_clasmnt = ClassementFournisseur.id
left join tr_dosclas DossierDeClassement
ON ClassementFournisseur.id = DossierDeClassement.iddf_clasmnt
AND DossierDeClassement.date_deb <= '2013-09-27'
AND COALESCE(DossierDeClassement.date_fin,
TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
left join tr_doscomm DossierCommercial
ON DossierDeClassement.idtr_doscomm = DossierCommercial.id
left join dp_valdico OrigineNegociation
ON DossierCommercial.iddp_dossref = OrigineNegociation.id
left join dp_motbloc MotifBlocageInformation
ON MotifBlocageInformation.id = ArticleDenormalise.idda_motinf
left join da_arblogi ArbreLogistique
ON Article.id = ArbreLogistique.idda_article
AND ArbreLogistique.princ = 1
AND ArbreLogistique.db_suplog = 0
left join df_filiere Filiere
ON ArbreLogistique.iddf_filiere = Filiere.id
left join df_fourn Fournisseur
ON Filiere.iddf_fourn = Fournisseur.id
left join od_dosal dossierALValo
ON dossierALValo.idda_arblogi = ArbreLogistique.id
AND dossierALValo.idod_dossier IS NULL
left join tt_val_dal valoDossier
ON valoDossier.idod_dosal = dossierALValo.id
AND valoDossier.estarecalculer = 0
left join tt_valo ValorisationAchat
ON ValorisationAchat.idtt_val_dal = valoDossier.id
AND ValorisationAchat.date_modif_retro IS NULL
AND ValorisationAchat.date_debut_achat <= '2013-09-27'
AND COALESCE(ValorisationAchat.date_fin_achat,
TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
AND ValorisationAchat.val_ach_pab IS NOT NULL
left join da_lienart assoALPXVT
ON assoALPXVT.idda_article = Article.id
AND assoALPXVT.iddp_typlien = 14893
left join da_lien LienPrixVente
ON LienPrixVente.id = assoALPXVT.idda_lien
left join da_lienart assoALGRAT
ON assoALGRAT.idda_article = Article.id
AND assoALGRAT.iddp_typlien = 14894
left join da_lien LienGratuite
ON LienGratuite.id = assoALGRAT.idda_lien
left join da_lienart assoALCOOR
ON assoALCOOR.idda_article = Article.id
AND assoALCOOR.iddp_typlien = 14899
left join da_lien LienCoordonnable
ON LienCoordonnable.id = assoALCOOR.idda_lien
left join da_lienal assoALSTAT
ON assoALSTAT.idda_arblogi = ArbreLogistique.id
AND assoALSTAT.iddp_typlien = 14897
left join da_lien LienStatistique
ON LienStatistique.id = assoALSTAT.idda_lien WHERE
SEARCHMC.rn = 1
AND ( ValorisationAchat.id IS NULL
OR ValorisationAchat.date_debut_achat = (
SELECT MAX(VALMAX.date_debut_achat)
FROM tt_valo VALMAX
WHERE VALMAX.idtt_val_dal = ValorisationAchat.idtt_val_dal
AND VALMAX.date_modif_retro IS NULL
AND VALMAX.val_ach_pab IS NOT NULL
AND VALMAX.date_debut_achat <= '2013-09-27') )
AND ( Article.id IN (SELECT A.id
FROM da_article A
join du_ucutiar AssoUcUtiAr
ON AssoUcUtiAr.idda_article = A.id
join du_asucuti AssoUcUti
ON AssoUcUti.id = AssoUcUtiAr.iddu_asucuti
WHERE ( AssoUcUti.iddu_uti IN ( 90000000000022 ) )
AND a.iddb_sitecl = 1012124) )
AND Article.db_suplog = 0
ORDER BY SEARCHMC.poids ASC
Comprendre un tel monstre implique souvent de l’imprimer pour acquérir une vision globale et prendre des notes :
Ce code a été généré initialement par Hibernate, puis édité plusieurs
fois à la main.
LIKE
pg_trgm
Les bases de données qui stockent des données textuelles ont souvent pour but de permettre des recherches sur ces données textuelles.
La première solution envisagée lorsque le besoin se fait sentir est
d’utiliser l’opérateur LIKE
. Il permet en effet de réaliser
des recherches de motif sur une colonne stockant des données textuelles.
C’est une solution simple et qui peut s’avérer simpliste dans de
nombreux cas.
Tout d’abord, les recherches de type LIKE '%motif%'
ne
peuvent généralement pas tirer partie d’un index btree normal. Cela
étant dit, l’extension pg_trgm
permet d’optimiser ces
recherches à l’aide d’un index GiST ou GIN. Elle fait partie des
extensions standard et ne nécessite pas d’adaptation du code.
Exemples
L’exemple ci-dessous montre l’utilisation du module
pg_trgm
pour accélérer une recherche avec
LIKE '%motif%'
:
QUERY PLAN
------------------------------------------------------------
Seq Scan on appellation (cost=0.00..6.99 rows=3 width=24)
Filter: (libelle ~~ '%wur%'::text)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_appellation_libelle_trgm ON appellation
USING gist (libelle gist_trgm_ops);
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on appellation (cost=4.27..7.41 rows=3 width=24)
Recheck Cond: (libelle ~~ '%wur%'::text)
-> Bitmap Index Scan on idx_appellation_libelle_trgm (cost=0.00..4.27...)
Index Cond: (libelle ~~ '%wur%'::text)
Mais cette solution n’offre pas la même souplesse que la recherche plein texte, en anglais Full Text Search, de PostgreSQL. Elle est cependant plus complexe à mettre en œuvre et possède une syntaxe spécifique.
Ce TP utilise les tables voitures
et
voitures_ecv
.
Les deux tables voitures
et voitures_ecv
peuvent être téléchargées installées comme suit :
createdb voitures
curl -kL https://dali.bo/tp_voitures -o /tmp/voitures.dmp
pg_restore -d voitures /tmp/voitures.dmp
# un message sur le schéma public préexistant est normal
Ne pas oublier d’effectuer un VACUUM ANALYZE
.
But : Normaliser un schéma de données.
La table voitures
viole la première forme normale
(attribut répétitif, non atomique). De plus elle n’a pas de clé
primaire.
Renommer la table en
voitures_orig
. Ne pas la supprimer (nous en aurons besoin plus tard).
Écrire des requêtes permettant d’éclater cette table en trois tables :
voitures
,caracteristiques
etcaracteristiques_voitures
. (La fonctionregexp_split_to_table
permettra de séparer les champs de caractéristiques.)
Mettre en place les contraintes d’intégrité : clé primaire sur chaque table, et clés étrangères. Ne pas prévoir encore d’index supplémentaire. Attention : la table de départ contient des immatriculations en doublon !
Tenter d’insérer une Clio avec les caractéristiques « ABS » (majusucules) et « phares LED ».
Comparer les performances entre les deux modèles pour une recherche des voitures ayant un toit ouvrant.
Les plans sont-ils les mêmes si la caractéristique recherchée n’existe pas ?
Indexer la colonne de clé étrangère
caracteristiques_voitures.carateristique
et voir ce que devient le plan de la dernière requête.
Rechercher une voitures possédant les 3 options ABS, toit ouvrant et 4 roues motrices, et voir le plan.
But : Manipuler des données au format entité/clé/valeur.
Une autre version de la table voiture
existe aussi dans
cette base au format « entité/clé/valeur » c’est la table
voitures_ecv
. Sa clé primaire est entite
(immatriculation) / cle
(caractéristique). En pratique il
n’y a que des booléens.
Afficher toutes les caractéristiques d’une voiture au hasard (par exemple ZY-745-KT).
Trouver toutes les caractéristiques de toutes les voitures ayant un toit ouvrant dans
voitures_ecv
. Trier par immatriculation. Quel est le plan d’exécution ?
hstore
est une extension qui permet de stocker des
clés/valeur dans un champ. Sa documentation est sur le site du
projet.
Installer l’extension
hstore
. Convertir cette table pour qu’elle utilise une ligne par immatriculation, avec les caractéristiques dans un champhstore
. Une méthode simple est de récupérer les lignes d’une même immatriculation avec la fonctionarray_agg
puis de convertir simplement en champhstore
.
Rechercher la voiture précédente.
Insérer une voiture avec les caractéristiques
couleur=>vert
etphares=>LED
.
Définir un index de type GiST sur ce champ
hstore
. Retrouver la voiture insérée par ses caractéristiques.
But : Indexer un champ tableau pour améliorer les performances.
Il est possible, si on peut réécrire la requête, d’obtenir de bonnes
performances avec la première table voitures_orig
. En
effet, PostgreSQL sait indexer des tableaux et des fonctions. Il saurait
donc indexer un tableau résultat d’une fonction sur le champ
caracteristiques
.
Trouver cette fonction dans la documentation de PostgreSQL (chercher dans les fonctions de découpage de chaîne de caractères).
Définir un index fonctionnel sur le résultat de cette fonction, de type GIN.
Rechercher toutes les voitures avec toit ouvrant et voir le plan.
But : Effectuer une requête avec pagination
La pagination est une fonctionnalité que l’on retrouve de plus en plus souvent, surtout depuis que les applications web ont pris une place prépondérante.
Nous allons utiliser une version simplifiée d’une table de forum.
La table posts
(dump de 358 Mo, 758 Mo sur disque) peut
être téléchargée et restaurée ainsi :
curl -kL https://dali.bo/tp_posts -o /tmp/posts.dump
createdb posts
pg_restore -d posts /tmp/posts.dump
# le message sur le schéma public préexistant est normal
rm -- /tmp/posts.dump
Ne pas oublier d’effectuer ensuite un
VACUUM ANALYZE
.
Nous voulons afficher le plus rapidement possible les messages (posts) associés à un article : les 10 premiers, puis du 11 au 20, etc. Nous allons examiner les différentes stratégies possibles.
La table contient 5 000 articles de 1000 posts, d’au plus 200 signes.
La description de la table est :
# \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_ts_idx" btree (ts)
Pour la clarté des plans, désactiver le JIT et le parallélisme dans votre session :
Écrire une requête permettant de récupérer les 10 premiers posts de l’article d’
id_article
=12
, triés dans l’ordre deid_post
. Il n’y a pas d’index, la requête va être très lente.
Créer un index permettant d’améliorer cette requête.
Utiliser les clauses
LIMIT
etOFFSET
pour récupérer les 10 posts suivants. Puis du post 901 au 921. Que constate-t-on sur le plan d’exécution ?
Trouver une réécriture de la requête pour trouver directement les posts 901 à 911 une fois connu le post 900 récupéré au travers de la pagination.
But : Mise en évidence de cas piégeux dans les clauses WHERE.
Nous utilisons toujours la table posts
. Nous allons
maintenant manipuler le champ ts
, de type
timestamp
. Ce champ est indexé.
La requête
SELECT * FROM posts WHERE to_char(ts,'YYYYMM')='201302'
retourne tous les enregistrements de février 2013. Examiner son plan d’exécution. Où est le problème ?
Réécrire la clause
WHERE
avec une inégalité de dates pour utiliser l’index surts
.
Plus compliqué : retourner tous les posts ayant eu lieu un dimanche, en 2013, en passant par un index et en une seule requête. (Indice : il est possible de générer la liste de tous les dimanches de l’année 2013 avec
generate_series('2013-01-06 00:00:00','2014-01-01 00:00:00', INTERVAL '7 days')
)
On cherche un article à peu près au tiers de la liste avec la requête suivante. Pourquoi est-elle si lente ?
Renommer la table en
voitures_orig
. Ne pas la supprimer (nous en aurons besoin plus tard).
Écrire des requêtes permettant d’éclater cette table en trois tables :
voitures
,caracteristiques
etcaracteristiques_voitures
. (La fonctionregexp_split_to_table
permettra de séparer les champs de caractéristiques.)
CREATE TABLE voitures AS
SELECT DISTINCT ON (immatriculation) immatriculation, modele
FROM voitures_orig ;
ALTER TABLE voitures ADD PRIMARY KEY (immatriculation);
CREATE TABLE caracteristiques
AS SELECT *
FROM (
SELECT DISTINCT
regexp_split_to_table(caracteristiques,',') caracteristique
FROM voitures_orig)
AS tmp
WHERE caracteristique <> '' ;
ALTER TABLE caracteristiques ADD PRIMARY KEY (caracteristique);
CREATE TABLE caracteristiques_voitures
AS SELECT DISTINCT *
FROM (
SELECT
immatriculation,
regexp_split_to_table(caracteristiques,',') caracteristique
FROM voitures_orig
)
AS tmp
WHERE caracteristique <> '';
VACUUM ANALYZE ;
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | ... | Taille | ...
--------+---------------------------+-------+--------------+-----+---------+-
public | caracteristiques | table | postgres | | 48 kB |
public | caracteristiques_voitures | table | postgres | | 3208 kB |
public | voitures | table | postgres | | 4952 kB |
public | voitures_ecv | table | postgres | | 3336 kB |
public | voitures_orig | table | postgres | | 5736 kB |
Mettre en place les contraintes d’intégrité : clé primaire sur chaque table, et clés étrangères. Ne pas prévoir encore d’index supplémentaire. Attention : la table de départ contient des immatriculations en doublon !
Sur caracteristiques_voitures
, la clé primaire comprend
les deux colonnes, et donc interdit qu’une même caractéristique soit
présente deux fois sur la même voiture :
Clé étrangère de cette table vers les deux autres tables :
ALTER TABLE caracteristiques_voitures
ADD FOREIGN KEY (immatriculation)
REFERENCES voitures(immatriculation);
ALTER TABLE caracteristiques_voitures
ADD FOREIGN KEY (caracteristique)
REFERENCES caracteristiques(caracteristique);
Tenter d’insérer une Clio avec les caractéristiques « ABS » (majusucules) et « phares LED ».
En toute rigueur il faut le faire dans une transaction :
BEGIN ;
INSERT INTO voitures VALUES ('AA-007-JB','clio') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','ABS') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','phares LED') ;
COMMIT ;
Évidemment, cela échoue :
ERROR: insert or update on table "caracteristiques_voitures" violates foreign key
constraint "caracteristiques_voitures_caracteristique_fkey"
DÉTAIL : Key (caracteristique)=(ABS) is not present in table "caracteristiques".
ERROR: insert or update on table "caracteristiques_voitures" violates foreign key
constraint "caracteristiques_voitures_immatriculation_fkey"
DÉTAIL : Key (immatriculation)=(AA-007-JB) is not present in table "voitures".
En cas d’erreur, c’est exactement ce que l’on veut.
Pour que l’insertion fonctionne, il faut corriger la casse de « ABS » et déclarer la nouvelle propriété :
BEGIN ;
INSERT INTO voitures VALUES ('AA-007-JB','clio') ;
INSERT INTO caracteristiques VALUES ('phares LED') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','abs') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','phares LED') ;
COMMIT ;
Comparer les performances entre les deux modèles pour une recherche des voitures ayant un toit ouvrant.
La version la plus simple est :
Plus rigoureusement ([[:>:]]
et
[[:<:]]
indiquent des frontières de mots.), on
préférera :
EXPLAIN ANALYZE
SELECT * FROM voitures_orig
WHERE caracteristiques ~ E'[[:<:]]toit ouvrant[[:>:]]' ;
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on voitures_orig (cost=0.00..1962.00 rows=8419 width=25)
(actual time=0.030..92.226 rows=8358 loops=1)
Filter: (caracteristiques ~ '[[:<:]]toit ouvrant[[:>:]]'::text)
Rows Removed by Filter: 91642
Planning Time: 0.658 ms
Execution Time: 92.512 ms
Toute la table a été parcourue, 91 642 lignes ont été rejetées, 8358 retenues (~8 %). Les estimations statistiques sont correctes.
NB : pour la lisibilité, les plans n’utilisent pas l’option
BUFFERS
d’EXPLAIN
. Si on l’active, on pourra
vérifier que tous les accès se font bien dans le cache de PostgreSQL
(shared hits
).
Avec le nouveau schéma on peut écrire la requête simplement avec une simple jointure :
SELECT *
FROM voitures
INNER JOIN caracteristiques_voitures
ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'toit ouvrant' ;
Il n’y a pas doublement de lignes si une caractéristique est en double car la clé primaire l’interdit. Sans cette contrainte, une autre écriture serait nécessaire :
SELECT *
FROM voitures
WHERE EXISTS (
SELECT 1 FROM caracteristiques_voitures
WHERE caracteristiques_voitures.immatriculation=voitures.immatriculation
AND caracteristique = 'toit ouvrant'
) ;
Dans les deux cas, on obtient ce plan :
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=1225.80..3102.17 rows=8329 width=16)
(actual time=6.307..31.811 rows=8358 loops=1)
Hash Cond: (voitures.immatriculation = caracteristiques_voitures.immatriculation)
-> Seq Scan on voitures (cost=0.00..1613.89 rows=99989 width=16)
(actual time=0.019..10.432 rows=99989 loops=1)
-> Hash (cost=1121.69..1121.69 rows=8329 width=10)
(actual time=6.278..6.279 rows=8358 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 577kB
-> Seq Scan on caracteristiques_voitures
(cost=0.00..1121.69 rows=8329 width=10)
(actual time=0.004..5.077 rows=8358 loops=1)
Filter: (caracteristique = 'toit ouvrant'::text)
Rows Removed by Filter: 49697
Planning Time: 0.351 ms
Execution Time: 32.155 ms
Le temps d’exécution est ici plus court malgré un parcours complet de
voitures
. PostgreSQL prévoit correctement qu’il ramènera
10 % de cette table, ce qui n’est pas si discriminant et justifie
fréquemment un Seq Scan
, surtout que voitures
est petite. caracteristiques_voitures
est aussi parcourue
entièrement : faute d’index, il n’y a pas d’autre moyen.
Les plans sont-ils les mêmes si la caractéristique recherchée n’existe pas ?
Si on cherche une option rare ou n’existant pas, le plan change :
EXPLAIN ANALYZE
SELECT *
FROM voitures
INNER JOIN caracteristiques_voitures
ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'ordinateur de bord' ;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.42..1130.12 rows=1 width=16)
(actual time=4.849..4.850 rows=0 loops=1)
-> Seq Scan on caracteristiques_voitures (cost=0.00..1121.69 rows=1 width=10)
(actual time=4.848..4.848 rows=0 loops=1)
Filter: (caracteristique = 'ordinateur de bord'::text)
Rows Removed by Filter: 58055
-> Index Scan using voitures_pkey on voitures (cost=0.42..8.44 rows=1 width=16)
(never executed)
Index Cond: (immatriculation = caracteristiques_voitures.immatriculation)
Planning Time: 0.337 ms
Execution Time: 4.872 ms
Avec un seul résultat attendu, ce qui est beaucoup plus discriminant,
l’utilisation de l’index sur voitures
devient
pertinente.
Avec l’ancien schéma, on doit toujours lire la table
voitures_orig
en entier.
Indexer la colonne de clé étrangère
caracteristiques_voitures.carateristique
et voir ce que devient le plan de la dernière requête.
Le plan d’exécution
devient foudroyant, puisque la table
caracteristiques_voitures
n’est plus intégralement
lue :
EXPLAIN ANALYZE
SELECT *
FROM voitures
INNER JOIN caracteristiques_voitures
ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'ordinateur de bord' ;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.83..16.78 rows=1 width=16)
(actual time=0.010..0.011 rows=0 loops=1)
-> Index Scan using caracteristiques_voitures_caracteristique_idx
on caracteristiques_voitures
(cost=0.41..8.35 rows=1 width=10)
(actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (caracteristique = 'ordinateur de bord'::text)
-> Index Scan using voitures_pkey on voitures (cost=0.42..8.44 rows=1 width=16)
(never executed)
Index Cond: (immatriculation = caracteristiques_voitures.immatriculation)
Planning Time: 0.268 ms
Execution Time: 0.035 ms
Avec voitures_orig
, il existerait aussi des méthodes
d’indexation mais elles sont plus lourdes (index GIN…).
Rechercher une voitures possédant les 3 options ABS, toit ouvrant et 4 roues motrices, et voir le plan.
Si on recherche plusieurs options en même temps, l’optimiseur peut améliorer les choses en prenant en compte la fréquence de chaque option pour restreindre plus efficacement les recherches. Le plan devient :
EXPLAIN (ANALYZE, COSTS OFF)
SELECT *
FROM voitures
JOIN caracteristiques_voitures AS cr1 USING (immatriculation)
JOIN caracteristiques_voitures AS cr2 USING (immatriculation)
JOIN caracteristiques_voitures AS cr3 USING (immatriculation)
WHERE cr1.caracteristique = 'toit ouvrant'
AND cr2.caracteristique = 'abs'
AND cr3.caracteristique='4 roues motrices' ;
QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop
-> Hash Join
Hash Cond: (cr2.immatriculation = cr1.immatriculation)
-> Bitmap Heap Scan on caracteristiques_voitures cr2
Recheck Cond: (caracteristique = 'abs'::text)
-> Bitmap Index Scan on caracteristiques_voitures_caracteristique_idx
Index Cond: (caracteristique = 'abs'::text)
-> Hash
-> Hash Join
Hash Cond: (cr1.immatriculation = cr3.immatriculation)
-> Bitmap Heap Scan on caracteristiques_voitures cr1
Recheck Cond: (caracteristique = 'toit ouvrant'::text)
-> Bitmap Index Scan
on caracteristiques_voitures_caracteristique_idx
Index Cond: (caracteristique = 'toit ouvrant'::text)
-> Hash
-> Bitmap Heap Scan on caracteristiques_voitures cr3
Recheck Cond: (caracteristique =
'4 roues motrices'::text)
-> Bitmap Index Scan
on caracteristiques_voitures_caracteristique_idx
Index Cond: (caracteristique =
'4 roues motrices'::text)
-> Index Scan using voitures_pkey on voitures
Index Cond: (immatriculation = cr1.immatriculation)
Ce plan parcoure deux index, joins leurs résultats, fait de même avec
le résultat de l’index pour la 3è caractéristique, puis opère la
jointure finale avec la table principale par l’index sur
immatriculation
(un plan complet indiquerait une estimation
de 56 lignes de résultat, même si le résultat final est de 461
lignes).
Mais les problématiques de performances ne sont pas le plus important
dans ce cas. Ce qu’on gagne réellement, c’est la garantie que les
caractéristiques ne seront que celles existant dans la table
caractéristique
, ce qui évite d’avoir à réparer la base
plus tard.
Afficher toutes les caractéristiques d’une voiture au hasard (par exemple ZY-745-KT).
entite | cle | valeur
-----------+-----------------------+--------
ZY-745-KT | climatisation | t
ZY-745-KT | jantes aluminium | t
ZY-745-KT | regulateur de vitesse | t
ZY-745-KT | toit ouvrant | t
Trouver toutes les caractéristiques de toutes les voitures ayant un toit ouvrant dans
voitures_ecv
. Trier par immatriculation. Quel est le plan d’exécution ?
Autrement dit : on sélectionne toutes les voitures avec un toit ouvrant, et l’on veut toutes les caractéristiques de ces voitures. Cela nécessite d’appeler deux fois la table.
Là encore une jointure de la table avec elle-même sur
entite
serait possible, mais serait dangereuse dans les cas
où il y a énormément de propriétés. On préférera encore la version avec
EXISTS
, et PostgreSQL en fera spontanément une jointure :
EXPLAIN ANALYZE
SELECT * FROM voitures_ecv
WHERE EXISTS (
SELECT 1 FROM voitures_ecv test
WHERE test.entite=voitures_ecv.entite
AND cle = 'toit ouvrant' AND valeur = true
)
ORDER BY entite ;
QUERY PLAN
---------------------------------------------------------------------
Sort (cost=3468.93..3507.74 rows=15527 width=25)
(actual time=29.854..30.692 rows=17782 loops=1)
Sort Key: voitures_ecv.entite
Sort Method: quicksort Memory: 2109kB
-> Hash Join (cost=1243.09..2388.05 rows=15527 width=25)
(actual time=6.915..23.964 rows=17782 loops=1)
Hash Cond: (voitures_ecv.entite = test.entite)
-> Seq Scan on voitures_ecv (cost=0.00..992.55 rows=58055 width=25)
(actual time=0.006..4.242 rows=58055 loops=1)
-> Hash (cost=1137.69..1137.69 rows=8432 width=10)
(actual time=6.899..6.899 rows=8358 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 471kB
-> Seq Scan on voitures_ecv test
(cost=0.00..1137.69 rows=8432 width=10)
(actual time=0.005..5.615 rows=8358 loops=1)
Filter: (valeur AND (cle = 'toit ouvrant'::text))
Rows Removed by Filter: 49697
Planning Time: 0.239 ms
Execution Time: 31.321 ms
Installer l’extension
hstore
. Convertir cette table pour qu’elle utilise une ligne par immatriculation, avec les caractéristiques dans un champhstore
. Une méthode simple est de récupérer les lignes d’une même immatriculation avec la fonctionarray_agg
puis de convertir simplement en champhstore
.
hstore
est normalement présente sur toutes les
installations (ou alors l’administrateur a négligé d’installer le paquet
contrib
). Il suffit donc d’une déclaration.
CREATE EXTENSION hstore;
CREATE TABLE voitures_hstore
AS
SELECT entite AS immatriculation,
hstore(array_agg(cle),array_agg(valeur)::text[]) AS caracteristiques
FROM voitures_ecv group by entite;
ALTER TABLE voitures_hstore ADD PRIMARY KEY (immatriculation);
Rechercher la voiture précédente.
-[ RECORD 1 ]----+--------------------------------------------------------------
immatriculation | ZY-745-KT
caracteristiques | "toit ouvrant"=>"true", "climatisation"=>"true",
| "jantes aluminium"=>"true", "regulateur de vitesse"=>"true"
L’accès à une caractéristique se fait ainsi (attention aux espaces) :
SELECT immatriculation, caracteristiques -> 'climatisation'
FROM voitures_hstore
WHERE immatriculation = 'ZY-745-KT' ;
Insérer une voiture avec les caractéristiques
couleur=>vert
etphares=>LED
.
Définir un index de type GiST sur ce champ
hstore
. Retrouver la voiture insérée par ses caractéristiques.
Les index B-tree classiques sont inadaptés aux types complexes, on préfère donc un index GiST :
L’opérateur @>
signifie « contient » :
SELECT *
FROM voitures_hstore
WHERE caracteristiques @> 'couleur=>vert' AND caracteristiques @> 'phares=>LED' ;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using voitures_hstore_caracteristiques on voitures_hstore
(cost=0.28..2.30 rows=1 width=55) (actual time=0.033..0.033 rows=1 loops=1)
Index Cond: ((caracteristiques @> '"couleur"=>"vert"'::hstore)
AND (caracteristiques @> '"phares"=>"LED"'::hstore))
Buffers: shared hit=4
Planning Time: 0.055 ms
Execution Time: 0.047 ms
Trouver cette fonction dans la documentation de PostgreSQL (chercher dans les fonctions de découpage de chaîne de caractères).
La fonction est regexp_split_to_array
(sa documentation
est sur https://docs.postgresql.fr/15/functions-matching.html) :
SELECT immatriculation, modele,
regexp_split_to_array(caracteristiques,',')
FROM voitures_orig
LIMIT 10;
immatriculation | modele | regexp_split_to_array
-----------------+--------+-----------------------------------------
WW-649-AI | twingo | {"regulateur de vitesse"}
QZ-533-JD | clio | {"4 roues motrices","jantes aluminium"}
YY-854-LE | megane | {climatisation}
QD-761-QV | twingo | {""}
LV-277-QC | megane | {abs,"jantes aluminium"}
ZI-003-BQ | kangoo | {"boite automatique",climatisation}
WT-817-IK | megane | {""}
JK-791-XB | megane | {""}
WW-019-EK | megane | {""}
BZ-544-OS | twingo | {""}
La syntaxe {}
est la représentation texte d’un
tableau.
Définir un index fonctionnel sur le résultat de cette fonction, de type GIN.
CREATE INDEX idx_voitures_array ON voitures_orig
USING gin (regexp_split_to_array(caracteristiques,','));
Rechercher toutes les voitures avec toit ouvrant et voir le plan.
EXPLAIN ANALYZE
SELECT * FROM voitures_orig
WHERE regexp_split_to_array(caracteristiques,',') @> '{"toit ouvrant"}';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on voitures_orig (cost=8.87..387.37 rows=500 width=25)
(actual time=0.707..2.756 rows=8358 loops=1)
Recheck Cond: (regexp_split_to_array(caracteristiques, ','::text)
@> '{"toit ouvrant"}'::text[])
Heap Blocks: exact=712
-> Bitmap Index Scan on idx_voitures_array (cost=0.00..8.75 rows=500 width=0)
(actual time=0.631..0.631 rows=8358 loops=1)
Index Cond: (regexp_split_to_array(caracteristiques, ','::text)
@> '{"toit ouvrant"}'::text[])
Planning Time: 0.129 ms
Execution Time: 3.028 ms
Noter que les estimations de statistiques sont plus délicates sur un résultat de fonction.
Écrire une requête permettant de récupérer les 10 premiers posts de l’article d’
id_article
=12
, triés dans l’ordre deid_post
. Il n’y a pas d’index, la requête va être très lente.
Le plan est un parcours complet de la table, rejetant 4 999 000 lignes et en gardant 1000 lignes, suivi d’un tri :
QUERY PLAN
------------------------------------------------------------------------------
Limit (cost=153694.51..153694.53 rows=10 width=115)
(actual time=500.525..500.528 rows=10 loops=1)
-> Sort (cost=153694.51..153696.95 rows=979 width=115)
(actual time=500.524..500.525 rows=10 loops=1)
Sort Key: id_post
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on posts (cost=0.00..153673.35 rows=979 width=115)
(actual time=1.300..500.442 rows=1000 loops=1)
Filter: (id_article = 12)
Rows Removed by Filter: 4999000
Planning Time: 0.089 ms
Execution Time: 500.549 ms
Créer un index permettant d’améliorer cette requête.
Un index sur id_article
améliorerait déjà les choses.
Mais comme on trie sur id_post
, il est intéressant de
rajouter aussi cette colonne dans l’index :
Testons cet index :
Le plan devient :
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
C’est beaucoup plus rapide : l’index trouve tout de suite les lignes
de l’article cherché, et retourne les enregistrements directement triés
par id_post
. On évite de parcourir toute la table, et il
n’y a même pas d’étape de tri (qui serait certes très rapide sur 10
lignes).
Utiliser les clauses
LIMIT
etOFFSET
pour récupérer les 10 posts suivants. Puis du post 901 au 921. Que constate-t-on sur le plan d’exécution ?
Les posts 11 à 20 se trouvent rapidement :
QUERY PLAN
---------------------------------------------------------
Limit (cost=18.26..36.09 rows=10 width=115)
(actual time=0.020..0.023 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.017..0.021 rows=20 loops=1)
Index Cond: (id_article = 12)
Planning Time: 0.061 ms
Execution Time: 0.036 ms
Tout va bien. La requête est à peine plus coûteuse. Noter que l’index a ramené 20 lignes et non 10.
À partir du post 900 :
Le plan reste similaire :
QUERY PLAN
---------------------------------------------------------
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
Cette requête est 4 fois plus lente. Si une exécution unitaire ne pose pas encore problème, des demandes très répétées poseraient problème. Noter que l’index ramène 910 lignes ! Dans notre exemple idéalisée, les posts sont bien rangés ensemble, et souvent présents dans les mêmes blocs. C’est très différent dans une table qui beaucoup vécu.
Trouver une réécriture de la requête pour trouver directement les posts 901 à 911 une fois connu le post 900 récupéré au travers de la pagination.
Pour se mettre dans la condition du test, récupérons l’enregistrement 900 :
(La valeur retournée peut différer sur une autre base.)
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
Nous sommes de retour à des temps d’exécution très faibles. Ajouter
la condition sur le id_post
permet de limiter à la source
le nombre de lignes à récupérer. L’index n’en renvoie bien que 10.
L’avantage de cette technique par rapport à l’offset est que le temps d’une requête ne variera que l’on chercher la première ou la millième page.
L’inconvénient est qu’il faut mémoriser l’id_post
où
l’on s’est arrêté sur la page précédente.
Nous allons maintenant manipuler le champ ts
(de type
timestamp
) de la table posts
.
La requête
SELECT * FROM posts WHERE to_char(ts,'YYYYMM')='201302'
retourne tous les enregistrements de février 2013. Examiner son plan d’exécution. Où est le problème ?
Le plan est un parcours complet de la table :
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on posts (cost=0.00..187728.49 rows=50000 width=269)
(actual time=0.380..14163.371 rows=18234 loops=1)
Filter: (to_char(ts, 'YYYYMM'::text) = '201302'::text)
Rows Removed by Filter: 9981766
Total runtime: 14166.265 ms
C’est normal : PostgreSQL ne peut pas deviner que
to_char(ts,'YYYYMM')='201302'
veut dire « toutes les dates
du mois de février 2013 ». Une fonction est pour lui une boîte noire, et
il ne voit pas le lien entre le résultat attendu et les données qu’il va
lire.
Ceci est une des causes les plus habituelles de ralentissement de requêtes : une fonction est appliquée à une colonne, ce qui rend le filtre incompatible avec l’utilisation d’un index.
Réécrire la clause
WHERE
avec une inégalité de dates pour utiliser l’index surts
.
C’est à nous d’indiquer une clause WHERE
au moteur qu’il
puisse directement appliquer sur notre date :
Le plan montre que l’index est maintenant utilisable :
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using posts_ts_idx on posts (cost=0.43..998.95 rows=20165 width=115)
(actual time=0.050..5.907 rows=20160 loops=1)
Index Cond: ((ts >= '2013-02-01 00:00:00+01'::timestamp with time zone)
AND (ts < '2013-03-01 00:00:00+01'::timestamp with time zone))
Planning Time: 0.095 ms
Execution Time: 6.526 ms
Noter la conversion automatique du critère en
timestamp with time zone
.
Plus compliqué : retourner tous les posts ayant eu lieu un dimanche, en 2013, en passant par un index et en une seule requête. (Indice : il est possible de générer la liste de tous les dimanches de l’année 2013 avec
generate_series('2013-01-06 00:00:00','2014-01-01 00:00:00', INTERVAL '7 days')
)
Construisons cette requête morceau par morceau. Listons tous les dimanches de 2013 (le premier dimanche est le 6 janvier) :
S’il faut calculer le premier dimanche de l’année, cela peut se faire ainsi :
WITH premiersjours AS (
SELECT '2000-01-01'::timestamp + i * interval '1 year' AS jan1
FROM generate_series(1, 30) i
),
dimanches AS (
SELECT jan1,
jan1
+ mod(13-extract(dow FROM (jan1 - interval '1 day'))::int, 7)
+ interval '1 day'
AS dim1
FROM premiersjours
)
SELECT jan1, dim1
FROM dimanches ;
On n’a encore que des dates à minuit. Il faut calculer les heures de début et de fin de chaque dimanche :
SELECT i AS debut,
i + INTERVAL '1 day' AS fin
FROM generate_series(
'2013-01-06 00:00:00',
'2013-12-31 00:00:00',
INTERVAL '7 days'
) g(i) ;
debut | fin
------------------------+------------------------
2013-01-06 00:00:00+01 | 2013-01-07 00:00:00+01
2013-01-13 00:00:00+01 | 2013-01-14 00:00:00+01
...
2013-12-29 00:00:00+01 | 2013-12-30 00:00:00+01
Il ne nous reste plus qu’à joindre ces deux ensembles. Comme clause de jointure, on teste la présence de la date du post dans un des intervalles des dimanches :
EXPLAIN ANALYZE
WITH dimanches AS (
SELECT i AS debut,
i + INTERVAL '1 day' AS fin
FROM generate_series(
'2013-01-06 00:00:00',
'2013-12-31 00:00:00',
INTERVAL '7 days'
) g(i)
)
SELECT posts.*
FROM posts
JOIN dimanches
ON (posts.ts >= dimanches.debut AND posts.ts < dimanches.fin) ;
Le plan devient :
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop (cost=0.44..17086517.00 rows=555555556 width=115)
(actual time=0.038..12.978 rows=37440 loops=1)
-> Function Scan on generate_series g (cost=0.00..10.00 rows=1000 width=8)
(actual time=0.016..0.031 rows=52 loops=1)
-> Index Scan using posts_ts_idx on posts
(cost=0.43..11530.95 rows=555556 width=115)
(actual time=0.009..0.168 rows=720 loops=52)
Index Cond: ((ts >= g.i) AND (ts < (g.i + '1 day'::interval)))
Planning Time: 0.131 ms
Execution Time: 14.178 ms
PostgreSQL génère les 52 lignes d’intervalles (noter qu’il ne sait
pas estimer le résultat de cette fonction), puis fait 52 appels à
l’index (noter le loops=52
). C’est efficace.
Attention : des inéqui-jointures entraînent forcément des nested loops (pour chaque ligne d’une table, on va chercher les lignes d’une autre table). Sur de grands volumes, ce ne peut pas être efficace. Ici, tout va bien parce que la liste des dimanches est raisonnablement courte.
On cherche un article à peu près au tiers de la liste avec la requête suivante. Pourquoi est-elle si lente ?
Le plan est :
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on posts (cost=0.48..166135.48 rows=25000 width=115)
(actual time=333.363..1000.696 rows=1000 loops=1)
Filter: ((id_article)::numeric = $1)
Rows Removed by Filter: 4999000
InitPlan 2 (returns $1)
-> Result (cost=0.46..0.48 rows=1 width=32)
(actual time=0.016..0.017 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.43..0.46 rows=1 width=4)
(actual time=0.012..0.014 rows=1 loops=1)
-> Index Only Scan Backward using posts_id_article_id_post
on posts posts_1
(cost=0.43..142352.43 rows=5000000 width=4)
(actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (id_article IS NOT NULL)
Heap Fetches: 0
Planning Time: 0.097 ms
Execution Time: 1000.753 ms
Ce plan indique une recherche du numéro d’article maximal (il est
dans l’index ; noter que PostgreSQL restreint à une valeur non vide),
puis il calcule la valeur correspondant au tiers et la met dans
$1
. Tout ceci est rapide. La partie lente est le
Seq Scan
pour retrouver cette valeur, avec un filtre et non
par l’index.
Le problème est visible sur le filtre même :
(id_article)::numeric
signifie que tous les
id_article
(des entiers) sont convertis en
numeric
pour ensuite être comparés au $1
. Or
une conversion est une fonction, ce qui rend l’index inutilisable. En
fait, notre problème est que $1
n’est pas un entier !
La conversion du critère en int
peut se faire à
plusieurs endroits. Par exemple :
Et l’index est donc utilisable immédiatement :
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using posts_id_article_id_post on posts
(cost=0.91..1796.42 rows=1007 width=115)
(actual time=0.031..0.198 rows=1000 loops=1)
Index Cond: (id_article = ($1)::integer)
InitPlan 2 (returns $1)
-> Result (cost=0.46..0.48 rows=1 width=32) (...)
InitPlan 1 (returns $0)
-> Limit (cost=0.43..0.46 rows=1 width=4) (...)
-> Index Only Scan Backward using posts_id_article_id_post
on posts posts_1 (...)
Index Cond: (id_article IS NOT NULL)
Heap Fetches: 0
Planning Time: 0.105 ms
Execution Time: 0.245 ms
Si l’on avait fait le calcul avec / 3
au lieu de
* 0.333
, on n’aurait pas eu le problème, car la division de
deux entiers donne un entier.
Attention donc à la cohérence des types dans vos critères. Le
problème peut se rencontrer même en joignant des int
et des
bigint
!
Ce module présente la programmation PL/pgSQL. Il commence par décrire les routines stockées et les différents langages disponibles. Puis il aborde les bases du langage PL/pgSQL, autrement dit :
PL est l’acronyme de « Procedural Languages ». En dehors du C et du SQL, tous les langages acceptés par PostgreSQL sont des PL.
Par défaut, trois langages sont installés et activés : C, SQL et PL/pgSQL.
Les quatre langages PL supportés nativement (en plus du C et du SQL bien sûr) sont décrits en détail dans la documentation officielle :
D’autres langages PL sont accessibles en tant qu’extensions tierces. Les plus stables sont mentionnés dans la documentation, comme PL/Java ou PL/R. Ils réclament généralement d’installer les bibliothèques du langage sur le serveur.
Une liste plus large est par ailleurs disponible sur le wiki PostgreSQL, Il en ressort qu’au moins 16 langages sont disponibles, dont 10 installables en production. De plus, il est possible d’en ajouter d’autres, comme décrit dans la documentation.
Les langages de confiance ne peuvent accéder qu’à la base de données. Ils ne peuvent pas accéder aux autres bases, aux systèmes de fichiers, au réseau, etc. Ils sont donc confinés, ce qui les rend moins facilement utilisables pour compromettre le système. PL/pgSQL est l’exemple typique. Mais de ce fait, ils offrent moins de possibilités que les autres langages.
Seuls les superutilisateurs peuvent créer une routine dans un langage untrusted. Par contre, ils peuvent ensuite donner les droits d’exécution à ces routines aux autres rôles dans la base :
La question se pose souvent de placer la logique applicative du côté de la base, dans un langage PL, ou des clients. Il peut y avoir de nombreuses raisons en faveur de la première option. Simplifier et centraliser des traitements clients directement dans la base est l’argument le plus fréquent. Par exemple, une insertion complexe dans plusieurs tables, avec mise en place d’identifiants pour liens entre ces tables, peut évidemment être écrite côté client. Il est quelquefois plus pratique de l’écrire sous forme de PL. Les avantages sont :
Centralisation du code :
Si plusieurs applications ont potentiellement besoin d’opérer un même traitement, à fortiori dans des langages différents, porter cette logique dans la base réduit d’autant les risques de bugs et facilite la maintenance.
Une règle peut être que tout ce qui a trait à l’intégrité des données devrait être exécuté au niveau de la base.
Performances :
Le code s’exécute localement, directement dans le moteur de la base. Il n’y a donc pas tous les changements de contexte et échanges de messages réseaux dus à l’exécution de nombreux ordres SQL consécutifs. L’impact de la latence due au trafic réseau de la base au client est souvent sous-estimée.
Les langages PL permettent aussi d’accéder à leurs bibliothèques spécifiques (extrêmement nombreuses en python ou perl, entre autres).
Une fonction en PL peut également servir à l’indexation des données. Cela est impossible si elle se calcule sur une autre machine.
Simplicité :
Suivant le besoin, un langage PL peut être bien plus pratique que le langage client.
Il est par exemple très simple d’écrire un traitement d’insertion/mise à jour en PL/pgSQL, le langage étant créé pour simplifier ce genre de traitements, et la gestion des exceptions pouvant s’y produire. Si vous avez besoin de réaliser du traitement de chaîne puissant, ou de la manipulation de fichiers, PL/Perl ou PL/Python seront probablement des options plus intéressantes car plus performantes, là aussi utilisables dans la base.
La grande variété des différents langages PL supportés par PostgreSQL permet normalement d’en trouver un correspondant aux besoins et aux langages déjà maîtrisés dans l’entreprise.
Les langages PL permettent donc de rajouter une couche d’abstraction et d’effectuer des traitements avancés directement en base.
Le langage étant assez ancien, proche du Pascal et de l’ADA, sa syntaxe ne choquera personne. Elle est d’ailleurs très proche de celle du PLSQL d’Oracle.
Le PL/pgSQL permet d’écrire des requêtes directement dans le code PL sans déclaration préalable, sans appel à des méthodes complexes, ni rien de cette sorte. Le code SQL est mélangé naturellement au code PL, et on a donc un sur-ensemble procédural de SQL.
PL/pgSQL étant intégré à PostgreSQL, il hérite de tous les types déclarés dans le moteur, même ceux rajoutés par l’utilisateur. Il peut les manipuler de façon transparente.
PL/pgSQL est trusted. Tous les utilisateurs peuvent donc
créer des routines dans ce langage (par défaut). Vous pouvez toujours
soit supprimer le langage, soit retirer les droits à un utilisateur sur
ce langage (via la commande SQL REVOKE
).
PL/pgSQL est donc raisonnablement facile à utiliser : il y a peu de complications, peu de pièges, et il dispose d’une gestion des erreurs évoluée (gestion d’exceptions).
Les langages PL « autres », comme PL/perl et PL/Python (les deux plus utilisés après PL/pgSQL), sont bien plus évolués que PL/PgSQL. Par exemple, ils sont bien plus efficaces en matière de traitement de chaînes de caractères, possèdent des structures avancées comme des tables de hachage, permettent l’utilisation de variables statiques pour maintenir des caches, voire, pour leur version untrusted, peuvent effectuer des appels systèmes. Dans ce cas, il devient possible d’appeler un service web par exemple, ou d’écrire des données dans un fichier externe.
Il existe des langages PL spécialisés. Le plus emblématique d’entre eux est PL/R. R est un langage utilisé par les statisticiens pour manipuler de gros jeux de données. PL/R permet donc d’effectuer ces traitements R directement en base, traitements qui seraient très pénibles à écrire dans d’autres langages, et avec une latence dans le transfert des données.
Il existe aussi un langage qui est, du moins sur le papier, plus rapide que tous les langages cités précédemment : vous pouvez écrire des procédures stockées en C, directement. Elles seront compilées à l’extérieur de PostgreSQL, en respectant un certain formalisme, puis seront chargées en indiquant la bibliothèque C qui les contient et leurs paramètres et types de retour.Mais attention : toute erreur dans le code C est susceptible d’accéder à toute la mémoire visible par le processus PostgreSQL qui l’exécute, et donc de corrompre les données. Il est donc conseillé de ne faire ceci qu’en dernière extrémité.
Le gros défaut est simple et commun à tous ces langages : ils ne sont
pas spécialement conçus pour s’exécuter en tant que langage de
procédures stockées. Ce que vous utilisez quand vous écrivez du PL/Perl
est donc du code Perl, avec quelques fonctions supplémentaires
(préfixées par spi
) pour accéder à la base de données ; de
même en C. L’accès aux données est assez fastidieux au niveau
syntaxique, comparé à PL/pgSQL.
Un autre problème des langages PL (autre que C et PL/pgSQL), est que ces langages n’ont pas les mêmes types natifs que PostgreSQL, et s’exécutent dans un interpréteur relativement séparé. Les performances sont donc moindres que PL/pgSQL et C, pour les traitements dont le plus consommateur est l’accès aux données. Souvent, le temps de traitement dans un de ces langages plus évolués est tout de même meilleur grâce au temps gagné par les autres fonctionnalités (la possibilité d’utiliser un cache, ou une table de hachage par exemple).
COMMIT
/
ROLLBACK
SELECT
TRIGGER
, agrégat, fenêtrageLes programmes écrits à l’aide des langages PL sont habituellement enregistrés sous forme de « routines » :
Le code source de ces objets est stocké dans la table
pg_proc
du catalogue.
Les procédures, apparues avec PostgreSQL 11, sont très similaires aux fonctions. Les principales différences entre les deux sont :
RETURNS
ou arguments OUT
). Elles peuvent
renvoyer n’importe quel type de donnée, ou des ensembles de lignes. Il
est possible d’utiliser void
pour une fonction sans
argument de sortie ; c’était d’ailleurs la méthode utilisée pour émuler
le comportement d’une procédure avant leur introduction avec PostgreSQL
11. Les procédures n’ont pas de code retour (on peut cependant utiliser
des paramètres OUT
ou INOUT
).COMMIT
) ou annuler
(ROLLBACK
) les modifications effectuées jusqu’à ce point
par la procédure. L’intégralité d’une fonction s’effectue dans la
transaction appelante.CALL
; les fonctions peuvent être appelées dans la plupart
des ordres DML/DQL (notamment SELECT
), mais pas par
CALL
.Pour savoir si PL/Perl ou PL/Python a été compilé, on peut demander à
pg_config
:
pg_config --configure
'--prefix=/usr/local/pgsql-10_icu' '--enable-thread-safety'
'--with-openssl' '--with-libxml' '--enable-nls' '--with-perl' '--enable-debug'
'ICU_CFLAGS=-I/usr/local/include/unicode/'
'ICU_LIBS=-L/usr/local/lib -licui18n -licuuc -licudata' '--with-icu'
Si besoin, les emplacements exacts d’installation des bibliothèques
peuvent être récupérés à l’aide des options --libdir
et
--pkglibdir
de pg_config
.
Cependant, dans les paquets fournis par le PGDG, il faudra installer
explicitement le paquet dédié à plperl
pour la version
majeure de PostgreSQL concernée. Pour PostgreSQL 16, les paquets sont
postgresql16-plperl
(depuis yum.postgresql.org) ou
postgresql-plperl-16
(depuis apt.postgresql.org). De même
pour Python 3 (paquets postgresql14-plpython3
ou
postgresql-plython3-14
).
Les bibliothèques plperl.so
, plpython3.so
ou plpgsql.so
contiennent les fonctions qui permettent
l’utilisation de chaque langage. La bibliothèque nécessaire est chargée
par le moteur à la première utilisation d’une procédure utilisant ce
langage.
La plupart des langages intéressants sont disponibles sous forme de paquets. Des versions très récentes, ou des langages plus exotiques, peuvent nécessiter une compilation de l’extension.
Activer un langage passe par la création de l’extension :
CREATE EXTENSION plperl ; -- pour tous
-- versions untrusted
CREATE EXTENSION plperlu ; -- pour le superutilisateur
CREATE EXTENSION plpython3u ;
\dL
ou pg_language
Le langage est activé uniquement dans la base dans laquelle la
commande est lancée. Il faudra donc répéter le
CREATE EXTENSION
dans chaque base au besoin (noter
qu’activer un langage dans la base modèle template1
l’activera aussi pour toutes les bases créées par la suite, comme c’est
déjà le cas pour le PL/pgSQL).
Pour voir les langages activés, utiliser la commande \dL
qui reprend le contenu de la table système
pg_language
:
CREATE EXTENSION plperl ;
CREATE EXTENSION plpython3u ;
CREATE EXTENSION plsh ;
CREATE EXTENSION plr;
postgres=# \dL
Liste des langages
Nom | … | De confiance | Description
------------+---+--------------+-------------------------------------------
plperl | … | t | PL/PerlU untrusted procedural language
plpgsql | … | t | PL/pgSQL procedural language
plpython3u | … | f | PL/Python3U untrusted procedural language
plr | … | f |
plsh | … | f | PL/sh procedural language
Noter la distinction entre les langages trusted (de confiance) et untrusted. Si un langage est trusted, tous les utilisateurs peuvent créer des procédures dans ce langage sans danger. Sinon seuls les superutilisateurs le peuvent.
Il existe par exemple deux variantes de PL/Perl : PL/Perl et PL/PerlU. La seconde est la variante untrusted et est un Perl « complet ». La version trusted n’a pas le droit d’ouvrir des fichiers, des sockets, ou autres appels systèmes qui seraient dangereux.
SQL, PL/pgSQL, PL/Tcl, PL/Perl (mais pas PL/Python) sont trusted et les utilisateurs peuvent les utiliser à volonté.
C, PL/TclU, PL/PerlU, et PL/Python3U sont untrusted. Un
superutilisateur doit alors écrire les fonctions et procédures et opérer
des GRANT EXECUTE
aux utilisateurs.
Une fonction simple en PL/pgSQL :
Même fonction en SQL pur :
CREATE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS ' SELECT entier1 + entier2 ; ' ;
Les fonctions simples peuvent être écrites en SQL pur. La syntaxe est plus claire, mais bien plus limitée qu’en PL/pgSQL (ni boucles, ni conditions, ni exceptions notamment).
À partir de PostgreSQL 14, il est possible de se passer des guillemets encadrants, pour les fonctions SQL uniquement. La même fonction devient donc :
CREATE OR REPLACE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
RETURN entier1 + entier2 ;
Cette nouvelle écriture respecte mieux le standard SQL. Surtout, elle autorise un parsing et une vérification des objets impliqués dès la déclaration, et non à l’utilisation. Les dépendances entre fonctions et objets utilisés sont aussi mieux tracées.
L’avantage principal des fonctions en pur SQL est, si elles sont assez simples, leur intégration lors de la réécriture interne de la requête (inlining) : elles ne sont donc pas pour l’optimiseur des « boîtes noires ». À l’inverse, l’optimiseur ne sait rien du contenu d’une fonction PL/pgSQL.
Dans l’exemple suivant, la fonction sert de filtre à la requête.
Comme elle est en pur SQL, elle permet d’utiliser l’index sur la colonne
date_embauche
de la table employes_big
:
CREATE FUNCTION employe_eligible_prime_sql (service int, date_embauche date)
RETURNS boolean
LANGUAGE sql
AS $$
SELECT ( service !=3 AND date_embauche < '2003-01-01') ;
$$ ;
EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
FROM employes_big
WHERE employe_eligible_prime_sql (num_service, date_embauche) ;
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using employes_big_date_embauche_idx on employes_big
(cost=0.42..1.54 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (date_embauche < '2003-01-01'::date)
Filter: (num_service <> 3)
Rows Removed by Filter: 1
Planning Time: 0.102 ms
Execution Time: 0.029 ms
Avec une version de la même fonction en PL/pgSQL, le planificateur ne voit pas le critère indexé. Il n’a pas d’autre choix que de lire toute la table et d’appeler la fonction pour chaque ligne, ce qui est bien sûr plus lent :
CREATE FUNCTION employe_eligible_prime_pl (service int, date_embauche date)
RETURNS boolean
LANGUAGE plpgsql AS $$
BEGIN
RETURN ( service !=3 AND date_embauche < '2003-01-01') ;
END ;
$$ ;
EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
FROM employes_big
WHERE employe_eligible_prime_pl (num_service, date_embauche) ;
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on employes_big (cost=0.00..134407.90 rows=166338 width=22)
(actual time=0.069..269.121 rows=1 loops=1)
Filter: employe_eligible_prime_pl(num_service, date_embauche)
Rows Removed by Filter: 499014
Planning Time: 0.038 ms
Execution Time: 269.157 ms
Le wiki
décrit les conditions pour que l’inlining des fonctions SQL
fonctionne : obligation d’un seul SELECT
, interdiction de
certains fonctionnalités…
Dans cet exemple, on récupère l’estimation du nombre de lignes actives d’une table passée en paramètres.
L’intérêt majeur du PL/pgSQL et du SQL sur les autres langages est la
facilité d’accès aux données. Ici, un simple
SELECT <champ> INTO <variable>
suffit à
récupérer une valeur depuis une table dans une variable.
spi_exec
Voici l’exemple de la fonction :
CREATE OR REPLACE FUNCTION
public.demo_insert_perl(nom_client text, titre_facture text)
RETURNS integer
LANGUAGE plperl
STRICT
AS $function$
use strict;
my ($nom_client, $titre_facture)=@_;
my $rv;
my $id_facture;
my $id_client;
# Le client existe t'il ?
$rv = spi_exec_query('SELECT id_client FROM mes_clients WHERE nom_client = '
. quote_literal($nom_client)
);
# Sinon on le crée :
if ($rv->{processed} == 0)
{
$rv = spi_exec_query('INSERT INTO mes_clients (nom_client) VALUES ('
. quote_literal($nom_client) . ') RETURNING id_client'
);
}
# Dans les deux cas, l'id client est dans $rv :
$id_client=$rv->{rows}[0]->{id_client};
# Insérons maintenant la facture
$rv = spi_exec_query(
'INSERT INTO mes_factures (titre_facture, id_client) VALUES ('
. quote_literal($titre_facture) . ", $id_client ) RETURNING id_facture"
);
$id_facture = $rv->{rows}[0]->{id_facture};
return $id_facture;
$function$ ;
Cette fonction n’est pas parfaite, elle ne protège pas de tout. Il
est tout à fait possible d’avoir une insertion concurrente entre le
SELECT
et le INSERT
par exemple.
Il est clair que l’accès aux données est malaisé en PL/Perl, comme dans la plupart des langages, puisqu’ils ne sont pas prévus spécifiquement pour cette tâche. Par contre, on dispose de toute la puissance de Perl pour les traitements de chaîne, les appels système…
PL/Perl, c’est :
spi_*
Pour éviter les conflits avec les objets de la base, il est conseillé de préfixer les variables.
CREATE OR REPLACE FUNCTION
public.demo_insert_plpgsql(p_nom_client text, p_titre_facture text)
RETURNS integer
LANGUAGE plpgsql
STRICT
AS $function$
DECLARE
v_id_facture int;
v_id_client int;
BEGIN
-- Le client existe t'il ?
SELECT id_client
INTO v_id_client
FROM mes_clients
WHERE nom_client = p_nom_client;
-- Sinon on le crée :
IF NOT FOUND THEN
INSERT INTO mes_clients (nom_client)
VALUES (p_nom_client)
RETURNING id_client INTO v_id_client;
END IF;
-- Dans les deux cas, l'id client est maintenant dans v_id_client
-- Insérons maintenant la facture
INSERT INTO mes_factures (titre_facture, id_client)
VALUES (p_titre_facture, v_id_client)
RETURNING id_facture INTO v_id_facture;
return v_id_facture;
END;
$function$ ;
Cette procédure tronque des tables de la base d’exemple
pgbench, et annule si dry_run
est
vrai.
Les procédures sont récentes dans PostgreSQL (à partir de la version
11). Elles sont à utiliser quand on n’attend pas de résultat en retour.
Surtout, elles permettent de gérer les transactions
(COMMIT
, ROLLBACK
), ce qui ne peut se faire
dans des fonctions, même si celles-ci peuvent modifier les données.
Une procédure ne peut utiliser le contrôle transactionnel que si elle est appelée en dehors de toute transaction.
Comme pour les fonctions, il est possible d’utiliser le SQL pur dans les cas les plus simples, sans contrôle transactionnel notamment :
CREATE OR REPLACE PROCEDURE vide_tables ()
AS '
TRUNCATE TABLE pgbench_history ;
TRUNCATE TABLE pgbench_accounts CASCADE ;
TRUNCATE TABLE pgbench_tellers CASCADE ;
TRUNCATE TABLE pgbench_branches CASCADE ;
' LANGUAGE sql;
Toujours pour les procédures en SQL, il existe une variante sans guillemets, à partir de PostgreSQL 14, mais qui ne supporte pas tous les ordres. Comme pour les fonctions, l’intérêt est la prise en compte des dépendances entre objets et procédures.
DO $$
DECLARE r record;
BEGIN
FOR r IN (SELECT schemaname, relname
FROM pg_stat_user_tables
WHERE coalesce(last_analyze, last_autoanalyze) IS NULL
) LOOP
RAISE NOTICE 'Analyze %.%', r.schemaname, r.relname ;
EXECUTE 'ANALYZE ' || quote_ident(r.schemaname)
|| '.' || quote_ident(r.relname) ;
END LOOP;
END$$;
Les blocs anonymes sont utiles pour des petits scripts ponctuels qui nécessitent des boucles ou du conditionnel, voire du transactionnel, sans avoir à créer une fonction ou une procédure. Ils ne renvoient rien. Ils sont habituellement en PL/pgSQL mais tout langage procédural installé est possible.
L’exemple ci-dessus lance un ANALYZE
sur toutes les
tables où les statistiques n’ont pas été calculées d’après la vue
système, et donne aussi un exemple de SQL dynamique. Le résultat est par
exemple :
NOTICE: Analyze public.pgbench_history
NOTICE: Analyze public.pgbench_tellers
NOTICE: Analyze public.pgbench_accounts
NOTICE: Analyze public.pgbench_branches
DO
Temps : 141,208 ms
(Pour ce genre de SQL dynamique, si l’on est sous psql
,
il est souvent plus pratique d’utiliser \gexec
.)
Noter que les ordres constituent une transaction unique, à moins de
rajouter des COMMIT
ou ROLLBACK
explicitement
(ce n’est autorisé qu’à partir de la version 11).
CALL
Demander l’exécution d’une procédure se fait en utilisant un ordre
SQL spécifique : CALL
. Il suffit
de fournir les paramètres. Il n’y a pas de code retour.
Les fonctions ne sont quant à elles pas directement compatibles avec
la commande CALL
, il faut les invoquer dans le contexte
d’une commande SQL. Elles sont le plus couramment appelées depuis des
commandes de type DML (SELECT
, INSERT
, etc.),
mais on peut aussi les trouver dans d’autres commandes.
Voici quelques exemples :
SELECT
(la fonction ne doit renvoyer qu’une
seule ligne) :SELECT
, en passant en argument les valeurs
d’une colonne d’une table :FROM
d’un SELECT
, la fonction
renvoit ici généralement plusieurs lignes (SETOF
), et un
résultat de type RECORD
:INSERT
pour générer la valeur à insérer :ma_fonction()
(qui doit renvoyer une seule ligne) est passé
en argument d’entrée de la procédure ma_procedure()
:Par ailleurs, certaines fonctions sont spécialisées et ne peuvent être invoquées que dans le contexte pour lequel elles ont été conçues (fonctions trigger, d’agrégat, de fenêtrage, etc.).
COMMIT
et ROLLBACK
: possibles dans les
procéduresBEGIN
EXCEPTION
Une procédure peut contenir des ordres COMMIT
ou
ROLLBACK
pour du contrôle transactionnel. (À l’inverse une
fonction est une transaction unique, ou opère dans une transaction.)
Voici un exemple validant ou annulant une insertion suivant que le nombre est pair ou impair :
CREATE TABLE test1 (a int) ;
CREATE OR REPLACE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..5 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
Une exemple plus fréquemment utilisé est celui d’une procédure
effectuant un traitement de modification des données par lots, et donc
faisant un COMMIT
à intervalle régulier.
Noter qu’il n’y a pas de BEGIN
explicite dans la gestion
des transactions. Après un COMMIT
ou un
ROLLBACK
, un BEGIN
est immédiatement
exécuté.
On ne peut pas imbriquer des transactions, car PostgreSQL ne connaît pas les sous-transactions :
BEGIN ; CALL transaction_test1() ;
ERROR: invalid transaction termination
CONTEXTE : PL/pgSQL function transaction_test1() line 6 at COMMIT
On ne peut pas utiliser en même temps une clause
EXCEPTION
et le contrôle transactionnel :
DO LANGUAGE plpgsql $$
BEGIN
BEGIN
INSERT INTO test1 (a) VALUES (1);
COMMIT;
INSERT INTO test1 (a) VALUES (1/0);
COMMIT;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
END;
END;
$$;
ERREUR: cannot commit while a subtransaction is active
CONTEXTE : fonction PL/pgSQL inline_code_block, ligne 5 à COMMIT
CREATE FUNCTION
CREATE PROCEDURE
Voici la syntaxe complète pour une fonction d’après la documentation :
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, …] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, …] ) ]
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, … ]
| WINDOW
| { IMMUTABLE | STABLE | VOLATILE }
| [ NOT ] LEAKPROOF
| { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
| { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
| SUPPORT support_function
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
| sql_body
} …
Voici la syntaxe complète pour une procédure d’après la documentation :
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, …] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, … ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
| sql_body
} …
Noter qu’il n’y a pas de langage par défaut. Il est donc nécessaire de le spécifier à chaque création d’une routine comme dans les exemples ci-dessous.
Le langage PL/pgSQL n’est pas sensible à la casse, tout comme SQL
(sauf les noms des objets ou variables, si vous les mettez entre des
guillemets doubles). L’opérateur de comparaison est =
,
l’opérateur d’affectation :=
DECLARE
BEGIN
END
--
ou compris entre
/*
et */
Une routine est composée d’un bloc de déclaration des variables
locales et d’un bloc de code. Le bloc de déclaration commence par le mot
clé DECLARE
et se termine avec le mot clé
BEGIN
. Ce mot clé est celui qui débute le bloc de code. La
fin est indiquée par le mot clé END
.
Toutes les instructions se terminent avec des points-virgules.
Attention, DECLARE
, BEGIN
et END
ne sont pas des instructions.
Il est possible d’ajouter des commentaires. --
indique
le début d’un commentaire qui se terminera en fin de ligne. Pour être
plus précis dans la délimitation, il est aussi possible d’utiliser la
notation C : /*
est le début d’un commentaire et
*/
la fin.
Indiquer le nom d’un label ainsi :
ou bien (pour une boucle)
Bien sûr, il est aussi possible d’utiliser des labels pour des
boucles FOR
, WHILE
, FOREACH
.
On sort d’un bloc ou d’une boucle avec la commande EXIT
,
on peut aussi utiliser CONTINUE
pour passer à l’exécution
suivante d’une boucle sans terminer l’itération courante.
Par exemple :
CREATE OR REPLACE FUNCTION
CREATE OR REPLACE PROCEDURE
Une routine est surchargeable. La seule façon de les différencier est de prendre en compte les arguments (nombre et type). Les noms des arguments peuvent être indiqués mais ils seront ignorés.
Deux routines identiques aux arguments près (on parle de prototype) ne sont pas identiques, mais bien deux routines distinctes.
CREATE OR REPLACE
a principalement pour but de modifier
le code d’une routine, mais il est aussi possible de modifier les
méta-données.
ALTER FUNCTION
/ ALTER PROCEDURE
Toutes les méta-données discutées plus haut sont modifiables avec un
ALTER
.
La suppression se fait avec l’ordre DROP
.
Une fonction pouvant exister en plusieurs exemplaires, avec le même nom et des arguments de type différents, il faudra parfois parfois préciser ces derniers.
$$
$fonction$
, $toto$
…Définir une fonction entre guillemets simples ('
)
devient très pénible dès que la fonction doit en contenir parce qu’elle
contient elle-même des chaînes de caractères. PostgreSQL permet de
remplacer les guillemets par $$
, ou tout mot encadré de
$
.
Par exemple, on peut reprendre la syntaxe de déclaration de la
fonction addition()
précédente en utilisant cette
méthode :
CREATE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $ma_fonction_addition$
DECLARE
resultat integer;
BEGIN
resultat := entier1 + entier2;
RETURN resultat;
END
$ma_fonction_addition$;
Ce peut être utile aussi dans tout code réalisant une concaténation de chaînes de caractères contenant des guillemets. La syntaxe traditionnelle impose de les multiplier pour les protéger, et le code devient difficile à lire. :
En voilà une simplification grâce aux dollars :
Si vous avez besoin de mettre entre guillemets du texte qui inclut
$$
, vous pouvez utiliser $Q$
, et ainsi de
suite. Le plus simple étant de définir un marqueur de fin de routine
plus complexe, par exemple incluant le nom de la fonction.
Ceci une forme de fonction très simple (et très courante) : deux paramètres en entrée (implicitement en entrée seulement), et une valeur en retour.
Dans le corps de la fonction, il est aussi possible d’utiliser une
notation numérotée au lieu des noms de paramètre : le premier argument a
pour nom $1
, le deuxième $2
, etc. C’est à
éviter.
Tous les types sont utilisables, y compris les types définis par l’utilisateur. En dehors des types natifs de PostgreSQL, PL/pgSQL ajoute des types de paramètres spécifiques pour faciliter l’écriture des routines.
CREATE FUNCTION cree_utilisateur (
nom text, -- IN
type_id int DEFAULT 0 -- IN
) RETURNS id_utilisateur int AS …
VARIADIC
: nombre variableSi le mode d’un argument est omis, IN
est la valeur
implicite : la valeur en entrée ne sera pas modifiée par la
fonction.
Un paramètre OUT
sera modifié. S’il s’agit d’une
variable d’un bloc PL appelant, sa valeur sera modifiée. Un paramètre
INOUT
est un paramètre en entrée qui peut être également
modifié. (Jusque PostgreSQL 13 inclus, les procédures ne supportent pas
les arguments OUT
, seulement IN
et
INOUT
.)
Dans le corps d’une fonction, RETURN
est inutile avec
des paramètres OUT
parce que c’est la valeur des paramètres
OUT
à la fin de la fonction qui est retournée, comme dans
l’exemple plus bas.
L’option VARIADIC
permet de définir une fonction avec un
nombre d’arguments libres à condition de respecter le type de l’argument
(comme printf
en C par exemple). Seul un argument
OUT
peut suivre un argument VARIADIC
:
l’argument VARIADIC
doit être le dernier de la liste des
paramètres en entrée puisque tous les paramètres en entrée suivant
seront considérées comme faisant partie du tableau variadic. Seuls les
arguments IN
et VARIADIC
sont utilisables avec
une fonction déclarée comme renvoyant une table (clause
RETURNS TABLE
, voir plus loin).
La clause DEFAULT
permet de rendre les paramètres
optionnels. Après le premier paramètre ayant une valeur par défaut, tous
les paramètres qui suivent doivent aussi avoir une valeur par défaut.
Pour rendre le paramètre optionnel, il doit être le dernier argument ou
alors les paramètres suivants doivent aussi avoir une valeur par
défaut.
void
Le type de retour (clause RETURNS
dans l’entête) est
obligatoire pour les fonctions et interdit pour les procédures.
Avant la version 11, il n’était pas possible de créer une procédure,
mais il était possible de créer une fonction se comportant globalement
comme une procédure en utilisant le type de retour
void
.
Des exemples plus haut utilisent des types simples, mais tous ceux de PostgreSQL ou les types créés par l’utilisateur sont utilisables.
Depuis le corps de la fonction, le résultat est renvoyé par un appel
à RETURN
(PL/pgSQL) ou SELECT
(SQL).
Comment obtenir ceci ?
3 options :
OUT
RETURNS TABLE
S’il y a besoin de renvoyer plusieurs valeurs à la fois, une première possibilité est de renvoyer un type composé défini auparavant.
Une alternative très courante est d’utiliser plusieurs paramètres
OUT
(et pas de clause RETURN
dans l’entête)
pour obtenir un enregistrement composite :
CREATE OR REPLACE FUNCTION explose_date
(IN d date, OUT jour int, OUT mois int, OUT annee int)
AS $$
SELECT extract (day FROM d)::int,
extract(month FROM d)::int, extract (year FROM d)::int
$$
LANGUAGE sql;
(Noter que l’exemple ci-dessus est en simple SQL.)
La clause TABLE
est une autre alternative, sans doute
plus claire. Cet exemple devient alors, toujours en pur SQL :
RETURNS SETOF :
Pour renvoyer plusieurs lignes, la première possibilité est de
déclarer un type de retour SETOF
. Cet exemple utilise
RETURN NEXT
pour renvoyer les lignes une à une :
CREATE OR REPLACE FUNCTION liste_entiers_setof (limite int)
RETURNS SETOF integer
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 1..limite LOOP
RETURN NEXT i;
END LOOP;
END
$$ ;
Renvoyer une structure existante :
S’il y a plusieurs champs à renvoyer, une possibilité est d’utiliser
un type dédié (composé), qu’il faudra cependant créer auparavant.
L’exemple suivant utilise aussi un RETURN QUERY
pour éviter
d’itérer sur toutes les lignes du résultat :
CREATE TYPE pgt AS (schemaname text, tablename text) ;
CREATE OR REPLACE FUNCTION tables_by_owner (p_owner text)
RETURNS SETOF pgt
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY SELECT schemaname::text, tablename::text
FROM pg_tables WHERE tableowner=p_owner
ORDER BY tablename ;
END $$ ;
schemaname | tablename
------------+------------------
public | pgbench_accounts
public | pgbench_branches
public | pgbench_history
public | pgbench_tellers
Si l’on veut renvoyer une structure correspondant exactement à une
table ou vue, la syntaxe est très simple (il n’y a même pas besoin de
%ROWTYPE
) :
CREATE OR REPLACE FUNCTION tables_jamais_analyzees ()
RETURNS SETOF pg_stat_user_tables
LANGUAGE sql
AS $$
SELECT * FROM pg_stat_user_tables
WHERE coalesce(last_analyze, last_autoanalyze) IS NULL ;
$$ ;
-[ RECORD 1 ]-------+------------------------------
relid | 414453
schemaname | public
relname | table_nouvelle
…
n_mod_since_analyze | 10
n_ins_since_vacuum | 10
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
-[ RECORD 2 ]-------+------------------------------
…
NB : attention de ne pas oublier le SETOF
, sinon une
seule ligne sera retournée.
RETURNS TABLE :
On a vu que la clause TABLE
permet de renvoyer plusieurs
champs. Or, elle implique aussi SETOF
, et les deux exemples
ci-dessus peuvent devenir :
CREATE OR REPLACE FUNCTION liste_entiers_table (limite int)
RETURNS TABLE (j int)
AS $$
BEGIN
FOR i IN 1..limite LOOP
j = i ;
RETURN NEXT ; -- renvoie la valeur de j en cours
END LOOP;
END $$ LANGUAGE plpgsql;
(Noter ici que le nom du champ retourné dépend du nom de la variable
utilisée, et n’est pas forcément le nom de la fonction. En effet, chaque
appel à RETURN NEXT
retourne un enregistrement composé
d’une copie de toutes les variables, au moment de l’appel à
RETURN NEXT
.)
DROP FUNCTION tables_by_owner ;
CREATE FUNCTION tables_by_owner (p_owner text)
RETURNS TABLE (schemaname text, tablename text)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY SELECT t.schemaname::text, t.tablename::text
FROM pg_tables t WHERE tableowner=p_owner
ORDER BY t.tablename ;
END $$ ;
Si RETURNS TABLE
est peut-être le plus souple et le plus
clair, le choix entre toutes ces méthodes est affaire de goût, ou de
compatibilité avec du code ancien ou converti d’un produit
concurrent.
Renvoyer le résultat d’une requête :
Les exemples ci-dessus utilisent RETURN NEXT
(pour du
ligne à ligne) ou RETURN QUERY
(pour envoyer directement le
résultat d’une requête).
La variante RETURN QUERY EXECUTE …
est destinée à des
requêtes en SQL dynamique (voir plus loin).
Quand plusieurs lignes sont renvoyées, tout est conservé en mémoire
jusqu’à la fin de la fonction. S’il y en a beaucoup, cela peut poser des
problèmes de latence, voire de mémoire. Le paramètre
work_mem
permet de définir la mémoire utilisée avant de
basculer sur un fichier temporaire, qui a bien sûr un impact sur les
performances.
Appel de fonction :
En général, l’appel se fait ainsi pour obtenir des lignes :
Une alternative est d’utiliser :
pour récupérer un résultat d’une seule colonne, scalaire, type
composite ou RECORD
suivant la fonction.
Cette différence concerne aussi les fonctions système :
Comment gérer les paramètres à NULL
?
STRICT
:
NULL
: retourne NULL
immédiatementSi une fonction est définie comme STRICT
et qu’un des
arguments d’entrée est NULL
, PostgreSQL n’exécute même pas
la fonction et utilise NULL
comme résultat.
Dans la logique relationnelle, NULL
signifie « la valeur
est inconnue ». La plupart du temps, il est logique qu’une fonction
ayant un paramètre à une valeur inconnue retourne aussi une valeur
inconnue, ce qui fait que cette optimisation est très souvent
pertinente.
On gagne à la fois en temps d’exécution, mais aussi en simplicité du
code (il n’y a pas à gérer les cas NULL
pour une fonction
dans laquelle NULL
ne doit jamais être injecté).
Dans la définition d’une fonction, les options sont
STRICT
ou son synonyme
RETURNS NULL ON NULL INPUT
, ou le défaut implicite
CALLED ON NULL INPUT
.
DECLARE
:DECLARE
/BEGIN
/END
imbriqués possible
En PL/pgSQL, pour utiliser une variable dans le corps de la routine
(entre le BEGIN
et le END
), il est obligatoire
de l’avoir déclarée précédemment :
IN
,
INOUT
ou OUT
) ;DECLARE
.La déclaration doit impérativement préciser le nom et le type de la variable.
En option, il est également possible de préciser :
sa valeur initiale (si rien n’est précisé, ce sera
NULL
par défaut) :
sa valeur par défaut, si on veut autre chose que
NULL
:
une contrainte NOT NULL
(dans ce cas, il faut
impérativement un défaut différent de NULL
, et toute
éventuelle affectation ultérieure de NULL
à la variable
provoquera une erreur) :
le collationnement à utiliser, pour les variables de type chaîne de caractères :
Pour les fonctions complexes, avec plusieurs niveaux de boucle par
exemple, il est possible d’imbriquer les blocs
DECLARE
/BEGIN
/END
en y déclarant
des variables locales à ce bloc. Si une variable est par erreur utilisée
hors du scope prévu, une erreur surviendra.
CONSTANT
:L’option CONSTANT
permet de définir une variable pour
laquelle il sera alors impossible d’assigner une valeur dans le reste de
la routine.
%TYPE
:Cela permet d’écrire des routines plus génériques.
L’utilisation de %ROWTYPE
permet de définir une variable
qui contient la structure d’un enregistrement de la table spécifiée.
%ROWTYPE
n’est pas obligatoire, il est néanmoins préférable
d’utiliser cette forme, bien plus portable. En effet, dans PostgreSQL,
toute création de table crée un type associé de même nom, le seul nom de
la table est donc suffisant.
RECORD
identique au type ROW
RECORD
peut changer de type au cours de l’exécution de
la routineRECORD
est beaucoup utilisé pour manipuler des curseurs,
ou dans des boucles FOR … LOOP
: cela évite de devoir se
préoccuper de déclarer un type correspondant exactement aux colonnes de
la requête associée à chaque curseur.
Dans ces exemples, on récupère la première ligne de la fonction avec
SELECT … INTO
, puis on ouvre un curseur implicite pour
balayer chaque ligne obtenue d’une deuxième table. Le type
RECORD
permet de ne pas déclarer une nouvelle variable de
type ligne.
SELECT
pour interprétation par le moteurPREPARE
implicite, avec cachePar expression, on entend par exemple des choses comme :
Dans ce cas, l’expression myvar > 0
sera préparée par
le moteur de la façon suivante :
Puis cette requête préparée sera exécutée en lui passant en paramètre
la valeur de myvar
et la constante 0
.
Si myvar
est supérieur à 0
, il en sera
ensuite de même pour l’instruction suivante :
Comme toute requête préparée, son plan sera mis en cache.
Pour les détails, voir les dessous de PL/pgSQL.
Privilégiez la première écriture pour la lisibilité, la seconde écriture est moins claire et n’apporte rien puisqu’il s’agit ici d’une affectation de constante.
À noter que l’écriture suivante est également possible pour une affectation :
Cette méthode profite du fait que toutes les expressions du code
PL/pgSQL vont être passées au moteur SQL de PostgreSQL dans un
SELECT
pour être résolues. Cela va fonctionner, mais c’est
très peu lisible, et donc non recommandé.
Affectation de la ligne :
INTO STRICT
pour garantir unicité
INTO
seul : juste 1è ligne !Plus d’un enregistrement :
Ordre statique :
WHERE
, tables figéesRécupérer une ligne de résultat d’une requête dans une ligne de type
ROW
ou RECORD
se fait avec
SELECT … INTO
. La première ligne est récupérée.
Généralement on préférera utiliser INTO STRICT
pour lever
une de ces erreurs si la requête renvoie zéro ou plusieurs lignes :
Dans le cas du type ROW
, la définition de la ligne doit
correspondre parfaitement à la définition de la ligne renvoyée. Utiliser
un type RECORD
permet d’éviter ce type de problème. La
variable obtient directement le type ROW
de la ligne
renvoyée.
Il est possible d’utiliser SELECT INTO
avec une simple
variable si l’on n’a qu’un champ d’une ligne à récupérer.
Cette fonction compte les tables, et en trace la liste (les tables ne font pas partie du résultat) :
PERFORM
: résultat ignoréFOUND
On peut déterminer qu’aucune ligne n’a été trouvée par la requête en
utilisant la variable FOUND
:
Pour appeler une fonction, il suffit d’utiliser PERFORM
de la manière suivante :
Pour récupérer le nombre de lignes affectées par l’instruction
exécutée, il faut récupérer la variable de diagnostic
ROW_COUNT
:
Il est à noter que le ROW_COUNT
récupéré ainsi
s’applique à l’ordre SQL précédent, quel qu’il soit :
PERFORM
;EXECUTE
;chaine
chaine
peut être construite à partir d’autres
variablescible
: résultat (une seule ligne)EXECUTE
dans un bloc PL/pgSQL permet notamment du SQL
dynamique : l’ordre peut être construit dans une variable.
Si nom
vaut :
« 'Robert' ; DROP TABLE eleves ;
»
que renvoie ceci ?
Un danger du SQL dynamique est de faire aveuglément confiance aux valeurs des variables en construisant un ordre SQL :
CREATE TEMP TABLE eleves (nom text, id int) ;
INSERT INTO eleves VALUES ('Robert', 0) ;
-- Mise à jour d'un ID
DO $f$
DECLARE
nom text := $$'Robert' ; DROP TABLE eleves;$$ ;
id int ;
BEGIN
RAISE NOTICE 'A exécuter : %','SELECT * FROM eleves WHERE nom = '|| nom ;
EXECUTE 'UPDATE eleves SET id = 327 WHERE nom = '|| nom ;
END ;
$f$ LANGUAGE plpgsql ;
NOTICE: A exécuter : SELECT * FROM eleves WHERE nom = 'Robert' ; DROP TABLE eleves;
\d+ eleves
Aucune relation nommée « eleves » n'a été trouvée.
Cet exemple est directement inspiré d’un dessin très connu de XKCD.
Dans la pratique, la variable nom
(entrée ici en dur)
proviendra par exemple d’un site web, et donc contient potentiellement
des caractères terminant la requête dynamique et en insérant une autre,
potentiellement destructrice.
Moins grave, une erreur peut être levée à cause d’une apostrophe (quote) dans une chaîne texte. Il existe effectivement des gens avec une apostrophe dans le nom.
Ce qui suit concerne le SQL dynamique dans des routines PL/pgSQL,
mais le principe concerne tous les langages et clients, y compris
psql
et sa méta-commande \gexec
.
En SQL pur, la protection contre les injections SQL est un argument pour
utiliser les requêtes
préparées, dont l’ordre EXECUTE
diffère de celui-ci du
PL/pgSQL ci-dessous.
EXECUTE 'UPDATE tbl SET '
|| quote_ident(nom_colonne)
|| ' = '
|| quote_literal(nouvelle_valeur)
|| ' WHERE cle = '
|| quote_literal(valeur_cle) ;
Les trois exemples précédents sont équivalents.
Le premier est le plus simple au premier abord. Il utilise
quote_ident
et quote_literal
pour protéger des
injections SQL
(voir plus loin).
Le second est plus lisible grâce à la fonction de formatage
format
qui évite ces concaténations et appelle implicitement les fonctions
quote_%
Si un paramètre ne peut pas prendre la valeur NULL,
utiliser %L
(équivalent de quote_nullable
) et
non %I
(équivalent de quote_ident
).
La troisième alternative avec USING
et les paramètres
numériques $1
et $2
est considérée comme la
plus performante. (Voir les détails
dans la documentation).
L’exemple complet suivant tiré
de la documentation officielle utilise EXECUTE
pour
rafraîchir des vues matérialisées en masse.
CREATE FUNCTION rafraichir_vuemat() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
RAISE NOTICE 'Rafraîchissement de toutes les vues matérialisées…';
FOR mviews IN
SELECT n.nspname AS mv_schema,
c.relname AS mv_name,
pg_catalog.pg_get_userbyid(c.relowner) AS owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'm'
ORDER BY 1
LOOP
-- Maintenant "mviews" contient un enregistrement
-- avec les informations sur la vue matérialisé
RAISE NOTICE 'Rafraichissement de la vue matérialisée %.% (owner: %)…',
quote_ident(mviews.mv_schema),
quote_ident(mviews.mv_name),
quote_ident(mviews.owner);
EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I',
mviews.mv_schema, mviews.mv_name) ;
END LOOP;
RAISE NOTICE 'Fin du rafraîchissement';
RETURN 1;
END;
$$ LANGUAGE plpgsql;
STRICT
: 1 résultat
NO_DATA_FOUND
ou TOO_MANY_ROWS
STRICT
:
NO_DATA_FOUND
GET DIAGNOSTICS integer_var = ROW_COUNT
De la même manière que pour SELECT … INTO
, utiliser
STRICT
permet de garantir qu’il y a exactement une valeur
comme résultat de EXECUTE
, ou alors une erreur sera
levée.
Nous verrons plus loin comment traiter les exceptions.
quote_ident ()
quote_literal ()
quote_nullable ()
||
: concaténerformat(…)
, équivalent de
sprintf
en CLa fonction format
est l’équivalent de la fonction
sprintf
en C : elle formate une chaîne en fonction d’un
patron et de valeurs à appliquer à ses paramètres et la retourne. Les
types de paramètre reconnus par format
sont :
%I
: est remplacé par un identifiant d’objet. C’est
l’équivalent de la fonction quote_ident
. L’objet en
question est entouré de guillemets doubles si nécessaire ;%L
: est remplacé par une valeur littérale. C’est
l’équivalent de la fonction quote_literal
. Des guillemets
simples sont ajoutés à la valeur et celle-ci est correctement échappée
si nécessaire ;%s
: est remplacé par la valeur donnée sans autre forme
de transformation ;%%
: est remplacé par un simple %
.Voici un exemple d’utilisation de cette fonction, utilisant des paramètres positionnels :
Exemple :
CASE nombre
WHEN nombre = 0 THEN 'zéro'
WHEN variable > 0 THEN 'positif'
WHEN variable < 0 THEN 'négatif'
ELSE 'indéterminé'
END CASE
ou :
L’instruction CASE WHEN
est proche de l’expression
CASE
des requêtes SQL dans son principe (à part qu’elle se clôt par
END
en SQL, et END CASE
en PL/pgSQL).
Elle est parfois plus légère à lire que des IF
imbriqués.
Exemple complet :
LOOP
/ END LOOP
EXIT [label] [WHEN expression_booléenne]
CONTINUE [label] [WHEN expression_booléenne]
Des boucles simples s’effectuent avec
LOOP
/END LOOP
.
Pour les détails, voir la documentation officielle.
Cette boucle incrémente le résultat de 1 à chaque itération tant que la valeur du résultat est inférieure à 50. Ensuite, le résultat est incrémenté de 1 à deux reprises pour chaque tour de boucle. On incrémente donc de 2 par tour de boucle. Arrivée à 100, la procédure sort de la boucle.
variable
va obtenir les différentes valeurs entre
entier1 et entier2La boucle FOR
n’a pas d’originalité par rapport à
d’autres langages.
L’option BY
permet d’augmenter l’incrémentation :
L’option REVERSE
permet de faire défiler les valeurs en
ordre inverse :
ligne
de type RECORD
, ROW
, ou
liste de variables séparées par des virgulesCette syntaxe très pratique permet de parcourir les lignes résultant
d’une requête sans avoir besoin de créer et parcourir un curseur.
Souvent on utilisera une variable de type ROW
ou
RECORD
(comme dans l’exemple de la fonction
rafraichir_vuemat
plus haut), mais l’utilisation directe de
variables (déclarées préalablement) est possible :
FOR a, b, c, d IN
(SELECT col_a, col_b, col_c, col_d FROM ma_table)
LOOP
-- instructions utilisant ces variables
…
END LOOP;
Attention de ne pas utiliser les variables en question hors de la boucle, elles auront gardé la valeur acquise dans la dernière itération.
variable
va obtenir les différentes valeurs du tableau
retourné par expression
SLICE
permet de jouer sur le nombre de dimensions du
tableau à passer à la variableVoici deux exemples permettant d’illustrer l’utilité de
SLICE
:
SLICE
:DO $$
DECLARE a int[] := ARRAY[[1,2],[3,4],[5,6]];
b int;
BEGIN
FOREACH b IN ARRAY a LOOP
RAISE INFO 'var: %', b;
END LOOP;
END $$ ;
SLICE
:DO $$
DECLARE a int[] := ARRAY[[1,2],[3,4],[5,6]];
b int[];
BEGIN
FOREACH b SLICE 1 IN ARRAY a LOOP
RAISE INFO 'var: %', b;
END LOOP;
END $$;
et avec SLICE 2
, on obtient :
SECURITY INVOKER
: défaut
SECURITY DEFINER
Une fonction SECURITY INVOKER
s’exécute avec les droits
de l’appelant. C’est le mode par défaut.
Une fonction SECURITY DEFINER
s’exécute avec les droits
du créateur. Cela permet, au travers d’une fonction, de permettre à un
utilisateur d’outrepasser ses droits de façon contrôlée. C’est
l’équivalent du sudo
d’Unix.
Bien sûr, une fonction SECURITY DEFINER
doit faire
l’objet d’encore plus d’attention qu’une fonction normale. Elle peut
facilement constituer un trou béant dans la sécurité de votre base.
C’est encore plus important si le propriétaire de la fonction est un
superutilisateur, car celui-ci a la possibilité d’accéder aux fichiers
de PostgreSQL et au système d’exploitation.
Plusieurs points importants sont à noter pour
SECURITY DEFINER
:
Par défaut, toute fonction créée dans public est exécutable par le rôle public. La première chose à faire est donc de révoquer ce droit. Mieux : créer la fonction dans un schéma séparé est recommandé pour gérer plus finalement les accès.
Il faut se protéger des variables de session qui pourraient être
utilisées pour modifier le comportement de la fonction, en particulier
le search_path (qui pourrait faire pointer vers des tables de
même nom dans un autre schéma). Il doit donc
impérativement être positionné en dur dans cette
fonction (soit d’emblée, avec un SET
en début de fonction,
soit en positionnant un SET
dans le
CREATE FUNCTION
) ; et/ou les fonctions doivent préciser
systématiquement le schéma dans les appels de tables
(SELECT … FROM nomschema.nomtable …
).
Exemple d’une fonction en SECURITY DEFINER
avec
un search path sécurisé :
\c pgbench pgbench
-- A exécuter en tant que pgbench, propriétaire de la base pgbench
CREATE SCHEMA pgbench_util ;
CREATE OR REPLACE FUNCTION pgbench_util.accounts_balance (pbid integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE PARALLEL SAFE
SECURITY DEFINER
SET search_path TO '' -- précaution supplémentaire
AS $function$
SELECT bbalance FROM public.pgbench_branches br WHERE br.bid = pbid ;
$function$ ;
GRANT USAGE ON SCHEMA pgbench_util TO lecteur ;
GRANT EXECUTE ON FUNCTION pgbench_util.accounts_balance TO lecteur ;
L’utilisateur lecteur peut bien lire le résultat de la fonction sans accès à la table :
Exemple de fonction laxiste et d’attaque :
-- Exemple sur une base pgbench, appartenant à pgbench
-- créée par exemple ainsi :
-- createdb pgbench -O pgbench
-- pgbench -U pgbench -i -s 1 pgbench
-- Deux utilisateurs :
-- pgbench
-- attaquant qui a son propre schéma
\set timing off
\set ECHO all
\set ON_ERROR_STOP 1
\c pgbench pgbench
-- Fonction non sécurisée fournie par l'utilisateur pgbench
-- à tout le monde par public
CREATE OR REPLACE FUNCTION public.accounts_balance_insecure(pbid integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
SECURITY DEFINER
-- oublié : SET search_path TO ''
AS $function$ BEGIN
RETURN bbalance FROM /* pas de schéma */ pgbench_branches br
WHERE br.bid = pbid ;
END $function$ ;
-- Droits trop ouverts
GRANT EXECUTE ON FUNCTION accounts_balance_insecure TO public ;
-- Résultat normal : renvoie 0
SELECT * FROM accounts_balance_insecure (1) ;
-- Création d'un utilisateur avec droit d'écrire dans un schéma
\c pgbench postgres
DROP SCHEMA IF EXISTS piege CASCADE ;
--DROP ROLE attaquant ;
CREATE ROLE attaquant LOGIN ; -- pg_hba.conf laissé en exercice au lecteur
-- Il faut que l'attaquant ait un schéma où écrire,
-- et puisse donner l'accès à la victime.
-- Le schéma public convient parfaitement pour cela avant PostgreSQL 15…
CREATE SCHEMA piege ;
GRANT ALL ON SCHEMA piege TO attaquant WITH GRANT OPTION ;
\c pgbench attaquant
\conninfo
-- Résultat normal (accès peut-être indu mais pour le moment sans danger)
SELECT * FROM accounts_balance_insecure (1) ;
-- L'attaquant peut voir la fonction et étudier comment la détourner
\sf accounts_balance_insecure
-- Fonction que l'attaquant veut faire exécuter à pgbench
CREATE FUNCTION piege.lit_donnees_cachees ()
RETURNS TABLE (bid int, bbalance int)
LANGUAGE plpgsql
AS $$
DECLARE
n int ;
BEGIN
-- affichage de l'utilisateur pgbench
RAISE NOTICE 'Entrée dans fonction piégée en tant que %', current_user ;
-- copie de données non autorisées dans le schéma de l'attaquant
CREATE TABLE piege.donnees_piratees AS SELECT * FROM pgbench_tellers ;
GRANT ALL ON piege.donnees_piratees TO attaquant ;
-- destruction de données…
DROP TABLE IF EXISTS pgbench_history ;
-- sortie propre impérative pour éviter le rollback
RETURN QUERY SELECT 666 AS bid, 42 AS bbalance ;
END ;
$$ ;
-- Vue d'enrobage pour « masquer » la vraie table de même nom
CREATE OR REPLACE VIEW piege.pgbench_branches AS
SELECT * FROM piege.lit_donnees_cachees () ;
-- Donner les droits au compte attaqué sur les objets
-- de l'attaquant
GRANT USAGE,CREATE ON SCHEMA piege TO pgbench ;
GRANT ALL ON piege.pgbench_branches TO pgbench ;
GRANT ALL ON FUNCTION piege.lit_donnees_cachees TO pgbench ;
-- Détournement du chemin d'accès
SET search_path TO piege,public ;
-- Attaque
SELECT * FROM accounts_balance_insecure (666) ;
-- Lecture des données piratées
SELECT COUNT (*) as nb_lignes_recuperees FROM piege.donnees_piratees ;
COST cout_execution
ROWS nb_lignes_resultat
COST
est un coût représenté en unité de
cpu_operator_cost
(100 par défaut).
ROWS
vaut par défaut 1000 pour les fonctions
SETOF
ou TABLE
, et 1 pour les autres.
Ces deux paramètres ne modifient pas le comportement de la fonction. Ils ne servent que pour aider l’optimiseur de requête à estimer le coût d’appel à la fonction, afin de savoir, si plusieurs plans sont possibles, lequel est le moins coûteux par rapport au nombre d’appels de la fonction et au nombre d’enregistrements qu’elle retourne.
PARALLEL UNSAFE
(défaut)PARALLEL RESTRICTED
PARALLEL SAFE
PARALLEL UNSAFE
indique que la fonction ne peut pas être
exécutée dans le mode parallèle. La présence d’une fonction de ce type
dans une requête SQL force un plan d’exécution en série. C’est la valeur
par défaut.
Une fonction est non parallélisable si elle modifie l’état d’une base ou si elle fait des changements sur la transaction.
PARALLEL RESTRICTED
indique que la fonction peut être
exécutée en mode parallèle mais l’exécution est restreinte au processus
principal d’exécution.
Une fonction peut être déclarée comme restreinte si elle accède aux tables temporaires, à l’état de connexion des clients, aux curseurs, aux requêtes préparées.
PARALLEL SAFE
indique que la fonction s’exécute
correctement dans le mode parallèle sans restriction.
En général, si une fonction est marquée sûre ou restreinte à la parallélisation alors qu’elle ne l’est pas, elle pourrait renvoyer des erreurs ou fournir de mauvaises réponses lorsqu’elle est utilisée dans une requête parallèle.
En cas de doute, les fonctions doivent être marquées comme
UNSAFE
, ce qui correspond à la valeur par défaut.
IMMUTABLE | STABLE | VOLATILE
On peut indiquer à PostgreSQL le niveau de volatilité (ou de stabilité) d’une fonction. Ceci permet d’aider PostgreSQL à optimiser les requêtes utilisant ces fonctions, mais aussi d’interdire leur utilisation dans certains contextes.
Une fonction est « immutable » si son exécution ne
dépend que de ses paramètres. Elle ne doit donc dépendre ni du contenu
de la base (pas de SELECT
, ni de modification de donnée de
quelque sorte), ni d’aucun autre élément qui ne soit
pas un de ses paramètres. Les fonctions arithmétiques simples
(+
, *
, abs
…) sont immutables.
À l’inverse, now()
n’est évidemment pas immutable. Une
fonction sélectionnant des données d’une table non plus.
to_char()
n’est pas non plus immutable, car son
comportement dépend des paramètres de session, par exemple
to_char(timestamp with time zone, text)
dépend du paramètre
de session timezone
…
Une fonction est « stable » si son exécution donne
toujours le même résultat sur toute la durée d’un ordre SQL, pour les
mêmes paramètres en entrée. Cela signifie que la fonction ne modifie pas
les données de la base. Une fonction n’exécutant que des
SELECT
sur des tables (pas des fonctions !) sera stable.
to_char()
est stable. L’optimiseur peut réduire ainsi le
nombre d’appels sans que ce soit en pratique toujours le cas.
Une fonction est « volatile » dans tous les autres
cas. random()
est volatile. Une fonction volatile peut même
modifier les donneés. Une fonction non déclarée comme stable ou
immutable est volatile par défaut.
La volatilité des fonctions intégrées à PostgreSQL est déjà définie. C’est au développeur de préciser la volatilité des fonctions qu’il écrit. Ce n’est pas forcément évident. Une erreur peut poser des problèmes quand le plan est mis en cache, ou, on le verra, dans des index.
Quelle importance cela a-t-il ?
Prenons une table d’exemple sur les heures de l’année 2020 :
-- Une ligne par heure dans l année, 8784 lignes
CREATE TABLE heures
AS
SELECT i, '2020-01-01 00:00:00+01:00'::timestamptz + i * interval '1 hour' AS t
FROM generate_series (1,366*24) i;
Définissons une fonction un peu naïve ramenant le premier jour du mois, volatile faute de mieux :
CREATE OR REPLACE FUNCTION premierjourdumois(t timestamptz)
RETURNS timestamptz
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
RAISE notice 'appel premierjourdumois' ; -- trace des appels
RETURN date_trunc ('month', t);
END $$ ;
Demandons juste le plan d’un appel ne portant que sur le dernier jour :
EXPLAIN SELECT * FROM heures
WHERE t > premierjourdumois('2020-12-31 00:00:00+02:00'::timestamptz)
LIMIT 10 ;
QUERY PLAN
-------------------------------------------------------------------------
Limit (cost=0.00..8.04 rows=10 width=12)
-> Seq Scan on heures (cost=0.00..2353.80 rows=2928 width=12)
Filter: (t > premierjourdumois(
'2020-12-30 23:00:00+01'::timestamp with time zone))
Le nombre de lignes attendues (2928) est le tiers de la table, alors que nous ne demandons que le dernier mois. Il s’agit de l’estimation forfaitaire que PostgreSQL utilise faute d’informations sur ce que va retourner la fonction.
Demander à voir le résultat mène à l’affichage de milliers de
NOTICE
: la fonction est appelée à chaque ligne pour
calculer s’il faut filtrer la valeur. En effet, une fonction volatile
sera systématiquement exécutée à chaque appel, et, selon le plan, ce
peut être pour chaque ligne parcourue !
Cependant notre fonction ne fait que des calculs à partir du paramètre, sans effet de bord. Déclarons-la donc stable :
Une fonction stable peut en théorie être remplacée par son résultat pendant l’exécution de la requête. Mais c’est impossible de le faire plus tôt, car on ne sait pas forcément dans quel contexte la fonction va être appelée (par exemple, en cas de requête préparée, les paramètres de la session ou les données de la base peuvent même changer entre la planification et l’exécution).
Dans notre cas, le même EXPLAIN
simple mène à ceci :
NOTICE: appel premierjourdumois
QUERY PLAN
-------------------------------------------------------------------------
Limit (cost=0.00..32.60 rows=10 width=12)
-> Seq Scan on heures (cost=0.00..2347.50 rows=720 width=12)
Filter: (t > premierjourdumois(
'2020-12-30 23:00:00+01'::timestamp with time zone))
Comme il s’agit d’un simple EXPLAIN
, la requête n’est
pas exécutée. Or le message NOTICE
est renvoyé : la
fonction est donc exécutée pour une simple planification. Un appel
unique suffit, puisque la valeur d’une fonction stable ne change pas
pendant toute la durée de la requête pour les mêmes paramètres (ici une
constante). Cet appel permet d’affiner la volumétrie des valeurs
attendues, ce qui peut avoir un impact énorme.
Cependant, à l’exécution, les NOTICE
apparaîtront pour
indiquer que la fonction est à nouveau appelée à chaque ligne. Pour
qu’un seul appel soit effectué pour toute la requête, il faudrait
déclarer la fonction comme immutable, ce qui serait faux, puisqu’elle
dépend implicitement du fuseau horaire.
Dans l’idéal, une fonction immutable peut être remplacée par son résultat avant même la planification d’une requête l’utilisant. C’est le cas avec les calculs arithmétiques par exemple :
EXPLAIN SELECT * FROM heures
WHERE i > abs(364*24) AND t > '2020-06-01'::date + interval '57 hours' ;
La valeur est substituée très tôt, ce qui permet de les comparer aux statistiques :
Seq Scan on heures (cost=0.00..179.40 rows=13 width=12)
Filter: ((i > 8736) AND (t > '2020-06-03 09:00:00'::timestamp without time zone))
Pour forcer un appel unique quand on sait que la fonction renverra une constante, du moins le temps de la requête, même si elle est volatile, une astuce est de signifier à l’optimiseur qu’il n’y aura qu’une seule valeur de comparaison, même si on ne sait pas laquelle :
EXPLAIN (ANALYZE) SELECT * FROM heures
WHERE t > (SELECT premierjourdumois('2020-12-31 00:00:00+02:00'::timestamptz)) ;
NOTICE: appel premierjourdumois
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on heures (cost=0.26..157.76 rows=2920 width=12)
(actual time=1.090..1.206 rows=721 loops=1)
Filter: (t > $0)
Rows Removed by Filter: 8039
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.26 rows=1 width=8)
(actual time=0.138..0.139 rows=1 loops=1)
Planning Time: 0.058 ms
Execution Time: 1.328 ms
On note qu’il n’y a qu’un appel. On comprend donc l’intérêt de se poser la question à l’écriture de chaque fonction.
La volatilité est encore plus importante quand il s’agit de créer des fonctions sur index :
Ceci n’est possible que si la fonction est immutable. En effet, si le résultat de la fonction dépend de l’état de la base ou d’autres paramètres, la fonction exécutée au moment de la création de la clé d’index pourrait ne plus retourner le même résultat quand viendra le moment de l’interroger. PostgreSQL n’acceptera donc que les fonctions immutables dans la déclaration des index fonctionnels.
Déclarer hâtivement une fonction comme immutable juste pour pouvoir l’utiliser dans un index est dangereux : en cas d’erreur, les résultats d’une requête peuvent alors dépendre du plan d’exécution, selon que les index seront utilisés ou pas !
Cela est particulièrement fréquent quand les fuseaux horaires ou les dictionnaires sont impliqués. Vérifiez bien que vous n’utilisez que des fonctions immutables dans les index fonctionnels, les pièges sont nombreux.
Par exemple, si l’on veut une version immutable de la fonction
précédente, il faut fixer le fuseau horaire dans l’appel à
date_trunc
. En effet, on peut voir avec
df+ date_trunc
que la seule version immutable de
date_trunc
n’accepte que des timestamp
(sans
fuseau), et en renvoie un. Notre fonction devient donc :
CREATE OR REPLACE FUNCTION premierjourdumois_utc(t timestamptz)
RETURNS timestamptz
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
jour1 timestamp ; --sans TZ
BEGIN
jour1 := date_trunc ('month', (t at time zone 'UTC')::timestamp) ;
RETURN jour1 AT TIME ZONE 'UTC';
END $$ ;
Testons avec une date dans les dernières heures de septembre en Alaska, qui correspond au tout début d’octobre en temps universel, et par exemple aussi au Japon :
SET timezone TO 'US/Alaska';
SELECT d,
d AT TIME ZONE 'UTC' AS d_en_utc,
premierjourdumois_utc (d),
premierjourdumois_utc (d) AT TIME ZONE 'UTC' as pjm_en_utc
FROM (SELECT '2020-09-30 18:00:00-08'::timestamptz AS d) x;
-[ RECORD 1 ]---------+-----------------------
d | 2020-09-30 18:00:00-08
d_en_utc | 2020-10-01 02:00:00
premierjourdumois_utc | 2020-09-30 16:00:00-08
pjm_en_utc | 2020-10-01 00:00:00
SET timezone TO 'Japan';
SELECT d,
d AT TIME ZONE 'UTC' AS d_en_utc,
premierjourdumois_utc (d),
premierjourdumois_utc (d) AT TIME ZONE 'UTC' as pjm_en_utc
FROM (SELECT '2020-09-30 18:00:00-08'::timestamptz AS d) x;
-[ RECORD 1 ]---------+-----------------------
d | 2020-10-01 11:00:00+09
d_en_utc | 2020-10-01 02:00:00
premierjourdumois_utc | 2020-10-01 09:00:00+09
pjm_en_utc | 2020-10-01 00:00:00
Malgré les différences d’affichage dues au fuseau horaire, c’est bien le même moment (la première seconde d’octobre en temps universel) qui est retourné par la fonction.
Pour une fonction aussi simple, la version SQL est même préférable :
CREATE OR REPLACE FUNCTION premierjourdumois_utc(t timestamptz)
RETURNS timestamptz
LANGUAGE sql
IMMUTABLE
AS $$
SELECT (date_trunc ('month',
(t at time zone 'UTC')::timestamp
)
) AT TIME ZONE 'UTC';
$$ ;
Enfin, la volatilité a également son importance lors d’autres opérations d’optimisation, comme l’exclusion de partitions. Seules les fonctions immutables sont compatibles avec le partition pruning effectué à la planification, mais les fonctions stable sont éligibles au dynamic partition pruning (à l’exécution) apparu avec PostgreSQL 11.
La documentation officielle sur le langage PL/pgSQL peut être consultée en français à cette adresse.
L’exercice sur les index fonctionnels utilise la base 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 magasin :
createdb magasin
curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump
pg_restore -d magasin /tmp/magasin.dump
# le message sur public préexistant est normal
rm -- /tmp/magasin.dump
Toutes les données sont dans deux schémas nommés magasin et facturation.
But : Premières fonctions
Écrire une fonction
hello()
qui renvoie la chaîne de caractère « Hello World! » en SQL.
Écrire une fonction
hello_pl()
qui renvoie la chaîne de caractère « Hello World! » en PL/pgSQL.
Comparer les coûts des deux plans d’exécutions de ces requêtes. Expliquer ces coûts.
But : Fonction avec calcul simple
Écrire en PL/pgSQL une fonction de division appelée
division
. Elle acceptera en entrée deux arguments de type entier et renverra un nombre réel (numeric
).
Écrire cette même fonction en SQL.
Comment corriger le problème de la division par zéro ? Écrire cette nouvelle fonction dans les deux langages. (Conseil : dans ce genre de calcul impossible, il est possible d’utiliser la constante
NaN
(Not A Number) ).
But : Utiliser une table à l’intérieur d’une fonction
Ce TP utilise les tables de la base employes_services. Le script de création se télécharge et s’installe ainsi dans une nouvelle base employes :
curl -kL https://dali.bo/tp_employes_services -o employes_services.sql
createdb employes
psql employes < employes_services.sql
Les quelques tables occupent environ 80 Mo sur le disque.
Créer une fonction qui ramène le nombre d’employés embauchés une année donnée (à partir du champ
employes.date_embauche
).
Utiliser la fonction
generate_series()
pour lister le nombre d’embauches pour chaque année entre 2000 et 2010.
Créer une fonction qui fait la même chose avec deux années en paramètres une boucle
FOR … LOOP
,RETURNS TABLE
etRETURN NEXT
.
But : Fonctions avec de nombreuses conditions, des manipulations de types, et un message.
Écrire une fonction de multiplication dont les arguments sont des chiffres en toute lettre, inférieurs ou égaux à « neuf ». Par exemple,
multiplication ('deux','trois')
doit renvoyer 6.
Si ce n’est déjà fait, faire en sorte que
multiplication
appelle une autre fonction pour faire la conversion de texte en chiffre, et n’effectue que le calcul.
Essayer de multiplier « deux » par 4. Qu’obtient-on et pourquoi ?
Corriger la fonction pour tomber en erreur si un argument est numérique (utiliser
RAISE EXCEPTION <message>
).
But : Fonction plus complexe
Écrire une fonction en PL/pgSQL qui prend en argument le nom de l’utilisateur, puis lui dit « Bonjour » ou « Bonsoir » suivant l’heure de la journée. Utiliser la fonction
to_char()
.
Écrire la même fonction avec un paramètre
OUT
.
Pour calculer l’heure courante, utiliser plutôt la fonction
extract
.
Réécrire la fonction en SQL.
But : Manipuler des chaînes
Écrire une fonction
inverser
qui inverse une chaîne (pour « toto » en entrée, afficher « otot » en sortie), à l’aide d’une boucleWHILE
et des fonctionschar_length
etsubstring
.
But : Calculs complexes avec des dates
Le calcul de la date de Pâques est complexe. On peut écrire la fonction suivante :
CREATE OR REPLACE FUNCTION paques (annee integer)
RETURNS date
AS $$
DECLARE
a integer ;
b integer ;
r date ;
BEGIN
a := (19*(annee % 19) + 24) % 30 ;
b := (2*(annee % 4) + 4*(annee % 7) + 6*a + 5) % 7 ;
SELECT (annee::text||'-03-31')::date + (a+b-9) INTO r ;
RETURN r ;
END ;
$$
LANGUAGE plpgsql ;
Principe : Soit m
l’année. On calcule
successivement :
m/19
: c’est la valeur de
a
.m/4
: c’est la valeur de
b
.m/7
: c’est la valeur de
c
.(19a + p)/30
: c’est la valeur de
d
.(2b + 4c + 6d + q)/7
: c’est la valeur de
e
.Les valeurs de p
et de q
varient de 100 ans
en 100 ans. De 2000 à 2100, p
vaut 24, q
vaut
5. La date de Pâques est le (22 + d + e)
mars ou le
(d + e - 9)
avril.
Afficher les dates de Pâques de 2018 à 2025.
Écrire une fonction qui calcule la date de l’Ascension, soit le jeudi de la sixième semaine après Pâques. Pour simplifier, on peut aussi considérer que l’Ascension se déroule 39 jours après Pâques.
Pour écrire une fonction qui renvoie tous les jours fériés d’une année (libellé et date), en France métropolitaine :
- Prévoir un paramètre supplémentaire pour l’Alsace-Moselle, où le Vendredi saint (précédant le dimanche de Pâques) et le 26 décembre sont aussi fériés (ou toute autre variation régionale).
- Cette fonction doit renvoyer plusieurs lignes : utiliser
RETURN NEXT
.- Plusieurs variantes sont possibles : avec
SETOF record
, avec des paramètresOUT
, ou avecRETURNS TABLE (libelle, jour)
.- Enfin, il est possible d’utiliser
RETURN QUERY
.
But : Cas d’usage d’un index fonctionnel
Pour répondre aux exigences de stockage, l’application a besoin de pouvoir trouver rapidement les produits dont le volume est compris entre certaines bornes (nous négligeons ici le facteur de forme, qui est problématique dans le cadre d’un véritable stockage en entrepôt !).
Écrire une requête permettant de renvoyer l’ensemble des produits (table
magasin.produits
) dont le volume ne dépasse pas 1 litre (les unités de longueur sont en mm, 1 litre = 1 000 000 mm³).
Quel index permet d’optimiser cette requête ? (Utiliser une fonction est possible, mais pas obligatoire.)
Écrire une fonction
hello()
qui renvoie la chaîne de caractère « Hello World! » en SQL.
CREATE OR REPLACE FUNCTION hello()
RETURNS text
AS $BODY$
SELECT 'hello world !'::text;
$BODY$
LANGUAGE SQL;
Écrire une fonction
hello_pl()
qui renvoie la chaîne de caractère « Hello World! » en PL/pgSQL.
CREATE OR REPLACE FUNCTION hello_pl()
RETURNS text
AS $BODY$
BEGIN
RETURN 'hello world !';
END
$BODY$
LANGUAGE plpgsql;
Comparer les coûts des deux plans d’exécutions de ces requêtes. Expliquer ces coûts.
Requêtage :
Par défaut, si on ne précise pas le coût (COST
) d’une
fonction, cette dernière a un coût par défaut de 100. Ce coût est à
multiplier par la valeur du paramètre cpu_operator_cost
,
par défaut à 0,0025. Le coût total d’appel de la fonction
hello_pl
est donc par défaut de :
100*cpu_operator_cost + cpu_tuple_cost
Ce n’est pas valable pour la fonction en SQL pur, qui est ici intégrée à la requête.
Écrire en PL/pgSQL une fonction de division appelée
division
. Elle acceptera en entrée deux arguments de type entier et renverra un nombre réel (numeric
).
Attention, sous PostgreSQL, la division de deux entiers est par défaut entière : il faut donc transtyper.
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
RETURNS numeric
AS $BODY$
BEGIN
RETURN arg1::numeric / arg2::numeric;
END
$BODY$
LANGUAGE plpgsql;
Écrire cette même fonction en SQL.
CREATE OR REPLACE FUNCTION division_sql (a integer, b integer)
RETURNS numeric
AS $$
SELECT a::numeric / b::numeric;
$$
LANGUAGE SQL;
Comment corriger le problème de la division par zéro ? Écrire cette nouvelle fonction dans les deux langages. (Conseil : dans ce genre de calcul impossible, il est possible d’utiliser la constante
NaN
(Not A Number) ).
Le problème se présente ainsi :
Pour la version en PL :
CREATE OR REPLACE FUNCTION division(arg1 integer, arg2 integer)
RETURNS numeric
AS $BODY$
BEGIN
IF arg2 = 0 THEN
RETURN 'NaN';
ELSE
RETURN arg1::numeric / arg2::numeric;
END IF;
END $BODY$
LANGUAGE plpgsql;
Pour la version en SQL :
CREATE OR REPLACE FUNCTION division_sql(a integer, b integer)
RETURNS numeric
AS $$
SELECT CASE $2
WHEN 0 THEN 'NaN'
ELSE $1::numeric / $2::numeric
END;
$$
LANGUAGE SQL;
Ce TP utilise les tables de la base employes_services. Le script de création se télécharge et s’installe ainsi dans une nouvelle base employes :
curl -kL https://dali.bo/tp_employes_services -o employes_services.sql
createdb employes
psql employes < employes_services.sql
Les quelques tables occupent environ 80 Mo sur le disque.
Créer une fonction qui ramène le nombre d’employés embauchés une année donnée (à partir du champ
employes.date_embauche
).
CREATE OR REPLACE FUNCTION nb_embauches (v_annee integer)
RETURNS integer
AS $BODY$
DECLARE
nb integer;
BEGIN
SELECT count(*)
INTO nb
FROM employes
WHERE extract (year from date_embauche) = v_annee ;
RETURN nb;
END
$BODY$
LANGUAGE plpgsql ;
Test :
Utiliser la fonction
generate_series()
pour lister le nombre d’embauches pour chaque année entre 2000 et 2010.
n | nb_embauches
------+--------------
2000 | 2
2001 | 0
2002 | 0
2003 | 1
2004 | 0
2005 | 2
2006 | 9
2007 | 0
2008 | 0
2009 | 0
2010 | 0
Créer une fonction qui fait la même chose avec deux années en paramètres une boucle
FOR … LOOP
,RETURNS TABLE
etRETURN NEXT
.
CREATE OR REPLACE FUNCTION nb_embauches (v_anneedeb int, v_anneefin int)
RETURNS TABLE (annee int, nombre_embauches int)
AS $BODY$
BEGIN
FOR i in v_anneedeb..v_anneefin
LOOP
SELECT i, nb_embauches (i)
INTO annee, nombre_embauches ;
RETURN NEXT ;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
Le nom de la fonction a été choisi identique à la précédente, mais avec des paramètres différents. Cela ne gêne pas le requêtage :
Écrire une fonction de multiplication dont les arguments sont des chiffres en toute lettre, inférieurs ou égaux à « neuf ». Par exemple,
multiplication ('deux','trois')
doit renvoyer 6.
CREATE OR REPLACE FUNCTION multiplication (arg1 text, arg2 text)
RETURNS integer
AS $BODY$
DECLARE
a1 integer;
a2 integer;
BEGIN
IF arg1 = 'zéro' THEN
a1 := 0;
ELSEIF arg1 = 'un' THEN
a1 := 1;
ELSEIF arg1 = 'deux' THEN
a1 := 2;
ELSEIF arg1 = 'trois' THEN
a1 := 3;
ELSEIF arg1 = 'quatre' THEN
a1 := 4;
ELSEIF arg1 = 'cinq' THEN
a1 := 5;
ELSEIF arg1 = 'six' THEN
a1 := 6;
ELSEIF arg1 = 'sept' THEN
a1 := 7;
ELSEIF arg1 = 'huit' THEN
a1 := 8;
ELSEIF arg1 = 'neuf' THEN
a1 := 9;
END IF;
IF arg2 = 'zéro' THEN
a2 := 0;
ELSEIF arg2 = 'un' THEN
a2 := 1;
ELSEIF arg2 = 'deux' THEN
a2 := 2;
ELSEIF arg2 = 'trois' THEN
a2 := 3;
ELSEIF arg2 = 'quatre' THEN
a2 := 4;
ELSEIF arg2 = 'cinq' THEN
a2 := 5;
ELSEIF arg2 = 'six' THEN
a2 := 6;
ELSEIF arg2 = 'sept' THEN
a2 := 7;
ELSEIF arg2 = 'huit' THEN
a2 := 8;
ELSEIF arg2 = 'neuf' THEN
a2 := 9;
END IF;
RETURN a1*a2;
END
$BODY$
LANGUAGE plpgsql;
Test :
Si ce n’est déjà fait, faire en sorte que
multiplication
appelle une autre fonction pour faire la conversion de texte en chiffre, et n’effectue que le calcul.
CREATE OR REPLACE FUNCTION texte_vers_entier(arg text)
RETURNS integer AS $BODY$
DECLARE
ret integer;
BEGIN
IF arg = 'zéro' THEN
ret := 0;
ELSEIF arg = 'un' THEN
ret := 1;
ELSEIF arg = 'deux' THEN
ret := 2;
ELSEIF arg = 'trois' THEN
ret := 3;
ELSEIF arg = 'quatre' THEN
ret := 4;
ELSEIF arg = 'cinq' THEN
ret := 5;
ELSEIF arg = 'six' THEN
ret := 6;
ELSEIF arg = 'sept' THEN
ret := 7;
ELSEIF arg = 'huit' THEN
ret := 8;
ELSEIF arg = 'neuf' THEN
ret := 9;
END IF;
RETURN ret;
END
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION multiplication(arg1 text, arg2 text)
RETURNS integer
AS $BODY$
DECLARE
a1 integer;
a2 integer;
BEGIN
a1 := texte_vers_entier(arg1);
a2 := texte_vers_entier(arg2);
RETURN a1*a2;
END
$BODY$
LANGUAGE plpgsql;
Essayer de multiplier « deux » par 4. Qu’obtient-on et pourquoi ?
Par défaut, les variables internes à la fonction valent NULL. Rien n’est prévu pour affecter le second argument, on obtient donc NULL en résultat.
Corriger la fonction pour tomber en erreur si un argument est numérique (utiliser
RAISE EXCEPTION <message>
).
CREATE OR REPLACE FUNCTION texte_vers_entier(arg text)
RETURNS integer AS $BODY$
DECLARE
ret integer;
BEGIN
IF arg = 'zéro' THEN
ret := 0;
ELSEIF arg = 'un' THEN
ret := 1;
ELSEIF arg = 'deux' THEN
ret := 2;
ELSEIF arg = 'trois' THEN
ret := 3;
ELSEIF arg = 'quatre' THEN
ret := 4;
ELSEIF arg = 'cinq' THEN
ret := 5;
ELSEIF arg = 'six' THEN
ret := 6;
ELSEIF arg = 'sept' THEN
ret := 7;
ELSEIF arg = 'huit' THEN
ret := 8;
ELSEIF arg = 'neuf' THEN
ret := 9;
ELSE
RAISE EXCEPTION 'argument "%" invalide', arg;
ret := NULL;
END IF;
RETURN ret;
END
$BODY$
LANGUAGE plpgsql;
ERROR: argument "4" invalide
CONTEXTE : PL/pgSQL function texte_vers_entier(text) line 26 at RAISE
PL/pgSQL function multiplication(text,text) line 7 at assignment
Écrire une fonction en PL/pgSQL qui prend en argument le nom de l’utilisateur, puis lui dit « Bonjour » ou « Bonsoir » suivant l’heure de la journée. Utiliser la fonction
to_char()
.
CREATE OR REPLACE FUNCTION salutation(utilisateur text)
RETURNS text
AS $BODY$
DECLARE
heure integer;
libelle text;
BEGIN
heure := to_char(now(), 'HH24');
IF heure > 12
THEN
libelle := 'Bonsoir';
ELSE
libelle := 'Bonjour';
END IF;
RETURN libelle||' '||utilisateur||' !';
END
$BODY$
LANGUAGE plpgsql;
Test :
Écrire la même fonction avec un paramètre
OUT
.
CREATE OR REPLACE FUNCTION salutation(IN utilisateur text, OUT message text)
AS $BODY$
DECLARE
heure integer;
libelle text;
BEGIN
heure := to_char(now(), 'HH24');
IF heure > 12
THEN
libelle := 'Bonsoir';
ELSE
libelle := 'Bonjour';
END IF;
message := libelle||' '||utilisateur||' !';
END
$BODY$
LANGUAGE plpgsql;
Elle s’utilise de la même manière :
Pour calculer l’heure courante, utiliser plutôt la fonction
extract
.
CREATE OR REPLACE FUNCTION salutation(IN utilisateur text, OUT message text)
AS $BODY$
DECLARE
heure integer;
libelle text;
BEGIN
SELECT INTO heure extract(hour from now())::int;
IF heure > 12
THEN
libelle := 'Bonsoir';
ELSE
libelle := 'Bonjour';
END IF;
message := libelle||' '||utilisateur||' !';
END
$BODY$
LANGUAGE plpgsql;
Réécrire la fonction en SQL.
Le CASE … WHEN
remplace aisément un
IF … THEN
:
CREATE OR REPLACE FUNCTION salutation_sql(nom text)
RETURNS text
AS $$
SELECT CASE extract(hour from now()) > 12
WHEN 't' THEN 'Bonsoir '|| nom
ELSE 'Bonjour '|| nom
END::text;
$$ LANGUAGE SQL;
Écrire une fonction
inverser
qui inverse une chaîne (pour « toto » en entrée, afficher « otot » en sortie), à l’aide d’une boucleWHILE
et des fonctionschar_length
etsubstring
.
CREATE OR REPLACE FUNCTION inverser(str_in varchar)
RETURNS varchar
AS $$
DECLARE
str_out varchar ; -- à renvoyer
position integer ;
BEGIN
-- Initialisation de str_out, sinon sa valeur reste à NULL
str_out := '';
-- Position initialisée ç la longueur de la chaîne
position := char_length(str_in);
-- La chaîne est traitée ç l'envers
-- Boucle: Inverse l'ordre des caractères d'une chaîne de caractères
WHILE position > 0 LOOP
-- la chaîne donnée en argument est parcourue
-- à l'envers,
-- et les caractères sont extraits individuellement
str_out := str_out || substring(str_in, position, 1);
position := position - 1;
END LOOP;
RETURN str_out;
END;
$$
LANGUAGE plpgsql;
La fonction suivante calcule la date de Pâques d’une année :
CREATE OR REPLACE FUNCTION paques (annee integer)
RETURNS date
AS $$
DECLARE
a integer ;
b integer ;
r date ;
BEGIN
a := (19*(annee % 19) + 24) % 30 ;
b := (2*(annee % 4) + 4*(annee % 7) + 6*a + 5) % 7 ;
SELECT (annee::text||'-03-31')::date + (a+b-9) INTO r ;
RETURN r ;
END ;
$$
LANGUAGE plpgsql ;
Afficher les dates de Pâques de 2018 à 2025.
paques
------------
2018-04-01
2019-04-21
2020-04-12
2021-04-04
2022-04-17
2023-04-09
2024-03-31
2025-04-20
Écrire une fonction qui calcule la date de l’Ascension, soit le jeudi de la sixième semaine après Pâques. Pour simplifier, on peut aussi considérer que l’Ascension se déroule 39 jours après Pâques.
Version complexe :
CREATE OR REPLACE FUNCTION ascension(annee integer)
RETURNS date
AS $$
DECLARE
r date;
BEGIN
SELECT paques(annee)::date + 40 INTO r;
SELECT r + (4 - extract(dow from r))::integer INTO r;
RETURN r;
END;
$$
LANGUAGE plpgsql;
Version simple :
CREATE OR REPLACE FUNCTION ascension(annee integer)
RETURNS date
AS $$
SELECT (paques (annee) + INTERVAL '39 days')::date ;
$$
LANGUAGE sql;
Test :
paques | ascension
------------+------------
2018-04-01 | 2018-05-10
2019-04-21 | 2019-05-30
2020-04-12 | 2020-05-21
2021-04-04 | 2021-05-13
2022-04-17 | 2022-05-26
2023-04-09 | 2023-05-18
2024-03-31 | 2024-05-09
2025-04-20 | 2025-05-29
Pour écrire une fonction qui renvoie tous les jours fériés d’une année (libellé et date), en France métropolitaine :
- Prévoir un paramètre supplémentaire pour l’Alsace-Moselle, où le Vendredi saint (précédant le dimanche de Pâques) et le 26 décembre sont aussi fériés (ou toute autre variation régionale).
- Cette fonction doit renvoyer plusieurs lignes : utiliser
RETURN NEXT
.- Plusieurs variantes sont possibles : avec
SETOF record
, avec des paramètresOUT
, ou avecRETURNS TABLE (libelle, jour)
.- Enfin, il est possible d’utiliser
RETURN QUERY
.
Version avec SETOF record :
CREATE OR REPLACE FUNCTION vacances (
annee integer,
alsace_moselle boolean DEFAULT false
) RETURNS SETOF record
AS $$
DECLARE
f integer;
r record;
BEGIN
SELECT 'Jour de l''an'::text, (annee::text||'-01-01')::date INTO r;
RETURN NEXT r;
SELECT 'Pâques'::text, paques(annee)::date + 1 INTO r;
RETURN NEXT r;
SELECT 'Ascension'::text, ascension(annee)::date INTO r;
RETURN NEXT r;
SELECT 'Fête du travail'::text, (annee::text||'-05-01')::date INTO r;
RETURN NEXT r;
SELECT 'Victoire 1945'::text, (annee::text||'-05-08')::date INTO r;
RETURN NEXT r;
SELECT 'Fête nationale'::text, (annee::text||'-07-14')::date INTO r;
RETURN NEXT r;
SELECT 'Assomption'::text, (annee::text||'-08-15')::date INTO r;
RETURN NEXT r;
SELECT 'La toussaint'::text, (annee::text||'-11-01')::date INTO r;
RETURN NEXT r;
SELECT 'Armistice 1918'::text, (annee::text||'-11-11')::date INTO r;
RETURN NEXT r;
SELECT 'Noël'::text, (annee::text||'-12-25')::date INTO r;
RETURN NEXT r;
IF alsace_moselle THEN
SELECT 'Vendredi saint'::text, paques(annee)::date - 2 INTO r;
RETURN NEXT r;
SELECT 'Lendemain de Noël'::text, (annee::text||'-12-26')::date INTO r;
RETURN NEXT r;
END IF;
RETURN;
END;
$$
LANGUAGE plpgsql;
Le requêtage implique de nommer les colonnes :
libelle | jour
--------------------+------------
Jour de l'an | 2020-01-01
Vendredi saint | 2020-04-10
Pâques | 2020-04-13
Fête du travail | 2020-05-01
Victoire 1945 | 2020-05-08
Ascension | 2020-05-21
Fête nationale | 2020-07-14
Assomption | 2020-08-15
La toussaint | 2020-11-01
Armistice 1918 | 2020-11-11
Noël | 2020-12-25
Lendemain de Noël | 2020-12-26
Version avec paramètres OUT :
Une autre forme d’écriture possible consiste à indiquer les deux
colonnes de retour comme des paramètres OUT
:
CREATE OR REPLACE FUNCTION vacances(
annee integer,
alsace_moselle boolean DEFAULT false,
OUT libelle text,
OUT jour date)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
DECLARE
f integer;
r record;
BEGIN
SELECT 'Jour de l''an'::text, (annee::text||'-01-01')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'Pâques'::text, paques(annee)::date + 1 INTO libelle, jour;
RETURN NEXT;
SELECT 'Ascension'::text, ascension(annee)::date INTO libelle, jour;
RETURN NEXT;
SELECT 'Fête du travail'::text, (annee::text||'-05-01')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'Victoire 1945'::text, (annee::text||'-05-08')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'Fête nationale'::text, (annee::text||'-07-14')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'Assomption'::text, (annee::text||'-08-15')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'La toussaint'::text, (annee::text||'-11-01')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'Armistice 1918'::text, (annee::text||'-11-11')::date
INTO libelle, jour;
RETURN NEXT;
SELECT 'Noël'::text, (annee::text||'-12-25')::date INTO libelle, jour;
RETURN NEXT;
IF alsace_moselle THEN
SELECT 'Vendredi saint'::text, paques(annee)::date - 2 INTO libelle, jour;
RETURN NEXT;
SELECT 'Lendemain de Noël'::text, (annee::text||'-12-26')::date
INTO libelle, jour;
RETURN NEXT;
END IF;
RETURN;
END;
$function$;
La fonction s’utilise alors de façon simple :
libelle | jour
-----------------+------------
Jour de l'an | 2020-01-01
Pâques | 2020-04-13
Fête du travail | 2020-05-01
Victoire 1945 | 2020-05-08
Ascension | 2020-05-21
Fête nationale | 2020-07-14
Assomption | 2020-08-15
La toussaint | 2020-11-01
Armistice 1918 | 2020-11-11
Noël | 2020-12-25
Version avec RETURNS TABLE
:
Seule la déclaration en début diffère de la version avec les
paramètres OUT
:
CREATE OR REPLACE FUNCTION vacances(
annee integer,alsace_moselle boolean DEFAULT false)
RETURNS TABLE (libelle text, jour date)
LANGUAGE plpgsql
AS $function$
…
L’utilisation est aussi simple que la version précédente.
Version avec RETURN QUERY :
C’est peut-être la version la plus compacte :
CREATE OR REPLACE FUNCTION vacances(annee integer,alsace_moselle boolean DEFAULT false)
RETURNS TABLE (libelle text, jour date)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY SELECT 'Jour de l''an'::text, (annee::text||'-01-01')::date ;
RETURN QUERY SELECT 'Pâques'::text, paques(annee)::date + 1 ;
RETURN QUERY SELECT 'Ascension'::text, ascension(annee)::date ;
RETURN QUERY SELECT 'Fête du travail'::text, (annee::text||'-05-01')::date ;
RETURN QUERY SELECT 'Victoire 1945'::text, (annee::text||'-05-08')::date ;
RETURN QUERY SELECT 'Fête nationale'::text, (annee::text||'-07-14')::date ;
RETURN QUERY SELECT 'Assomption'::text, (annee::text||'-08-15')::date ;
RETURN QUERY SELECT 'La toussaint'::text, (annee::text||'-11-01')::date ;
RETURN QUERY SELECT 'Armistice 1918'::text, (annee::text||'-11-11')::date ;
RETURN QUERY SELECT 'Noël'::text, (annee::text||'-12-25')::date ;
IF alsace_moselle THEN
RETURN QUERY SELECT 'Vendredi saint'::text, paques(annee)::date - 2 ;
RETURN QUERY SELECT 'Lendemain de Noël'::text, (annee::text||'-12-26')::date ;
END IF;
RETURN;
END;
$function$;
Ce TP utilise la base 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 magasin :
createdb magasin
curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump
pg_restore -d magasin /tmp/magasin.dump
# le message sur public préexistant est normal
rm -- /tmp/magasin.dump
Toutes les données sont dans deux schémas nommés magasin et facturation.
Écrire une requête permettant de renvoyer l’ensemble des produits (table
magasin.produits
) dont le volume ne dépasse pas 1 litre (les unités de longueur sont en mm, 1 litre = 1 000 000 mm³).
Concernant le volume des produits, la requête est assez simple :
Quel index permet d’optimiser cette requête ? (Utiliser une fonction est possible, mais pas obligatoire.)
L’option la plus simple est de créer l’index de cette façon, sans avoir besoin d’une fonction :
En général, il est plus propre de créer une fonction. On peut passer
la ligne entière en paramètre pour éviter de fournir 3 paramètres. Il
faut que cette fonction soit IMMUTABLE
pour être
indexable :
CREATE OR REPLACE function volume (p produits)
RETURNS numeric
AS $$
SELECT p.longueur * p.hauteur * p.largeur;
$$ language SQL
PARALLEL SAFE
IMMUTABLE ;
(Elle est même PARALLEL SAFE
pour la même raison qu’elle
est IMMUTABLE
: elle dépend uniquement des données de la
table.)
On peut ensuite indexer le résultat de cette fonction :
Il est ensuite possible d’écrire la requête de plusieurs manières, la fonction étant ici écrite en SQL et non en PL/pgSQL ou autre langage procédural :
SELECT * FROM produits WHERE longueur * hauteur * largeur < 1000000 ;
SELECT * FROM produits WHERE volume(produits) < 1000000 ;
En effet, l’optimiseur est capable de « regarder » à l’intérieur de la fonction SQL pour déterminer que les clauses sont les mêmes, ce qui n’est pas vrai pour les autres langages.
En revanche, la requête suivante, où la multiplication est faite dans un ordre différent, n’utilise pas l’index :
et c’est notamment pour cette raison qu’il est plus propre d’utiliser la fonction.
De part l’origine « relationnel-objet » de PostgreSQL, on peut même écrire la requête de la manière suivante :