Dalibo SCOP
Formation | Module P2 |
Titre | PL/pgSQL avancé |
Révision | 25.03 |
https://dali.bo/p2_pdf | |
EPUB | https://dali.bo/p2_epub |
HTML | https://dali.bo/p2_html |
Slides | https://dali.bo/p2_slides |
TP | https://dali.bo/p2_tp |
TP (solutions) | https://dali.bo/p2_solutions |
Vous trouverez en ligne les différentes versions complètes de ce document.
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.
Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.
Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.
Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode
Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.
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 13 à 17.
Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.
Sauf précision contraire, le système d’exploitation utilisé est Linux.
L’utilisation du mot clé VARIADIC
dans la déclaration
des routines permet d’utiliser un nombre variable d’arguments dans la
mesure où tous les arguments optionnels sont du même type de données.
Ces arguments sont passés à la fonction sous forme de tableau
d’arguments du même type.
VARIADIC tableau text[]
Il n’est pas possible d’utiliser d’autres arguments en entrée à la
suite d’un paramètre VARIADIC
.
Quelques explications sur cette fonction :
generate_subscript()
prend un tableau en premier
paramètre et la dimension de ce tableau (un tableau peut avoir plusieurs
dimensions), et elle retourne une série d’entiers allant du premier au
dernier indice du tableau dans cette dimensiongenerate_subscripts
est une SRF
(set-returning function
, retourne un SETOF
), g
est donc le nom de l’alias de table, et i le nom de l’alias de
colonne.En PL/pgSQL, il est possible d’utiliser une boucle
FOREACH
pour parcourir directement le tableau des arguments
optionnels.
CREATE OR REPLACE FUNCTION pluspetit(VARIADIC liste numeric[])
numeric
RETURNS
LANGUAGE plpgsqlAS $function$
DECLARE
numeric;
courant numeric;
plus_petit BEGIN
IN ARRAY liste LOOP
FOREACH courant IF plus_petit IS NULL OR courant < plus_petit THEN
:= courant;
plus_petit END IF;
END LOOP;
RETURN plus_petit;
END
$function$;
Auparavant, il fallait développer le tableau avec la fonction
unnest()
pour réaliser la même opération.
CREATE OR REPLACE FUNCTION pluspetit(VARIADIC liste numeric[])
numeric
RETURNS
LANGUAGE plpgsqlAS $function$
DECLARE
numeric;
courant numeric;
plus_petit BEGIN
FOR courant IN SELECT unnest(liste) LOOP
IF plus_petit IS NULL OR courant < plus_petit THEN
:= courant;
plus_petit END IF;
END LOOP;
RETURN plus_petit;
END
$function$;
Pour pouvoir utiliser la même fonction en utilisant des types
différents, il est nécessaire de la redéfinir avec les différents types
autorisés en entrée. Par exemple, pour autoriser l’utilisation de
données de type integer
ou float
en entrée et
retournés par une même fonction, il faut la dupliquer.
CREATE OR REPLACE FUNCTION
integer, var2 integer)
addition(var1 integer
RETURNS AS $$
DECLARE
integer;
somme BEGIN
:= var1 + var2;
somme RETURN somme;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION
float, var2 float)
addition(var1 float
RETURNS AS $$
DECLARE
float;
somme BEGIN
:= var1 + var2;
somme RETURN somme;
END;
$$ LANGUAGE plpgsql;
L’utilisation de types polymorphes permet d’éviter ce genre de duplications fastidieuses.
L’opérateur +
étant défini pour les entiers comme pour
les numeric
, la fonction ne pose aucun problème pour ces
deux types de données, et retourne une donnée du même type que les
données d’entrée.
Le typage n’étant connu qu’à l’exécution, c’est aussi à ce moment que se déclenchent les erreurs.
De même, l’affectation du type unique pour tous les éléments se fait sur la base du premier élément, ainsi :
# SELECT addition(1, 3.5);
ERROR: function addition(integer, numeric) does not exist
LIGNE 1 : SELECT addition(1, 3.5);
^
ASTUCE : No function matches the given name and argument types.
You might need to add explicit type casts.
génère une erreur car du premier argument est déduit le type
integer
, ce qui n’est évidement pas le cas du deuxième. Il
peut donc être nécessaire d’utiliser une conversion explicite pour
résoudre ce genre de problématique.
# SELECT addition(1::numeric, 3.5);
addition
----------
4.5
Un trigger est une spécification précisant que la base de données
doit exécuter une fonction particulière quand un certain type
d’opération est traité. Les fonctions trigger peuvent être définies pour
s’exécuter avant ou après une commande INSERT
,
UPDATE
, DELETE
ou TRUNCATE
.
La fonction trigger doit être définie avant que le trigger lui-même
puisse être créé. La fonction trigger doit être déclarée comme une
fonction ne prenant aucun argument et retournant un type
trigger
.
Une fois qu’une fonction trigger est créée, le trigger est créé avec
CREATE TRIGGER
. La même fonction trigger est utilisable par
plusieurs triggers.
Un trigger TRUNCATE
ne peut utiliser que le mode par
instruction, contrairement aux autres triggers pour lesquels vous avez
le choix entre « par ligne » et « par instruction ».
Enfin, l’instruction COPY
est traitée comme s’il
s’agissait d’une commande INSERT
.
À noter que les problématiques de visibilité et de volatilité depuis un trigger sont assez complexes dès lors que l’on lit ou modifie les données. Voir la documentation pour plus de détails à ce sujet.
Vous pourriez aussi rencontrer dans du code la variable
TG_RELNAME
. C’est aussi le nom de la table qui a déclenché
le trigger. Attention, cette variable est obsolète, il est préférable
d’utiliser maintenant TG_TABLE_NAME
.
La fonction trigger est déclarée sans arguments mais il est possible de lui en passer dans la déclaration du trigger. Dans ce cas, il faut utiliser les deux variables ci-dessus pour y accéder. Attention, tous les arguments sont convertis en texte. Il faut donc se cantonner à des informations simples, sous peine de compliquer le code.
CREATE OR REPLACE FUNCTION verifier_somme()
trigger AS $$
RETURNS DECLARE
integer;
fact_limit varchar;
arg_color BEGIN
:= TG_ARGV[0];
fact_limit
IF NEW.somme > fact_limit THEN
'La facture % necessite une verification. '
RAISE NOTICE 'La somme % depasse la limite autorisee de %.',
NEW.idfact, NEW.somme, fact_limit;
END IF;
NEW.datecreate := current_timestamp;
return NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trig_verifier_debit
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE verifier_somme(400);
CREATE TRIGGER trig_verifier_credit
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE verifier_somme(800);
Une fonction trigger retourne le type spécial trigger
.
Pour cette raison, ces fonctions ne peuvent être utilisées que dans le
contexte d’un ou plusieurs triggers.
Pour pouvoir être utilisée comme valeur de retour dans la fonction
(avec RETURN
), une variable doit être de structure
identique à celle de la table sur laquelle le trigger a été déclenché.
Les variables spéciales OLD
(ancienne valeur avant
application de l’action à l’origine du déclenchement) et
NEW
(nouvelle valeur après application de l’action) sont
également disponibles, utilisables et même modifiables.
Trigger BEFORE … FOR EACH ROW :
La valeur de retour d’un trigger de type ligne déclenché avant l’opération peut changer complètement l’effet de la commande.
Il est possible de changer les valeurs de la nouvelle ligne créée par
une action INSERT
ou UPDATE
en modifiant
NEW
directement, puis en faisant
RETURN NEW
.
Il est également possible d’annuler complètement l’action sur la
ligne, sans erreur en retournant NULL
. Cette annulation ne
vaut que pour la ligne, et la transaction continue normalement. Les
triggers ne sont plus déclenchés pour cette ligne.
Attention ! Dans le cas d’une fonction trigger
BEFORE DELETE
, il faut prendre en compte que
NEW
contient NULL
, et en conséquence
RETURN NEW
provoque l’annulation du DELETE
!
Dans ce cas, la convention est de faire un RETURN OLD
pour
poursuivre, ou RETURN NULL
pour une annulation
explicite.
Trigger AFTER … FOR EACH ROW et FOR EACH STATEMENT :
En revanche, la valeur de retour utilisée n’a pas d’effet dans les
cas des triggers ROW
et AFTER
, et des triggers
FOR EACH STATEMENT
(par exemple les triggers sur
TRUNCATE
ou les triggers utilisant les tables de
transition). La documentation conseille alors de toujours retourner
NULL
.
Dans ces cas, il reste possible d’annuler l’action en remontant une
erreur à l’exécution de la fonction (RAISE EXCEPTION
). Mais
cela va annuler toute la transaction.
UPDATE
.
Encore un moyen de simplifier le code et de gagner en performances en
évitant les déclenchements inutiles.INSTEAD OF
, qui permet de programmer de façon efficace les
INSERT
/UPDATE
/DELETE
/TRUNCATE
sur les vues. Auparavant, il fallait passer par le système de règles
(RULES
), complexe et sujet à erreurs.Dans le cas d’un trigger en mode instruction, il n’est pas possible
d’utiliser les variables OLD
et NEW
car elles
ciblent une seule ligne. Pour cela, le standard SQL parle de tables de
transition.
La version 10 de PostgreSQL permet donc de rattraper le retard à ce sujet par rapport au standard SQL et SQL Server.
Voici un exemple de leur utilisation.
Nous allons créer une table t1 qui aura le trigger et une table archives qui a pour but de récupérer les enregistrements supprimés de la table t1.
CREATE TABLE t1 (c1 integer, c2 text);
CREATE TABLE archives (id integer GENERATED ALWAYS AS IDENTITY,
timestamp DEFAULT now(),
dlog integer, t1_c2 text); t1_c1
Maintenant, il faut créer le code de la procédure stockée :
CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO archives (t1_c1, t1_c2) SELECT c1, c2 FROM oldtable;
RETURN null;
END
$$;
Et ajouter le trigger sur la table t1 :
CREATE TRIGGER tr1
AFTER DELETE ON t1
REFERENCING OLD TABLE AS oldtable
FOR EACH STATEMENT
EXECUTE PROCEDURE log_delete();
Maintenant, insérons un million de ligne dans t1 et supprimons-les :
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
DELETE FROM t1;
Time: 2141.871 ms (00:02.142)
La suppression avec le trigger prend 2 secondes. Il est possible de
connaître le temps à supprimer les lignes et le temps à exécuter le
trigger en utilisant l’ordre EXPLAIN ANALYZE
:
TRUNCATE archives;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
EXPLAIN (ANALYZE) DELETE FROM t1;
QUERY PLAN
--------------------------------------------------------------------------
Delete on t1 (cost=0.00..14241.98 rows=796798 width=6)
time=781.612..781.612 rows=0 loops=1)
(actual -> Seq Scan on t1 (cost=0.00..14241.98 rows=796798 width=6)
time=0.113..104.328 rows=1000000 loops=1)
(actual time: 0.079 ms
Planning Trigger tr1: time=1501.688 calls=1
time: 2287.907 ms
Execution 5 rows) (
Donc la suppression des lignes met 0,7 seconde alors que l’exécution du trigger met 1,5 seconde.
Pour comparer, voici l’ancienne façon de faire (configuration d’un trigger en mode ligne) :
CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO archives (t1_c1, t1_c2) VALUES (old.c1, old.c2);
RETURN null;
END
$$;
DROP TRIGGER tr1 ON t1;
CREATE TRIGGER tr1
AFTER DELETE ON t1
FOR EACH ROW
EXECUTE PROCEDURE log_delete();
TRUNCATE archives;
TRUNCATE t1;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
DELETE FROM t1;
Time: 8445.697 ms (00:08.446)
TRUNCATE archives;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
EXPLAIN (ANALYZE) DELETE FROM t1;
QUERY PLAN
--------------------------------------------------------------------------
Delete on t1 (cost=0.00..14241.98 rows=796798 width=6)
time=1049.420..1049.420 rows=0 loops=1)
(actual -> Seq Scan on t1 (cost=0.00..14241.98 rows=796798 width=6)
time=0.061..121.701 rows=1000000 loops=1)
(actual time: 0.096 ms
Planning Trigger tr1: time=7709.725 calls=1000000
time: 8825.958 ms
Execution 5 rows) (
Donc avec un trigger en mode ligne, la suppression du million de lignes met presque 9 secondes à s’exécuter, dont 7,7 pour l’exécution du trigger. Sur le trigger en mode instruction, il faut compter 2,2 secondes, dont 1,5 sur le trigger. Les tables de transition nous permettent de gagner en performance.
Le gros intérêt des tables de transition est le gain en performance que cela apporte.
À noter que la notion de curseur existe aussi en SQL pur, sans passer
par une routine PL/pgSQL. On les crée en utilisant la commande
DECLARE
, et les règles de manipulation sont légèrement
différentes (on peut par exemple créer un curseur
WITH HOLD
, qui persistera après la fin de la transaction).
Voir la documentation pour plus d’informations à ce sujet :
https://docs.postgresql.fr/current/sql-declare.html
La première forme permet la création d’un curseur non lié à une requête.
Voici un exemple de lien entre une requête et un curseur :
OPEN curseur FOR SELECT * FROM ma_table;
Et voici un exemple d’utilisation d’une requête dynamique :
OPEN curseur FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_TABLE_NAME);
Attention, ces différentes syntaxes ne modifient pas les données dans le curseur en mémoire, mais font réellement la modification dans la table. L’emplacement actuel du curseur est utilisé ici pour identifier la ligne correspondante à mettre à jour.
Voici un exemple d’utilisation d’une référence de curseur retournée par une fonction :
CREATE FUNCTION consult_all_stock(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM stock;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
-- doit être dans une transaction pour utiliser les curseurs.
BEGIN;
SELECT * FROM consult_all_stock('cursor_a');
ALL FROM cursor_a;
FETCH COMMIT;
Toutes les erreurs sont référencées dans la documentation
Attention, des codes d’erreurs nouveaux apparaissent à chaque version.
La classe data_exception contient de nombreuses erreurs, comme datetime_field_overflow, invalid_escape_character, invalid_binary_representation… On peut donc, dans la déclaration de l’exception, intercepter toutes les erreurs de type data_exception d’un coup, ou une par une.
L’instruction GET STACKED DIAGNOSTICS
permet d’avoir une
vision plus précise de l’erreur récupéré par le bloc de traitement des
exceptions. La liste de toutes les informations que l’on peut collecter
est disponible dans
la documentation .
La démonstration ci-dessous montre comment elle peut être utilisée.
CREATE TABLE t5(c1 integer PRIMARY KEY);
# CREATE TABLE
INSERT INTO t5 VALUES (1);
# INSERT 0 1
CREATE OR REPLACE FUNCTION test(INT4) RETURNS void AS $$
# DECLARE
v_state TEXT;
v_msg TEXT;
v_detail TEXT;
v_hint TEXT;
v_context TEXT;BEGIN
BEGIN
INSERT INTO t5 (c1) VALUES ($1);
EXCEPTION WHEN others THEN
GET STACKED DIAGNOSTICS= RETURNED_SQLSTATE,
v_state = MESSAGE_TEXT,
v_msg = PG_EXCEPTION_DETAIL,
v_detail = PG_EXCEPTION_HINT,
v_hint = PG_EXCEPTION_CONTEXT;
v_context 'Et une exception :
raise notice E state : %
message: %
detail : %
hint : %
context: %', v_state, v_msg, v_detail, v_hint, v_context;
END;
RETURN;
END;
$$ LANGUAGE plpgsql;SELECT test(2);
#
test------
1 row)
(
SELECT test(2);
# exception :
NOTICE: Et une 23505
state : key value violates unique constraint "t5_pkey"
message: duplicate Key (c1)=(2) already exists.
detail :
hint :context: SQL statement "INSERT INTO t5 (c1) VALUES ($1)"
/pgSQL function test(integer) line 10 at SQL statement
PL
test------
1 row) (
Il convient de noter qu’un message envoyé de cette manière ne fera
pas partie de l’éventuel résultat d’une fonction, et ne sera donc pas
exploitable en SQL. Pour cela, il faut utiliser l’instruction
RETURN
avec un type de retour approprié.
Le traitement des messages de ce type et leur destination d’envoi
sont contrôlés par le serveur à l’aide des paramètres
log_min_messages
et client_min_messages
.
Les autres niveaux pour RAISE
ne sont que des messages,
sans déclenchement d’exception.
Le rôle d’une exception est d’intercepter une erreur pour exécuter un traitement permettant soit de corriger l’erreur, soit de remonter une erreur pertinente. Intercepter un problème pour retourner « erreur interne » n’est pas une bonne idée.
Démonstration en plusieurs étapes :
CREATE TABLE ma_table (
# id integer unique
);CREATE TABLE
CREATE OR REPLACE FUNCTION public.demo_exception()
#
RETURNS void
LANGUAGE plpgsqlAS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
END
$function$;CREATE FUNCTION
SELECT demo_exception();
# key value violates unique constraint "ma_table_id_key"
ERROR: duplicate Key (id)=(1) already exists.
DETAIL: CONTEXT: SQL statement "INSERT INTO ma_table VALUES (1)"
/pgSQL function demo_exception() line 6 at SQL statement PL
Une exception a été remontée avec un message explicite.
SELECT * FROM ma_table ;
#
a---
0 row) (
La fonction a bien été annulée.
CREATE OR REPLACE FUNCTION public.demo_exception()
#
RETURNS void
LANGUAGE plpgsqlAS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
EXCEPTION WHEN unique_violation THEN
'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
RAISE NOTICE END
$function$;CREATE FUNCTION
SELECT demo_exception();
# 'unicite, mais celle-ci n'est pas grave
NOTICE: violation dkey value violates unique constraint "ma_table_id_key"
NOTICE: erreur: duplicate
demo_exception----------------
1 row) (
L’erreur est bien devenue un message de niveau
NOTICE
.
SELECT * FROM ma_table ;
#
a---
0 row) (
La table n’en reste pas moins vide pour autant puisque le bloc a été annulé.
Voici une nouvelle version de la fonction :
CREATE OR REPLACE FUNCTION public.demo_exception()
#
RETURNS void
LANGUAGE plpgsqlAS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- L'operation suivante pourrait échouer.
-- Il ne faut pas perdre le travail effectué jusqu'à ici
BEGIN
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
EXCEPTION WHEN unique_violation THEN
-- Cette exception est bien celle du bloc imbriqué
'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
RAISE NOTICE END; -- Fin du bloc imbriqué
END
$function$;CREATE FUNCTION
SELECT demo_exception();
# 'unicite, mais celle-ci n'est pas grave
NOTICE: violation dkey value violates unique constraint "ma_table_id_key"
NOTICE: erreur: duplicate
demo_exception----------------
1 row) (
En apparence, le résultat est identique.
SELECT * FROM ma_table ;
#
a---
1
1 row) (
Mais cette fois-ci, le bloc BEGIN
parent n’a pas eu
d’exception, il s’est donc bien terminé.
On commence par ajouter une contrainte sur la colonne pour empêcher les valeurs supérieures ou égales à 10 :
ALTER TABLE ma_table ADD CHECK (id < 10 ) ;
# ALTER TABLE
Puis, on recrée la fonction de façon à ce qu’elle déclenche cette erreur dans le bloc le plus bas, et la gère uniquement dans le bloc parent :
CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsqlAS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- L'operation suivante pourrait échouer.
-- Il ne faut pas perdre le travail effectué jusqu'à ici
BEGIN
-- Va déclencher une erreur de violation de check (col < 10)
INSERT INTO ma_table VALUES (100);
EXCEPTION WHEN unique_violation THEN
-- Cette exception est bien celle du bloc imbriqué
'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
RAISE NOTICE END; -- Fin du bloc imbriqué
EXCEPTION WHEN check_violation THEN
'violation de contrainte check';
RAISE NOTICE EXCEPTION 'mais on va remonter une exception à l''appelant, '
RAISE 'juste pour le montrer';
END
$function$;
Exécutons la fonction :
SELECT demo_exception();
# key value violates unique constraint "ma_table_id_key"
ERROR: duplicate Key (id)=(1) already exists.
DETAIL: CONTEXT: SQL statement "INSERT INTO ma_table VALUES (1)"
/pgSQL function demo_exception() line 4 at SQL statement PL
C’est normal, nous avons toujours l’enregistrement à 1 du test précédent. L’exception se déclenche donc dans le bloc parent, sans espoir d’interception: nous n’avons pas d’exception pour lui.
Nettoyons donc la table, pour reprendre le test :
TRUNCATE ma_table ;
# TRUNCATE TABLE
SELECT demo_exception();
# check
NOTICE: violation de contrainte on va remonter une exception à l'appelant, juste pour le montrer
ERREUR: mais CONTEXT: PL/pgSQL function demo_exception() line 17 at RAISE
Le gestionnaire d’exception qui intercepte l’erreur est bien ici celui de l’appelant. Par ailleurs, comme nous retournons nous-même une exception, la requête ne retourne pas de résultat, mais une erreur : il n’y a plus personne pour récupérer l’exception, c’est donc PostgreSQL lui-même qui s’en charge.
La levée d’une exception revient à poser un SAVEPOINT
pendant la transaction (une forme de sous-transaction).
En cas de trop nombreuses levées d’exception, un des caches dédiés en
mémoire partagée (SLRU) de PostgreSQL peut saturer et poser des soucis
de performance (le wait event SubtransSLRU
apparaît dans pg_stat_activity.wait_event
). Depuis
PostgreSQL 17 le DBA peut monter ce cache, avec le paramètre
subtransaction_buffers
.
Rappelons qu’une fonction s’exécute par défaut avec les droits
d’accès de l’utilisateur qui l’exécute ; et que pour donner accès à ses
données, un utilisateur peut mettre à disposition des autres des
fonctions en mode SECURITY DEFINER
qui profiteront de ses
droits. Il est impératif de réinitialiser l’environnement et en
particulier le search_path
pour éviter des attaques de la
part d’utilisateurs malveillants, et de restreindre au maximum l’accès à
ces fonctions.
Certains utilisateurs créent des vues pour filtrer des lignes, afin
de restreindre la visibilité sur certaines données. Or, cela peut se
révéler dangereux si un utilisateur malintentionné a la possibilité de
créer une fonction car il peut facilement contourner cette sécurité si
cette option n’est pas utilisée, notamment en jouant sur des paramètres
de fonction comme COST
, qui permet d’indiquer au
planificateur un coût estimé pour la fonction.
En indiquant un coût extrêmement faible, le planificateur aura
tendance à réécrire la requête, et à déplacer l’exécution de la fonction
dans le code même de la vue, avant l’application des filtres
restreignant l’accès aux données : la fonction a donc accès a tout le
contenu de la table, et peut faire fuiter des données normalement
inaccessibles, par exemple à travers l’utilisation de la commande
RAISE
.
L’option security_barrier
des vues dans PostgreSQL
bloque ce comportement du planificateur, mais en conséquence empêche le
choix de plans d’exécutions potentiellement plus performants. Déclarer
une fonction avec l’option LEAKPROOF
permet d’indiquer à
PostgreSQL que celle-ci ne peut pas occasionner de fuite d’informations.
Ainsi, le planificateur de PostgreSQL sait qu’il peut en optimiser
l’exécution. Cette option n’est accessible qu’aux superutilisateurs.
La méta-commande psql \df+ public.addition
permet
également d’obtenir cette information.
Voici un exemple simple :
CREATE TABLE ma_table_secrete1 (b integer, a integer);
INSERT INTO ma_table_secrete1 SELECT i,i from generate_series(1,20) i;
CREATE OR REPLACE FUNCTION demo_injection ( param1 text, value1 text )
RETURNS SETOF ma_table_secrete1
LANGUAGE plpgsqlDEFINER
SECURITY AS $function$
-- Cette fonction prend un nom de colonne variable
-- et l'utilise dans une clause WHERE
-- Il faut donc une requête dynamique
-- Par contre, mon utilisateur 'normal' qui appelle
-- n'a droit qu'aux enregistrements où a<10
DECLARE
ma_requete text;record;
ma_ligne BEGIN
:= 'SELECT * FROM ma_table_secrete1 WHERE ' || param1 || ' = ' ||
ma_requete || ' AND a < 10';
value1 RETURN QUERY EXECUTE ma_requete;
END
$function$;
SELECT * from demo_injection ('b','2');
#
a | b---+---
2 | 2
1 row)
(
SELECT * from demo_injection ('a','20');
#
a | b---+---
0 row) (
Tout va bien, elle effectue ce qui est demandé.
Par contre, elle effectue aussi ce qui n’est pas prévu :
SELECT * from demo_injection ('1=1 --','');
#
a | b-----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
11 | 11
12 | 12
13 | 13
14 | 14
15 | 15
16 | 16
17 | 17
18 | 18
19 | 19
20 | 20
20 lignes) (
Cet exemple est évidemment simplifié.
Une règle demeure : ne jamais faire confiance aux paramètres d’une
fonction. Au minimum, un quote_ident
pour param1 et un
quote_literal
pour val1 étaient obligatoires, pour se
protéger de ce genre de problèmes.
Les fonctions de ce type sont susceptibles de renvoyer un résultat
différent à chaque appel, comme par exemple random()
ou
setval()
.
Toute fonction ayant des effets de bords doit être qualifiée
volatile
dans le but d’éviter que PostgreSQL utilise un
résultat intermédiaire déjà calculé et évite ainsi d’exécuter le code de
la fonction.
À noter qu’il est possible de « forcer » le pré-calcul du résultat
d’une fonction volatile dans une requête SQL en utilisant une
sous-requête. Par exemple, dans l’exemple suivant, random()
est exécutée pour chaque ligne de la table ma_table
, et
renverra donc une valeur différente par ligne :
SELECT random() FROM ma_table;
Par contre, en utilisant une sous-requête, l’optimiseur va
pré-calculer le résultat de random()
… l’exécution sera donc
plus rapide, mais le résultat différent, puisque la même valeur sera
affichée pour toutes les lignes !
SELECT ( SELECT random() ) FROM ma_table;
Certaines fonctions que l’on écrit sont déterministes. C’est-à-dire qu’à paramètre(s) identique(s), le résultat est identique.
Le résultat de telles fonctions est alors remplaçable par son résultat avant même de commencer à planifier la requête.
Voici un exemple qui utilise cette particularité :
create function factorielle (a integer) returns bigint as
$$declare
result bigint;begin
if a=1 then
return 1;
else
return a*(factorielle(a-1));
end if;
end;
$$ language plpgsql immutable;
CREATE TABLE test (a bigint UNIQUE);
# CREATE TABLE
INSERT INTO test SELECT generate_series(1,1000000);
# INSERT 0 1000000
ANALYZE test;
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
# QUERY PLAN
--------------------------------------------------------------------
Scan on test (cost=0.00..16925.00 rows=1000000 width=8)
Seq time=0.032..130.921 rows=1000000 loops=1)
(actual Filter: (a < '479001600'::bigint)
time: 896.039 ms
Planning time: 169.954 ms
Execution 4 rows) (
La fonction est exécutée une fois, remplacée par sa constante, et la requête est ensuite planifiée.
Si on déclare la fonction comme stable :
EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
# QUERY PLAN
----------------------------------------------------------
Index Only Scan using test_a_key on test
cost=0.68..28480.67 rows=1000000 width=8)
(time=0.137..115.592 rows=1000000 loops=1)
(actual Index Cond: (a < factorielle(12))
Heap Fetches: 0
time: 4.682 ms
Planning time: 153.762 ms
Execution 5 rows) (
La requête est planifiée sans connaître factorielle(12)
,
donc avec une hypothèse très approximative sur la cardinalité.
factorielle(12)
est calculé, et la requête est exécutée.
Grâce au Index Only Scan
, le requête s’effectue
rapidement.
Si on déclare la fonction comme volatile :
EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
# QUERY PLAN
-----------------------------------------------------------------------
Scan on test (cost=0.00..266925.00 rows=333333 width=8)
Seq time=1.005..57519.702 rows=1000000 loops=1)
(actual Filter: (a < factorielle(12))
time: 0.388 ms
Planning time: 57573.508 ms
Execution 4 rows) (
La requête est planifiée, et factorielle(12) est calculé pour chaque enregistrement de la table, car on ne sait pas si elle retourne toujours le même résultat.
Ces fonctions retournent la même valeur pour la même requête SQL, mais peuvent retourner une valeur différente dans la prochaine instruction.
Il s’agit typiquement de fonctions dont le traitement dépend d’autres
valeurs dans la base de données, ou bien de réglages de configuration.
Les fonctions comme to_char()
, to_date()
sont
STABLE
et non IMMUTABLE
car des paramètres de
configuration (locale utilisée pour to_char()
,
timezone pour les fonctions temporelles, etc.) pourraient
influer sur le résultat.
À noter au passage que les fonctions de la famille de
current_timestamp
(et donc le fréquemment utilisé
now()
) renvoient de plus une valeur constante au sein d’une
même transaction.
PostgreSQL refusera de déclarer comme STABLE
toute
fonction modifiant des données : elle ne peut pas être stable si elle
modifie la base.
Les fonctions définies comme STRICT
ou
RETURNS NULL ON NULL INPUT
annule l’exécution de la requête
si l’un des paramètres passés est NULL
. Dans ce cas, la
fonction est considérée comme ayant renvoyé NULL
.
Si l’on reprend l’exemple de la fonction
factorielle()
:
create or replace function factorielle (a integer) returns bigint as
$$declare
result bigint;begin
if a=1 then
return 1;
else
return a*(factorielle(a-1));
end if;
end;
$$ language plpgsql immutable STRICT;
on obtient le résultat suivant si elle est exécutée avec la valeur
NULL
passée en paramètre :
EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(NULL);
# QUERY PLAN
---------------------------------------------------
cost=0.00..0.00 rows=0 width=8)
Result (time=0.002..0.002 rows=0 loops=1)
(actual -Time Filter: false
Onetime: 0.100 ms
Planning time: 0.039 ms
Execution 4 rows) (
Avant la version 9.2, un plan générique (indépendant des paramètres de l’ordre SQL) était systématiquement généré et utilisé. Ce système permet de gagner du temps d’exécution si la requête est réutilisée plusieurs fois, et qu’elle est coûteuse à planifier.
Toutefois, un plan générique n’est pas forcément idéal dans toutes les situations, et peut conduire à des mauvaises performances.
Par exemple :
SELECT * FROM ma_table WHERE col_pk = param_function ;
est un excellent candidat à être écrit statiquement : le plan sera toujours le même : on attaque l’index de la clé primaire pour trouver l’enregistrement.
SELECT * FROM ma_table WHERE col_timestamp > param_function ;
est un moins bon candidat : le plan, idéalement, dépend de param_function : on ne parcourt pas la même fraction de la table suivant la valeur de param_function.
Par défaut, un plan générique ne sera utilisé dès la première exécution d’une requête statique que si celle-ci ne dépend d’aucun paramètre. Dans le cas contraire, cela ne se produira qu’au bout de plusieurs exécutions de la requête, et seulement si le planificateur détermine que les plans spécifiques utilisés n’apportent pas d’avantage par rapport au plan générique.
L’écriture d’une requête dynamique est par contre un peu plus
pénible, puisqu’il faut fabriquer un ordre SQL, puis le passer en
paramètre à EXECUTE
, avec tous les quote_* que cela
implique pour en protéger les paramètres.
Pour se faciliter la vie, on peut utiliser
EXECUTE query USING param1, param2 …,
qui est même
quelquefois plus lisible que la syntaxe en dur : les paramètres de la
requête sont clairement identifiés dans cette syntaxe.
Par contre, la syntaxe USING
n’est utilisable que si le
nombre de paramètres est fixe.
La limite est difficile à placer, il s’agit de faire un compromis entre le temps de planification d’une requête (quelques dizaines de microsecondes pour une requête basique à potentiellement plusieurs secondes si on dépasse la dizaine de jointures) et le temps d’exécution.
Dans le doute, réalisez un test de performance de la fonction sur un jeu de données représentatif.
Tous les outils d’administration PostgreSQL permettent d’écrire des routines stockées en PL/pgSQL, la plupart avec les fonctionnalités habituelles (comme le surlignage des mots clés, l’indentation automatique, etc.).
Par contre, pour aller plus loin, l’offre est restreinte. Il existe tout de même un debugger qui fonctionne avec pgAdmin 4, sous la forme d’une extension.
pldebugger est un outil initialement créé par Dave Page et Korry Douglas au sein d’EnterpriseDB, repris par la communauté. Il est proposé sous license libre (Artistic 2.0).
Il fonctionne grâce à des hooks implémentés dans la version 8.2 de PostgreSQL.
Il est assez peu connu, ce qui explique que peu l’utilisent. Seul l’outil d’installation « one-click installer » l’installe par défaut. Pour tous les autres systèmes, cela réclame une compilation supplémentaire. Cette compilation est d’ailleurs peu aisée étant donné qu’il n’utilise pas le système pgxs.
Voici les étapes à réaliser pour compiler pldebugger en prenant pour
hypothèse que les sources de PostgreSQL sont disponibles dans le
répertoire /usr/src/postgresql-10
et qu’ils ont été
préconfigurés avec la commande ./configure
:
$ cd /usr/src/postgresql-10/contrib
$ git clone git://git.postgresql.org/git/pldebugger.git
Cloning into 'pldebugger'...
remote: Counting objects: 441, done.
remote: Compressing objects: 100% (337/337), done.
remote: Total 441 (delta 282), reused 171 (delta 104)
Receiving objects: 100% (441/441), 170.24 KiB, done.
Resolving deltas: 100% (282/282), done.
pldebugger
:$ cd pldebugger
$ make
# make install
L’installation copie le fichier plugin_debugger.so
dans
le répertoire des bibliothèques partagées de PostgreSQL. L’installation
copie ensuite les fichiers SQL et de contrôle de l’extension
pldbgapi
dans le répertoire extension
du
répertoire share
de PostgreSQL.
La configuration du paramètre shared_preload_libraries
permet au démarrage de PostgreSQL de laisser la bibliothèque
plugin_debugger
s’accrocher aux hooks de l’interpréteur
PL/pgSQL. Du coup, pour que la modification de ce paramètre soit prise
en compte, il faut redémarrer PostgreSQL.
L’interaction avec pldebugger se fait par l’intermédiaire de procédures stockées. Il faut donc au préalable créer ces procédures stockées dans la base contenant les procédures PL/pgSQL à débugguer. Cela se fait en créant l’extension :
$ psql13.0)
psql (Type "help" for help.
create extension pldbgapi;
postgres# CREATE EXTENSION
auto_explain
est une « contrib » officielle de
PostgreSQL (et non une extension). Il permet de tracer le plan d’une
requête. En général, on ne trace ainsi que les requêtes dont la durée
d’exécution dépasse la durée configurée avec le
paramètre’auto_explain.log_min_duration
. Par défaut, ce
paramètre est à -1 pour ne tracer aucun plan.
Comme dans un EXPLAIN
classique, on peut activer toutes
les options (par exemple ANALYZE
ou TIMING
avec, respectivement SET auto_explain.log_analyze TO true;
et SET auto_explain.log_timing TO true;
) mais l’impact en
performance peut être important même pour les requêtes qui ne seront pas
tracées.
D’autres options existent, qui reprennent les paramètres habituels
d’EXPLAIN
, notamment auto_explain.log_buffers
et auto_explain.log_settings
(voir la documentation).
L’exemple suivant utilise deux fonctions imbriquées mais cela marche pour une simple requête :
CREATE OR REPLACE FUNCTION table_nb_indexes (tabname IN text, nbi OUT int)
int
RETURNS
LANGUAGE plpgsqlAS $$
BEGIN
SELECT COUNT(*) INTO nbi
FROM pg_index i INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname ;
RETURN ;
END ;
$$
;CREATE OR REPLACE FUNCTION table_nb_col_indexes
IN text, nb_cols OUT int, nb_indexes OUT int)
(tabname record
RETURNS
LANGUAGE plpgsqlAS $$
BEGIN
SELECT COUNT(*) INTO nb_cols
FROM pg_attribute
WHERE attname LIKE tabname ;
SELECT nbi INTO nb_indexes FROM table_nb_indexes (tabname) ;
RETURN ;
END ;
$$ ;
Chargement dans la session d’auto_explain
(si pas déjà
présent dans shared_preload_libraries
) :
'auto_explain' ; LOAD
Activation pour toutes les requêtes, avec les options
ANALYZE
et BUFFERS
, puis affichage dans la
console (si la sortie dans les traces ne suffit pas) :
SET auto_explain.log_min_duration TO 0 ;
SET auto_explain.log_analyze TO on ;
SET auto_explain.log_buffers TO on ;
SET client_min_messages TO log ;
Test de la première fonction : le plan s’affiche, mais les compteurs (ici juste shared hit), ne concernent que la fonction dans son ensemble.
postgres=# SELECT * FROM table_nb_col_indexes ('pg_class') ;
LOG: duration: 2.208 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=2.203..2.203 rows=1 loops=1)
Buffers: shared hit=294
nb_cols | nb_indexes
---------+------------
0 | 3
En activant auto_explain.log_nested_statements
, on voit
clairement les plans de chaque requête exécutée :
SET auto_explain.log_nested_statements TO on ;
postgres=# SELECT * FROM table_nb_col_indexes ('pg_class') ;
LOG: duration: 0.235 ms plan:
Query Text: SELECT COUNT(*) FROM pg_attribute
WHERE attname LIKE tabname
Aggregate (cost=65.95..65.96 rows=1 width=8)
(actual time=0.234..0.234 rows=1 loops=1)
Buffers: shared hit=24
-> Index Only Scan using pg_attribute_relid_attnam_index on pg_attribute
(cost=0.28..65.94 rows=1 width=0)
(actual time=0.233..0.233 rows=0 loops=1)
Index Cond: ((attname >= 'pg'::text) AND (attname < 'ph'::text))
Filter: (attname ~~ 'pg_class'::text)
Heap Fetches: 0
Buffers: shared hit=24
LOG: duration: 0.102 ms plan:
Query Text: SELECT COUNT(*) FROM pg_index i
INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname
Aggregate (cost=24.48..24.49 rows=1 width=8)
(actual time=0.100..0.100 rows=1 loops=1)
Buffers: shared hit=18
-> Nested Loop (cost=0.14..24.47 rows=1 width=0)
(actual time=0.096..0.099 rows=3 loops=1)
Buffers: shared hit=18
-> Seq Scan on pg_class c (cost=0.00..23.30 rows=1 width=4)
(actual time=0.091..0.093 rows=1 loops=1)
Filter: (relname ~~ 'pg_class'::text)
Rows Removed by Filter: 580
Buffers: shared hit=16
-> Index Only Scan using pg_index_indrelid_index on pg_index i
(cost=0.14..1.16 rows=1 width=4)
(actual time=0.003..0.004 rows=3 loops=1)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=2
LOG: duration: 0.703 ms plan:
Query Text: SELECT nbi FROM table_nb_indexes (tabname)
Function Scan on table_nb_indexes (cost=0.25..0.26 rows=1 width=4)
(actual time=0.702..0.702 rows=1 loops=1)
Buffers: shared hit=26
LOG: duration: 1.524 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=1.520..1.520 rows=1 loops=1)
Buffers: shared hit=59
nb_cols | nb_indexes
---------+------------
0 | 3
Cet exemple permet de mettre le doigt sur un petit problème de
performance dans la fonction : le _
est interprété comme
critère de recherche. En modifiant le paramètre on peut constater le
changement de plan au niveau des index :
postgres=# SELECT * FROM table_nb_col_indexes ('pg\_class') ;
LOG: duration: 0.141 ms plan:
Query Text: SELECT COUNT(*) FROM pg_attribute
WHERE attname LIKE tabname
Aggregate (cost=56.28..56.29 rows=1 width=8)
(actual time=0.140..0.140 rows=1 loops=1)
Buffers: shared hit=24
-> Index Only Scan using pg_attribute_relid_attnam_index on pg_attribute
(cost=0.28..56.28 rows=1 width=0)
(actual time=0.138..0.138 rows=0 loops=1)
Index Cond: (attname = 'pg_class'::text)
Filter: (attname ~~ 'pg\_class'::text)
Heap Fetches: 0
Buffers: shared hit=24
LOG: duration: 0.026 ms plan:
Query Text: SELECT COUNT(*) FROM pg_index i
INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname
Aggregate (cost=3.47..3.48 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.42..3.47 rows=1 width=0) (…)
Buffers: shared hit=8
-> Index Scan using pg_class_relname_nsp_index on pg_class c
(cost=0.28..2.29 rows=1 width=4)
(actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (relname = 'pg_class'::text)
Filter: (relname ~~ 'pg\_class'::text)
Buffers: shared hit=6
-> Index Only Scan using pg_index_indrelid_index on pg_index i (…)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=2
LOG: duration: 0.414 ms plan:
Query Text: SELECT nbi FROM table_nb_indexes (tabname)
Function Scan on table_nb_indexes (cost=0.25..0.26 rows=1 width=4)
(actual time=0.412..0.412 rows=1 loops=1)
Buffers: shared hit=16
LOG: duration: 1.046 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg\_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=1.042..1.043 rows=1 loops=1)
Buffers: shared hit=56
nb_cols | nb_indexes
---------+------------
0 | 3
Pour les procédures, il est possible de mettre en place cette trace
avec
ALTER PROCEDURE … SET auto_explain.log_min_duration = 0
.
Cela ne fonctionne pas pour les fonctions.
pgBadger est capable de lire les plans tracés par
auto_explain
, de les intégrer à son rapport et d’inclure un
lien vers depesz.com pour une
version plus lisible.
Le menu contextuel pour accéder au débuggage d’une fonction :
La fenêtre du débugger :
log_functions
est un outil créé par Guillaume Lelarge au
sein de Dalibo. Il est proposé sous license libre (BSD).
Voici les étapes à réaliser pour compiler log_functions en prenant
pour hypothèse que les sources de PostgreSQL sont disponibles dans le
répertoire /home/guillaume/postgresql-9.1.4
et qu’ils ont
été préconfigurés avec la commande ./configure
:
$ cd /home/guillaume/postgresql-9.1.4/contrib
$ git://github.com/gleu/log_functions.git
Cloning into 'log_functions'...
remote: Counting objects: 24, done.
remote: Compressing objects: 100% (15/15), done.
remote: Total 24 (delta 8), reused 24 (delta 8)
Receiving objects: 100% (24/24), 11.71 KiB, done.
Resolving deltas: 100% (8/8), done.
log_functions
:$ cd log_functions
$ make
$ make install
L’installation copie le fichier log_functions.o
dans le
répertoire des bibliothèques partagées de PostgreSQL.
Si la version de PostgreSQL est supérieure ou égale à la 9.2, alors l’installation est plus simple et les sources de PostgreSQL ne sont plus nécessaires.
Téléchargement de log_functions :
wget http://api.pgxn.org/dist/log_functions/1.0.0/log_functions-1.0.0.zip
puis décompression et installation de l’extension :
unzip log_functions-1.0.0.zip
cd log_functions-1.0.0/
make USE_PGXS=1 && make USE_PGXS=1 install
L’installation copie aussi le fichier log_functions.so
dans le répertoire des bibliothèques partagées de PostgreSQL.
Le module log_functions est activable de deux façons.
La première consiste à demander à PostgreSQL de le charger au
démarrage. Pour cela, il faut configurer la variable
shared_preload_libraries
, puis redémarrer PostgreSQL pour
que le changement soit pris en compte.
La deuxième manière de l’activer est de l’activer seulement au moment
où son utilisation s’avère nécessaire. Il faut utiliser pour cela la
commande LOAD
en précisant le module à charger.
La première méthode a un coût en terme de performances car le module s’exécute à chaque exécution d’une procédure stockée écrite en PL/pgSQL. La deuxième méthode rend l’utilisation du profiler un peu plus complexe. Le choix est donc laissé à l’administrateur.
Les informations de profilage récupérées par log_functions sont envoyées dans les traces de PostgreSQL. Comme cela va générer plus d’écriture, et donc plus de lenteurs, il est possible de configurer chaque trace.
La configuration se fait soit dans le fichier
postgresql.conf
soit avec l’instruction
SET
.
Voici la liste des paramètres et leur utilité :
log_functions.log_declare
, à mettre à true pour tracer
le moment où PL/pgSQL exécute la partie DECLARE
d’une
procédure stockée ;log_functions.log_function_begin
, à mettre à true pour
tracer le moment où PL/pgSQL exécute la partie BEGIN
d’une
procédure stockée ;log_functions.log_function_end
, à mettre à true pour
tracer le moment où PL/pgSQL exécute la partie END
d’une
procédure stockée ;log_functions.log_statement_begin
, à mettre à true pour
tracer le moment où PL/pgSQL commence l’exécution d’une instruction dans
une procédure stockée ;log_functions.log_statement_end
, à mettre à true pour
tracer le moment où PL/pgSQL termine l’exécution d’une instruction dans
une procédure stockée.Par défaut, seuls log_statement_begin
et
log_statement_end
sont à false pour éviter la génération de
traces trop importantes.
Voici un exemple d’utilisation de cet outil :
SELECT incremente(4);
b2#
incremente------------
5
1 row)
(
'log_functions';
b2# LOAD
LOADSET client_min_messages TO log;
b2# LOG: duration: 0.136 ms statement: set client_min_messages to log;
SET
SELECT incremente(4);
b2# LOG: log_functions, DECLARE, incremente
LOG: log_functions, BEGIN, incremente
CONTEXT: PL/pgSQL function "incremente" during function entry
LOG: valeur de b : 5
LOG: log_functions, END, incremente
CONTEXT: PL/pgSQL function "incremente" during function exit
LOG: duration: 118.332 ms statement: select incremente(4);
incremente------------
5
1 row) (
Quelques liens utiles dans la documentation de PostgreSQL :
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/p2_solutions.
Ré-écrire la fonction suivante pour intercepter une division par zéro (utiliser l’exception DIVISION_BY_ZERO), afficher un message et renvoyer
Nan
.
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
float
RETURNS AS $BODY$
BEGIN
RETURN arg1::float / arg2::float;
END
$BODY$ LANGUAGE plpgsql;
Créer la table suivante :
CREATE TABLE stock (
integer PRIMARY KEY,
article_id DEFAULT now(),
dateheure timestamptz integer NOT NULL DEFAULT 0 ); stock
Créer une table
log_stock
comme ci-desssous pour tracer toutes les modifications du champnombre
dansstock
, avec l’ancienne et la nouvelle valeur, l’article, l’année, le moment, et qui a fait la modification (variablesession_user
). Créer un triggerAFTER
par ligne, et sa fonction associée pour tracer cela. Insérer quelques lignes, les mettre à jour, en supprimer une.
CREATE TABLE log_stock (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
utilisateur text,
dateheure timestamptz,integer,
article_id integer,
ancien_stock integer); nouveau_stock
Dans un autre trigger
BEFORE DELETE
par ligne, interdire la suppression des lignes dansstock
.
Afficher un message d’erreur dans les logs dans ce cas.
Tenter d’interdire l’exécution d’un
TRUNCATE
avec un triggerBEFORE TRUNCATE
(Il faudra unRAISE EXCEPTION
).
Quelle alternative y a-t-il pour interdire
DELETE
ouTRUNCATE
sur cette table ? Tester avec un nouvel utilisateur nommé pierre.
Supprimer le trigger
BEFORE DELETE
pour le remplacer par un autre plus complet qui :
- interdit les
DELETE
comme précédemment ;- affiche un message
NOTICE
quand le stock est ou devient inférieur ou égal à 5 (à moins qu’il ne remonte) ;- affiche un message
WARNING
quand il est ou tombe à 0 ;- remplace une valeur négative ou NULL par un 0.
Tester et vérifier danslog_stock
.
Insérer des lignes et comparer les performances de la requête suivante avant et après la suppression de tous les triggers :
INSERT INTO stock (article_id, stock)
SELECT i, 10 FROM generate_series (5000, 15000) i ;
Réécrire le trigger de trace en utilisant des tables de transition (il va falloir trois triggers et une fonction qui distingue les trois cas
UPSERT
/INSERT
/DELETE
).
En profiter pour ajouter desRAISE NOTICE
qui affichent les valeurs des fonctionscurrent_user
,session_user
,system_user
(sur PostgreSQL 15 ou plus).
Créer un rôle adminlog.
Lui donner la propriété et les droits exclusifs surlog_stock
.
Modifier les droits de la fonction de trace destock
pour qu’elle fonctionne encore.
Écrire une fonction
checkadmin
qui utilise un curseur pour parcourir la tablepg_stat_user_tables
, et affiche unRAISE LOG
dans les traces silast_autovacuum
etlast_vacuum
sont tous deux vides.
Afficher le numéro de la ligne dans le curseur (tri par nom de table).
Sortir après le troisième avertissement, sinon retourner le nombre de lignes lues.
Ré-écrire la fonction suivante pour intercepter une division par zéro (utiliser l’exception DIVISION_BY_ZERO), afficher un message et renvoyer
Nan
.
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
float
RETURNS AS $BODY$
BEGIN
RETURN arg1::float / arg2::float;
END
$BODY$ LANGUAGE plpgsql;
Effectivement :
SELECT division (1,5);
division
---------- 0.2
SELECT division (5,0);
ERROR: division by zero CONTEXTE : PL/pgSQL function division(integer,integer) line 3 at RETURN
La division par zéro peut s’intercepter ainsi :
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
float AS
RETURNS
$BODY$BEGIN
RETURN arg1::float/arg2::float;
EXCEPTION WHEN DIVISION_BY_ZERO THEN
LOG '[%] %', SQLSTATE, SQLERRM;
RAISE RETURN 'NaN';
END
$BODY$ LANGUAGE plpgsql ;
Requêtage :
SELECT division (5,0);
division
---------- NaN
Mais le message d’erreur ne s’affiche que dans les traces
(postgresql.log
), à moins de changer ce paramètre dans la
session :
SET client_min_messages TO log; -- le défaut est 'notice'
SELECT division(1,5), division (5,0);
LOG: [22012] division by zero
division | division
----------+---------- 0.2 | NaN
Une autre possibilité est d’utiliser RAISE NOTICE
au
lieu de RAISE LOG
. Auquel cas le message apparaîtra dans la
session et non dans les traces du serveur.
Créer la table suivante :
CREATE TABLE stock (
integer PRIMARY KEY,
article_id DEFAULT now(),
dateheure timestamptz integer NOT NULL DEFAULT 0 ); stock
Créer une table
log_stock
comme ci-desssous pour tracer toutes les modifications du champnombre
dansstock
, avec l’ancienne et la nouvelle valeur, l’article, l’année, le moment, et qui a fait la modification (variablesession_user
). Créer un triggerAFTER
par ligne, et sa fonction associée pour tracer cela. Insérer quelques lignes, les mettre à jour, en supprimer une.
CREATE TABLE log_stock (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
utilisateur text,
dateheure timestamptz,integer,
article_id integer,
ancien_stock integer); nouveau_stock
La colonne id
n’est pas totalement nécessaire, mais il
est préférable de poser des clés primaires sur toutes les tables.
La fonction trigger :
CREATE OR REPLACE FUNCTION stock_trace()
TRIGGER AS
RETURNS
$BODY$DECLARE
integer;
v_articleid integer;
v_ancien_stock integer;
v_nouveau_stock boolean := false;
v_atracer BEGIN
--
-- Ce code est volontairement verbeux pour pointer
-- des différents cas où des variables peuvent être NULL.
-- On pourrait économiser la plupart des variables.
--
IF TG_OP = 'INSERT' THEN
-- cas de l'insertion
:= true;
v_atracer := NEW.article_id;
v_articleid := NULL;
v_ancien_stock := NEW.stock;
v_nouveau_stock = 'UPDATE' THEN
ELSEIF TG_OP -- Ce test pour vérifier que le contenu de stock a bien changé
:= OLD.stock != NEW.stock;
v_atracer := NEW.article_id;
v_articleid := OLD.stock;
v_ancien_stock := NEW.stock;
v_nouveau_stock = 'DELETE' THEN
ELSEIF TG_OP -- cas de la suppression
:= true;
v_atracer := OLD.article_id;
v_articleid := OLD.stock;
v_ancien_stock := NULL;
v_nouveau_stock END IF;
-- pour débogage éventuel
-- RAISE NOTICE '% : % %->% %',
-- TG_OP, v_articleid, v_ancien_stock, v_nouveau_stock, v_atracer ;
IF v_atracer THEN
INSERT INTO log_stock
(utilisateur, dateheure, article_id,
ancien_stock, nouveau_stock)VALUES
(session_user, now(), v_articleid,
v_ancien_stock, v_nouveau_stock);END IF;
RETURN NEW ;
END $BODY$
LANGUAGE plpgsql ;
Le trigger :
CREATE TRIGGER stock_trace
AFTER INSERT OR UPDATE OR DELETE
ON stock FOR EACH ROW
EXECUTE PROCEDURE stock_trace();
Test :
\d stock
Table « public.stock »
Colonne | Type | Collationnement | NULL-able | Par défaut
------------+--------------------------+-----------------+-----------+------------
article_id | integer | | not null |
dateheure | timestamp with time zone | | | now()
stock | integer | | not null | 0
Index :
"stock_pkey" PRIMARY KEY, btree (article_id)
Triggers : stock_trace AFTER INSERT OR DELETE OR UPDATE ON stock FOR EACH ROW EXECUTE FUNCTION stock_trace()
-- Alimentation et vérification du contenu
INSERT INTO stock (article_id, stock)
VALUES (33, 10), (42, 6), (99, 20) ;
UPDATE stock SET stock = 9
WHERE article_id = 33 ;
UPDATE stock SET stock = 7
WHERE article_id = 42 ;
UPDATE stock SET stock = 7 -- répétition
WHERE article_id = 42 ;
DELETE FROM stock
WHERE article_id = 99 ;
TABLE stock ;
article_id | dateheure | stock
------------+------------------------------+-------
33 | 2025-02-07 18:19:31.57928+01 | 9 42 | 2025-02-07 18:19:31.57928+01 | 7
La table de trace contient alors ce qui suit. Noter que
l’UPDATE
inutile n’est pas tracé (on pourrait en décider
autrement).
SELECT
:date, article_id, ancien_stock, nouveau_stock
utilisateur, dateheure:FROM log_stock ;
utilisateur | dateheure | article_id | ancien_stock | nouveau_stock
-------------+------------+------------+--------------+---------------
postgres | 2025-02-07 | 33 | | 10
postgres | 2025-02-07 | 42 | | 6
postgres | 2025-02-07 | 99 | | 20
postgres | 2025-02-07 | 33 | 10 | 9
postgres | 2025-02-07 | 42 | 6 | 7 postgres | 2025-02-07 | 99 | 20 |
Dans un autre trigger
BEFORE DELETE
par ligne, interdire la suppression des lignes dansstock
.
Afficher un message d’erreur dans les logs dans ce cas.
On pourrait continuer à utiliser le trigger AFTER
, mais,
sur le principe, il vaut mieux interdire une modification plutôt que de
l’annuler.
La fonction trigger :
CREATE OR REPLACE FUNCTION interdit_suppression ()
TRIGGER AS $BODY$
RETURNS BEGIN
'% : article % - Suppression de ligne de stock interdite',
RAISE WARNING OLD.article_id ;
TG_OP, RETURN NULL ; -- annulation de l'ordre mais pas de la transaction
END $BODY$ LANGUAGE plpgsql ;
Le trigger :
CREATE TRIGGER stock_interdit_delete
BEFORE DELETE
ON stock FOR EACH ROW
EXECUTE PROCEDURE interdit_suppression();
Dans la transaction suivante, le DELETE
est annulé mais
la transaction se poursuit et l’INSERT
a bien lieu :
BEGIN ;
DELETE FROM stock
WHERE article_id = 33 ;
INSERT INTO stock (article_id, stock)
VALUES (100, 1000) ;
COMMIT ;
TABLE stock ;
…
WARNING: DELETE : article 33 - Suppression de ligne de stock interdite
…
article_id | dateheure | stock
------------+-------------------------------+-------
33 | 2025-02-07 18:19:31.57928+01 | 9
42 | 2025-02-07 18:19:31.57928+01 | 7 100 | 2025-02-07 18:19:31.584649+01 | 1000
Si l’on préfère une erreur franche avec annulation de la transaction
en cours, il faut un RAISE EXCEPTION
(et non
RAISE WARNING
), qui donnera lieu à un message
ERROR
(et non WARNING
). La transaction sera
interrompue.
Tenter d’interdire l’exécution d’un
TRUNCATE
avec un triggerBEFORE TRUNCATE
(Il faudra unRAISE EXCEPTION
).
Effectivement, le trigger BEFORE DELETE
ne protège pas
d’un TRUNCATE
:
BEGIN ;
TRUNCATE TABLE stock ;
TABLE stock;
ROLLBACK ;
article_id | dateheure | stock
------------+-----------+------- (0 ligne)
Cependant, un trigger sur TRUNCATE
est un trigger par
ordre (FOR EACH STATEMENT
). On ne peut annuler son
fonctionnement avec un simple RETURN NULL
. Il faut un
RAISE EXCEPTION
, et malheureusement, cela implique
d’interrompre la transaction.
CREATE OR REPLACE FUNCTION interdit_truncate()
TRIGGER AS $$
RETURNS BEGIN
EXCEPTION '% : Suppression de ligne de stock interdite', TG_OP ;
RAISE RETURN NULL ;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER stock_interdit_truncate
BEFORE TRUNCATE
ON stock FOR EACH STATEMENT
EXECUTE PROCEDURE interdit_truncate();
TRUNCATE stock ;
TABLE stock ;
ERROR: TRUNCATE : Suppression de ligne de stock interdite CONTEXTE : PL/pgSQL function interdit_truncate() line 3 at RAISE
TABLE stock ;
article_id | dateheure | stock
------------+-------------------------------+-------
33 | 2025-02-07 18:19:31.57928+01 | 9
42 | 2025-02-07 18:19:31.57928+01 | 7 100 | 2025-02-07 18:19:31.584649+01 | 1000
Avec tous ces triggers, la définition de la table devient lourde :
\d stock
Table « public.stock »
Colonne | Type | Collationnement | NULL-able | Par défaut
------------+--------------------------+-----------------+-----------+------------
article_id | integer | | not null |
dateheure | timestamp with time zone | | | now()
stock | integer | | not null | 0
Index :
"stock_pkey" PRIMARY KEY, btree (article_id)
Triggers :
stock_trace AFTER INSERT OR DELETE OR UPDATE ON stock FOR EACH ROW EXECUTE FUNCTION stock_trace()
stock_interdit_delete BEFORE DELETE ON stock FOR EACH ROW EXECUTE FUNCTION interdit_suppression() stock_interdit_truncate BEFORE TRUNCATE ON stock FOR EACH STATEMENT EXECUTE FUNCTION interdit_truncate()
Quelle alternative y a-t-il pour interdire
DELETE
ouTRUNCATE
sur cette table ? Tester avec un nouvel utilisateur nommé pierre.
-- Exécuter en tant que superutilisateur
REVOKE TRUNCATE, DELETE ON TABLE stock FROM public;
CREATE ROLE pierre LOGIN ;
GRANT ALL ON TABLE stock TO pierre ;
SET ROLE pierre ;
UPDATE stock SET stock=998
WHERE article_id = 100 ;
DELETE FROM stock WHERE article_id = 100;
TRUNCATE TABLE stock ;
NOTICE: UPDATE : 100 1000->1000 t ERROR: permission denied for table log_stock
TRUNCATE
(un TRUNCATE
sur une vue ne
fonctionne pas).SET ROLE postgres ;
CREATE VIEW v_stock AS SELECT * FROM stock ;
GRANT ALL ON v_stock TO pierre ;
SET ROLE pierre ;
TRUNCATE v_stock ;
ERROR: "v_stock" is not a table
Supprimer le trigger
BEFORE DELETE
pour le remplacer par un autre plus complet qui :
- interdit les
DELETE
comme précédemment ;- affiche un message
NOTICE
quand le stock est ou devient inférieur ou égal à 5 (à moins qu’il ne remonte) ;- affiche un message
WARNING
quand il est ou tombe à 0 ;- remplace une valeur négative ou NULL par un 0.
Tester et vérifier danslog_stock
.
À la suppression du trigger BEFORE DELETE
, ne pas
oublier de supprimer la fonction associée :
-- en tant que superutilisateur
DROP TRIGGER stock_interdit_delete ON stock ;
DROP FUNCTION interdit_suppression ;
CREATE OR REPLACE FUNCTION verifications_stock ()
TRIGGER AS $BODY$
RETURNS BEGIN
IF TG_OP = 'DELETE' THEN
RAISE WARNING'% : article % - Suppression de ligne de stock interdite',
OLD.article_id ;
TG_OP, RETURN NULL ;
IN ('UPDATE','INSERT') THEN
ELSEIF TG_OP IF NEW.stock BETWEEN 1 AND 5 AND NEW.stock < OLD.stock THEN
'article % - Valeur % faible !',
RAISE NOTICE OLD.article_id, NEW.stock ;
coalesce(NEW.stock,0) <= 0 THEN
ELSEIF 'article % - Plus de stock !', OLD.article_id ;
RAISE WARNING NEW.stock = 0;
END IF ;
RETURN NEW ;
END IF ;
END $BODY$ LANGUAGE plpgsql ;
CREATE TRIGGER stock_verifications
BEFORE DELETE OR INSERT OR UPDATE
ON stock FOR EACH ROW
EXECUTE PROCEDURE verifications_stock();
Test :
INSERT INTO stock (article_id, stock)
VALUES (1789, 3) ;
UPDATE stock SET stock = stock - 2 -- stock : 1
WHERE article_id = 1789 ;
UPDATE stock SET stock = stock - 2 -- stock : 0
WHERE article_id = 1789 ;
UPDATE stock SET stock = stock - 2 -- idem
WHERE article_id = 1789 ;
UPDATE stock SET stock = stock + 20 -- stock 20
WHERE article_id = 1789 ;
UPDATE stock SET stock = NULL
WHERE article_id = 1789 ;
INSERT INTO stock (article_id, stock)
VALUES (1789, 3) ;
INSERT 0 1
UPDATE stock SET stock = stock - 2 -- stock : 1
WHERE article_id = 1789 ;
NOTICE: article 1789 - Valeur 1 faible !
UPDATE 1
UPDATE stock SET stock = stock - 2 -- stock : 0
WHERE article_id = 1789 ;
WARNING: article 1789 - Plus de stock !
UPDATE 1
UPDATE stock SET stock = stock - 2 -- idem
WHERE article_id = 1789 ;
WARNING: article 1789 - Plus de stock !
UPDATE 1
UPDATE stock SET stock = stock + 20 -- stock 20
WHERE article_id = 1789 ;
UPDATE 1
UPDATE stock SET stock = NULL
WHERE article_id = 1789 ;
WARNING: article 1789 - Plus de stock ! UPDATE 1
Noter que le SET stock = NULL
ne provoque pas d’erreur
sur la contrainte NOT NULL
de la colonne
stock
. La vérification se fait après l’ordre SQL et le
trigger BEFORE
.
La trace reflète bien les valeurs modifiées :
SELECT
:date, article_id, ancien_stock, nouveau_stock
utilisateur, dateheure:FROM log_stock WHERE article_id = 1789 ;
utilisateur | dateheure | article_id | ancien_stock | nouveau_stock
-------------+------------+------------+--------------+---------------
postgres | 2025-02-07 | 1789 | | 3
postgres | 2025-02-07 | 1789 | 3 | 1
postgres | 2025-02-07 | 1789 | 1 | 0
postgres | 2025-02-07 | 1789 | 0 | 20 postgres | 2025-02-07 | 1789 | 20 | 0
Insérer des lignes et comparer les performances de la requête suivante avant et après la suppression de tous les triggers :
INSERT INTO stock (article_id, stock)
SELECT i, 10 FROM generate_series (5000, 15000) i ;
BEGIN ;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, VERBOSE)
UPDATE stock SET stock = 9
WHERE article_id BETWEEN 5000 and 15000 ;
ROLLBACK ;
DROP TRIGGER stock_verifications ON stock ;
DROP TRIGGER stock_interdit_truncate ON stock ;
DROP TRIGGER stock_trace ON stock ;
BEGIN ;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, VERBOSE)
UPDATE stock SET stock = 9
WHERE article_id BETWEEN 5000 and 15000 ;
ROLLBACK ;
Ce test est un peu rapide, mais il doit montrer un rapport de temps
d’exécution de 1 à 3 ou 1 à 4, bien sûr en défaveur des triggers. La
trace est l’opération la plus lourde (il faut
EXPLAIN (VERBOSE)
pour l’afficher) :
…
Trigger stock_trace: time=45.730 calls=10001
Trigger stock_verifications: time=6.725 calls=10001 Execution Time: 79.630 ms
Sans les triggers, sur la même machine :
… Execution Time: 25.583 ms
Réécrire le trigger de trace en utilisant des tables de transition (il va falloir trois triggers et une fonction qui distingue les trois cas
UPSERT
/INSERT
/DELETE
).
En profiter pour ajouter desRAISE NOTICE
qui affichent les valeurs des fonctionscurrent_user
,session_user
,system_user
(sur PostgreSQL 15 ou plus).
La première difficulté est que les tables correspondant à
NEW
et OLD
ne sont pas définies dans tous les
cas. La seconde est qu’il est délicat (et lourd) de rapprocher les
valeurs des deux tableaux NEW
et OLD
si les
deux sont remplis, on se contente donc ici d’ajouter deux lignes
séparées pour l’UPDATE
.
CREATE OR REPLACE FUNCTION stock_trace2()
TRIGGER AS $BODY$
RETURNS DECLARE
int DEFAULT 0 ;
nb BEGIN
IF TG_OP IN ('UPDATE','DELETE') THEN
INSERT INTO log_stock
(utilisateur, dateheure, article_id,
ancien_stock, nouveau_stock)SELECT
session_user, now(), oldstock.article_id,NULL
oldstock.stock, FROM oldstock ;
-- pour débogage
'Trigger de trace pour % (utilisateur %/%/%)',
RAISE NOTICE
TG_OP, current_user, session_user, system_user ;END IF ;
IF TG_OP IN ('UPDATE','INSERT') THEN
INSERT INTO log_stock
(utilisateur, dateheure, article_id,
ancien_stock, nouveau_stock)SELECT
session_user, now(), newstock.article_id,NULL, newstock.stock
FROM newstock ;
-- pour débogage
'Trigger de trace pour % (utilisateur %/%/%)',
RAISE NOTICE
TG_OP, current_user, session_user, system_user ;END IF ;
RETURN NEW ;
END $BODY$ LANGUAGE plpgsql ;
Les triggers :
CREATE TRIGGER stock_trace_ins
AFTER INSERT ON stock
REFERENCING NEW TABLE AS newstock
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_trace2();
CREATE TRIGGER stock_trace_del
AFTER DELETE ON stock
REFERENCING OLD TABLE AS oldstock
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_trace2();
-- seul trigger à gérer REFERENCING OLD et NEW
CREATE TRIGGER stock_trace_upd
AFTER UPDATE ON stock
REFERENCING
OLD TABLE AS oldstock
NEW TABLE AS newstock
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_trace2();
Test basique :
TRUNCATE TABLE log_stock ;
INSERT INTO stock (article_id, stock)
VALUES (1000, 3) ;
UPDATE stock SET stock = 5
WHERE article_id = 1000 ;
DELETE FROM stock
WHERE article_id = 1000 ;
SELECT dateheure::time, article_id, ancien_stock, nouveau_stock
FROM log_stock
WHERE article_id = 1000 ;
dateheure | article_id | ancien_stock | nouveau_stock
-----------------+------------+--------------+---------------
23:23:33.325255 | 1000 | | 3
23:23:33.326288 | 1000 | 3 |
23:23:33.326288 | 1000 | | 5 23:23:33.327044 | 1000 | 5 |
BEGIN ;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, VERBOSE)
UPDATE stock SET stock = 9
WHERE article_id BETWEEN 5000 and 15000 ;
COMMIT ;
Le trigger n’est appelé qu’une seule fois par opération, le gain est donc sensible. Cette simple requête reste tout de même deux fois plus lente avec le trigger.
Trigger stock_trace_upd: time=24.596 calls=1 Execution Time: 53.306 ms
-- Test avec un article
SELECT dateheure::time, article_id, ancien_stock, nouveau_stock
FROM log_stock
WHERE article_id = 9999 ;
dateheure | article_id | ancien_stock | nouveau_stock
-----------------+------------+--------------+---------------
23:26:41.040846 | 9999 | 10 | 23:26:41.040846 | 9999 | | 9
Créer un rôle adminlog.
Lui donner la propriété et les droits exclusifs surlog_stock
.
Modifier les droits de la fonction de trace destock
pour qu’elle fonctionne encore.
Pour des raisons de sécurité, il est logique que l’utilisateur
applicatif ne puisse pas modifier la table de trace
log_stock
.
-- En tant que superutilisateur
CREATE ROLE adminlog ;
ALTER TABLE log_stock OWNER TO adminlog ;
REVOKE ALL ON TABLE log_stock FROM public ;
SET ROLE pierre ;
\d
Schéma | Nom | Type | Propriétaire
--------+------------------+----------+--------------
public | log_stock | table | adminlog
public | log_stock_id_seq | séquence | adminlog
public | stock | table | postgres …
\d
Schéma | Nom | Type | Propriétaire
--------+------------------+----------+--------------
public | log_stock | table | adminlog
public | log_stock_id_seq | séquence | adminlog
public | stock | table | postgres …
Suite au changement de droits, une modification de la table échoue :
INSERT INTO stock (article_id, stock)
VALUES (20000, 10) ;
ERROR: permission denied for table log_stock
CONTEXTE : SQL statement "INSERT INTO log_stock
… PL/pgSQL function stock_trace2() line 17 at SQL statement
La fonction stock_trace2
est
SECURITY INVOKER
(elle utilise les droits de
l’utilisateur), c’est le défaut. On peut lui permettre de tourner avec
les droits plus étendus de son propriétaire
(SECURITY DEFINER
). Il faut donc la donner à
adminlog, puis donner les droits d’exécution aux
utilisateurs.
SET ROLE postgres ;
ALTER FUNCTION stock_trace2() OWNER TO adminlog ;
ALTER FUNCTION stock_trace2() SECURITY DEFINER ;
SET ROLE pierre ;
INSERT INTO stock (article_id, stock)
VALUES (20000, 10) ;
NOTICE: Trigger de trace pour INSERT (utilisateur adminlog/postgres/peer:dalibo)
Noter que le trigger insère la ligne en tant que
adminlog (current_user
) et non
pierre ou postgres (l’appelant,
session_user
). C’est à savoir si l’on trace
l’utilisateur.
Écrire une fonction
checkadmin
qui utilise un curseur pour parcourir la tablepg_stat_user_tables
, et affiche unRAISE LOG
dans les traces silast_autovacuum
etlast_vacuum
sont tous deux vides.
Afficher le numéro de la ligne dans le curseur (tri par nom de table).
Sortir après le troisième avertissement, sinon retourner le nombre de lignes lues.
-- Tables d'exemples
CREATE TABLE demo1 AS SELECT 1 AS t ;
CREATE TABLE demo2 AS SELECT 1 AS t ;
CREATE TABLE demo3 AS SELECT 1 AS t ;
CREATE TABLE demo4 AS SELECT 1 AS t ;
CREATE OR REPLACE FUNCTION checkadmin()
int AS
RETURNS
$BODY$DECLARE
c_curseur refcursor;%ROWTYPE;
r_resultat pg_stat_user_tablesinteger := 0 ;
v_index integer := 0 ;
v_nb_avertissements BEGIN
OPEN c_curseur FOR
SELECT * FROM pg_stat_user_tables
ORDER BY relname ;
LOOP
INTO r_resultat;
FETCH c_curseur IF NOT FOUND THEN
EXIT;END IF;
-- debogage
'Table % (%)',
RAISE NOTICE
r_resultat.relname, v_index ;:= v_index + 1;
v_index IF r_resultat.last_autovacuum IS NULL
AND r_resultat.last_vacuum IS NULL
THEN
:= v_nb_avertissements + 1 ;
v_nb_avertissements LOG 'Autovacuum jamais passé sur table % (ligne %)',
RAISE
r_resultat.relname, v_index ;IF v_nb_avertissements >= 3 THEN
EXIT ;END IF ;
END IF ;
END LOOP;
CLOSE c_curseur ;
RETURN v_index;
END $BODY$ LANGUAGE plpgsql ;
-- Affichage dans la session du contenu des traces
SET client_min_messages TO log;
-- Test
SELECT checkadmin() ;
NOTICE: Table demo1 (0)
LOG: Autovacuum jamais passé sur table demo1 (ligne 1)
NOTICE: Table demo2 (1)
LOG: Autovacuum jamais passé sur table demo2 (ligne 2)
NOTICE: Table demo3 (2)
LOG: Autovacuum jamais passé sur table demo3 (ligne 3)
checkadmin
------------ 3
CREATE OR REPLACE FUNCTION checkadmin()
int AS
RETURNS
$BODY$DECLARE
record ;
r_resultat integer := 0 ;
v_index integer := 0 ;
v_nb_avertissements BEGIN
FOR r_resultat IN (
SELECT relname,last_autovacuum, last_vacuum
FROM pg_stat_user_tables ORDER BY relname )
LOOP
-- debogage
'Table % (%)',
RAISE NOTICE
r_resultat.relname, v_index ;:= v_index + 1;
v_index IF coalesce(r_resultat.last_autovacuum,
IS NULL
r_resultat.last_vacuum) THEN
:= v_nb_avertissements + 1 ;
v_nb_avertissements LOG 'Autovacuum jamais passé sur table % (ligne %)',
RAISE
r_resultat.relname, v_index ;WHEN v_nb_avertissements >= 3 ;
EXIT END IF ;
END LOOP ;
RETURN v_index ;
END $BODY$ LANGUAGE plpgsql ;
SELECT checkadmin() ;
VACUUM ;
SELECT checkadmin() ;
NOTICE: Table demo1 (0)
NOTICE: Table demo2 (1)
NOTICE: Table demo3 (2)
NOTICE: Table demo4 (3)
NOTICE: Table log_stock (4)
NOTICE: Table stock (5)
checkadmin
------------ 6