La volatilité des fonctions dans PostgreSQL

2026-10-08   1758 mots, 9 minutes de lecture.   Nicolas LUTIC

Lorsque nous écrivons une fonction dans PostgreSQL, notre attention se porte naturellement sur ce qu’elle calcule. Nous oublions souvent de déclarer ce qui intéresse le plus l’optimiseur : dans quelles conditions son résultat peut-il changer ?

C’est précisément le rôle de la catégorie de volatilité, ce mot-clé que l’on place dans le CREATE FUNCTION et que l’on recopie trop souvent d’un exemple trouvé en ligne.

Dans cet article, nous présentons les différents niveaux de volatilité et le choix à préconiser selon les cas.

Qu’est-ce que la volatilité d’une fonction ?

La volatilité est une classification que PostgreSQL attache à chaque fonction. C’est une promesse faite à l’optimiseur sur le comportement de la fonction vis-à-vis des données. Trois niveaux sont disponibles :

• VOLATILE : le résultat peut changer à chaque appel, même au sein d’une même requête, et la fonction peut modifier la base
• STABLE : le résultat est constant pour les mêmes arguments au sein d’une même instruction, mais peut varier d’une instruction à l’autre
• IMMUTABLE : le résultat est constant pour les mêmes arguments, en toute circonstance

VOLATILE est le niveau appliqué par défaut si aucune annotation n’est précisée à la création de la fonction. La documentation officielle détaille ces trois catégories dans le chapitre consacré à la volatilité des fonctions.

Cette classification a un effet concret : elle dit à PostgreSQL s’il peut réutiliser un résultat déjà calculé, et surtout quel snapshot la fonction a le droit de consulter.

VOLATILE

Une fonction VOLATILE peut modifier la base (INSERT, UPDATE, DELETE) ou lire des données changeantes. Sans annotation explicite, c’est le niveau que PostgreSQL applique automatiquement.

Pour illustrer le comportement, imaginons une table stock contenant 10 produits. Nous créons une fonction qui retourne le prix d’un produit via un simple SELECT dans cette table, avec l’id du produit en paramètre.

Cette fonction est volontairement ralentie par un appel à pg_sleep(1), afin de laisser le temps de modifier les données pendant son exécution. Le but est d’observer si le résultat diffère selon le moment de l’appel.

CREATE TABLE stock (id int primary key, price numeric);
INSERT INTO stock SELECT g, 10 FROM generate_series(1, 10) g;

CREATE OR REPLACE FUNCTION get_price_volatile_plpgsql(p_id int)
RETURNS numeric AS $$
DECLARE
  v numeric;
BEGIN
  PERFORM pg_sleep(1);
  SELECT price INTO v FROM stock WHERE id = p_id;
  RETURN v;
END;
$$ LANGUAGE plpgsql VOLATILE;

Pour reproduire le test, il faut deux sessions distinctes (deux connexions psql, par exemple) :

1. Dans la session A, nous lançons la requête suivante, qui va s’exécuter pendant une dizaine de secondes à cause des dix appels à pg_sleep(1) :

SELECT id,
  get_price_volatile_plpgsql(id) AS price_via_volatile
FROM stock
ORDER BY id;

2. Pendant que la session A tourne encore, nous exécutons dans la session B :

UPDATE stock SET price = 15 WHERE id > 3;

Voici le résultat obtenu en session A :

┌────┬────────────────────┐
│ id │ price_via_volatile │
├────┼────────────────────┤
│  1 │              10.00 │
│  2 │              10.00 │
│  3 │              10.00 │
│  4 │                 15 │
│  5 │                 15 │
│  6 │                 15 │
│  7 │                 15 │
│  8 │                 15 │
│  9 │                 15 │
│ 10 │                 15 │
└────┴────────────────────┘

Le comportement est cohérent avec la définition, les lignes traitées avant l’UPDATE (id 1 à 3) renvoient encore l’ancien prix, tandis que celles traitées après (id 4 et suivants) renvoient le nouveau. Cela s’explique par le fait qu’en isolation READ COMMITTED (le niveau par défaut de PostgreSQL), une fonction VOLATILE est autorisée à prendre un nouveau snapshot des données à chaque appel, elle peut donc « voir » un UPDATE validé entre deux de ses propres exécutions, au sein d’une seule et même requête.

STABLE

Une fonction STABLE renvoie le même résultat pour les mêmes arguments au sein d’une même instruction. Elle peut lire la base sans la modifier.

Reprenons l’exemple de la table stock et créons cette fois une version STABLE de la fonction :

CREATE OR REPLACE FUNCTION get_price_stable_plpgsql(p_id int)
RETURNS numeric AS $$
DECLARE
  v numeric;
BEGIN
  PERFORM pg_sleep(1);
  SELECT price INTO v FROM stock WHERE id = p_id;
  RETURN v;
END;
$$ LANGUAGE plpgsql STABLE;

Remettons d’abord tous les prix à 10 :

UPDATE stock SET price = 10;

Puis, comme précédemment avec deux sessions, lançons en session A :

SELECT id,
  get_price_stable_plpgsql(id)   AS price_via_stable,
  get_price_volatile_plpgsql(id) AS price_via_volatile
FROM stock
ORDER BY id;

Et en session B, pendant que la requête tourne :

UPDATE stock SET price = 15 WHERE id > 3;

┌────┬──────────────────┬─────────────────────┐
│ id │ price_via_stable │ price_via_volatile  │
├────┼──────────────────┼─────────────────────┤
│  1 │               10 │                  10 │
│  2 │               10 │                  10 │
│  3 │               10 │                  10 │
│  4 │               10 │                  15 │
│  5 │               10 │                  15 │
│  6 │               10 │                  15 │
│  7 │               10 │                  15 │
│  8 │               10 │                  15 │
│  9 │               10 │                  15 │
│ 10 │               10 │                  15 │
└────┴──────────────────┴─────────────────────┘
(10 rows)

La colonne price_via_stable reste figée à 10 pour toutes les lignes, alors que price_via_volatile reflète l’UPDATE concurrent à partir de l’id 4. La fonction STABLE réutilise, elle, le snapshot pris au début de la requête appelante, elle ne voit donc jamais les changements survenus pendant son exécution, quel que soit le nombre d’appels effectués au sein de cette même requête.

IMMUTABLE

Une fonction IMMUTABLE renvoie un résultat constant pour les mêmes arguments, en toute circonstance, pas seulement au sein d’une requête comme STABLE, mais y compris d’une requête à l’autre, indéfiniment. Cela signifie qu’elle ne doit jamais lire une table, ni dépendre de paramètres de session ou de l’horloge système.

PostgreSQL ne vérifie pas cette promesse. Si elle est fausse, deux mécanismes peuvent être affectés : le constant folding, qui fige une valeur calculée une fois pour toutes dans le plan, et l’index fonctionnel, dont les entrées ne sont jamais recalculées après un changement du résultat de la fonction pour une même entrée. Dans ce second cas, l’index continue de pointer vers des lignes qui ne correspondent plus à la condition recherchée.

L’intérêt de ce niveau va au-delà de la cohérence des résultats, déjà garantie par STABLE à l’échelle d’une requête. Il ouvre deux usages interdits aux deux autres niveaux : le précalcul de l’expression au moment de la planification, et l’index fonctionnel, où IMMUTABLE est une exigence vérifiée par PostgreSQL (CREATE INDEX échoue sinon).

Illustrons avec un calcul qui ne dépend que de son argument :

CREATE OR REPLACE FUNCTION price_ttc(p_ht numeric)
RETURNS numeric AS $$
BEGIN
  RETURN round(p_ht * 1.20, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Cette fonction peut servir de base à un index fonctionnel :

CREATE INDEX idx_stock_price_ttc ON stock (price_ttc(price));

Pour utiliser idx_stock_price_ttc, il faut appliquer la fonction à la colonne, et non pas à la constante :

EXPLAIN (ANALYZE)
SELECT * FROM stock WHERE price_ttc(price) = 12.00;

Sur notre table de 10 lignes, cette requête n’utilisera pourtant toujours pas l’index : le volume est trop faible pour que l’accès par index soit jugé rentable par l’optimiseur face à un simple Seq Scan.

Constituons un jeu de données plus réaliste pour l’observer réellement :

TRUNCATE stock;

INSERT INTO stock (id, price)
SELECT g, (random() * 90 + 10)::numeric(10,2)
FROM generate_series(1, 200000) g;

ANALYZE stock;

Consultons maintenant le plan d’exécution :

EXPLAIN (ANALYZE)
SELECT * FROM stock WHERE price_ttc(price) = 12.00;

Index Scan using idx_stock_price_ttc on stock (cost=0.42..26.10 rows=22 width=10)
                                              (actual time=0.050..0.074 rows=12 loops=1)
  Index Cond: (price_ttc(price) = 12.00)
Planning Time: 0.131 ms
Execution Time: 0.104 ms

L’index idx_stock_price_ttc est bien sollicité : le plan bascule d’un Seq Scan à un Index Scan.

C’est la confirmation concrète de ce qu’annonçait la théorie : un index fonctionnel n’est utilisable que si la fonction indexée est IMMUTABLE, et seulement si la requête compare exactement l’expression indexée (price_ttc(price), et non price seul) à une valeur.

Le piège de l’inlining : VOLATILE n’exclut pas toujours l’index

Une fonction VOLATILE dans un WHERE ne bloque pas systématiquement l’index. Pour une fonction SQL au corps simple, PostgreSQL peut l’inliner avant de regarder sa volatilité, et l’index reste utilisable malgré l’annotation.

CREATE OR REPLACE FUNCTION add_volatile(a int, b int)
RETURNS int AS $$ SELECT a + b; $$ LANGUAGE sql VOLATILE;

EXPLAIN SELECT * FROM stock WHERE id = add_volatile(2, 3);
Index Scan using stock_pkey on stock  (cost=0.42..8.44 rows=1 width=36)
  Index Cond: (id = 5)
  -- l'index est utilisé malgré le VOLATILE

La fonction add_volatile a un corps trivial, une seule expression, sans effet de bord détectable syntaxiquement. Avant même de considérer sa volatilité, PostgreSQL l’inline : il remplace l’appel par le contenu du corps de la fonction directement dans l’arbre de la requête, tôt dans la planification (eval_const_expressions). Une fois la substitution faite, il ne reste plus de fonction add_volatile dans le plan, seulement une addition entre deux constantes évaluée par l’opérateur +, natif et immuable. L’annotation VOLATILE devient sans objet : le nœud auquel elle s’appliquait a disparu.

Le rapport de bug BUG #11637 documente un cas similaire, sur CREATE INDEX cette fois, une fonction SQL marquée VOLATILE peut se retrouver utilisée dans un index fonctionnel, parce qu’elle est inlinée avant que PostgreSQL ne vérifie sa mutabilité.

Tom Lane a confirmé sur la mailing-list que ce n’est pas un bug au sens strict du terme, mais la conséquence attendue de deux mécanismes indépendants qui se chevauchent, l’inlining et la vérification de volatilité.

Ce qui bloque réellement l’inlining, et fait retrouver au VOLATILE son effet attendu :

• plusieurs instructions dans le corps de la fonction
• une fonction SECURITY DEFINER
• du code en PL/pgSQL, un langage jamais inliné

Le rapport de bug d’origine porte sur CREATE INDEX qui accepte un index sur une fonction marquée VOLATILE parce qu’elle est inlinée avant la vérification de mutabilité,

Conclusion

La volatilité n’est pas une case à cocher administrative, c’est un contrat entre le développeur et le planificateur de requêtes, que PostgreSQL ne vérifie jamais lui-même. Le niveau le plus restrictif que la fonction justifie réellement reste la seule règle sûre, l’inlining rappelle simplement qu’il faut aussi regarder si la fonction est inlinable avant de prédire un plan sur la seule base de son annotation.

Tableau récapitulatif

La ligne « Utilisation d’index » pour VOLATILE suppose une fonction que PostgreSQL ne peut pas inliner. C’est le sujet de la section suivante, et c’est le point le plus souvent mal expliqué sur ce sujet.