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.
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)
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.
Seuls les super-utilisateurs peuvent créer ou modifier des correspondances
d'utilisateur avec les paramètres sslcert et
sslkey.
Seuls les super-utilisateurs 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 super-utilisateur 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 super-utilisateur, seul le super-utilisateur 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 super-utilisateur 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.
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_nameCette 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_nameCette 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_nameCette 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 :
use_remote_estimate
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
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
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.
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 :
extensionsCette 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
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
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.
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
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.
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
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.
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
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.
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
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
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
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
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.
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
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.
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
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.
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).
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.
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.
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.
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.
Shigeru Hanada <shigeru.hanada@gmail.com>