Le module postgres_fdw fournit le wrapper de données distantes postgres_fdw, dont le but est de données 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(7).
Créez un objet serveur distant en utilisant CREATE SERVER(7), 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(7) 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(7) 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 et DELETE. (Bien sûr, l'utilisateur utilisé pour la connexion au serveur distant doit avoir le droits de faire tout cela.)
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 clauses WHERE.
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.
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 31.1.2, « Mots clés de la chaîne de connexion ». Cependant, ces options ne sont pas autorisées :
user et password (spécifiez-les au niveau de la correspondance d'utilisateur)
client_encoding (ceci est configuré automatiquement à partir de l'encodage du serveur local)
fallback_application_name (toujours configuré à postgres_fdw)
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.
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.
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é.
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é.
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é.
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 :
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.
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.
Cette option, qui peut être indiquée pour un serveur distant, est une valeur numérique 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(7) 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.
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 :
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.
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. La connexion est conservée et réutilisée pour les requêtes suivants de la même session. Néanmoins, si plusieurs identités d'utilisateur (correspondances d'utilisateur) sont utilisées pour accéder au serveur distant, une connexion est établie pour chaque correspondance d'utilisateur.
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.
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 internes. Les opérateurs et les fonctions dans les clauses doivent en plus être IMMUTABLE.
La requête envoyée au serveur distant pour exécution peut être examinée en utilisant EXPLAIN VERBOSE.
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.
Shigeru Hanada <shigeru.hanada@gmail.com>
pg_trgm
