Par Michel SALAIS
La documentation PostgreSQL définit une fonction stockée signalée comme IMMUTABLE comme suit:
« IMMUTABLE indique que la fonction ne peut pas modifier la base de données et qu’à arguments constants, la fonction renvoie toujours le même résultat ; c’est-à-dire qu’elle n’effectue pas de recherches dans la base de données, ou alors qu’elle utilise des informations non directement présentes dans la liste d’arguments. Si cette option est précisée, tout appel de la fonction avec des arguments constants peut être immédiatement remplacé par la valeur de la fonction. »
Quel intérêt à définir une fonction stockée comme étant IMMUTABLE ?
Préparation
Nous allons étudier la question en utilisant la table t créée ci-dessous.
msym=# drop table if exists t cascade; DROP TABLE msym=# create table t msym-# ( msym(# a int primary key, msym(# b int msym(# ); CREATE TABLE msym=# msym=# insert into t (a, b) msym-# values msym-# (1, 1), msym-# (2, 1), msym-# (3, 2), msym-# (4, 1), msym-# (5, 2); INSERT 0 5
Fonction immuable et parcours d’une table
Ci-après la création et l’utilisation d’une « fausse » fonction immuable f_immutable(). En fait, l’acceptation qu’une fonction immuable puisse appeler une fonction quelconque ouvre les portes à des résultats inattendus.
La fonction f_immutable() s’endort d’abord le nombre de secondes donné par l’instruction
perform pg_sleep(x);
Ensuite, elle retourne l’horodatage en utilisant la fonction timeofday(). Cette fonction donne l’horodatage du moment de son exécution et sa valeur varie même pendant le même parcours d’une table. L’utilisation de la fonction pg_sleep() permet de mettre en évidence le temps qui passe…
msym=# drop function if exists f_immutable(int); DROP FUNCTION msym=# msym=# create or replace function f_immutable(x int) msym-# returns timestamp without time zone as msym-# $$ msym$# declare msym$# v timestamp without time zone := to_timestamp(timeofday(),'dy mon dd hh24:mi:ss.us yyyy') + x * interval '1' second; msym$# begin msym$# perform pg_sleep(x); msym$# raise notice 'valeurs de x, v sont : %, %', x, v ; msym$# return v; msym$# end; msym$# $$ msym-# language plpgsql immutable; CREATE FUNCTION msym=# msym=# select a, b, f_immutable(b) ib, f_immutable(1) i1, f_immutable(1) i2, f_immutable(b) - f_immutable(1) i_diff msym-# from t msym-# where f_immutable(b) - f_immutable(1) < interval '10' second; NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:23.736 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:24.75 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:25.755 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:26.759 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:27.788 /* calcul de f_immutable(b) pour la première ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:28.8 /* calcul de f_immutable(b) pour la première ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:29.801 /* calcul de f_immutable(b) pour la première ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:30.803 /* calcul de f_immutable(b) pour la deuxième ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:31.818 /* calcul de f_immutable(b) pour la deuxième ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:32.831 /* calcul de f_immutable(b) pour la deuxième ligne : b=1 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:34.845 /* calcul de f_immutable(b) pour la troisième ligne : b=2 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:36.856 /* calcul de f_immutable(b) pour la troisième ligne : b=2 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:38.865 /* calcul de f_immutable(b) pour la troisième ligne : b=2 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:39.865 /* calcul de f_immutable(b) pour la quatrième ligne : b=1 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:41.865 /* calcul de f_immutable(b) pour la cinquième ligne : b=2 */ a | b | ib | i1 | i2 | i_diff ---+---+-------------------------+-------------------------+------------------------+-------------- 1 | 1 | 2017-06-24 09:33:28.8 | 2017-06-24 09:33:23.736 | 2017-06-24 09:33:24.75 | 00:00:04.046 2 | 1 | 2017-06-24 09:33:31.818 | 2017-06-24 09:33:23.736 | 2017-06-24 09:33:24.75 | 00:00:07.076 3 | 2 | 2017-06-24 09:33:36.856 | 2017-06-24 09:33:23.736 | 2017-06-24 09:33:24.75 | 00:00:13.11 (3 lignes) msym=# msym=# select a, b, f_immutable(b) ib, f_immutable(1) i1, f_immutable(1) i2, f_immutable(b) - f_immutable(1) i_diff msym-# from t msym-# where f_immutable(b) - f_immutable(1) < interval '10' second; NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:42.929 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:43.943 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:44.949 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:45.977 /* calcul de f_immutable(1) pendant la génération du plan */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:46.978 /* calcul de f_immutable(b) pour la première ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:47.985 /* calcul de f_immutable(b) pour la première ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:48.998 /* calcul de f_immutable(b) pour la première ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:50.013 /* calcul de f_immutable(b) pour la deuxième ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:51.042 /* calcul de f_immutable(b) pour la deuxième ligne : b=1 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:52.055 /* calcul de f_immutable(b) pour la deuxième ligne : b=1 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:54.057 /* calcul de f_immutable(b) pour la troisième ligne : b=2 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:56.058 /* calcul de f_immutable(b) pour la troisième ligne : b=2 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:33:58.069 /* calcul de f_immutable(b) pour la troisième ligne : b=2 */ NOTICE: valeurs de x, v sont : 1, 2017-06-24 09:33:59.076 /* calcul de f_immutable(b) pour la quatrième ligne : b=1 */ NOTICE: valeurs de x, v sont : 2, 2017-06-24 09:34:01.091 /* calcul de f_immutable(b) pour la cinquième ligne : b=2 */ a | b | ib | i1 | i2 | i_diff ---+---+-------------------------+-------------------------+-------------------------+-------------- 1 | 1 | 2017-06-24 09:33:47.985 | 2017-06-24 09:33:42.929 | 2017-06-24 09:33:43.943 | 00:00:04.049 2 | 1 | 2017-06-24 09:33:51.042 | 2017-06-24 09:33:42.929 | 2017-06-24 09:33:43.943 | 00:00:07.106 3 | 2 | 2017-06-24 09:33:56.058 | 2017-06-24 09:33:42.929 | 2017-06-24 09:33:43.943 | 00:00:13.12 (3 lignes)
Notons, avant d’aller vers les conclusions, que les commentaires dans les notices entre « /* » et « */ » sont – évidemment – ajoutés à la main après l’exécution.
Que montre ces résultats ?
- Si la fonction immuable est appelée avec des arguments constants, alors elle est exécutée une et une seule fois pour la durée de l’instruction SQL. En fait cette exécution se fait pendant la génération du plan. Dans ce cas, la fonction immuable est traitée comme les expressions constantes.
- Deux apparitions de la fonction immuable dans le texte SQL, même avec les mêmes valeurs constantes, provoquent la ré-exécution de la fonction.
- Si la fonction immuable est appelée avec des arguments non constants, alors elle est exécutée pour chaque ligne de la table même lorsque des lignes différentes donne les mêmes valeurs aux arguments de la fonction. En fait, le résultat du calcul n’est pas mis dans un cache pour réutilisation ultérieure avec d’autres lignes.
Fonctions immuables et instructions préparées
Rappelons nous qu’une instruction préparée permet de générer un plan une seule fois et de le réutiliser par la suite évitant une régénération « inutile » du plan d’exécution. Alors, que se passera-t-il pour les fonctions immuables ? regardons…
msym=# deallocate test_prep; DEALLOCATE msym=# msym=# prepare test_prep msym-# as msym-# select a, b, f_immutable(b) ib, f_immutable(1) i1, f_immutable(1) i2, f_immutable(b) - f_immutable(1) i_diff msym-# from t msym-# where f_immutable(b) - f_immutable(1) < interval '30' second; PREPARE msym=# msym=# execute test_prep; NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:20.877 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:21.888 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:22.946 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:23.948 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:24.95 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:25.963 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:26.967 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:27.98 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:28.981 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:29.982 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:31.983 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:33.984 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:35.994 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:36.998 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:38.008 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:39.02 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:41.032 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:43.035 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:45.061 a | b | ib | i1 | i2 | i_diff ---+---+-------------------------+-------------------------+-------------------------+-------------- 1 | 1 | 2017-06-24 16:26:25.963 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:04.021 2 | 1 | 2017-06-24 16:26:28.981 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:07.036 3 | 2 | 2017-06-24 16:26:33.984 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:13.048 4 | 1 | 2017-06-24 16:26:38.008 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:16.074 5 | 2 | 2017-06-24 16:26:43.035 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:22.115 (5 lignes) msym=# execute test_prep; NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:46.428 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:47.429 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:48.435 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:49.447 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:50.469 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:51.502 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:53.502 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:55.513 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:26:57.798 NOTICE: valeurs de x, v sont : 1, 2017-06-24 16:26:58.799 NOTICE: valeurs de x, v sont : 2, 2017-06-24 16:27:00.799 a | b | ib | i1 | i2 | i_diff ---+---+-------------------------+-------------------------+-------------------------+-------------- 1 | 1 | 2017-06-24 16:26:47.429 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:25.489 2 | 1 | 2017-06-24 16:26:50.469 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:28.556 3 | 2 | 2017-06-24 16:26:55.513 | 2017-06-24 16:26:20.877 | 2017-06-24 16:26:21.888 | 00:00:34.852 (3 lignes)
l’exécution deux fois de l’instruction préparée montre que l’évaluation de la fonction immuable avec des arguments constants se fait une fois pour toute à la génération du plan d’exécution et les valeurs trouvées sont donc utilisées pour toutes les exécutions de l’instruction préparée. Mais, de toute façon, l’usage d’arguments variables provoque la ré-exécution de la fonction à chaque fois même si les arguments, variables, reprennent les mêmes valeurs.
Contrôle des mises à jour dans les fonctions immuables
Regardons attentivement l’exécution suivante
msym=# create or replace function f_immutable_maj(x int) msym-# returns timestamp without time zone as msym-# $$ msym$# begin msym$# update t set b = b + 1; msym$# return to_timestamp(timeofday(),'dy mon dd hh24:mi:ss.us yyyy') + x; msym$# end; msym$# $$ msym-# language plpgsql immutable; CREATE FUNCTION msym=# select f_immutable_maj(1); ERROR: UPDATE is not allowed in a non-volatile function CONTEXTE : SQL statement "update t set b = b + 1" PL/pgSQL function f_immutable_maj(integer) line 3 at SQL statement
L’erreur montre que le contrôle se fait pendant l’exécution de la fonction. Effectivement, le contrôle n’est pas effectué pendant la compilation. Mais que se passera-t-il si l’instruction de mise à jour est cachée à l’intérieur d’une fonction ?
Voici un exemple qui montre que le placement de l’instruction à l’intérieur d’une fonction empêche le contrôle de la fonction immuable vis-à-vis des mises à jour.
msym=# create or replace function f_immutable_maj(x int) msym-# returns timestamp without time zone as msym-# $$ msym$# begin msym$# update t set b = b + 1; msym$# return to_timestamp(timeofday(),'dy mon dd hh24:mi:ss.us yyyy') + x * interval'1' second; msym$# end; msym$# $$ msym-# language plpgsql immutable; CREATE FUNCTION msym=# create or replace function f_immutable_maj(x int) msym-# returns timestamp without time zone as msym-# $$ msym$# begin msym$# perform f_maj(); msym$# return to_timestamp(timeofday(),'dy mon dd hh24:mi:ss.us yyyy') + x; msym$# end; msym$# $$ msym-# language plpgsql immutable; CREATE FUNCTION msym=# select f_immutable_maj(1); f_immutable_maj ------------------------- 2017-06-24 09:00:01.854 (1 ligne)
Cet exemple montre tout d’abord qu’une fonction immuable peut appeler une fonction non immuable ! Aucun contrôle ne sera effectué afin d’empêcher un tel usage et la prudence s’impose, faute de quoi, des résultats inattendus seront au rendez-vous.