Développer des fonctions scalaires (UDF) avec PLpgSQL

SQLpro · 20/06/2011 09:43:23

Bonjour,

voici le premier d'une nouvelle série d'articles consacrés au développement des fonctions sous PLpgSQL

Ce premier article est consacré aux fonctions scalaires appelées dans la norme SQL "UDF" pour User Defined Function

Il sera suivi dans les mois prochains de deux autres articles : l'un sur les fonctions de manipulation des tables et l'autre sur les fonctions

Sommaire :

0 - INTRODUCTION
1 - CRÉATION D'UNE FONCTION SCALAIRE
2 - UTILISATION DE LA FONCTION
3 - FONCTION AVEC LANGAGE SQL
4 - QUELQUES MOTS CLEFS DE PL/pgSQL
5 - ARGUMENTS
6 - VARIABLES ET CONSTANTES
7 - POLYMORPHISME
8 - STRUCTURES DE TEST ET BRANCHEMENTS
9 - GESTION D'ERREUR
10 - DÉBOGAGE
11 - DIRECTIVES DIVERSES À LA CRÉATION DES FONCTIONS
12 - MÉTADONNÉES DES FONCTIONS
13 - CONSEILS DE CODAGE
ANNEXE 1 : COMPARAISON MS SQL SERVER vs POSTGRESQL
ANNEXE 2 : VOCABULAIRE
ANNEXE 3 : QUELQUES RÉFÉRENCES

A lire : http://blog.developpez.com/sqlpro/p1006 … fonctions/

Toutes vos remarques et conseils sont les bienvenus !

A +

Dernière modification par SQLpro (20/06/2011 09:44:01)

Marc Cousin · 20/06/2011 11:00:54

Lu très en diagonale.

- la case du tableau final «récupération des erreurs» est fausse, ou maladroitement formulée: on peut avoir autant de bloc exception qu'on veut dans une fonction, pour peu que chacune soit dans un bloc begin/end. Il y a d'ailleurs une modification du comportement de RAISE sur le sujet sur la 9.1. Vous pouvez lire mon doc sur le sujet: http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1

- pour l'absence de masquage de code, ce n'est vrai qu'en plpgsql. En C, évidemment, mais même dans d'autres langages aussi qui ont des modules d'obfuscation, il y a moyen. mais il ne faut pas attendre d'option pour ça dans plpgsql avant un moment à mon avis, le masquage de code n'est pas vraiment dans les préoccupations de gens développant un logiciel sous licence affiliée à BSD. Si quelqu'un veut en contribuer un, par contre, je présume que ça sera accepté, si c'est fait proprement.

- un rapport de bug pour pgadmin3 aurait été plus constructif qu'une note dans cet article, si bug il y a (je n'ai pas regardé)

- un lien vers la doc en français ça aurait pu être bien, vu qu'on s'embête à la traduire, même si moi aussi je ne lis que la version anglaise.

- un mot sur anyelement/anyarray lors du polymorphisme, ça aurait certainement valu la peine (et ça aurait permis d'être complet dans le tableau récapitulatif sur ce point)

- pour ce qui est de l'impact du logging sur les performances, c'est mesurable, mais loin d'être énorme dans la majorité des cas. J'ai des clients qui tracent intégralement plusieurs milliers de requête à la seconde, l'impact reste faible, excepté sur la taille des logs bien sûr. Et on n'est pas obligé de les envoyer dans pg_log, on peut l'envoyer dans un syslogger, voir un syslogger sur un serveur distant, pour avoir un mécanisme asynchrone. En tout cas, conseiller aux lecteurs passer les message en terse, je ne pense pas que ça soit une bonne idée: on perd la trace du «contexte», le «hint», la requête en cours. Donc on rend le débogage ou l'analyse des traces bien plus difficile. Le plus important, c'est de ne pas dépasser debug1, après ça log vraiment très fort. Par ailleurs, le client_min_messages peut se modifier dans une session, avec un SET. Pas la peine d'éditer le fichier de configuration, et d'altérer toutes les sessions, si c'est pour débugger ce qu'on est en train de faire.

- le transtypage n'est pas fait par le compilateur, mais directement dans la phase d'analyse de la requête appelante. le cast de int -> smallint est déclaré 'AS ASSIGNMENT', pas 'AS IMPLICIT', ce qui explique qu'il est utilisable 'dans le SQL' (pour un insert, je présume ?), mais pas automatiquement pour détecter si une fonction est candidate dans le cadre du polymorphisme. Le cast dans l'autre sens est implicite, lui, ce qui est logique. Tout ça est expliqué dans la doc de 'CREATE CAST'. Trop de casts implicites risqueraient de rendre plusieurs fonctions 'candidates', et donc de rendre les casts normaux inutilisables.

Sinon il y a pas mal de fautes de grammaire, mais je présume que ce n'était pas la question.

Et puis, cette apparemment irrépressible envie de comparer à SQL Server… le plpgsql est fait avant tout pour ressembler au PL/SQL d'Oracle (c'est très clair dans la doc, les développeurs ne s'en sont jamais caché non plus), même si évidemment il ne va pas aussi loin (mais il y a 14 autres langages de procédures stockées pour compenser, en cas de besoin).

SQLpro · 20/06/2011 11:36:13

Marc Cousin a écrit :

- la case du tableau final «récupération des erreurs» est fausse, ou maladroitement formulée: on peut avoir autant de bloc exception qu'on veut dans une fonction, pour peu que chacune soit dans un bloc begin/end. Il y a d'ailleurs une modification du comportement de RAISE sur le sujet sur la 9.1. Vous pouvez lire mon doc sur le sujet: http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1

Je vais corriger le tableau. Votre lien ne marche pas.

- pour l'absence de masquage de code, ce n'est vrai qu'en plpgsql. En C, évidemment, mais même dans d'autres langages aussi qui ont des modules d'obfuscation, il y a moyen. mais il ne faut pas attendre d'option pour ça dans plpgsql avant un moment à mon avis, le masquage de code n'est pas vraiment dans les préoccupations de gens développant un logiciel sous licence affiliée à BSD. Si quelqu'un veut en contribuer un, par contre, je présume que ça sera accepté, si c'est fait proprement.

L'article parle de fonction en, PGplSQL, pas en C ou d'autres langage. Le fait de coder des fonctions directement en natif est souvent plus performant pour des fonctions basiques proche du traitement des données. De plus les langages externes ont tous le même défaut d'impédance ce qui les rends difficilement apte aux traitements des données relationnelles (gestion du NULL, de la collation...).
Le faite que PostGreSQL ne mette pas en place des mécanisme de masquage des UDF PLpgSQL est dommage, car la plupart des SGBDR le font...

- un lien vers la doc en français ça aurait pu être bien, vu qu'on s'embête à la traduire, même si moi aussi je ne lis que la version anglaise.

Le problème est que la traduction est parfois assez mauvais comme l'exemple que j'ai donné en PS dans l'article. D’où mon souhait d'aller à la source pour être le plus exact possible.

- un mot sur anyelement/anyarray lors du polymorphisme, ça aurait certainement valu la peine (et ça aurait permis d'être complet dans le tableau récapitulatif sur ce point)

Mon article ne traite pas des types de données de PGplSQL, ni du polymorphisme en général, mais des fonctions. J'aurais aussi pu parler de bien d'autres types, ainsi que des choses plus subtiles sur le polymorphisme, mais ce n'est pas le sujet !

- pour ce qui est de l'impact du logging sur les performances, c'est mesurable, mais loin d'être énorme dans la majorité des cas. J'ai des clients qui tracent intégralement plusieurs milliers de requête à la seconde, l'impact reste faible, excepté sur la taille des logs bien sûr. Et on n'est pas obligé de les envoyer dans pg_log, on peut l'envoyer dans un syslogger, voir un syslogger sur un serveur distant, pour avoir un mécanisme asynchrone. En tout cas, conseiller aux lecteurs passer les message en terse, je ne pense pas que ça soit une bonne idée: on perd la trace du «contexte», le «hint», la requête en cours. Donc on rend le débogage ou l'analyse des traces bien plus difficile. Le plus important, c'est de ne pas dépasser debug1, après ça log vraiment très fort. Par ailleurs, le client_min_messages peut se modifier dans une session, avec un SET. Pas la peine d'éditer le fichier de configuration, et d'altérer toutes les sessions, si c'est pour débugger ce qu'on est en train de faire.

Là vous aurez du mal à me convaincre, car j'ai vu en prod des ralentissements épouvantables due à la journalisation, notamment quand l'install de PG a été faite sans considération des disques... Et en production on ne fait pas de débogage... Ça c'est valable dans les premières heures de prod. Pas dans la continuité !

- le transtypage n'est pas fait par le compilateur, mais directement dans la phase d'analyse de la requête appelante. le cast de int -> smallint est déclaré 'AS ASSIGNMENT', pas 'AS IMPLICIT', ce qui explique qu'il est utilisable 'dans le SQL' (pour un insert, je présume ?), mais pas automatiquement pour détecter si une fonction est candidate dans le cadre du polymorphisme. Le cast dans l'autre sens est implicite, lui, ce qui est logique. Tout ça est expliqué dans la doc de 'CREATE CAST'. Trop de casts implicites risqueraient de rendre plusieurs fonctions 'candidates', et donc de rendre les casts normaux inutilisables.

Sauf que c'est contraire aux principes fondamentaux du SQL qui est de réaliser systématqiuement des CAST implicite au plus juste... et c'est particulièrement lourd pour le développement comme pour l'écriture des requêtes...
Je présume que cela est aussi du au fait qu'il n'existe pas de RECOMPILE....

Et puis, cette apparemment irrépressible envie de comparer à SQL Server… le plpgsql est fait avant tout pour ressembler au PL/SQL d'Oracle (c'est très clair dans la doc, les développeurs ne s'en sont jamais caché non plus), même si évidemment il ne va pas aussi loin (mais il y a 14 autres langages de procédures stockées pour compenser, en cas de besoin).

Cela ne vous intéresse pas et apparemment cela vous ennui que je fasse ce genre de comparaison. Pourtant cela aide bien des gens à commencer par mes clients qui passent de SQL Server à PG et inversement (d'ailleurs plus dans ce sens) mais aussi utilisent les deux ! Et c'est toujours instructif de voir ce que font les autres, en particulier parce que PG emprunte à beaucoup de SGBDR les mécanismes nouveaux qu'il y introduit, à commencer, comme vous l'avez justement fait remarqué, par Oracle.
Il est d'ailleurs amusant de voir qu'en règle général chez les gens du libre, dès que l'on compare à un produit commercial, ils ressentent immédiatement le besoin de dire que ce n'est pas bien, ça ne sert à rien, c'est stupide, que sous Linux c'est toujours mieux.... etc. Le problème c'est que dans la vrai vie c'est pas d'un côté 100% libre et de l'autre 100% commercial. La plupart des entreprises ont à la fois du libre et du commercial et je connais peu d'entreprises qui n'ont que du libre ! Voyez par exemple les postes client !
Mais là le sujet devient politique !

A +

Marc Cousin · 20/06/2011 11:53:15

Sauf que c'est contraire aux principes fondamentaux du SQL qui est de réaliser systématqiuement des CAST implicite au plus juste... et c'est particulièrement lourd pour le développement comme pour l'écriture des requêtes...

Les casts implicites sont faits quand les développeurs sont surs que ça ne va pas vous exploser à la figure. Caster un int dans un smallint, ça pourrait valoir de belles surprises à l'exécution: que faire quand j'ai deux variantes d'une fonction: une qui prend 2 smallint en paramètre, une qui prend deux bigint, et que la fonction smallint va sortir en erreur alors que la bigint va fonctionner (dépassement de capacité si je fais tout bêtement la somme)? Ça aussi c'est totalement incompréhensible pour l'utilisateur. Les casts sont donc implicites vers des types plus capacitifs. Le cast smallint vers int et vers bigint le sont. Real vers numeric aussi.

Tandis que le caster dans un smallint quand on veut le stocker dans une colonne smallint, c'est sans risque.

Après, que ça soit dans la norme ou pas, on a déjà eu cette discussion (avec insultes à la clé). Il y a de nombreux endroits dans la doc de PostgreSQL où ils expliquent ne pas suivre la norme, et pourquoi. Je n'ai pas l'intention d'y revenir.

Je présume que cela est aussi du au fait qu'il n'existe pas de RECOMPILE....

Rien à voir, les procédures PLPgSQL ne sont pas stockées compilées dans la base. Elles sont compilées à la première exécution, dans chaque session. Et recompilées automatiquement quand une nouvelle version devient visible.

Cela ne vous intéresse pas et apparemment cela vous ennui que je fasse ce genre de comparaison. Pourtant cela aide bien des gens à commencer par mes clients qui passent de SQL Server à PG et inversement (d'ailleurs plus dans ce sens) mais aussi utilisent les deux ! Et c'est toujours instructif de voir ce que font les autres, en particulier parce que PG emprunte à beaucoup de SGBDR les mécanismes nouveaux qu'il y introduit, à commencer, comme vous l'avez justement fait remarqué, par Oracle.
Il est d'ailleurs amusant de voir qu'en règle général chez les gens du libre, dès que l'on compare à un produit commercial, ils ressentent immédiatement le besoin de dire que ce n'est pas bien, ça ne sert à rien, c'est stupide, que sous Linux c'est toujours mieux.... etc. Le problème c'est que dans la vrai vie c'est pas d'un côté 100% libre et de l'autre 100% commercial. La plupart des entreprises ont à la fois du libre et du commercial et je connais peu d'entreprises qui n'ont que du libre ! Voyez par exemple les postes client !
Mais là le sujet devient politique !

Excellente généralisation. Je remarque simplement que vous revenez toujours sur SQL Server. Et que ce n'est pas l'exemple pertinent ici. Ici, c'est la comparaison avec Oracle qui est la plus intéressante. Et la plus en la défaveur de PLPgSQL. Gardez donc vos délires sur les «gens du libre».

Marc Cousin · 20/06/2011 12:00:34

Un dernier point:

Là vous aurez du mal à me convaincre, car j'ai vu en prod des ralentissements épouvantables due à la journalisation, notamment quand l'install de PG a été faite sans considération des disques... Et en production on ne fait pas de débogage... Ça c'est valable dans les premières heures de prod. Pas dans la continuité !

Merci de m'expliquer mon métier. Il ne s'agit pas de déboggage, il s'agit de remonter des traces utilisables en production pour le mode normal par rapport au mode terse. Un serveur sans debug trace habituellement quelques dizaines de kilos par jour. Autant avoir la meilleure trace possible quand ça se produit.

Et pour le client traçant ses requêtes, il s'agit évidemment de profiling de la production. C'est la façon la plus simple et la plus efficace de le faire aujourd'hui sous Postgres. Et là aussi, en mode terse, vous n'aurez pas la requête. Vous serez bien avancé.

Quand à votre impact en performances, je vous conseille d'essayer avec un mécanisme de journalisation externe. La pénalité est bien moins grande. Encore que je n'aie jamais essayé avec un eventlog, bien sûr.

SQLpro · 20/06/2011 14:23:24

Marc Cousin a écrit :

...Caster un int dans un smallint, ça pourrait valoir de belles surprises à l'exécution: que faire quand j'ai deux variantes d'une fonction: une qui prend 2 smallint en paramètre, une qui prend deux bigint, et que la fonction smallint va sortir en erreur alors que la bigint va fonctionner (dépassement de capacité si je fais tout bêtement la somme)? Ça aussi c'est totalement incompréhensible pour l'utilisateur. Les casts sont donc implicites vers des types plus capacitifs. Le cast smallint vers int et vers bigint le sont. Real vers numeric aussi.

Quand je dit "au plus juste", c'est bien entendu pas au plus petit, mais vers le type le plus juste qui permet de satisfaire les différents types donné. Il n'y a donc jamais de de INT vers SMALLINT !
Sauf que ce n'est pas le comportement de PostGreSQL en matière de passage des arguments aux fonctions, et cela est un handicap, car oblige soit à caster explicitement dans ses requêtes, soit à faire plusieurs version de la même fonction.

Marc Cousin a écrit :

... sur la recompliation...
Rien à voir, les procédures PLPgSQL ne sont pas stockées compilées dans la base. Elles sont compilées à la première exécution, dans chaque session. Et recompilées automatiquement quand une nouvelle version devient visible.

C'est bien là le problème. Il y a des fois ou une recompilation favorise la création d'un plan plus adapté. Et cela fait défaut au niveau de PG.

... sur la comparaison PG SQL Server ...
Excellente généralisation. Je remarque simplement que vous revenez toujours sur SQL Server. Et que ce n'est pas l'exemple pertinent ici. Ici, c'est la comparaison avec Oracle qui est la plus intéressante. Et la plus en la défaveur de PLPgSQL. Gardez donc vos délires sur les «gens du libre».

Le mot est donc lâché... Je suis délirant ! Merci, votre masque est tombé !!!

A +

Marc Cousin · 20/06/2011 14:46:17

Quand je dit "au plus juste", c'est bien entendu pas au plus petit, mais vers le type le plus juste qui permet de satisfaire les différents types donné. Il n'y a donc jamais de de INT vers SMALLINT !
Sauf que ce n'est pas le comportement de PostGreSQL en matière de passage des arguments aux fonctions, et cela est un handicap, car oblige soit à caster explicitement dans ses requêtes, soit à faire plusieurs version de la même fonction.

CREATE FUNCTION test (a bigint) returns void language plpgsql as $$
begin
raise notice 'test';
end
$$
;
CREATE FUNCTION
marc=# SELECT test(1::int);
NOTICE:  test
 test 
------
 
(1 row)

J'aimerais comprendre. Il y a bien un cast implicite ici de int vers bigint. Il n'y aura jamais de cast vers smallint implicite.

C'est bien là le problème. Il y a des fois ou une recompilation favorise la création d'un plan plus adapté. Et cela fait défaut au niveau de PG.

Si le plan dépend des données en base, on utilise execute habituellement (sous Postgres). C'est un peu plus lourd, surtout au niveau syntaxique, mais ça résout le problème. Sinon, si vous voulez tenez absolument à forcer la «recompilation» dans toutes les sessions, il suffit de recréer la procédure. Ce qui n'est pas très dur, vu que vous disposez d'une fonction PL pour regénérer le create function. De toutes façons, si votre plan est si dépendant des données dans la table, il faut revenir à execute. Évidemment, la façon de travailler est différente de celle de SQL Server.

Le mot est donc lâché... Je suis délirant ! Merci, votre masque est tombé !!!

Quel masque? J'ai encore la patience, après toutes les insultes que vous avez débitées envers les membres du forum (vous en êtes déjà à deux avertissements), de vous répondre, vous signaler les choses qui ne vont pas, de mon point de vue (je fais du PLPgSQL à longueur d'année), dans votre document, et vous me sortez un discours malodorant, prétentieux, moralisateur sur les utilisateurs de logiciels libres qui sont tous des vilains intégristes qui ne supportent pas que le vrai monde ne soit pas conforme à leurs désirs et qui disent, ô les vilains, du mal des autres produits. Je crois avoir fait preuve jusque là de largement plus de patience et de largeur d'esprit que nécessaire. Vous assimilez toute une communauté au comportement de fanboys, et c'est extrêmement insultant.

Maintenant, j'en suis sûr, j'ai perdu assez de temps avec vous. Fin de la discussion.

gleu · 22/06/2011 23:39:21

Même les outils comme pgAdminIII ont tendance à oublier les schémas SQL, notamment quand vous dzmandez par l'IHM un script SELECT !

C'est faux. pgAdmin ajoute toujours le schéma lorsque ce dernier n'est pas dans le search_path.

SQLpro · 26/06/2011 16:48:26

Mais comme le "serarch path" peut changer à tout moment, je ne voit pas l'intérêt de l'oublier pour ces schémas là.

Et par votre réponse, vous me confirmez bien qu'il ne le met pas lorsque c'est dans le "search path".

A +

gleu · 26/06/2011 22:25:06

Oui, comme tous les autres outils PostgreSQL.

#1 20/06/2011 09:43:23

Développer des fonctions scalaires (UDF) avec PLpgSQL

#2 20/06/2011 11:00:54

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#3 20/06/2011 11:36:13

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#4 20/06/2011 11:53:15

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#5 20/06/2011 12:00:34

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#6 20/06/2011 14:23:24

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#7 20/06/2011 14:46:17

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#8 22/06/2011 23:39:21

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#9 26/06/2011 16:48:26

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

#10 26/06/2011 22:25:06

Re : Développer des fonctions scalaires (UDF) avec PLpgSQL

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux