Forums PostgreSQL.fr

aznur

Membre

optimisation requette full text ne pas metre a jour toute la table

bonjour voila je debute avec les base de donnee surtout postgresql

j'ai utiliser un tutoriel   

  pour faire une recherche dite full texte (sans dico pour le moment mais ce n'est pas le probleme )

a un moment la personne du tuto tutoriel en question
execute une requete qui permet de remplire l'index d'ont il se sert pour la recherche full text

puis apre nous dit gentillement qu'il fodra cree un trigger ( efectivement sinon la table enfin du moin la colone de cette index text de se remplie pas pendant un insert )

donc j'ai cree un trigger avec une fonction trigger le probleme c'est que je pense que j'utilise une bombe atomique pour tuer une mouche avec ma fonction

enfaite ma fonction doit refaire tout les indice de la table a chaque insert j'imagine

voici la fonction et le triger 

CREATE FUNCTION maj() RETURNS TRIGGER AS
$$
BEGIN
 UPDATE information  
SET idxshearch = to_tsvector(coalesce(frequencemot,'')); 
 RETURN NULL;
END
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER montriggermaj AFTER INSERT ON information 
FOR EACH ROW EXECUTE PROCEDURE maj();

le probleme et qu'enfaite je ne vois pas comment acceder à l'insert  qui et fait au temp T et ne pas metre a jour toute la table systematiquement   
jai vue une variable NEW qui devai resoudre une parti du probleme  code mais je ne sais pas comment je peu m'en servir

une autre question j'en profite  pgadmin  me dit que je n'est pas de clee primaire est ce normal pour une recherche full text ou pas merci de m'avoir lu
à oui je suis en 9.1 pour postgrsql

Dernière modification par aznur (19/08/2012 23:38:14)

MitsuTomoe

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

Bonjour,
bien qu'inexpérimenté sur Pg, j'ai eu récemment à faire de la recherche full text.
Voici comment j'ai procédé :
dans la table concernée, 2 champs ==>
keywords text,
    tsv tsvector,

1 index : CREATE INDEX tsv_idx2 ON bien USING gin (tsv);

Le champ keywords est renseigné par une fonction plPgSQL en insert/update, tsv est utilisé pour la recherche.

2 triggers :
pour renseigner keywords :
CREATE TRIGGER bien_keywords BEFORE INSERT OR UPDATE ON bien FOR EACH ROW EXECUTE PROCEDURE bien_keywords();
pour mettre à jour tsv :
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON bien FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('tsv', 'pg_catalog.french', 'keywords');

Je pense que c'est ce dernier trigger qui vous intéresse .

Pour les recherches (en php), je fais une boucle sur les mots saisis par l'utilisateur que je concatène dans la requête :
$mkeysel = "AND bien.tsv @@ to_tsquery('";
$mkeysel = $mkeysel.$mk;
$mkeysel = $mkeysel."')";

En espérant vous avoir aidé

rjuju

Administrateur

Re : optimisation requette full text ne pas metre a jour toute la table

Bonjour,

Il est en général fortement conseillé d'avoir une clé primaire, ne serait-ce que pour identifier une ligne unique facilement. Si votre table n'admet pas de clé primaire naturelle, vous pouvez toujours déclarer un champ de type serial ou bigserial comme clé primaire.

Ensuite, pour ne mettre à jour que la ligne modifiée, il faut rajouter un where suffisamment précis (idéalement une clé primaire), du genre :

UPDATE information 
SET idxshearch = to_tsvector(coalesce(frequencemot,'')) WHERE champ=NEW.champ AND champ2 = NEW.champ2;

Il ne faut pas non plus oublier de gérer les trigger de type UPDATE.

Vous pouvez voir la doc associée : http://docs.postgresqlfr.org/9.1/plpgsql-trigger.html

---

Julien.
https://rjuju.github.io/

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

merci pour les réponses à mes questions

j'ai à peu pres tout compris seulement la vous me parler de geree l'update ce n'est pas faux mais pourais je utiliser le meme trigger  (j'imagine que oui) parce que  je vais tomber dans une boucle infini
l'update fait appele au trigger qui fait apele à update ... qui ...... trigger update  trigger update

parce que si je fait un update  qui change des mots  par mon code dans mon programme ici  java/tomcat  il faut que je reface

UPDATE information 
SET idxshearch = to_tsvector(coalesce(frequencemot,'')) WHERE clee=NEW.clee;

pour remetre à jour mon idxshearch
puisque  que je vien de metre à jour  frequencemot par exemple
or je tombe sur un bon gros cas de boucle infinie bien velue

j'ai vue dans le lien de la doc  pour faire sa requete utilise une timekey
l'idee serai:
-je fait un update via mon logiciel pour modifier un ou plusieur  champ
-le trigger et appeler il fait un update
-il verifie le temps si le temps et superieur à la limite du temp qui et fixer on fait un update
-le trigger se redeclenche forcement
-le temps vien d'etre mi a jour
-puisque le temp et inferieur a la limite
-il ne passe pas une nouvel fois dans l'update

ce serais une solution mais est ce "la" solution la plus efficance/simple

parce que avec cette solution je  relance à nouveau le trigger pourais t'on dire pour rien et encore une fois j'ai  l'impresion d'utiliser la bombe nucleaire pour chasser les mouche
sa fonctionne mais c'est pas tres fin

Dernière modification par aznur (20/08/2012 14:00:18)

rjuju

Administrateur

Re : optimisation requette full text ne pas metre a jour toute la table

Pour éviter les boucles infinies, il ne faut pas lancer une requête update dans le trigger, mais faire un trigger BEFORE UPDATE, et faire un

NEW. idxsearch = to_tsvector(coalesce(NEW.frequencemot,''));
RETURN NEW;

---

Julien.
https://rjuju.github.io/

MitsuTomoe

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

Il n'y a pas de problême de boucle infinie avec les triggers sur update (heureusement) .
D'après ce que j'ai compris, le fonctionnement est le suivant :
1) un update est fait sur la table
2) PG déclenche le trigger avec la nouvelle ligne dans la variable record NEW et les anciennes dans la variable record OLD.
3) La fonction exécutée par le trigger fait son traitement, et un RETURN NEW si tout va bien. RETURN NULL fait échouer l'update.
Ce traitement peut comprendre des mises à jour , et ne va pas redéclencher le trigger.
Exemple d'une fonction appelée dans l'update de la plupart de nos tables :
DECLARE
nom_base varchar;

BEGIN

       SELECT INTO nom_base current_database();

        NEW.dat_tran := current_timestamp;

        NEW.user_db_tran := nom_base;

        RETURN NEW;

    END;

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

donc enfaite le trigger s'intercale  entre la requete et la bdd
se qui permet l'interception des donnees par la variable new
mais sa ouvre des perspective tout à fait nouvel ça

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

du coup mon code de creation trigger et devenu

CREATE FUNCTION maj() RETURNS TRIGGER AS
$$
BEGIN
NEW.idxsearch = to_tsvector(coalesce(NEW.frequencemot,''));
RETURN NEW;
END
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER montriggermaj BEFORE INSERT OR UPDATE ON information 
FOR EACH ROW EXECUTE PROCEDURE maj();

j'ai bon ? la j'ai essayer sa à l'aire de fonctionner comme je veux du moin mon programe fai pas encore d'update maintenant ya peu etre moyen d'optimisé
par exemple la fin et

FOR EACH ROW EXECUTE PROCEDURE maj();

si j'ai bien compris pour chaque  colone il effectue la requete meme si l'update ne met pas a jour frequence mots  mai dans ce cas la je devrai faire 2 trigger
1 qui soccupe de l'insert
et un qui s'occupe de l'update de la colone frequence mots

est ce que ça vos le coup je veut dire gagnerais t'on meme quelque disieme de  seconde sur des millier/million de requete  ?
tant qu'a faire quelque chose ... autant le faire le mieux possible

Dernière modification par aznur (20/08/2012 16:25:19)

rjuju

Administrateur

Re : optimisation requette full text ne pas metre a jour toute la table

Effectivement, il pourra s'exécuter des fois où cela ne serait pas utile.

Vous pouvez rajouter un test dans votre trigger un test pour cela :

IF ( (TG_OP = 'INSERT') OR ((TG_OG = 'UPDATE') AND (OLD.frequencemot <> NEW.frequencemot)) ) THEN
BEGIN
  NEW.idxsearch = to_tsvector(coalesce(NEW.frequencemot,''));
END;
RETURN NEW;

---

Julien.
https://rjuju.github.io/

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

merci pour le if sa peu etre interesant dans un autre cas 

mais je ne penser pas forcement à ce cas la enfaite

je voudrais savoir si on  ne  peu  pa jouer sur  LA conlone quand on declare lactivation de la fonction  genre

FOR EACH ROW EXECUTE PROCEDURE maj();

au lieu de EACH ROW  je ne sais pas comment sa fonctionne mais ne pourai t'on pas metre juste la colone concernee  exemple frequencemot

qui devient alor

FOR frequencemot  EXECUTE PROCEDURE maj();

car par exemple je pourai avoir une variable de maj dans ma table qui  contient la derniere date a laquel la maj a ete faite

je fait une requete select * dans mon programe par exemple et je refait le  calcule  de la source avec le meme algorythme que la premier insertion (sa parai logique)

si les valeur change alor je fai un update de tout  se qui doi l'etre  on passe alor dans le trigger d'update

si par contre rien n'a changer sauf la date de la maj  qui devient la valeur apres le dernier scan effectuer dans le programe alor je fait un update seulement sur la maj

si je peu specifier la colone sur laquele le trigger agit  je ne declenche meme pas celui ci

si je fai un update de ma variable maj

est ce possible ?

dans ce cas la j'utilise un trigger d'insert qui se declenche  à l'insert quelque soi la colone donc EACH ROW  et un autre d'update  QUI se declenche seuelement si la colone Frequencemot subit l'update

Dernière modification par aznur (20/08/2012 17:19:59)

rjuju

Administrateur

Re : optimisation requette full text ne pas metre a jour toute la table

A partir de la version 9.0, il est possible de préciser le nom de la colonne déclenchant le trigger:

CREATE TRIGGER montriggermaj
    BEFORE INSERT OR UPDATE OF frequencemot ON information
    FOR EACH ROW
    EXECUTE PROCEDURE maj();

---

Julien.
https://rjuju.github.io/

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

lol autant pour moi j'ai confondu row et column ... (boulet)

ok je te remercie pour le temps passer a m'aider
depuis la version 9.0 donc enfaite c'est pas tres vieux ok merci encore

Dernière modification par aznur (20/08/2012 17:23:40)

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

donc cela nous donne 2 triggers et une fonction trigger
le premier trigger sur insert et le seconde sur update et plus particulierement sur les update de la colone frequencemot

CREATE FUNCTION maj() RETURNS TRIGGER AS
$$
BEGIN
NEW.idxsearch = to_tsvector(coalesce(NEW.frequencemot,''));
RETURN NEW;
END
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER montriggermajUpd BEFORE UPDATE OF frequencemot ON information 
FOR EACH ROW EXECUTE PROCEDURE maj();

CREATE TRIGGER montriggermajIns BEFORE INSERT  ON information 
FOR EACH ROW EXECUTE PROCEDURE maj();

j'ai tout bon ?

Dernière modification par aznur (20/08/2012 17:51:08)

rjuju

Administrateur

Re : optimisation requette full text ne pas metre a jour toute la table

Vous pouvez n'utiliser qu'un seul trigger comme dans mon exemple précédent.

Pour être complet, il y a encore un cas possible, c'est si un utilisateur fait un UPDATE de frequencemot pour remettre la même valeur, le trigger se déclenchera quand même.

Pour éviter cela, il faudrait ajouter un test tel que

IF ( (TG_OP = 'UPDATE') AND (OLD.frequencemot <> NEW.frequencemot ) )

---

Julien.
https://rjuju.github.io/

aznur

Membre

Re : optimisation requette full text ne pas metre a jour toute la table

oue enfin sa depend de quel point de vue on se place

enfaite mon idee de depart ete
faire un select  de la date  de mise a jour
si besoin de mise a jour on fait un select de frequencemot
le programme java  calcule  avec l'algorithme
on fait un controle pour savoir si l'ancien contenu et valide  dans le programme java donc en claire si frequence mot a changer
on fait un update juste sur la fonction maj  ou sur les colone maj et frequence mot suivant les cas

donc du coup cela devient

on fait un select de la date
si besoin de mise a jour
le programme java  calcule  avec l'algorithme
on fait donc systematiquement un update de la colone maj et frequencemot
la le trigger intervient
si frequencemot  n'a pas changer ou si il a changer

en terme de performance  le select et bcp plus couteux que l'update ? je ne connais pas beaucoup les bdd donc ... je me renseigne  bon j'imagine que oui

bon donc mon super trigger devient

CREATE FUNCTION maj() RETURNS TRIGGER AS
$$
BEGIN
IF ( (TG_OP = 'INSERT') OR ((TG_OP = 'UPDATE') AND (OLD.frequencemot <> NEW.frequencemot)) ) THEN
NEW.idxsearch = to_tsvector(coalesce(NEW.frequencemot,''));
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER montriggermajupd BEFORE INSERT OR UPDATE OF frequencemot ON information 
FOR EACH ROW EXECUTE PROCEDURE maj();

Dernière modification par aznur (20/08/2012 19:05:44)