F.38. postgres_fdw -- accéder des données enregistrées dans des serveurs PostgreSQL externes

» » »

Précédent	Niveau supérieur	Suivant
F.37. pg_walinspect -- inspection bas niveau des journaux de transaction	Sommaire	F.39. seg -- un type de données pour les segments de ligne et pour les intervalles en virgule flottante

F.38. postgres_fdw -- accéder des données enregistrées dans des serveurs PostgreSQL externes #

F.38.1. Options FDW de postgres_fdw
F.38.2. Fonctions
F.38.3. Gestion des connexions
F.38.4. Gestion des transactions
F.38.5. Optimisation des requêtes distantes
F.38.6. Environnement d'exécution de requêtes distantes
F.38.7. Compatibilité entre versions
F.38.8. Paramètres de configuration
F.38.9. Exemples
F.38.10. Auteur

Le module postgres_fdw fournit le wrapper de données distantes postgres_fdw, dont le but est de donner accès à des données enregistrées dans des serveurs PostgreSQL externes.

Les fonctionnalités proposées par ce module sont à peu près les mêmes que celles proposées par le module dblink. Mais postgres_fdw fournit une syntaxe plus transparente et respectant les standards pour l'accès à des tables distantes. Elle peut aussi donner de meilleures performances dans beaucoup de cas.

Pour préparer un accès distant en utilisant postgres_fdw :

Installez l'extension postgres_fdw en utilisant CREATE EXTENSION.
Créez un objet serveur distant en utilisant CREATE SERVER, pour représenter chaque base distante à laquelle vous souhaitez vous connecter. Indiquez les informations de connexions, sauf user et password, comme options de l'objet serveur.
Créez une correspondance d'utilisateur avec CREATE USER MAPPING pour chaque utilisateur de la base que vous voulez autoriser à accéder à un serveur distant. Indiquez le nom et le mot de passe de l'utilisateur distant avec les options user et password de la correspondance d'utilisateur.
Créez une table distante avec CREATE FOREIGN TABLE ou IMPORT FOREIGN SCHEMA pour chaque table distante que vous voulez utilisé. Les colonnes de la table distante doit correspondre aux colonnes de la table sur le serveur distant. Néanmoins, vous pouvez utiliser un nom de table et des noms de colonne différents de ceux de la table sur le serveur distant si vous indiquez les bons noms de colonne en options de la table distante.

Maintenant, vous avez seulement besoin de SELECT sur la table distante pour accéder aux données de la table du serveur distant. Vous pouvez aussi modifier la table sur le serveur distant en utilisant les commandes INSERT, UPDATE, DELETE, COPY et TRUNCATE. (Bien sûr, l'utilisateur distant que vous avez spécifié dans vos correspondances d'utilisateur doit avoir les droits pour réaliser ces actions.)

Notez que l'option ONLY spécifiée dans SELECT, UPDATE, DELETE ou TRUNCATE n'a pas d'effet lors des accès ou modifications d'une table distante.

Notez que postgres_fdw n'a pour l'instant pas de support pour les instructions INSERT avec une clause ON CONFLICT DO UPDATE. Néanmoins, la clause ON CONFLICT DO NOTHING est supporté, si la spécification de l'index unique est omise. Notez aussi que postgres_fdw supporte le déplacement de ligne demandé par des instructions UPDATE exécutées sur des tables partitionnées, mais il ne gère pas le cas où une partition distante choisir pour insérer une ligne est aussi une partition cible d'UPDATE qui sera mise à jour ailleurs dans la même commande.

Il est généralement recommandé que les colonnes d'une table distante soient déclarées avec exactement les mêmes types de données et le même collationnement que celles utilisées pour les colonnes référencées dans le table du serveur distant. Bien que postgres_fdw est actuellement assez lâche sur les conversions de type de données, des anomalies sémantiques surprenantes peuvent survenir quand les types ou les collationnements ne correspondent pas dans le cas où le serveur distant interprète légèrement différemment les conditions de la requête.

Notez qu'une table distante peut être déclarée avec moins de colonnes ou avec les colonnes dans un ordre différent. La correspondance des colonnes sur la table du serveur distant se fait par nom, et non pas par position.

F.38.1. Options FDW de postgres_fdw #

F.38.1.1. Options de connexions #

Un serveur distant utilisant le wrapper de données distantes postgres_fdw peut avoir les mêmes options que celles acceptées par libpq dans les chaînes de connexion comme décrit dans Section 34.1.2. Cependant, ces options ne sont pas autorisées ou sont gérées d'une façon spéciale :

user, password et sslpassword (spécifiez-les au niveau de la correspondance d'utilisateur, ou utilisez un fichier service)
client_encoding (ceci est configuré automatiquement à partir de l'encodage du serveur local)
application_name - ceci pourrait apparaître dans une ou les deux connexion et postgres_fdw.application_name. Si les deux sont présents, postgres_fdw.application_name surcharge le paramètre de connexion. Contrairement à libpq, postgres_fdw permet à application_name d'inclure des « séquences d'échappement ». Voir postgres_fdw.application_name pour les détails.
fallback_application_name (toujours configuré à postgres_fdw)
sslkey et sslcert - ils peuvent apparaître soit dans une connexion, soit dans la correspondance d'utilisateur soit dans les deux. Si ce dernier cas est vrai, la configuration de la correspondance d'utilisateur surcharge la configuration de la connexion.

Des utilisateurs non-superutilisateur peuvent se connecter à des serveurs distants en utilisant l'authentification par mot de passe ou les pouvoirs délégués GSSAPI, donc indiquez l'option password pour la correspondance d'utilisateur appartenant à de non-superutilisateurs où l'authentification par mot de passe est requise.

Seuls les superutilisateurs peuvent se connecter à un serveur distant sans authentification par mot de passe. Donc spécifiez toujours l'option password pour les correspondances d'utilisateur appartenant aux utilisateurs simples.

Un superutilisateur peut dépasser cette vérification sur une base par-correspondance-utilisateur en configurant l'option password_required 'false', par exemple :

ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

Pour empêcher des utilisateurs sans droit d'exploiter les droits d'authentification de l'utilisateur unix utilisé par le serveur PostgreSQL pour escalader vers des droits superutilisateur, seul le superutilisateur peut configurer cette option sur une correspondance d'utilisateur.

Une grande attention est nécessaire pour s'assurer que cela n'autorise pas l'utilisateur à se connecter comme un superutilisateur sur la base de données distante d'après les CVE-2007-3278 et CVE-2007-6601. Ne configurez pas password_required=false sur le rôle public. Gardez en tête que l'utilisateur peut potentiellement utiliser tout certificat client, le fichier .pgpass, le fichier .pg_service.conf, et les autres fichiers se trouvant dans le répertoire personnel unix de l'utilisateur système qui exécute le serveur PostgreSQL. Ils peuvent aussi utiliser toute relation de confiance autorisée par les modes d'authentification comme peer et ident.

F.38.1.2. Options pour le nom de l'objet #

Ces options peuvent être utilisées pour contrôler les noms utilisés dans les requêtes SQL envoyées au serveur PostgreSQL distant. Ces options sont nécessaires lorsqu'une table distante est créée avec des noms différents de ceux de la table du serveur distant.

schema_name (string): Cette option, qui peut être indiquée pour une table distante, donne le nom du schéma à utiliser pour la table du serveur distant. Si cette option est omise, le nom du schéma de la table distante est utilisé.
table_name (string): Cette option, qui peut être indiquée pour une table distante, donne le nom de la table à utiliser pour la table du serveur distant. Si cette option est omise, le nom de la table distante est utilisé.
column_name (string): Cette option, qui peut être indiquée pour une colonne d'une table distante, donne le nom de la colonne à utiliser pour la colonne de la table du serveur distant. Si cette option est omise, le nom de la colonne de la table distante est utilisé.

F.38.1.3. Options d'estimation du coût #

postgres_fdw récupère des données distantes en exécutant des requêtes sur des serveurs distants. Idéalement, le coût estimé du parcours d'une table distante devrait être celui occasionné par le parcours de la table sur le serveur distant, et un supplément causé par la communication entre le serveur local et le serveur distant. Le moyen le plus fiable d'obtenir une telle estimation est de demander au serveur distant, puis d'ajouter quelque chose pour le supplément. Pour des requêtes simples, cela ne vaut pas le coût d'une requête supplémentaire vers le serveur distant. Donc postgres_fdw propose les options suivantes pour contrôler la façon dont l'estimation de coût est faite :

use_remote_estimate (boolean): Cette option, qui peut être indiquée pour une table distante ou pour un serveur distant, contrôle si postgres_fdw exécute des commandes EXPLAIN distantes pour obtenir les estimations de coût. Une configuration sur la table distante surcharge celle sur le serveur, mais seulement pour cette table. La valeur par défaut est false.
fdw_startup_cost (floating point): Cette option, qui peut être indiquée pour un serveur distant, est une valeur numérique qui est ajoutée au coût de démarrage estimé de tout parcours de table distante sur ce serveur. Cela représente le coût supplémentaire causé par l'établissement d'une connexion, l'analyse et la planification de la requête du côté du serveur distant, etc. La valeur par défaut est 100.
fdw_tuple_cost (floating point): Cette option, qui peut être indiquée pour un serveur distant, est une valeur en virgule flottante qui est utilisée comme coût supplémentaire par ligne pour les parcours de la table distante sur ce serveur. Cela représente le coût supplémentaire associé au transfert de données entre les serveurs. Vous pouvez augmenter ou réduire ce nombre pour refléter les latences réseau vers le serveur distant. La valeur par défaut est 0.01.

Quand use_remote_estimate est vrai, postgres_fdw obtient le nombre de lignes et les estimations de coût à partir du serveur distant. Il ajoute fdw_startup_cost et fdw_tuple_cost aux estimations de coût. Quand use_remote_estimate est faux, postgres_fdw réalise le décompte local des lignes ainsi que l'estimation de coût, puis ajoute fdw_startup_cost et fdw_tuple_cost aux estimations de coût. Cette estimation locale a peu de chances d'être précise sauf si des copies locales des statistiques de la table distante sont disponibles. Exécuter ANALYZE sur la table distante permet de mettre à jour les statistiques locales ; cela exécute un parcours sur la table distante, puis calcule et enregistre les statistiques comme si la table était locale. Garder des statistiques locales peut être utile pour réduire la surcharge de planification par requête pour une table distante mais, si la table distante est fréquemment mise à jour, les statistiques locales seront rapidement obsolètes.

L'option suivante contrôle comme une telle opération ANALYZE se comporte :

analyze_sampling (string): Cette option, qui peut être indiquée pour une table distante ou pour un serveur distant, détermine si ANALYZE sur une table distante échantillonne les données du côté distant, ou lit et transfert toutes les données, et réalise l'échantillonnage localement. Les valeurs acceptées sont off, random, system, bernoulli et auto. off désactive l'échantillonnage à distance, donc toutes les données sont transférées et échantillonnées localement. random réalise un échantillonnage à distance en utilisant la fonction random() pour choisir les lignes renvoyées, alors que system et bernoulli se basent sur les méthodes TABLESAMPLE natives du même nom. random fonctionne sur toutes les versions de serveur distant, alors que TABLESAMPLE n'est accepté qu'à partir de la version 9.5. auto (la valeur par défaut) sélectionne l'échantillonnage recommandée automatiquement ; actuellement, cela signifie soit bernoulli soit random suivant la version du serveur distant.

F.38.1.4. Options d'exécution à distance #

Par défaut, seules les clauses WHERE utilisant des opérateurs et des fonctions intégrés sont considérés pour une exécution sur le serveur distant. Les clauses impliquant des fonctions non intégrées sont vérifiées localement une fois les lignes récupérées. Si ces fonctions sont disponibles sur le serveur distant et peuvent produire les mêmes résultats que localement, les performances peuvent être améliorées en envoyant ces clauses WHERE pour une exécution distante. Ce comportement peut être contrôlé en utilisant l'option suivante :

extensions (string)

Cette option est une liste de noms d'extensions PostgreSQL, séparés par des virgules, installées dans des versions compatibles sur les serveurs local et distant. Les fonctions et opérateurs immutables et appartenant à une extension listée seront considérées pour une exécution sur le serveur distant. Cette option peut seulement être spécifiée sur les serveurs distants, et non pas par table.

Lors de l'utilisation de l'option extensions, il est de la responsabilité de l'utilisateur que les extensions listées existent bien et se comportent de façon identique sur les serveurs local et distant. Dans le cas contraire, les requêtes pourraient échouer ou se comporter de façon inattendue.

fetch_size (integer)

Cette option indique le nombre de lignes que postgres_fdw doit récupérer à chaque opération de lecture. Cette option est disponible au niveau serveur et table. Une configuration spécifiée sur une table surcharge celle du serveur. La valeur par défaut est 100.

batch_size (integer)

Cette option spécifie le nombre de lignes que postgres_fdw doit insérer à chaque opération d'insertion. Elle est disponible au niveau serveur et/ou table distante. L'option spécifiée sur une table surcharge celle spécifiée sur le serveur. La valeur par défaut est 1.

Notez que le nombre réel de lignes qu'insère postgres_fdw en une fois dépend du nombre de colonnes et de la valeur fournie pour batch_size. Le batch est exécuté comme une seule requête, et le protocole libpq (qu'utilise postgres_fdw pour se connecter au serveur distant) limitent le nombre de paramètres dans une seule requête à 65535. Quand le nombre de colonne * batch_size dépasse la limite, le paramètre batch_size sera ajusté pour éviter une erreur.

Cette option s'applique aussi lors de la copie dans des tables distantes. Dans ce cas, le nombre réel de lignes que postgres_fdw copie à la fois est déterminé d'une façon similaire au cas de l'insertion, mais c'est limité à au plus 1000 du fait des restrictions d'implémentation de la commande COPY.

F.38.1.5. Options d'exécution asynchrone #

postgres_fdw supporte les exécutions asynchrones, qui exécutent plusieurs parties d'un nœud Append de manière concurrente plutôt qu'en série pour améliorer les performances. L'exécution peut être contrôlée en utilisant l'option suivante :

async_capable (boolean)

Cette option contrôle si postgres_fdw permet de parcourir de manière concurrente les tables distantes pour les exécutions asynchrones. Elle est configurable au niveau du serveur distant et/ou de la table distante. L'option spécifiée au niveau table surcharge l'option au niveau serveur. La valeur par défaut est false.

Pour s'assurer que les données retournées depuis un serveur distant sont cohérentes, postgres_fdw n'ouvrira qu'une seule connexion pour un serveur distant et exécutera les requêtes sur ce serveur séquentiellement même si de nombreuses tables distantes sont impliquées, sauf si ces tables sont sujettes à différentes correspondances d'utilisateur. Dans ce cas, il peut être plus performant de désactiver cette option pour éliminer la surcharge associée à l'exécution asynchrone des requêtes.

Une exécution asynchrone est réalisée quand un nœud Append contient aussi bien un ou des sous-plans exécutés de manière synchrone que un ou des sous-plans exécutés de manière asynchrone. Dans un tel cas, si les sous-plans asynchones sont ceux traités par postgres_fdw, les lignes des sous-plans asynchrones ne sont pas retournés avant qu'au moins un sous-plan synchrone retourne toutes ses lignes, car ce sous-plan est exécuté pendant que les sous-plans aynchrones attendent les résultats des requêtes asynchrones envoyées au serveur distant. Ce comportement pourrait changer dans une version future.

F.38.1.6. Options de gestion de la transaction #

Comme décrit dans la section sur la gestion des transactions, avec postgres_fdw, les transactions sont gérées en créant des transactions distantes correspondantes et les sous-transactions sont gérées par la création de sous-transactions distantes correspondantes. Lorsque plusieurs transactions distantes sont impliquées dans la transaction locale courante, par défaut, postgres_fdw valide ou annule les transactions distantes en série quand la transaction locale est validée ou annulée. Lorsque plusieurs sous-transactions distantes sont impliquées dans la sous-transaction locale courante, par défaut, postgres_fdw valide ou annule ces sous-transactions distantes en série quand la sous-transaction locale est validée ou annulée. Les performances peuvent être améliorées avec les options suivantes :

parallel_commit (boolean): Cette option contrôle si postgres_fdw valide en parallèle des transactions distantes ouvertes à partir d'un serveur distant dans une transaction locale quand la transaction locale est validée. Ce paramètre s'applique aussi aux sous-transactions locales et distantes. Cette option peut seulement être données aux serveurs distants, et non pas par table. La valeur par défaut est false.
parallel_abort (boolean): Cette option contrôle si postgres_fdw annule, en parallèle, des transactions distantes ouvertes sur un serveur distant dans une transaction locale quand la transaction locale est annulée. Cette configuration s'applique aussi aux sous-transactions distantes et locales. Cette option peut seulement être indiquée pour les serveurs distants, et non par table. La valeur par défaut est false.

Si plusieurs serveurs distants avec ces options activées sont impliqués dans une transaction locale, plusieurs transactions distantes sur ces serveurs distants sont validées ou annulées en parallèle à travers ces serveurs distants quand la transaction locale est validée ou annulée.

Quand ces options sont activées, un serveur distant avec plusieurs transactions distantes pourrait voir un impact négatif sur les performances quand la transaction locale est validée ou annulée.

F.38.1.7. Options de mise à jour #

Par défaut, toutes les tables distantes utilisant postgres_fdw sont supposées comme étant modifiables. Cela peut se surcharger en utilisant l'option suivante :

updatable (boolean)

Cette option contrôle si postgres_fdw autorise les tables distantes à être modifiées en utilisant les commandes INSERT, UPDATE et DELETE. Cette option est utilisable sur une table distante ou sur un serveur distant. La configuration de cette option au niveau table surcharge celle au niveau serveur. La valeur par défaut est true.

Bien sûr, si la table distante n'est pas modifiable, une erreur surviendra malgré tout. L'utilisation de cette option permet principalement que l'erreur soit renvoyée localement, sans avoir à tenter l'exécution sur le serveur distant. Notez néanmoins que les vues information_schema indiqueront que la table distante est modifiable ou pas, suivant la configuration de cette option, et donc sans vérification du serveur distant.

F.38.1.8. Options de troncabilité #

Par défaut, toutes les tables distantes utilisant postgres_fdw sont considérées comme troncable. Ceci peut être surchargé en utilisant l'option suivante :

truncatable (boolean)

Cette option contrôle si postgres_fdw permet aux tables distantes d'être tronquées en utilisant la commande TRUNCATE. Elle est disponible au niveau du server distant ainsi qu'à celui de la table distante. L'option au niveau table surcharge l'option au niveau serveur. La valeur par défaut est true.

Bien sûr, si la table distante n'est en fait pas troncable, une erreur se produira de toute façon. L'utilisation principale de cette option permet que l'erreur soit déclenchée localement sans requêter le serveur distant.

F.38.1.9. Options d'import #

postgres_fdw est capable d'importer les définitions des tables distantes en utilisant IMPORT FOREIGN SCHEMA. Cette commande crée les définitions des tables distantes sur le serveur local, correspondant aux tables et vues présentes sur le serveur distant. Si les tables distantes à importer ont des colonnes de type défini par des utilisateurs, le serveur local doit avoir des types compatibles de même nom.

Le comportement de l'import est personnalisable avec les options suivantes (à fournir à la commande IMPORT FOREIGN SCHEMA) :

import_collate (boolean)

Cette option contrôle si les options COLLATE d'une colonne sont inclus dans les définitions des tables distantes importées à partir d'un serveur distant. La valeur par défaut est true. Vous pourriez avoir besoin de la désactiver si le serveur distant possède un ensemble de noms de collation différent de celui du serveur local, ce qui risque d'être le cas s'il utilise un autre système d'exploitation. Néanmoins, si vous le faites, il existe un risque réel que les collations importées dans colonnes de la table ne correspondent pas aux données sous-jacentes, résultant en un comportement anormal des requêtes.

Même quand ce paramètre est configuré à true, l'import des colonnes dont la collation est la valeur par défaut du serveur distant peut êre risqué. Elles seront importées avec COLLATE "default", ce qui sélectionnera la collation par défaut du serveur local, qui pourrait être différent.

import_default (boolean)

Cette option contrôle si les expressions DEFAULT d'une colonne sont incluses dans les définitions des tables distantes importées d'un serveur distant. La valeur par défaut est false. Si vous activez cette option, faites attention au fait que les valeurs par défaut pourraient être calculées différemment sur le serveur local et sur le serveur distant ; par exemple, nextval() est une source habituelle de problèmes. La commande IMPORT échouera si une expression par défaut importée utilise une fonction ou un opérateur qui n'existe pas localement.

import_generated (boolean)

Cette option contrôle si les expressions GENERATED de colonnes sont incluses dans les définitions des tables distantes importées à partir d'un serveur distant. La valeur par défaut est true. La commande IMPORT échouera si une expression importée utilise une fonction ou un opérateur qui n'existe pas localement.

import_not_null (boolean)

Cette option contrôle si les contraintes NOT NULL des colonnes sont incluses dans les définitions des tables distantes importées à partir d'un serveur distant. La valeur par défaut est true.

Notez que les contraintes autres que NOT NULL ne seront jamais importées des tables distantes. Bien que PostgreSQL supporte les contraintes CHECK sur les tables distantes, rien n'existe pour les importer automatiquement à cause du risque qu'une expression de contrainte puisse être évaluée différemment entre les serveurs local et distant. Toute incohérence du comportement d'une contrainte CHECK pourrait amener des erreurs difficile à détecter dans l'optimisation des requêtes. Donc, si vous souhaitez importer les contraintes CHECK, vous devez le faire manuellement et vous devez vérifier la sémantique de chaque contrainte avec attention. Pour plus de détails sur le traitement des contraintes CHECK sur les tables distantes, voir CREATE FOREIGN TABLE.

Les tables ou tables distantes qui sont des partitions d'autres tables sont imported only when they are explicitly specified in LIMIT TO clause. Otherwise they are automatically excluded from IMPORT FOREIGN SCHEMA. Since all data can be accessed through the partitioned table which is the root of the partitioning hierarchy, importing only partitioned tables should allow access to all the data without creating extra objects.

F.38.1.10. Options de gestion de connexions #

Par défaut, toutes les connexions que postgres_fdw établit au serveur distant sont conservées ouvertes en session locale pour une réutilisation.

keep_connections (boolean): Cette option contrôle si postgres_fdw conserve les connexions au serveur distant ouvertes, permettant aux requêtes ultérieures de les réutiliser. Elle n'est disponible qu'au niveau serveur distant. La valeur par défaut est on. Si elle est configurée à off, toutes les connexions à ce serveur distant seront supprimées à la fin de chaque transaction.

F.38.2. Fonctions #

postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) retourne un setof record

Cette fonction renvoie les noms des serveurs distants de toutes les connexions ouvertes que postgres_fdw a établies. Elle renvoie aussi la validité ou non de chaque connexion. false est retourné si la connexion au serveur distant est utilisé dans la transaction courante locale sauf si le serveur distant ou les correspondances d'utilisateur sont modifiées ou supprimées(notez que le nom de serveur d'une connexion invalide sera NULL si le serveur est supprimé), et ainsi de telles connexions invalides seront fermées à la fin de cette transaction. true est retournée dans les autres cas. S'il n'y a pas de connexions ouvertes, aucun enregistrement n'est retourné. Exemple d'utilisation de cette fonction :

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;

 server_name | valid
-------------+-------
 loopback1   | t
 loopback2   | f

postgres_fdw_disconnect(server_name text) retourne un boolean

Cette fonction supprime les connexions ouvertes qui sont établies par postgres_fdw depuis une session locale à un serveur distant avec un nom donné. Notez qu'il peut y avoir de multiples connexions au serveur distant donné en utilisant différentes correspondances d'utilisateur. Si les connexions sont utilisées dans la transaction courante locale, elles ne sont pas déconnectées et des messages d'alerte sont affichés. La fonction retourne true si elle déconnecte au moins une connexion, sinon false. Si aucun serveur distant avec un nom donné n'est trouvé, une erreur est affichée. Exemple d'utilisation de la fonction :

postgres=# SELECT postgres_fdw_disconnect('loopback1');

 postgres_fdw_disconnect
-------------------------
 t

postgres_fdw_disconnect_all() retourne un boolean

La fonction supprime toutes les connexions ouvertes établies par postgres_fdw depuis la session locale au serveur distant. Si les connexions sont utilisées dans la transaction locale courante, elles ne sont pas déconnectées et des messages d'alerte sont affichés. La fonction retourne true si elle déconnecte au moins une connexion, sinon false. Exemple d'utilisation de la fonction :

postgres=# SELECT postgres_fdw_disconnect_all();

 postgres_fdw_disconnect_all
-----------------------------
 t

F.38.3. Gestion des connexions #

postgres_fdw établit une connexion au serveur distant lors de la première requête qui utilise une table distante associée avec le serveur distant. Par défaut cette connexion est conservée et ré-utilisée pour les requêtes suivantes dans la même session. Ce comportement peut être contrôlé en utilisant l'option keep_connections pour un serveur distant. Si plusieurs correspondances d'utilisateur sont utilisées pour accéder au serveur distant, une connexion est établie pour chaque correspondance d'utilisateur.

Lors de changement de la définition ou suppression d'un serveur distant ou de correspondances d'utilisateur, les connexions associées sont fermées. Mais notez que si des connexions sont utilisées dans la transaction locale courante, elles sont conservées jusqu'à la fin de transaction. Les connexions fermées seront ré-établies quand cela sera nécessaire par les futures requêtes utilisant une table distante.

Une fois qu'une connexion à un serveur distant a été établie, elle est par défaut conservée jusqu'à ce que la session locale ou distante correspondante termine. Pour déconnecter une connexion explicitement, l'option keep_connections pour un serveur distant peut être désactivée. Il est possible aussi d'utiliser les fonctions postgres_fdw_disconnect et postgres_fdw_disconnect_all. Par exemple, ces dernières sont utiles pour fermer les connexions qui ne sont plus nécessaires, libérant ainsi les connexions sur le serveur distant.

F.38.4. Gestion des transactions #

Lorsqu'une requête référence des tables sur un serveur distant, postgres_fdw ouvre une transaction sur le serveur distant si une transaction n'est pas déjà ouverte pour la transaction locale en cours. La transaction distante est validée ou annulée suivant que la transaction locale est validée ou annulée. Les points de sauvegardes sont gérés de la même façon en créant les points de sauvegarde correspondants.

La transaction distante utilise le niveau d'isolation SERIALIZABLE quand la transaction locale a le niveau SERIALIZABLE. Dans les cas contraires, elle utilise le niveau REPEATABLE READ. Ce choix assure que, si une requête réalise plusieurs parcours de table sur le serveur distant, elle obtiendra des résultats cohérents pour tous les parcours. Une conséquence est que les requêtes successives à l'intérieur d'une seule transaction verront les mêmes données provenant du serveur distant, même si des mises à jour sont réalisées en même temps avec l'activité standard du serveur. Ce comportement serait attendue de toute façon si la transaction locale utilise le niveau d'isolation SERIALIZABLE ou REPEATABLE READ mais elle pourrait surprendre pour une transaction locale en niveau READ COMMITTED. Une prochaine version de PostgreSQL pourrait modifier ce comportement.

Notez que postgres_fdw ne supporte pas actuellement de préparer la transaction distante pour une validation en deux phases (2PC).

F.38.5. Optimisation des requêtes distantes #

postgres_fdw tente d'optimiser les requêtes distantes pour réduire la quantité de données transférées depuis les serveurs distants. Cela se fait en envoyant les clauses WHERE au serveur distant pour exécution, et en ne récupérant que les colonnes nécessaires pour la requête courante. Pour réduire le risque de mauvaise exécution des requêtes, les clauses WHERE ne sont pas envoyées au serveur distant sauf si elles utilisent seulement des types de données, opérateurs et fonctions intégrées ou appartenant à une extension listée dans l'option extensions du serveur distant. Les opérateurs et fonctions dans ce type de clause doivent aussi être IMMUTABLE. Pour une requête UPDATE ou DELETE, postgres_fdw tente d'optimiser l'exécution de la requête en envoyant la requête complète au serveur distant s'il n'existe pas de clauses WHERE pouvant être envoyées au serveur distant, pas de jointures locales pour la requête, pas de triggers BEFORE ou AFTER au niveau ligne ou de colonnes calculées automatiquement sur la table cible, et pas de contraintes CHECK OPTION pour les vues parentes. Dans un UPDATE, les expressions à affecter aux colonnes cibles doivent seulement utiliser les types de données intégrées, les opérateurs ou les fonctions IMMUTABLE pour réduire le risque de mauvaise exécution de la requête.

Quand postgres_fdw rencontre une jointure entre des tables externes sur le même serveur distant, il envoie la jointure entière au serveur distant, sauf s'il pense qu'il sera plus efficace de récupérer les lignes de chaque table individuellement ou si les références de table sont sujet à des correspondances d'utilisateur différentes. Lors de l'envoi des clauses JOIN, il prend les mêmes précautions que mentionnées ci-dessus pour les clauses WHERE.

La requête envoyée au serveur distant pour exécution peut être examinée en utilisant EXPLAIN VERBOSE.

F.38.6. Environnement d'exécution de requêtes distantes #

Dans les sessions distantes ouvertes par postgres_fdw, le paramètre search_path est configuré à pg_catalog, pour que seuls les objets internes soient visibles, sauf utilisant d'un nom de schéma. Ceci n'est pas un problème pour les requêtes générées par postgres_fdw lui-même car il fournit toujours ce type de qualification. Néanmoins, cela peut se révéler problématique pour les fonctions exécutées sur le serveur distant via des triggers ou des règles sur les tables distantes. Par exemple, si une table distante est en fait une vue, toute fonction utilisée dans cette vue sera exécutée avec le chemin de recherche restreint. Il est recommandé de qualifier tous les noms dans ce type de fonctions ou de leur attacher une option SET search_path (voir CREATE FUNCTION) pour établir le chemin de recherche attendu.

De même, postgres_fdw établie une configuration des sessions distantes pour différents paramètres :

TimeZone est positionné à UTC
datestyle est positionné à ISO
IntervalStyle est positionné à postgres
extra_float_digits est positionné à 3 pour les serveurs distants de version 9.0 et après et est positionné à 2 pour les versions plus anciennes

Ces paramètres sont moins à même d'être problématique que search_path, mais ils peuvent être gérés avec les options de fonction SET si le besoin devait se faire sentir.

Il n'est pas recommandé de surcharger ce comportement en modifiant la configuration de la session pour ces paramètres. Cela peut être la cause d'un mauvais fonctionnement de postgres_fdw.

F.38.7. Compatibilité entre versions #

postgres_fdw peut être utilisé avec les serveurs distants de version 8.3 et ultérieures. En lecture seule, il est possible d'aller aussi loin que la 8.1. Néanmoins, une limitation est que postgres_fdw assume généralement que les fonctions et opérateurs internes immutables sont sûrs pour être envoyés au serveur distant pour exécution s'ils apparaissent dans une clause WHERE de la table distante. Du coup, une fonction interne ajoutée depuis la sortie du serveur distant pourrait être envoyée pour exécution, résultant en un message d'erreur indiquant que la fonction n'existe pas (« function does not exist ») ou une erreur similaire. Ce type d'échec peut être contourné en réécrivant la requête, par exemple en embarquant la table distance dans un sous-SELECT avec OFFSET 0 comme optimisation, et plaçant la fonction ou l'opérateur problématique en dehors du sous-SELECT.

F.38.8. Paramètres de configuration #

postgres_fdw.application_name (string) #

Donne une valeur pour le paramètre de configuration application_name utilisé quand postgres_fdw établie une connexion vers un serveur distant. Elle surcharge l'option application_name de l'objet serveur. Notez que la modification de ce paramètre n'affecte pas les connexions existantes jusqu'à ce qu'elles soient ré-établies.

postgres_fdw.application_name peut être toute chaîne, quelque soit sa longueur, et peut même contenir des caractères non ASCII. Néanmoins, quand il est passé et utilisé comme dans un serveur distant, notez qu'il sera tronqué à moins que NAMEDATALEN caractères. Tout caractère autre que les caractères ASCII affichables sera remplacé par des échappements hexadécimaux du style langage C. Voir application_name pour les détails.

Le caractère % commence une « séquence d'échappement » qui est remplacé par une information de statut comme indiqué ci-dessous. Les échappements non reconnus sont ignorés. Les autres caractères sont recopiés directement dans le nom de l'application. Notez qu'il n'est pas autorisé d'indiquer un signe plus/moins ou un littéral numérique après le % et avant l'option, pour de l'alignement ou du remplissage.

Échappement	Effet
`%a`	Nom de l'application sur le serveur local
`%c`	Identifiant de session sur le serveur local (voir log_line_prefix pour plus de détails)
`%C`	Nom de l'instance sur le serveur local (voir cluster_name pour plus de détails)
`%u`	Nom de l'utilisateur sur le serveur local
`%d`	Nom de la base sur le serveur local
`%p`	Identifiant du processus sur le serveur local
`%%`	Caractère %

Par exemple, supposons que l'utilisateur local_user établisse une connexion à partir de la base local_db vers foreign_db en tant qu'utilisateur foreign_user, la configuration 'db=%d, user=%u' est remplacée par 'db=local_db, user=local_user'.

F.38.9. Exemples #

Voici un exemple de création d'une table distante avec postgres_fdw. Tout d'abord, il faut installer l'extension :

CREATE EXTENSION postgres_fdw;

Ensuite, il faut créer un serveur distant avec CREATE SERVER. Dans cet exemple, nous souhaitons nous connecter à un serveur PostgreSQL sur l'hôte 192.83.123.89 écoutant sur le port 5432. La base de données sur le serveur distant sur laquelle la connexion est faite s'appelle foreign_db :

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

Une correspondance d'utilisateur, définie avec CREATE USER MAPPING, est également nécessaire pour identifier le rôle qui sera utilisé sur le serveur distant :

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

Il est maintenant possible de créer une table distante avec CREATE FOREIGN TABLE. Dans cet exemple, nous souhaitons accéder à la table nommée some_schema.some_table sur le serveur distant. Le nom local pour celle-ci sera foreign_table :

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

Il est essentiel que les types de données et autres propriétés des colonnes déclarées dans CREATE FOREIGN TABLE correspondent à la vraie table distante. Les noms des colonnes doivent également correspondre, à moins que des options column_name soient attachées aux colonnes individuelles pour montrer comment elles sont nommées sur la table distante. Dans de nombreux cas, l'utilisation de IMPORT FOREIGN SCHEMA est préférable à la construction manuelle des tables distantes.

F.38.10. Auteur #

Shigeru Hanada <shigeru.hanada@gmail.com>