Transformations inattendues des types de données

Les types de données de votre fonction ont changé et vous n’avez pas été informés

Cet article est le résultat d’une revue de code demandée par l’un de nos clients.

Notre client a exécuté la commande pg_dump --schema-only pour nous fournir
les définitions des objets de niveau database ainsi que le code PLpgsql à auditer.
Jusque là, tout allait bien.

J’ai commencé à lire le code et tout d’un coup je fus pris d’un doute, vous savez
le méchant doute qui vous donne l’impression que votre château de cartes va s’écrouler.
Voici la fonction que j’avais sous les yeux:

CREATE FUNCTION xxx( p_id character, p_info character varying )
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
   ...
   INSERT INTO t1
   SELECT * FROM t2 WHERE t2.id = p_id;
   ...
END;
$$
;

Peut-être que la première lecture de la fonction ne vous a pas choqué.
Et si je vous disais que la définition de la table t2 ressemble à ce qui suit:

CREATE TABLE t2 (
   id VARCHAR(130) NOT NULL
   ...
   PRIMARY KEY (id)
);

En pratique t2.id est toujours une chaîne de 130 caractères et il y a 400
millions de tuples dans cette table. Comme vous l’avez déjà probablement compris,
il semble étonnant d’avoir p_id CHARACTER pour manipuler id VARCHAR(130). Pour
mémoire, CHARACTER est l’alias de CHAR(1).

Notre client utilise cette fonction depuis plusieurs années et il n’a pas eu de
problème avec ce code. Néanmoins, notre client m’a dit que la définition de cette
fonction ne correspond pas à ce qu’il a écrit: c’était p_id CHARACTER(130) et
non CHARACTER.

Mais que s’est-il donc passé ? Attaquons la pelotte de laine et voyons si Ariane
nous est d’un grand secours.

CREATE FUNCTION test( c character, d character varying )
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'c=%, d=%', c,d;
END;
$$;

SELECT test( '123465789', '987654321' );
NOTICE:  c=123465789, d=987654321
 test 
------
 
(1 row)

Voilà un résultat bien intéressant: la chaîne ‘123456789’ n’a pas été réduite à
‘1’ (pas de CAST CHAR(1)). Augmentons le niveau de détails:

EXPLAIN (COSTS OFF,ANALYZE,VERBOSE)
        SELECT test( '123465789', '987654321' );
NOTICE:  c=123465789, d=987654321
                             QUERY PLAN                              
---------------------------------------------------------------------
 Result (actual time=0.040..0.041 rows=1 loops=1)
   Output: test('123465789'::bpchar, '987654321'::character varying)
 Planning Time: 0.023 ms
 Execution Time: 0.053 ms
(4 rows)

Nous observons un CAST vers bpchar. Pour mémoire, BPCHAR représente une
chaîne pouvant contenir 10 485 760 caractères. Il est aussi un alias de
CHARACTER ce qui explique partiellement le passage de CHARACTER à BPCHAR.

Explorons une autre facette du problème:

CREATE FUNCTION test(c character(4))
RETURNS character(4)
LANGUAGE sql
AS $$
select c;
$$;

Nous avons changé le langage de la fonction pour savoir si le comportement observé
est propre à PLpgsql. Les données en entrée et en sortie sont de type CHAR(4).
Comment cela va-t-il se comporter ?

SELECT test('123456789');
   test    
-----------
 123456789
(1 row)

EXPLAIN VERBOSE SELECT test('123456789');
                QUERY PLAN                 
-------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32)
   Output: '123456789'::bpchar
(2 rows)

La bonne nouvelle (si si) c’est que le comportement observé est le même dans
les deux contextes. En revanche, alors que l’on s’attend à manipuler des données
de type CHAR(4), la fonction manipule des données de taille arbitraire (limitée
à ~10M caractères) !!

Cependant, ne vous précipitez pas dans la mailing list de PostgreSQL pour vous plaindre !

En fait, ce comportement n’est pas un bug. La documentation affirme:

“The full SQL type syntax is allowed for declaring a function’s arguments and return value. However, parenthesized type modifiers (e.g., the precision field for type numeric) are discarded by CREATE FUNCTION. Thus for example CREATE FUNCTION foo (varchar(10)) … is exactly the same as CREATE FUNCTION foo (varchar) …”

Ceci explique que CHARACTER(x) devient CHARACTER transformé (par aliasing)
en BPCHAR qui n’est pas CHAR(1) mais plutôt VARCHAR(10485760). Le
comportement est maintenant complétement expliqué.

Attendez une seconde ! L’intention était de traiter des données CHAR(4) pas
de taille différente.

N’y a-t-il pas de solution ? Non, désolé… (à mois que…)

Plus loin dans la page de la documentation référencée ci-dessus, on peut lire
que argtype et rettype peuvent être de type base, composite, domain
ou même du type d’une table/colonne.

L’astuce est de créer un type composite ou un domain a utiliser en tant que
argtype et/ou rettype.

Voici quelques exemples:

-- Works in simple case trick
SELECT test( '12345789'::char(4) );

-- Domain trick
CREATE DOMAIN c4 AS char(4);
CREATE FUNCTION test(param c4)
RETURNS c4
AS $$
BEGIN
  RAISE NOTICE 'param=%', param;
  RETURN param;
END;
$$ LANGUAGE plpgsql;

SELECT test( '123456789' );
ERROR:  value too long for type character(4)

SELECT test( '123456789'::char(4) );
NOTICE:  param=1234
 test 
------
 1234
(1 row)

SELECT test( '123456789'::c4);
NOTICE:  param=1234
 test 
------
 1234
(1 row)

SELECT pg_typeof( test( '123456789'::char(4) ) );
NOTICE:  param=1234
 pg_typeof 
-----------
 c4
(1 row)

Le résultat obtenu devrait vous satisfaire.

Vous n’êtes pas satisfait ?

Je vous propose une dernière astuce.

-- Map a table structure
CREATE TABLE qq ( c char(4));

CREATE FUNCTION test(IN c qq, OUT d qq)
LANGUAGE sql
AS $$
SELECT c;
$$;

SELECT * FROM test(ROW('12345'));
ERROR:  value too long for type character(4)

SELECT * from test(ROW('1234'));
  c   
------
 1234

Hmm, d’accord mais en quoi est-ce différent de l’utilisation d’un domain ?

-- Easy Type Alteration
ALTER TABLE qq ALTER c TYPE char(5);

SELECT * FROM test( ROW('12345') );
   c   
-------
 12345

Essayer de modifier un domain, vous verrez comme c’est simple et pas impactant (…).

L’astuce basée sur la définition de table permet de la flexibilité:

ALTER TABLE qq ADD ee int;

SELECT test( ROW('12345', 4) );
   test   
----------
 (12345,4)

SELECT * FROM test( ROW('12345', 4) );
   c   | ee 
-------+----
 12345 |  4

Nous espérons que vous avez apprécié cet article et que vous avez appris quelque chose de nouveau et d’intéressant.