Les extensions utiles à PostgreSQL™ contiennent généralement plusieurs objets SQL. Par exemple, un nouveau type de données va nécessiter de nouvelles fonctions, de nouveaux opérateurs et probablement de nouvelles méthodes d'indexation. Il peut être utile de les grouper en un unique paquetage pour simplifier la gestion des bases de données. Avec PostgreSQL™, ces paquetages sont appelés extension. Pour créer une extension, vous avez besoin au minimum d'un fichier de script qui contient les commandes SQL permettant de créer ses objets, et un fichier de contrôle qui rapporte quelques propriétés de base de cette extension. Si cette extension inclut du code C, elle sera aussi généralement accompagnée d'une bibliothèque dans lequel le code C aura été compilé. Une fois ces fichiers en votre possession, un simple appel à la commande CREATE EXTENSION(7) vous permettra de charger ses objets dans la base de données.
Le principal avantage des extensions n'est toutefois pas de pouvoir de charger une grande quantité d'objets dans votre base de donnée. Les extensions permettent en effet surtout à PostgreSQL™ de comprendre que ces objets sont liés par cette extension. Vous pouvez par exemple supprimer tous ces objets avec une simple commande DROP EXTENSION(7). Il n'est ainsi pas nécessaire de maintenir un script de « désintallation ». Plus utile encore, l'outil pg_dump saura reconnaître les objets appartenant à une extension et, plutôt que de les extraire individuellement, ajoutera simplement une commande CREATE EXTENSION à la sauvegarde. Ce mécanisme simplifie aussi la migration à une nouvelle version de l'extension qui peut contenir de nouveaux objets ou des objets différents de la version d'origine. Notez bien toutefois qu'il est nécessaire de disposer des fichiers de contrôles, de script, et autres pour permettre la restauration d'une telle sauvegarde dans une nouvelle base de donnée.
PostgreSQL™ ne permet pas de supprimer de manière individuelle les objets d'une extension sans supprimer l'extension tout entière. Aussi, bien que vous ayez la possibilité de modifier la définition d'un objet inclus dans une extension (par exemple via la commande CREATE OR REPLACE FUNCTION dans le cas d'une fonction), il faut garder en tête que cette modification ne sera pas sauvegardée par l'outil pg_dump. Une telle modification n'est en pratique raisonnable que si vous modifiez parallèlement le fichier de script de l'extension. Il existe toutefois des cas particuliers comme celui des tables qui contiennent des données de configuration (voir ci-dessous).
Il existe aussi un mécanisme permettant de créer des scripts de mise à jour de la définition des objets SQL contenus dans une extension. Par exemple, si la version 1.1 d'une extension ajoute une fonction et change le corps d'une autre vis-à-vis de la version 1.0 d'origine, l'auteur de l'extension peut fournir un script de mise à jour qui effectue uniquement ces deux modifications. La commande ALTER EXTENSION UPDATE peut alors être utilisée pour appliquer ces changements et vérifier quelle version de l'extension est actuellement installée sur une base de donnée spécifiée.
Les catégories d'objets SQL qui peuvent être inclus dans une extension sont spécifiées dans la description de la commande ALTER EXTENSION(7). D'une manière générale, les objets qui sont communs à l'ensemble de la base ou du cluster, comme les bases de données, les rôles, les tablespaces ne peuvent être inclus dans une extension car une extension n'est référencée qu'à l'intérieur d'une base de donnée. À noter que rien n'empêche la création de fichier de script qui crée de tels objets, mais qu'ils ne seront alors pas considérés après leur création comme faisant partie de l'extension. À savoir en outre que bien que les tables puissent être incluses dans une extension, les objets annexes tels que les index ne sont pas automatiquement inclus dans l'extension et devront être explicitement mentionnés dans les fichiers de script.
La commande CREATE EXTENSION(7) repose sur un fichier de contrôle associé à chaque extension. Ce fichier doit avoir le même nom que l'extension suivi du suffixe .control, et doit être placé dans le sous-répertoire SHAREDIR/extension du répertoire d'installation. Il doit être accompagné d'au moins un fichier de script SQL dont le nom doit répondre à la syntaxe extension--version.sql (par exemple, foo--1.0.sql pour la version 1.0 de l'extension foo). Par défaut, les fichiers de script sont eux-aussi situés dans le répertoire SHAREDIR/extension. Le fichier de contrôle peut toutefois spécifier un répertoire différent pour chaque fichier de script.
Le format du fichier de contrôle d'une extension est le même que pour le fichier postgresql.conf, à savoir une liste d'affectation nom_paramètre = valeur avec un maximum d'une affectation par ligne. Les lignes vides et les commentaires introduits par # sont eux-aussi autorisés. Prenez garde à placer entre guillemets les valeurs qui ne sont ni des nombres ni des mots isolés.
Un fichier de contrôle peut définir les paramètres suivants :
Le répertoire qui inclut les scripts SQL de l'extension. Si un chemin relatif est spécifié, le sous-répertoire SHAREDIR du répertoire d'installation sera choisi comme base. Le comportement par défaut de ce paramètre revient à le définir tel que directory = 'extension'.
La version par défaut de l'extension, qui sera installée si aucune version n'est spécifiée avec la commande CREATE EXTENSION. Ainsi, bien que ce paramètre puisse ne pas être précisé, il reste recommandé de le définir pour éviter que la commande CREATE EXTENSION ne provoque une erreur en l'absence de l'option VERSION.
Un commentaire de type chaîne de caractère au sujet de l'extension. Une alternative consiste à utiliser la commande COMMENT(7) dans le script de l'extension.
L'encodage des caractères utilisé par les fichiers de script. Ce paramètre doit être spécifié si les fichiers de script contiennent des caractères non ASCII. Le comportement par défaut en l'absence de ce paramètre consiste à utiliser l'encodage de la base de donnée.
La valeur de ce paramètre sera utilisée pour toute référence à MODULE_PATHNAME dans les fichiers de script. Si ce paramètre n'est pas défini, la substitution ne sera pas effectuée. La valeur $libdir/nom_de_bibliothèque lui est usuellement attribuée et dans ce cas, MODULE_PATHNAME est utilisé dans la commande CREATE FUNCTION concernant les fonctions en langage C, de manière à ne pas mentionner « en dur » le nom de la bibliothèque partagée.
Une liste de noms d'extension dont dépend cette extension, comme par exemple requires = 'foo, bar'. Ces extensions doivent être installées avant que l'extension puisse être installée.
Si ce paramètre est à true (il s'agit de la valeur par défaut), seuls les superutilisateurs pourront créer cet extension ou la mettre à jour. Si ce paramètre est à false, seuls les droits nécessaires seront requis pour installer ou mettre à jour l'extension.
Une extension est dite « déplaçable » (relocatable) s'il est possible de déplacer les objets qu'elle contient dans un schéma différent de celui attribué initialement par l'extension. La valeur par défaut est à false, ce qui signifie que l'extension n'est pas déplaçable. Voir ci-dessous pour des informations complémentaires.
Ce paramètre ne peut être spécifié que pour les extensions non déplaçables. Il permet de forcer l'extension à charger ses objets dans le schéma spécifié et aucun autre. Voir ci-dessous pour des informations complémentaires.
En complément au fichier de contrôle extension.control, une extension peut disposer de fichiers de contrôle secondaires pour chaque version dont le nommage correspond à extension--version.control. Ces fichiers doivent se trouver dans le répertoire des fichiers de script de l'extension. Les fichiers de contrôle secondaires suivent le même format que le fichier de contrôle principal. Tout paramètre spécifié dans un fichier de contrôle secondaire surcharge la valeur spécifiée dans le fichier de contrôle principal concernant les installations ou mises à jour à la version considérée. Cependant, il n'est pas possible de spécifier les paramètres directory et default_version dans un fichier de contrôle secondaire.
Un fichier de script SQL d'une extension peut contenir toute commande SQL, à l'exception des commandes de contrôle de transaction (BEGIN, COMMIT, etc), et des commandes qui ne peuvent être exécutées au sein d'un bloc transactionnel (comme la commande VACUUM). Cette contrainte est liée au fait que les fichiers de script sont implicitement exécutés dans une transaction.
An extension's SQL script files can also contain lines beginning with \echo, which will be ignored (treated as comments) by the extension mechanism. This provision is commonly used to throw an error if the script file is fed to psql rather than being loaded via CREATE EXTENSION (see example script below). Without that, users might accidentally load the extension's contents as « loose » objects rather than as an extension, a state of affairs that's a bit tedious to recover from.
Bien que les fichiers de script puissent contenir n'importe quel caractère autorisé par l'encodage spécifié, les fichiers de contrôle ne peuvent contenir que des caractères ASCII non formatés. En effet, PostgreSQL™ ne peut pas déterminer l'encodage utilisé par les fichiers de contrôle. Dans la pratique, cela ne pose problème que dans le cas où vous voudriez utiliser des caractères non ASCII dans le commentaire de l'extension. Dans ce cas de figure, il est recommandé de ne pas utiliser le paramètre comment du fichier de contrôle pour définir ce commentaire, mais plutôt la commande COMMENT ON EXTENSION dans un fichier de script.
Les utilisateurs souhaitent souvent charger les objets d'une extension dans un schéma différent de celui imposé par l'auteur. Trois niveaux de déplacement sont supportés :
Une extension supportant complétement le déplacement peut être déplacé dans un autre schéma à tout moment, y compris après son chargement dans une base de donnée. Initialement, tous les objets de l'extension installée appartiennent à un premier schéma (excepté les objets qui n'appartiennent à aucun schéma comme les langages procéduraux). L'opération de déplacement peut alors être réalisée avec la commande ALTER EXTENSION SET SCHEMA, qui renomme automatiquement tous les objets de l'extension pour être intégrés dans le nouveau schéma. Le déplacement ne sera toutefois fonctionnel que si l'extension ne contient aucune référence de l'appartenance d'un de ses objets à un schéma. Dans ce cadre, il est alors possible de spécifier qu'une extension supporte complétement le déplacement en initialisant relocatable = true dans son fichier de contrôle.
Une extension peut être déplaçable durant l'installation et ne plus l'être par la suite. Un exemple courant est celui du fichier de script de l'extension qui doit référencer un schéma cible de manière explicite pour des fonctions SQL, par exemple en définissant la propriété search_path. Pour de telles extensions, il faut définir relocatable = false dans son fichier de contrôle, et utiliser @extschema@ pour référencer le schéma cible dans le fichier de script. Toutes les occurences de cette chaîne dans le fichier de script seront remplacées par le nom du schéma choisi avant son exécution. Le nom du schéma choisi peut être fixé par l'option SCHEMA de la commande CREATE EXTENSION>.
Si l'extension ne permet pas du tout le déplacement, il faut définir relocatable = false dans le fichier de contrôle, mais aussi définir schema comme étant le nom du schéma cible. Cette précaution permettra d'empêcher l'usage de l'option SCHEMA de la commande CREATE EXTENSION, à moins que cette option ne référence la même valeur que celle spécifiée dans le fichier de contrôle. Ce choix est à priori nécessaire si l'extension contient des références à des noms de schéma qui ne peuvent être remplacés par @extschema@. À noter que même si son usage reste relativement limité dans ce cas de figure puisque le nom du schéma est alors fixé dans le fichier de contrôle, le mécanisme de substitution de @extschema@ reste toujours opérationnel.
Dans tous les cas, le fichier de script sera exécuté avec comme valeur de search_path le schéma cible. Cela signifie que la commande CREATE EXTENSION réalisera l'équivalent de la commande suivante :
SET LOCAL search_path TO @extschema@;
Cela permettra aux objets du fichier de script d'être créés dans le schéma cible. Le fichier de script peut toutefois modifier la valeur de search_path si nécessaire, mais cela n'est généralement pas le comportement souhaité. La variable search_path retrouvera sa valeur initiale à la fin de l'exécution de la commande CREATE EXTENSION.
Le schéma cible est déterminé par le paramètre schema dans le fichier de contrôle s'il est précisé, sinon par l'option SCHEMA de la commande CREATE EXTENSION si elle est spécifiée, sinon par le schéma de création par défaut actuel (le premier rencontré en suivant le chemin de recherche search_path de l'appelant). Quand le paramètre schema du fichier de contrôle est utilisé, le schéma cible sera créé s'il n'existe pas encore. Dans les autres cas, il devra exister au préalable.
Si des extensions requises sont définies par requires dans le fichier de contrôle, leur schéma cible est ajouté à la valeur initiale de search_path. Cela permet à leurs objets d'être visibles dans le fichier de script de l'extension installée.
Une extension peut contenir des objets répartis dans plusieurs schémas. Il est alors conseillé de regrouper dans un unique schéma l'ensemble des objets destinés à un usage externe à l'extension, qui sera alors le schéma cible de l'extension. Une telle organisation est compatible avec la définition par défaut de search_path pour la création d'extensions qui en seront dépendantes.
Certaines extensions incluent des tables de configuration, contenant des données qui peuvent être ajoutées ou changées par l'utilisateur après l'installation de l'extension. Normalement, si la table fait partie de l'extension, ni la définition de la table, ni son contenu ne sera sauvegardé par pg_dump. Mais ce comportement n'est pas celui attendu pour une table de configuration. Les données modifiées par un utilisateur nécessitent d'être sauvegardées, ou l'extension aura un comportement différent après rechargement.
Pour résoudre ce problème, un fichier de script d'extension peut marquer une table comme étant une table de configuration, ce qui indiquera à pg_dump d'inclure le contenu de la table (et non sa définition) dans la sauvegarde. Pour cela, il s'agit d'appeler la fonction pg_extension_config_dump(regclass, text) après avoir créé la table, par exemple
CREATE TABLE my_config (key text, value text);
SELECT pg_catalog.pg_extension_config_dump('my_config', '');
Cette fonction permet de marquer autant de tables que nécessaire.
Si le second argument de pg_extension_config_dump est une chaîne vide, le contenu entier de la table sera sauvegardé par l'application pg_dump. Cela n'est correct que si la table était initialement vide après l'installation du script. Si un mélange de données initiales et de données ajoutées par l'utilisateur est présent dans la table, le second argument de pg_extension_config_dump permet de spécifier une condition WHERE qui selectionne les données à sauvegarder. Par exemple, vous pourriez faire
CREATE TABLE my_config (key text, value text, standard_entry boolean);
SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');
et vous assurer que la valeur de standard_entry soit true uniquement lorsque les lignes ont été créées par le script de l'extension.
Des situations plus compliquées, comme des données initiales qui peuvent être modifiées par l'utilisateur, peuvent être prises en charge en créant des triggers sur la table de configuration pour s'assurer que les lignes ont été marquées correctement.
Vous pouvez modifier la condition du filtre associé avec une table de configuration en appelant de nouveau pg_extension_config_dump. (Ceci serait typiquement utile dans un script de mise à jour d'extension.) La seule façon de marquer une table est de la dissocier de l'extension avec la commande ALTER EXTENSION ... DROP TABLE.
Un des avantages du mécanisme d'extension est de proposer un moyen simple de gérer la mise à jour des commandes SQL qui définissent les objets de l'extension. Cela est rendu possible par l'association d'un nom ou d'un numéro de version à chaque nouvelle version du script d'installation de l'extension. En complément, si vous voulez qu'un utilisateur soit capable de mettre à jour sa base de données dynamiquement d'une version à une autre, vous pouvez fournir des scripts de mise à jour qui feront les modifications nécessaires. Les scripts de mise à jour ont un nom qui correspond au format extension--oldversion--newversion.sql (par exemple, foo--1.0--1.1.sql contient les commandes pour modifier la version 1.0 de l'extension foo en la version 1.1).
En admettant qu'un tel script de mise à jour soit disponible, la commande ALTER EXTENSION UPDATE mettra à jour une extension installée vers la nouvelle version spécifiée. Le script de mise à jour est exécuté dans le même environnement que celui que la commande CREATE EXTENSION fournit pour l'installation de scripts : en particulier, la variable search_path est définie de la même façon et tout nouvel objet créé par le script est automatiquement ajouté à l'extension.
Si une extension a un fichier de contrôle secondaire, les paramètres de contrôle qui sont utilisés par un script de mise à jour sont ceux définis par le script de la version cible.
Le mécanisme de mise à jour peut être utilisé pour résoudre un cas particulier important : convertir une collection éparse d'objets en une extension. Avant que le mécanisme d'extension ne soit introduit à PostgreSQL™ (dans la version 9.1), de nombreuses personnes écrivaient des modules d'extension qui créaient simplement un assortiment d'objets non empaquetés. Etant donné une base de donnée existante contenant de tels objets, comment convertir ces objets en des extensions proprement empaquetées ? Les supprimer puis exécuter la commande CREATE EXTENSION est une première méthode, mais elle n'est pas envisageable lorsque les objets ont des dépendances (par exemple, s'il y a des colonnes de table dont le type de données appartient à une extension). Le moyen proposé pour résoudre ce problème est de créer une extension vide, d'utiliser la commande ALTER EXTENSION ADD pour lier chaque objet pré-existant à l'extension, et finalement créer les nouveaux objets présents dans la nouvelle extension mais absents de celle non empaquetée. La commande CREATE EXTENSION prend en charge cette fonction avec son option FROM old_version, qui permet de ne pas charger le script d'installation par défaut pour la version ciblée, mais celui nommé extension--old_version--target_version.sql. Le choix de la valeur de old_version relève de la responsabilité de l'auteur de l'extension, même si unpackaged est souvent rencontré. Il est aussi possible de multiplier les valeurs de old_version pour prendre en compte une mise à jour depuis différentes anciennes versions.
La commande ALTER EXTENSION peut exécuter des mises à jour en séquence pour réussir une mise à jour. Par exemple, si seuls les fichiers foo--1.0--1.1.sql et foo--1.1--2.0.sql sont disponibles, la commande ALTER EXTENSION les exécutera séquentiellement si une mise à jour vers la version 2.0 est demandée alors que la version 1.0 est installée.
PostgreSQL™ ne suppose rien au sujet des noms de version. Par exemple, il ne sait pas si 1.1 suit 1.0. Il effectue juste une correspondance entre les noms de version et suit un chemin qui nécessite d'appliquer le moins de fichier de script possible. Un nom de version peut en réalité être toute chaîne qui ne contiendrait pas -- ou qui ne commencerait ou ne finirait pas par -.
Il peut parfois être utile de fournir des scripts de retour en arrière, comme par exemple foo--1.1--1.0.sql pour autoriser d'inverser les modifications effectuées par la mise à jour en version 1.1. Si vous procédez ainsi, ayez conscience de la possibilité laissée à PostgreSQL™ d'exécuter un tel script de retour en arrière s'il permet d'atteindre la version cible d'une mise à jour en un nombre réduit d'étapes. La cause du risque se trouve dans les scripts de mise à jour optimisés permettant de passer plusieurs versions en un seul script. La longueur du chemin commençant par un retour en arrière suivi d'un script optimisé pourrait être inférieure à la longueur du chemin qui monterait de version une par une. Si le script de retour en arrière supprime un objet irremplaçable, les conséquences pourraient en être facheuses.
Pour vérifier que vous ne serez pas confronté à des chemins de mise à jour inattendus, utilisez cette commande :
SELECT * FROM pg_extension_update_paths('extension_name');
Cette commande permet d'afficher chaque paire de noms de version connues pour l'extension spécifiée, ainsi que le chemin de mise à jour qui serait suivi depuis la version de départ jusque la version cible, ou la valeur NULL si aucun chemin valable n'est disponible. Le chemin est affiché sous une forme textuelle avec des séparateurs --. Vous pouvez utiliser regexp_split_to_array(path,'--') si vous préférez le format tableau.
Ci-après, un exemple complet d'une extension écrite uniquement en SQL, un type composite de deux éléments qui peut stocker n'importe quelle valeur dans chaque emplacement, qui sont nommés « k » et « v ». Les valeurs non textuelles sont automatiquement changées en texte avant stockage.
Le fichier de script pair--1.0.sql ressemble à ceci:
-- se plaint si le script est exécuté directement dans psql, plutôt que via CREATE EXTENSION \echo Use "CREATE EXTENSION pair" to load this file. \quit CREATE TYPE pair AS ( k text, v text ); CREATE OR REPLACE FUNCTION pair(anyelement, text) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair'; CREATE OR REPLACE FUNCTION pair(text, anyelement) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair'; CREATE OR REPLACE FUNCTION pair(anyelement, anyelement) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair'; CREATE OR REPLACE FUNCTION pair(text, text) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair;'; CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = anyelement, PROCEDURE = pair); CREATE OPERATOR ~> (LEFTARG = anyelement, RIGHTARG = text, PROCEDURE = pair); CREATE OPERATOR ~> (LEFTARG = anyelement, RIGHTARG = anyelement, PROCEDURE = pair); CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, PROCEDURE = pair);
Le fichier de contrôle pair.control ressemble à ceci:
# extension pair comment = 'Un type de donnees representant un couple clef/valeur' default_version = '1.0' relocatable = true
Si vous avez besoin d'un fichier d'installation pour installer ces deux fichiers dans le bon répertoire, vous pouvez utiliser le fichier Makefile qui suit :
EXTENSION = pair DATA = pair--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS)
Ce fichier d'installation s'appuye sur PGXS, qui est décrit dans Section 35.16, « Outils de construction d'extension ». La commande make install va installer les fichiers de contrôle et de script dans le répertoire adéquat tel qu'indiqué par pg_config.
Une fois les fichiers installés, utilisez la commande CREATE EXTENSION(7) pour charger les objets dans une base de donnée.
Interfacer des extensions d'index
