• Quelle histoire !
  • parmi les plus vieux logiciels libres
  • et les plus sophistiqués
• Souvent cité comme exemple
  • qualité du code
  • indépendance des développeurs
  • réactivité de la communauté

1. Origines et historique du projet
2. Versions et feuille de route
3. Concepts de base
4. Fonctionnalités
5. Sponsors et références

• Comprendre les origines du projet
• Revoir les principes fondamentaux
• Découvrir des exemples concrets

• La licence
• L'origine du nom
• Les origines du projet
• Les principes
• La philosophie

• Licence PostgreSQL
  • BSD / MIT
  • http://www.postgresql.org/about/licence/
• Droit de
  • utiliser, copier, modifier, distribuer sans coût de licence
• Reconnu par l'Open Source Initiative
  • http://opensource.org/licenses/PostgreSQL
• Utilisé par un grand nombre de projets de l'écosystème

• Michael Stonebraker recode Ingres
• post « ingres » => postingres => postgres
• postgres => PostgreSQL

• Sécurité des données (ACID)
• Respect des normes (ISO SQL)
• Fonctionnalités
• Performances
• Simplicité du code

• Années 1970 : Ingres est développé à Berkeley
• 1985 : Postgres succède à Ingres
• 1995 : Ajout du langage SQL.
• 1996 : Postgres devient PostgreSQL
• 1996 : Création du PostgreSQL Global Development Group

Apparitions de la communauté internationale

• ~ 2000: Communauté japonaise
• 2004 : Communauté francophone
• 2006 : SPI
• 2007 : Communauté italienne
• 2008 : PostgreSQL Europe et US
• 2009 : Boom des PGDay

• Versions obsolètes : 9.2 et antérieures
• Versions actuelles : de 9.3 à 10
• Version en cours de développement : 11
• Versions dérivées

• 1996 : v1.0 -> première version publiée
• 2003 : v7.4 -> première version réellement stable
• 2005 : v8.0 -> arrivée sur Windows
• 2008 : v8.3 -> performance et fonctionnalités
• 2010 : v9.0 -> réplication intégrée
• 2016 : v9.6 -> parallélisation
• 2017 : v10 -> réplication logique

• Avant la version 10
  • X.Y : version majeure (8.4, 9.6)
  • X.Y.Z : version mineure (9.6.4)
• Après la version 10
  • X : version majeure (10, 11)
  • X.Y : version mineure (10.1)

• Dernières releases (9 novembre 2017) :
  • version 9.3.20
  • version 9.4.15
  • version 9.5.10
  • version 9.6.6
  • version 10.1
• Prochaine sortie, 8 février 2018

• Juillet 2009 - juillet 2014 (cette version n'est plus maintenue)
• Fonctions Window (clause OVER)
• CTE (vues non persistantes) et requêtes récursives
• Infrastructure SQL/MED (données externes)
• Paramètres par défaut et nombre variant de paramètres pour les fonctions
• Restauration parallélisée d'une sauvegarde
• Droits sur les colonnes

• Septembre 2010 - septembre 2015
• Hot Standby + Streaming Replication
• Contraintes d'exclusion
• Améliorations pour l'EXPLAIN
• Contrainte UNIQUE différable
• Droits par défaut, GRANT ALL
• Triggers sur colonne, et clause WHEN

• Septembre 2011 - septembre 2016
• Réplication synchrone
• Supervision et administration plus aisée de la réplication
• Gestion des extensions
• Support des tables distantes via SQL/MED
• Support des labels de sécurité
• Support des tables non journalisées

• Septembre 2012 - septembre 2017
• Réplication en cascade
  • pg_basebackup utilisable sur un esclave
  • réplication synchrone en mémoire seulement sur l'esclave
• Axé performances
  • amélioration de la scalabilité (lecture et écriture)
  • parcours d'index seuls
• Support de la méthode d'accès SP-GiST pour les index
• Support des types d'intervalles de valeurs
• Support du type de données JSON

• Septembre 2013 - septembre 2018 (?)
• Meilleure gestion de la mémoire partagée
• Support de la clause LATERAL dans un SELECT
• 4 To maxi au lieu de 2 Go pour les Large Objects
• COPY FREEZE
• Vues en mise à jour
• Vues matérialisées

• décembre 2014 - décembre 2019 (?)
• amélioration des index GIN (taille réduite et meilleures performances)
• nouveau type JSONB
• rafraîchissement sans verrou des vues matérialisées
• possibilité pour une instance répliquée de mémoriser la position des instances secondaires (replication slots)
• décodage logique (première briques pour la réplication logique intégrée)

• Janvier 2016 - Janvier 2021 (?)
• Row Level Security
• Index BRIN
• INSERT ... ON CONFLICT { UPDATE | IGNORE }
• SKIP LOCKED
• SQL/MED
  • import de schéma, héritage
• Supervision
  • amélioration de pg_stat_statements, ajout de pg_stat_ssl
• fonctions OLAP (GROUPING SETS, CUBE et ROLLUP)

• Septembre 2016 - Septembre 2021 (?)
• Parallélisation
  • parcours séquentiel, jointure, agrégation
• SQL/MED
  • tri distant, jointures impliquant deux tables distantes
• Réplication synchrone
• MVCC
  • VACUUM FREEZE, CHECKPOINT, ancien snapshot
• Maintenance

• Septembre 2017 - Septembre 2022 (?)
• Meilleure parallélisation
  • parcours d'index, jointure MergeJoin, sous-requêtes corrélées
• Réplication logique
• Partitionnement

• Versions 7
  • fondations
  • durabilité
• Versions 8
  • fonctionnalités
  • performances
• Versions 9
  • réplication physique
  • extensibilité
• Versions 10
  • réplication logique
  • parallélisation

• 9.2 et inférieures
  • Danger !
• 9.3
  • planifier une migration rapidement
• 9.4, 9.5 et 9.6
  • mise à jour uniquement
• 10
  • nouvelles installations et nouveaux développements

• Compatibilité Oracle
  • EnterpriseDB Postgres Plus
• Data warehouse
  • Greenplum
  • Netezza
  • Amazon RedShift

• ACID
• MVCC
• Transactions
• Journaux de transactions

• Atomicité (Atomic)
• Cohérence (Consistent)
• Isolation (Isolated)
• Durabilité (Durable)

• Le « noyau » de PostgreSQL
• Garantit ACID
• Permet les écritures concurrentes sur la même table

• Une lecture ne bloque pas une écriture
• Une écriture ne bloque pas une lecture
• Une écriture ne bloque pas les autres écritures...
• ...sauf pour la mise à jour de la même ligne.

• Intimement liées à ACID et MVCC :
• Une transaction est un ensemble d'opérations atomique
• Le résultat d'une transaction est « tout ou rien »
• SAVEPOINT disponible pour sauvegarde des modifications d'une transaction à un instant t

• Chaque transaction (et donc session) est isolée à un certain point :
  • elle ne voit pas les opérations des autres
  • elle s'exécute indépendamment des autres
• On peut spécifier le niveau d'isolation au démarrage d'une transaction:
  • BEGIN ISOLATION LEVEL xxx;
• Niveaux d'isolation supportés
  • read commited
  • repeatable read
  • serializable

• Chaque donnée est écrite 2 fois sur le disque !
• Sécurité quasiment infaillible
• Comparable à la journalisation des systèmes de fichiers

• Un seul sync sur le fichier de transactions
• Le fichier de transactions est écrit de manière séquentielle
• Les fichiers de données sont écrits de façon asynchrone
• Point In Time Recovery
• Réplication (WAL shipping)

• Développement
• Sécurité
• Le « catalogue » d'objets SQL

• PostgreSQL est une plate-forme de développement !
• 15 langages de procédures stockées
• Interfaces natives pour ODBC, JDBC, C, PHP, Perl, etc.
• API ouverte et documentée
• Un nouveau langage peut être ajouté sans recompilation de PostgreSQL

Création de types de données et

• de leurs fonctions
• de leurs opérateurs
• de leurs règles
• de leurs aggrégats
• de leurs méthodes d'indexations

• Fichier pg_hba.conf
• Filtrage IP
• Authentification interne (MD5, SCRAM-SHA-256)
• Authentification externe (identd, LDAP, Kerberos, ...)
• Support natif de SSL

• Excellent support du SQL ISO
• Objets SQL
  • tables, vues, séquences, triggers
• Opérations
  • jointures, sous-requêtes, requêtes CTE, requêtes de fenêtrage, etc.
• Unicode et plus de 50 encodages

• Espace de noms
• Concept différent des schémas d'Oracle
• Sous-ensemble de la base

• Masquer la complexité
  • structure : interface cohérente vers les données, même si les tables évoluent
  • sécurité : contrôler l'accès aux données de manière sélective
• Améliorations en 9.3 et 9.4
  • vues matérialisées
  • vues automatiquement modifiables

Les algorithmes suivants sont supportés :

• B-tree (par défaut)
• GiST / SP-GiST
• Hash
• GIN (version 8.2)
• BRIN (version 9.5)

• CHECK : prix > 0
• NOT NULL : id_client NOT NULL
• Unicité : id_client UNIQUE
• Clés primaires : UNIQUE NOT NULL ==> PRIMARY KEY (id_client)
• Clés étrangères : produit_id REFERENCES produits(id_produit)
• EXCLUDE : EXCLUDE USING gist (room WITH =, during WITH &&)

• Types créés par les utilisateurs
• Permettent de créer un nouveau type à partir d'un type de base
• En lui ajoutant des contraintes supplémentaires.

• Types créés par les utilisateurs
• Permettent de définir une liste ordonnée de valeurs de type chaîne de caractère pour ce type

• Opérations: INSERT, COPY, UPDATE, DELETE
• 8.4, trigger TRUNCATE
• 9.0, trigger pour une colonne, et/ou avec condition
• 9.1, trigger sur vue
• 9.3, trigger DDL
• 10, tables de transition
• Effet sur :
  • l'ensemble de la requête (FOR STATEMENT)
  • chaque ligne impactée (FOR EACH ROW)

• Sponsors
• Références
  • françaises
  • et internationales

• NTT (Streaming Replication)
• Crunchy Data Solutions (Tom Lane, Stephen Frost, Joe Conway, Greg Smith)
• Microsoft Skype Division (projet skytools)
• EnterpriseDB (Bruce Momjian, Dave Page...)
• 2nd Quadrant (Simon Riggs...)
• VMWare (Heikki Linnakangas)
• Dalibo
• Fujitsu
• Red Hat
• Sun Microsystems (avant le rachat par Oracle)

• Yahoo
• Météo France
• RATP
• CNAF
• Le Bon Coin
• Instagram
• Zalando
• TripAdvisor

Site de petites annonces :

• Base transactionnelle de 6 To
• 4è site le plus consulté en France (2017)
• 800 000 nouvelles annonces par jour
• 4 serveurs PostgreSQL en réplication
  • 160 cœurs par serveur
  • 2 To de RAM
  • 10 To de stockage flash

• Un projet de grande ampleur
• Un SGBD complet
• Souplesse, extensibilité
• De belles références
• Une solution stable, ouverte, performante et éprouvée

• Documentation officielle (préface)
• Articles fondateurs de M. Stonebracker
• Présentation du projet PostgreSQL

N'hésitez pas, c'est le moment !

• Vous apprendrez :
  • À choisir si vous voulez écrire du PL
  • À choisir votre langage PL
  • Les principes généraux des langages PL autres que PL/PgSQL
  • Les bases de PL/PgSQL

• Présentation du PL et des principes
• Présentations de PL/pgSQL et des autres langages PL
• Installation d'un langage PL
• Détails sur PL/pgSQL

• Comprendre les cas d'utilisation d'une fonction PL/PgSQL
• Choisir son langage PL en connaissance de cause
• Comprendre la différence entre PL/PgSQL et les autres langages PL
• Écrire une fonction simple en PL/PgSQL

• PL = Procedural Languages
• 3 langages activés par défaut : C, SQL et PL/PgSQL
  • PL/PgSQL n'était pas activé par défaut avant la 9.0

• Nombreux langages disponibles
• PL/Tcl, PL/Perl, PL/PerlU, PL/python, PL/php, PL/java, PL/mono, etc.

• Différence entre les langages de confiance (trusted) et les autres
• Langage de confiance
  • ne permet que l'accès à la base de données
  • donc pas d'accès aux systèmes de fichiers, aux sockets réseaux, etc.
• Trusted : PL/PgSQL, SQL, PL/Perl, PL/Python…
• Untrusted : PL/PerlU, C…

Les langages PL fournissent :

• Des fonctionnalités procédurales dans un univers relationnel
• Des fonctionnalités avancées du langage PL choisi
• Des performances de traitement souvent supérieures à celles du même code côté client

• Structure inspirée de l'ADA, donc proche du Pascal
• Ajout de structures de contrôle au langage SQL
• Peut effectuer des traitements complexes
• Hérite de tous les types, fonctions et opérateurs définis par les utilisateurs
• Est «Trusted»
• Et facile à utiliser

• Avantages des autres langages PL par rapport à PL/PgSQL :
  • Beaucoup plus de possibilités
  • Souvent plus performants pour la résolution de certains problèmes
• Mais un gros défaut :
  • Pas spécialisés dans le traitement de requêtes

• PL/pgsql compilé et installé par défaut
• Autres langages à compiler explicitement
• Paquets Debian et RedHat
  • PL/PgSQL par défaut
  • PL/Perl et PL/Python souvent
• L'installeur Windows contient aussi PL/PgSQL

Comment vérifier la présence de la bibliothèque :

find $(pg_config --libdir) -name "plpgsql.so"

find $(pg_config --pkglibdir) -name "plpgsql.so"

• Activé par défaut depuis PostgreSQL 9.0
• Commande similaire pour les autres langages

• Activer :

  CREATE EXTENSION plpgsql;

• Désactiver :

  DROP EXTENSION plpgsql;

• Cochez la case PL/pgsql pour que l'installeur Windows active le langage dans la base modèle template1

• Interroger le catalogue système pg_language
• Il contient une ligne par langage installé
• Un langage peut avoir lanpltrusted à false

• vérification dans psql avec la commande \dx
• pgAdmin affiche aussi cette information à partir du nœud « Langages » de la base de données
• phpPgAdmin le permet aussi

• Ordre SQL : CREATE FUNCTION
• Pas de procédure
• Le langage est un paramètre comme un autre

• Préciser les arguments :

  [ [ mode_argument ] [ nom_argument ] type_argument
  [ { DEFAULT | = } expr_defaut ] [, ...] ]

• mode_argument : en entrée (IN), en sortie (OUT), en entrée/sortie (INOUT) ou à nombre variant (VARIADIC)
• nom_argument : nom (libre et optionnel)
• type_argument : type (parmi tous les types de base et les types utilisateur)
• valeur par défaut : clause DEFAULT

• Il faut aussi indiquer un type de retour :

  RETURNS type_ret

• sauf si un ou plusieurs paramètres sont en mode OUT ou INOUT
• type_ret : type de la valeur en retour (parmi tous les types de base et les types utilisateurs)
• void est un type de retour valide
• Il est aussi possible d'indiquer un type table
• Peut renvoyer plusieurs lignes : clause SETOF

• Le langage de la fonction doit être précisé :

  LANGUAGE nomlang

• Dans notre cas, nous utiliserons plpgsql.
• Mais il est possible de créer des fonctions en plphp, plruby, voire des langages spécialisés comme plproxy.

• Mode de la fonction :

  IMMUTABLE | STABLE | VOLATILE

• Ce mode précise la «volatilité» de la fonction.

• Précision sur la façon dont la fonction gère les valeurs NULL :

  CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

• CALLED ON NULL INPUT : fonction appelée même si certains arguments sont NULL.
• RETURNS NULL ON NULL INPUT ou STRICT : la fonction renvoie NULL à chaque fois qu'au moins un argument est NULL.

• Précision sur la politique de sécurité :

  [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER

• Permet de déterminer l'utilisateur avec lequel sera exécutée la fonction
• Le «sudo» de la base de données
  • Potentiellement dangereux

• Précision du code à exécuter :

  AS 'definition' | AS 'fichier_obj', 'symbole_lien'

• Premier cas : chaîne « definition » contenant le code réel de la fonction
• Deuxième cas : fichier_obj est le nom de la bibliothèque, symbole_lien est le nom de la fonction dans le code source C

• Paramètre obsolète :

  WITH ( attribut [, ...] )

• isStrict, équivalent à STRICT ou RETURNS NULL ON NULL INPUT
• isCachable, équivalent à IMMUTABLE
• À remplacer par les syntaxes présentées précédemment

• COST cout_execution
  • coût estimé pour l'exécution de la fonction
• ROWS nb_lignes_resultat
  • nombre estimé de lignes que la fonction renvoie

• PARALLEL [UNSAFE | RESTRICTED | SAFE]
  • la fonction peut-elle être excutée en mode parallèle

• Permet d'insérer une facture associée à un client
• Si le client n'existe pas, une entrée est créée

• Même fonction en PL/PgSQL
• L'accès aux données est simple et naturel
• Les types de données SQL sont natifs
• La capacité de traitement est limitée par le langage
• Attention au nommage des variables et paramètres

• Le code de la fonction est structuré comme suit :
  • DECLARE, pour la déclaration des variables locales
  • BEGIN, pour indiquer le début du code de la fonction
  • END, pour en indiquer la fin
  • Instructions séparées par des points-virgules
  • Commentaires commençent par -- ou compris entre /* et */

• Labels de bloc possibles
• Plusieurs blocs d'exception possibles dans une fonction
• Permet de préfixer des variables avec le label du bloc
• De donner un label à une boucle itérative et de préciser de quelle boucle on veut sortir, quand plusieurs d'entre elles sont imbriquées

• Dans le cas d'une modification, CREATE OR REPLACE FUNCTION
• Une fonction est définie par son nom et ses arguments
• Si type de retour différent, la fonction doit d'abord être supprimée puis recréée

• Ordre SQL : DROP FUNCTION

• Arguments (en entrée) nécessaires à l'identification de la fonction à supprimer :

  DROP FUNCTION addition(integer, integer);
  DROP FUNCTION public.addition(integer, integer);

• L'utilisation des guillemets devient très rapidement complexe.
• Surtout lorsque le source se place directement entre guillemets
  • doublage de tous les guillemets du code source.
• Utilisation de $$ à la place des guillemets qui entourent les sources.
• Ou de n'importe quel autre marqueur

• Types natifs de PostgreSQL intégralement supportés
• Quelques types spécifiques à PL/PgSQL

• Déclarés en dehors du code de la fonction
• Possible d'associer directement un nom au type
• Les arguments sans nom peuvent être nommés avec l'argument ALIAS FOR dans la partie DECLARE

• Variables déclarées dans le source, dans la partie DECLARE :

  DECLARE
  nombre integer;
  contenu text;

• Les variables peuvent se voir associées une valeur initiale :

  nombre integer := 5;

• Clause supplémentaire CONSTANT :

  DECLARE
  valeur_fixe CONSTANT integer := 12;
  version_fonction CONSTANT text := '1.12';

• Possible de récupérer le type d'une autre variable avec %TYPE :

  quantite integer;
  total quantite%TYPE

• Possible de récupérer le type de la colonne d'une table :

quantite ma_table.ma_colonne%TYPE

• But :
  • utilisation de structures,
  • renvoi de plusieurs valeurs à partir d'une fonction

• Utiliser un type composite :

  CREATE TYPE ma_structure AS (un_entier integer,
  une_chaine text,
  ...);
  CREATE FUNCTION ma_fonction ()
  RETURNS ma_structure...;

• Possible d'utiliser le type composite défini par la ligne d'une table :

  CREATE FUNCTION ma_fonction () RETURNS integer
  AS '
  DECLARE
  ligne ma_table%ROWTYPE;
  ...

• Identique au type ROW
  • sauf que son type n'est connu que lors de son affectation
• Une variable de type RECORD peut changer de type au cours de l'exécution de la fonction, suivant les affectations réalisées

CREATE FUNCTION ma_fonction () RETURNS integer
AS '
DECLARE
  ligne RECORD;
  ...
BEGIN
  SELECT INTO ligne * FROM ma_premiere_table;
  -- traitement de la ligne
  FOR ligne IN SELECT * FROM ma_deuxieme_table LOOP
  -- traitement de cette nouvelle ligne
  ...

• Avant la version 8.1, paramètres en entrée uniquement
• À partir de la 8.1, trois modes de paramètres
  • IN : en entrée
  • OUT : en sortie
  • INOUT : en entrée et en sortie
• Fonction à plusieurs paramètres OUT : identique à une fonction qui renvoie un ROWTYPE pour un type composite créé préalablement
• Pas besoin de l'expression RETURN ou RETURN NEXT dans une fonction avec paramètre(s) OUT

• Concernent les opérations sur la base de données
  • extraction ou modification

• SELECT INTO :

  SELECT INTO un_entier 5;

• Opérateur := :

  un_entier := 5;
  un_entier := une_colonne FROM ma_table WHERE id = 5;

• Exécution de la requête en direct

• Utilisation de PERFORM :

  PERFORM * FROM ma_table WHERE une_colonne>0;

• Affectation de la variable FOUND si une ligne est affectée par l'instruction
• Permet aussi d'appeler une autre fonction sans en récupérer de résultat

• Affectation de la ligne renvoyée dans une variable de type RECORD ou ROW :

  SELECT * INTO ma_variable_ligne FROM ma_table...;

• Si plusieurs enregistrements renvoyés, seul le premier est récupéré
• Pour contrôler qu'un seul enregistrement est renvoyé, remplacer INTO par INTO STRICT
• Pour récupérer plus d'un enregistrement, écrire une boucle
• L'ordre est statique : on ne peut pas faire varier les colonnes retournées, la clause WHERE, les tables…

• Doit renvoyer un ensemble d'un type SETOF
• Chaque ligne sera récupérée par l'instruction RETURN NEXT

Exemple :

CREATE FUNCTION liste_entier (limite integer)
RETURNS SETOF integer
AS $$
BEGIN
  FOR i IN 1..limite LOOP
    RETURN NEXT i;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Utilisation de cette requête :

ma_base=# SELECT * FROM liste_entier(5);
 
liste_entier
--------------
1
2
3
4
5
(5 lignes)

• PERFORM <query>
• Permet l'exécution
  • d'un INSERT, UPDATE, DELETE (si la clause RETURNING n'est pas utilisée)
  • ou même SELECT, si le résultat importe peu

• Pour obtenir le nombre de lignes affectées :

  GET DIAGNOSTICS variable = ROW_COUNT;

• Instruction :

  EXECUTE '<chaine>' [INTO [STRICT] cible];

• Exécute la requête comprise dans la variable chaîne
• La variable chaine peut être construite à partir d'autres variables
• Cible contient le résultat de l'exécution de la requête dans le cas d'un résultat sur une seule ligne
• Mot clé USING supplémentaire depuis PostgreSQL 8.4.

• Sans STRICT, cible contient la première ligne d'un résultat multi-lignes ou NULL s'il n'y a pas de résultat.
• Avec STRICT, une exception est levée si le résultat ne contient aucune ligne (NO_DATA_FOUND) ou en contient plusieurs (TOO_MANY_ROWS).
• GET DIAGNOSTICS integer_var = ROW_COUNT

• Fonction quote_ident pour mettre entre guillemets un identifiant d'un objet PostgreSQL (table, colonne, etc.)
• Fonction quote_literal pour mettre entre guillemets une valeur (chaîne de caractères)
• Fonction quote_nullable pour mettre entre guillemets une valeur (chaîne de caractères), sauf NULL qui sera alors renvoyé sans les guillemets
• L'opérateur de concaténation || est à utiliser pour concaténer tous les morceaux de la requête.
• Ou utiliser la fonction format(...), équivalent de sprintf et disponible depuis 9.1

• EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
• Permet de créer une requête dynamique avec des variables de substitution

• Beaucoup plus lisible que des quote_nullable :

  EXECUTE 'SELECT count(*) FROM mytable
  WHERE inserted_by = $1 AND inserted <= $2'
  INTO c
  USING checked_user, checked_date;

• Le nombre de paramètres de la requête doit être fixe, ainsi que leur type
• Ne concerne pas les identifiants !

• Pourquoi du PL?
• Le but du PL est de pouvoir effectuer des traitements procéduraux.
• Nous allons donc maintenant aborder les structures de contrôle.

Exemple :

IF nombre = 0 THEN
  resultat := 'zero';
ELSEIF nombre > 0 THEN
   resultat := 'positif';
ELSEIF nombre < 0 THEN
   resultat := 'négatif';
ELSE
   resultat := 'indéterminé';
END IF;

Deux possibilités :

• 1ère :

  CASE variable
  WHEN expression THEN instructions
  ELSE instructions
  END CASE

• 2nde :

  CASE
  WHEN expression-booléene THEN instructions
  ELSE instructions
  END CASE

• Créer une boucle (label possible)
  • LOOP / END LOOP :
• Sortir de la boucle
  • EXIT [label] [WHEN expression_booléenne]
• Commencer une nouvelle itération de la boucle
  • CONTINUE [label] [WHEN expression_booléenne]

Exemple :

LOOP
  resultat := resultat + 1;
  EXIT WHEN resultat > 100;
  CONTINUE WHEN resultat < 50;
  resultat := resultat + 1;
END LOOP;

• Instruction :

  WHILE condition LOOP instructions END LOOP;

• Boucle jusqu'à ce que la condition soit fausse
• Label possible

• Synopsys :

  FOR variable in [REVERSE] entier1..entier2 [BY incrément]
  LOOP
  instructions
  END LOOP;

• variable va obtenir les différentes valeurs entre entier1 et entier2
• Label possible.

• L'option BY permet d'augmenter l'incrémentation :

  FOR variable in 1..10 BY 5...

• L'option REVERSE permet de faire défiler les valeurs en ordre inverse :

  FOR variable in REVERSE 10..1 ...

• Permet de boucler dans les lignes résultats d'une requête

• Exemple :

  FOR ligne IN SELECT * FROM ma_table LOOP
    instructions
  END LOOP;

• Label possible
• ligne de type RECORD, ROW ou liste de variables séparées par des virgules
• Utilise un curseur en interne

• Permet de boucler sur les éléments d'un tableau

• Syntaxe :

  FOREACH variable [SLICE n] IN ARRAY expression LOOP
      instructions
  END LOOP

• 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 variable
• label possible

• RETURN [expression]
• Renvoie cette expression à la requête appelante
• expression optionnelle si argument(s) déclarés OUT
  • RETURN lui-même optionnel si argument(s) déclarés OUT

• Fonction SETOF, aussi appelé fonction SRF (Set Returning Function)
• Fonctionne avec des types scalaires (normaux) et des types composites
• RETURN NEXT renvoie une ligne du SETOF

• Cette fonction s'appelle de cette façon :

  SELECT * FROM ma_fonction();

• expression de renvoi optionnelle si argument de mode OUT

• Fonctionne comme RETURN NEXT
• RETURN QUERY la_requete
• RETURN QUERY EXECUTE chaine_requete

• Ajoute un grand nombre de structure de contrôle (test, boucle, etc.)
• Facile à utiliser et à comprendre
• Attention à la compatibilité ascendante

• Documentation officielle
  • « Chapitre 40. PL/pgsql - Langage de procédures SQL »

N'hésitez pas, c'est le moment !

• Fonctions « variadic » et polymorphes
• Procédures trigger
• Curseurs
• Récupérer les erreurs
• Messages d'erreur dans les logs
• Sécurité
• Optimisation
• Problèmes fréquents

Objectifs :

• Connaître la majorité des possibilités de PL/PgSQL
• Les utiliser pour étendre les fonctionnalités de la base
• Écrire du code robuste
• Éviter les pièges de sécurité
• Savoir optimiser une fonction

• Permet de créer des fonctions avec un nombre d'arguments variables
• ... mais du même type

Récupérer le minimum d'une liste :

CREATE FUNCTION pluspetit(VARIADIC numeric[])
RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT pluspetit(10, -1, 5, 4.4);
 pluspetit
-----------
        -1
(1 row)

• En PL/PgSQL, cette fois-ci
• Démonstration de FOREACH xxx IN ARRAY aaa LOOP
• Précédemment, obligé de convertir le tableau en relation pour boucler (unnest)

• Typer les variables oblige à dupliquer les fonctions communes à plusieurs types
• PostgreSQL propose des types polymorphes
• Le typage se fait à l'exécution

• Remplace tout type de données simples et composites
  • pour les paramètres en entrée comme pour les paramètres en sortie
• Tous les paramètres et type de retour de type « anyelement » se voient attribués le même type
• Donc un seul type pour tous les anyelement autorisés
• Paramètre spécial $0 : du type attribué aux éléments « anyelement »

• « anyarray » remplace tout tableau de type de données simples et composites
  • pour les paramètres en entrée comme pour les paramètres en sortie
• Le typage se fait à l'exécution
• Tous les paramètres de type « anyarray » se voient attribués le même type

L'addition est un exemple fréquent :

CREATE OR REPLACE FUNCTION
  addition(var1 anyelement, var2 anyelement)
RETURNS anyelement
AS $$
DECLARE
  somme ALIAS FOR $0;
BEGIN
  somme := var1 + var2;
  RETURN somme;
END;
$$ LANGUAGE plpgsql;

• Attention lors de l'utilisation de type polymorphe...

# select addition('un'::text, 'mot'::text);
ERREUR:  L'opérateur n'existe pas : text + text
LIGNE 1 : SELECT   $1  +  $2
^
ASTUCE : Aucun opérateur correspond au nom donné et aux types d'arguments.
    Vous devez ajouter des conversions explicites de type.
REQUÊTE : SELECT   $1  +  $2
CONTEXTE : PL/pgSQL function "addition" line 4 at assignment

• Procédure stockée
• Action déclenchée par INSERT (incluant COPY), UPDATE, DELETE, TRUNCATE
• Mode par ligne ou par instruction
• Exécution d'une procédure stockée codée à partir de tout langage de procédure activée dans la base de données

• OLD :
  • type de données RECORD correspondant à la ligne avant modification
  • valable pour un DELETE et un UPDATE
• NEW :
  • type de données RECORD correspondant à la ligne après modification
  • valable pour un INSERT et un UPDATE

• Ces deux variables sont valables uniquement pour les triggers en mode ligne
  • pour les triggers en mode instruction, la version 10 propose les tables de transition
• Accès aux champs par la notation pointée
  • NEW.champ1 pour accéder à la nouvelle valeur de champ1

• TG_NAME : nom du trigger qui a déclenché l'appel de la fonction
• TG_WHEN : chaîne valant BEFORE, AFTER ou INSTEAD OF suivant le type du trigger
• TG_LEVEL : chaîne valant ROW ou STATEMENT suivant le mode du tigger
• TG_OP : chaîne valant INSERT, UPDATE, DELETE, TRUNCATE suivant l'opération qui a déclenché le trigger

• TG_RELID : OID de la table qui a déclenché le trigger
• TG_TABLE_NAME : nom de la table qui a déclenché le trigger
• TG_TABLE_SCHEMA : nom du schéma contenant la table qui a déclenché le trigger

• TG_NARGS : nombre d'arguments donnés à la fonction trigger
• TG_ARGV : les arguments donnés à la fonction trigger (le tableau commence à 0)

• Une fonction trigger a un type de retour spécial, trigger
• Trigger ROW, BEFORE :
  • Si retour NULL, annulation de l'opération, sans déclencher d'erreur
  • Sinon, poursuite de l'opération avec cette valeur de ligne
• Trigger ROW, AFTER : valeur de retour ignorée
• Trigger STATEMENT : valeur de retour ignorée
• Pour ces deux derniers cas, annulation possible dans le cas d'une erreur à l'exécution de la fonction (que vous pouvez déclencher dans le code du trigger)

• Horodater une opération sur une ligne :

CREATE TABLE ma_table (
id serial,
-- un certain nombre de champs informatifs
date_ajout timestamp,
date_modif timestamp);

CREATE TRIGGER permet quelques variantes :

• CREATE TRIGGER name WHEN ( condition )
• CREATE TRIGGER name BEFORE UPDATE OF colx ON my_table
• CREATE CONSTRAINT TRIGGER : exécuté qu'au moment de la validation de la transaction
• CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view

• Pour les triggers de type AFTER et de niveau STATEMENT
• Possibilité de stocker les lignes avant et/ou après modification
  • REFERENCING OLD TABLE
  • REFERENCING NEW TABLE

• Par exemple

  CREATE TRIGGER tr1
  AFTER DELETE ON t1
  REFERENCING OLD TABLE AS oldtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE log_delete();

• À partir de la version 10
• REFERENCING OLD TABLE
• REFERENCING NEW TABLE

• Par exemple

  CREATE TRIGGER tr1
    AFTER DELETE ON t1
    REFERENCING OLD TABLE AS oldtable
    FOR EACH STATEMENT
    EXECUTE PROCEDURE log_delete();

• Exécuter une requête en une fois peut ramener beaucoup de résultats
• Tout ce résultat est en mémoire
  • risque de dépassement mémoire
• La solution : les curseurs
• Un curseur permet d'exécuter la requête sur le serveur mais de ne récupérer les résultats que petit bout par petit bout
• Dans une transaction ou une fonction

• Avec le type refcursor :

  curseur refcursor;

• Avec la pseudo-instruction CURSOR FOR :

  curseur CURSOR FOR SELECT * FROM ma_table;

• Avec une requête paramétrée :

  curseur CURSOR (param integer) IS
  SELECT * FROM ma_table WHERE un_champ=param;

• Lier une requête à un curseur :

  OPEN curseur FOR requete

• Plan de la requête mis en cache

• Lier une requête dynamique à un curseur

  OPEN curseur FOR EXECUTE chaine_requete

• Instruction SQL : OPEN curseur(arguments)
• Permet d'ouvrir un curseur déjà lié à une requête
• Impossible d'ouvrir deux fois le même curseur
• Plan de la requête mise en cache
• Exemple :

curseur CURSOR FOR SELECT * FROM ma_table;
...
OPEN curseur;

• Instruction SQL :

  FETCH [ direction { FROM | IN } ] curseur INTO cible

• Récupère la prochaine ligne
• FOUND indique si cette nouvelle ligne a été récupérée
• Cible est :
  • une variable RECORD
  • une variable ROW
  • un ensemble de variables séparées par des virgules

• direction du FETCH :
  • NEXT, PRIOR
  • FIRST, LAST
  • ABSOLUTE nombre, RELATIVE nombre
  • nombre
  • ALL
  • FORWARD, FORWARD nombre, FORWARD ALL
  • BACKWARD, BACKWARD nombre, BACKWARD ALL

• Mise à jour d'une ligne d'un curseur :

  UPDATE une_table SET ... WHERE CURRENT OF curseur

• Suppression d'une ligne d'un curseur :

  DELETE FROM une_table WHERE CURRENT OF curseur

• Instruction SQL : CLOSE curseur
• Ferme le curseur
• Permet de récupérer de la mémoire
• Permet aussi de réouvrir le curseur

• Fonction renvoyant une valeur de type refcursor
• Permet donc de renvoyer plusieurs valeurs

Sans exceptions :

• Toute erreur provoque un arrêt de la fonction
• Toute modification suite à une instruction SQL (INSERT, UPDATE, DELETE) est annulée
• D'où l'ajout d'une gestion personnalisée des erreurs avec le concept des exceptions

• La fonction comporte un bloc supplémentaire, EXCEPTION :

DECLARE
  -- déclaration des variables locales
BEGIN
  -- instructions de la fonction
EXCEPTION
WHEN condition THEN
  -- instructions traitant cette erreur
WHEN condition THEN
  -- autres instructions traitant cette autre erreur
  -- etc.
END

• L'exécution de la fonction commence après le BEGIN
• Si aucune erreur ne survient, le bloc EXCEPTION est ignoré
• Si une erreur se produit
  • tout ce qui a été modifié dans la base dans le bloc est annulé
  • les variables gardent par contre leur état
  • l'exécution passe directement dans le bloc de gestion de l'exception

• Recherche d'une condition satisfaisante
• Si cette condition est trouvée
  • exécution des instructions correspondantes
• Si aucune condition n'est compatible
  • sortie du bloc BEGIN/END comme si le bloc d'exception n'existait pas
  • passage de l'exception au bloc BEGIN/END contenant (après annulation de ce que ce bloc a modifié en base)
• Dans un bloc d'exception, les instructions INSERT, UPDATE, DELETE de la fonction ont été annulées
• Dans un bloc d'exception, les variables locales de la fonction ont gardé leur ancienne valeur

• SQLSTATE : code d'erreur
• SQLERRM : message d'erreur
• par exemple :
  • Data Exception : division par zéro, overflow, argument invalide pour certaines fonctions, etc.
  • Integrity Constraint Violation : unicité, CHECK, clé étrangère, etc.
  • Syntax Error
  • PL/pgsql Error : RAISE EXCEPTION, pas de données, trop de lignes, etc.
• Les erreurs sont contenues dans des classes d'erreurs plus génériques, qui peuvent aussi être utilisées

• Envoyer une trace dans les journaux et/ou vers le client
  • RAISE niveau message
• Niveau correspond au niveau d'importance du message
  • DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
• Message est la trace à enregistrer
• Message dynamique... tout signe % est remplacé par la valeur indiquée après le message
• Champs DETAIL et HINT disponibles à partir de la version 8.4

Exemples :

RAISE WARNING 'valeur % interdite', valeur;

RAISE WARNING 'valeur % ambigue',
               valeur
               USING HINT = 'Controlez la valeur saisie en amont';

• Deux paramètres importants pour les traces
• log_min_messages
  • niveau minimum pour que la trace soit enregistrée dans les journaux
• client_min_messages
  • niveau minimum pour que la trace soit envoyée au client
• Dans le cas d'un RAISE NOTICE message, il faut avoir soit log_min_messages, soit client_min_messages, soit les deux à la valeur NOTICE au minimum.

• Annule le bloc en cours d'exécution : RAISE EXCEPTION message
• Sauf en cas de présence d'un bloc EXCEPTION gérant la condition RAISE_EXCEPTION
• message est la trace à enregistrer, et est dynamique... tout signe % est remplacé par la valeur indiquée après le message

Exemple :

RAISE EXCEPTION 'erreur interne';
-- La chose à ne pas faire !

Les exceptions non traitées «remontent»

• de bloc BEGIN/END imbriqués vers les blocs parents (fonctions appelantes comprises)
• Jusqu'à ce que personne ne puisse les traiter
• Voir note pour démonstration. Commençons par une fonction sans exception.

• Les erreurs remontent
• Cette fois-ci on rajoute un bloc PL pour intercepter l'erreur.

• Cette fois-ci, on rajoute un bloc PL indépendant pour gérer le second insert.

• Illustrons maintenant la remontée d'erreurs.
• Nous avons deux blocs imbriqués.
• Une erreur non prévue va se produire dans le bloc intérieur.

• L'exécution de la fonction dépend du droit EXECUTE
• Par défaut, ce droit est donné à la création de la fonction :
  • au propriétaire de la fonction
  • au groupe spécial PUBLIC

• Ce droit peut être donné avec l'instruction SQL GRANT :

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ]
                                                                            arg_type [, ...] ] ) ] [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

• Un droit peut être révoqué avec l'instruction SQL REVOKE :

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ]
                                            arg_type [, ...] ] ) ] [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

• SECURITY INVOKER : la fonction s'exécute avec les droits de l'utilisateur qui l'exécute
• SECURITY DEFINER : la fonction s'exécute avec les droits de l'utilisateur qui en est le propriétaire
  • Équivalent du sudo Unix
• Il faut impérativement sécuriser les variables d'environnement (surtout le search_path) en SECURITY DEFINER

• LEAKPROOF : indique au planificateur que la fonction ne peut pas faire fuiter d'information de contexte
  • réservé aux super-utilisateurs
  • si on la déclare telle, s'assurer que la fonction est véritablement sûre !
• option utile lorsque l'on utilise des vues avec l'option security_barrier

• Le code d'une fonction est visible par tout le monde
• Y compris ceux qui n'ont pas le droit d'exécuter la fonction
• Vous devez donc écrire un code robuste
  • pas espérer que, comme personne n'en a le code, personne ne trouvera de faille
• Surtout pour les fonctions SECURITY DEFINER

• Les paramètres d'une fonction doivent être considérés comme hostiles :
  • Ils contiennent des données non validées (qui appelle la fonction ?)
  • Ils peuvent, si l'utilisateur est imaginatif, être utilisés pour exécuter du code
• Utiliser quote_ident, quote_literal et quote_nullable

• Par défaut, PostgreSQL considère que les fonctions sont VOLATILE
• volatile : Fonction dont l'exécution ne peut ni ne doit être évitée

• immutable : Fonctions déterministes, dont le résultat peut être précalculé avant de planifier la requête.

• stable : Fonction ayant un comportement stable au sein d'un même ordre SQL.

• Fonction STRICT
• La fonction renvoit NULL si au moins un des arguments est NULL

• Un bloc contenant une clause EXCEPTION est plus coûteuse en entrée/sortie qu'un bloc sans :
  • un SAVEPOINT est créé à chaque fois pour pouvoir annuler le bloc uniquement.
• À utiliser avec parcimonie
• Un bloc BEGIN imbriqué a un coût aussi : un SAVEPOINT est créé à chaque fois.

Les requêtes statiques :

• Sont écrites «en dur» dans le code PL/PgSQL
• Donc pas d'EXECUTE ou PERFORM
• Sont préparées une fois par session, à leur première exécution
• Peuvent avoir un plan générique lorsque c'est jugé utile par le planificateur

Les requêtes dynamiques :

• Sont écrites avec un EXECUTE, PERFORM…
• Sont préparées à chaque exécution
• Ont un plan optimisé
• Sont donc plus coûteuses en planification
• Mais potentiellement plus rapides à l'exécution

Alors, statique ou dynamique ?

• Si la requête est simple, statique
  • peu de WHERE
  • peu ou pas de jointure
• Sinon dynamique

• Trois outils disponibles
  • un debugger
  • un pseudo profiler

• License Artistic 2.0
• À partir de PostgreSQL 8.2
• Installé par défaut avec le one-click installer
  • Mais non activé
• Compilation nécessaire pour les autres systèmes

• Récupérer le source avec git
• Copier le répertoire dans le répertoire contrib des sources de PostgreSQL
• Et les suivre étapes standards
  • make
  • make install

• Configurer shared_preload_libraries
  • shared_preload_libraries = 'plugin_debugger'
• Redémarrer PostgreSQL

• Installer l'extension pldbgapi :

  mabase# CREATE EXTENSION pldbgapi;

• Via pgAdmin (version 1.8 au minimum)

• Créé par Dalibo
• License BSD
• Compilation nécessaire

• Récupérer l'archive sur PGXN.org
• Décompresser l'archive puis : make USE_PGXS=1 && make USE_PGXS=1 install

• Permanente
  • shared_preload_libraries = 'log_functions'
  • Redémarrage de PostgreSQL
• Au cas par cas
  • LOAD 'log_functions'

• 5 paramètres en tout
• À configurer
  • dans Postgresql.conf
  • ou avec SET
• Ne pas oublier custom_variable_classes

• Exécuter des procédures stockées en PL/pgsql
• Lire les journaux applicatifs
  • grep très utile

« relation with OID XXXX does not exist »

• PL/pgsql met en cache les fonctions et leurs requêtes
• Si une fonction accède à une table temporaire, l'OID de la table est mise en cache
• Si la table est supprimée entre temps, le prochain appel à la fonction fera toujours référence à l'ancien OID
• Solution : utiliser EXECUTE, pour lequel la requête est analysée à chaque exécution
• Plus d'actualité depuis PostgreSQL 8.3.
• Attention avant PostgreSQL 9.3 si deux objets de même nom résident dans des schémas différents

• PL/PgSQL est un langage puissant
• Seul inconvénient : sa lenteur par rapport à d'autres PL comme PL/perl ou C
  • PL/perl est très efficace pour les traitements de chaîne uniquement
• Permet néanmoins de traiter la plupart des cas, de façon simple et efficace.

• Documentation officielle
  • « Chapitre 40. PL/pgsql - Langage de procédures SQL »

N'hésitez pas, c'est le moment !