Linux, Base de données Postgresql, développement, Internet, emailing et déliverabilité.

Aller au contenu | Aller au menu | Aller à la recherche

dblink et trigger sur PostgreSQL

Après un certain nombre de recherches sur le net ... sans grand succès autour du module dblink de PostgreSQL, je tenais à faire partager à la communauté les possibilités très intéressantes de cet outils.
Le principe de dblink pour ceux qui ne le connaissent pas encore est de permettre de faire des requêtes d'une database vers une autre.
L'intérêt peut paraître limité, mais dès que vous envisagez de manipuler des millions de données, une machine seule ne suffit plus à stocker l'intégralité de la base. Il faut alors envisager la démultiplication physique de la base de données.
Viennent alors, naturellement d'autres problèmes comme par exemple la cohérence, ou l'intégrité de la base de données qui se retrouve répartie sur plusieurs machines et donc plusieurs bases. C'est là encore une fois qu'intervient le module dblink.
Voilà donc un petit exemple de ce qui peut être fait simplement pour par exemple tenir à jour un compteur de données sur la database principale, et qui évitera par la suite d'aller faire des COUNT sur les bases déportées. Cet exemple est assez basique mais démontre les capacités de dblink.
Structure de cet exemple :
Database 1 : base1 sur 192.168.0.1
Database 2 : base2 sur 192.168.0.2
Table sur base1 :
CREATE TABLE compteur_base2 (id_produit integer not null primary key, nb integer default 1);

Table sur base2 :
CREATE TABLE produits (id_produit integer, libelle text, prix float);

On admet donc que la base2 contient des millions de produits, faire un COUNT sur cette table produits est très couteux, surtout pour compter des produits qui ont le même identifiant ... maintenir un compteur de son contenu devient donc une vraie nécessité depuis base1, qui est la base principale.
La solution proposée consiste donc à mettre en place sur la table produits de base2, un trigger qui va être en charge de mettre automatiquement à chaque insertion dans cette table le compteur contenu sur base1 dans la table compteur_base2.
Ce trigger fait appel à une fonction (add_product) écrite en plpgsql ... le language interne de programmation SQL intégré à PostgreSQL.

CREATE OR REPLACE function add_product() RETURNS trigger AS $$
DECLARE
    val RECORD;
BEGIN
    SELECT INTO val id_produit FROM dblink('dbname=base1 host=192.168.0.1'::text, 'SELECT id_produit FROM compteur_base2 where id_produit = ' || NEW.id_produit) AS t1(id_produit integer);
    IF NOT FOUND THEN
         PERFORM dblink_exec('dbname=base1 host=192.168.0.1', 'INSERT INTO compteur_base2 (id_produit) VALUES (' || NEW.id_produit || ')');
    ELSE
        PERFORM dblink_exec('dbname=base1 host=192.168.0.1', 'UPDATE compteur_base2 SET nb = nb + 1 WHERE id_produit = ' || NEW.id_produit);
    END IF;
    RETURN NEW;
END;
$$ language plpgsql;

Le principe est donc simple, cette fonction commence par regarder sur base1 via un dblink si l'id_produit existe déjà ... si le SELECT ne retourne pas de résultat nous procédons à un INSERT en base qui prendra la valeur 1 par defaut de la définition de la table compteur_base2. Sinon nous mettons à jour la valeur actuelle du compteur pour cet id_produit. Vous noterez que pour excuter une commande d'INSERT ou d'UPDATE via dblink il est impératif d'utiliser la commande PERFORM. L'inconvénient de la commande PERFORM c'est que dans le cas d'un dblink elle n'est pas capable de prendre en compte la valeur retournée par dblink pour nous indiquer si l'UPDATE a bien fonctionné. Ce qui implique de faire un SELECT précédemment pour tester l'existance de l'identifiant produit sur base1.
Il suffit donc maintenant de brancher la fonction add_product sur un trigger sur la table produits de base2, de la façon suivante :

create trigger produits_ins AFTER INSERT ON produits FOR EACH ROW EXECUTE PROCEDURE add_product();

A chaque insertion en base la fonction add_product sera donc exécutée, elle mettra à jour notre compteur déporté sur base1 automatiquement.
Il va de soit qu'il faudra faire un autre trigger pour mettre à jour le compteur lors d'un effacement d'un produit dans base2 ... mais j'espère que vous parviendrez à le faire par vous-même ... sinon contactez-moi.

Commentaires

1. Le jeudi, 28 mai 2009, 17:16 par chouf

Bonjour,

J'ai une fonction plpgsql utilisant dblink_exec.

La ligne suivante dans la fonction marche normalement:
INSERT INTO devicelocationhistory (loch_location, device_dev_id, statuslist_sta_id, loch_datetime) VALUES( NEW.dev_location, NEW.dev_id, NEW.statuslist_sta_id, NEW.dev_datetimelastloc);

En revanche la même requete SQL lancée par dblink_exec me renvoi un message d'erreur.
Voici la ligne dans la fonction:
PERFORM dblink_exec('dbname=avltest', 'INSERT INTO devicelocationhistory (loch_location, device_dev_id, statuslist_sta_id, loch_datetime) VALUES( ' || NEW.dev_location || ', ' || NEW.dev_id || ', '|| NEW.statuslist_sta_id ||', ' || NEW.dev_datetimelastloc || ')');

Et le message d'erreur:

ERREUR: l'opérateur n'existe pas : bytea || integer
LINE 1: ..._sta_id, loch_datetime) VALUES( ' || $1 || ', ' || $2 ||...
^
HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
QUERY: SELECT dblink_exec('dbname=avltest', 'INSERT INTO devicelocationhistory (loch_location, device_dev_id, statuslist_sta_id, loch_datetime) VALUES( ' || $1 || ', ' || $2 || ', '|| $3 ||', ' || $4 || ')')

J'ai essayé de caster les variables mais rien n'y fait.

Auriez vous une idée?

2. Le vendredi, 29 mai 2009, 17:43 par footcow

Est-ce que les deux versions des bases sont identiques ?

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

La discussion continue ailleurs

URL de rétrolien : https://www.footcow.com/index.php/trackback/20

Fil des commentaires de ce billet