Le portail francophone de la géomatique

tevrard · Mon 15 October 2018 10:59

Bonjour,

Je débute dans l'utilisation des triggers, et celui-ci prends beaucoup trop de temps.

L'objectif du trigger est de mettre à jour une table à chaque fois qu'une insertion de données est réalisée dans une table "source"
Le trigger est paramétré depuis pgadmin, il se déclenche "after insert" sur la table bdd_territoires.data_insee_menages_source
Voici le code de la fonction trigger (je vous épargne la liste des champs):

Code:

DECLARE
BEGIN

TRUNCATE bdd_territoires.data_insee_menages;

INSERT INTO bdd_territoires.data_insee_menages (
    [champs de la table cible])

SELECT [champs de la table source à importer]
FROM bdd_territoires.data_insee_menages_source;

RETURN NULL;
END;

J'ai fait un COPY pour insérer dans la table source, il a fallu 5h pour que la table data_insee_menages soit remplie (36000 lignes)

=> Un problème dans le code ?
=> Faut il chercher autre part pour expliquer ce temps? Si oui où?

Merci d'avances pour vos réponses !

Dernière modification par tevrard (Mon 15 October 2018 11:01)

Nicolas Ribot · Mon 15 October 2018 11:08

Bonjour,

De ce que je comprends du code, a chaque insert dans la table bdd_territoires.data_insee_menages_source, vous videz la table bdd_territoires.data_insee_menages et vous réinsérer toutes les lignes (pas de condition where): ca fait beaucoup.

Votre triggger est défini FOR EACH ROW ou FOR EACH STATEMENT ?

Nicolas

tevrard · Mon 15 October 2018 11:18

D'après ce que j'ai sous pgadmin, je dirais for each row car "chaque ligne?" est cochée

cf pj

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Pierre · Mon 15 October 2018 11:31

Aloha,

Je ne suis pas sûr de comprendre non plus le besoin.
Lorsqu'un utilisateur insère une donnée dans t_A, il faut que qqqs colonnes de t_A de ce/ces nouveaux enregistrements soient répliqués dans t_data_insee... ?

Si c'est cela, je pense qu'il faudrait passer par des déclarations de type :

Code:

EXECUTE 'INSERT INTO t_data_insee VALUE '||new.champ1||','|| new.champ2...';

Du moins c'est vers ce type de fonction que j'irai.
Après je déclarerai le trigger comme appelant cette fonction.

PS. Le truncate lancé à chaque exécution du trigger est une très mauvaise idée.

Dernière modification par Pierre (Mon 15 October 2018 11:33)

tevrard · Mon 15 October 2018 11:54

Pierre a écrit:

Je ne suis pas sûr de comprendre non plus le besoin.
Lorsqu'un utilisateur insère une donnée dans t_A, il faut que qqqs colonnes de t_A de ce/ces nouveaux enregistrements soient répliqués dans t_data_insee... ?

Non. Le trigger intervient lors une mise à jour annuelle sur l'ensemble des lignes. De plus je ne souhaite pas conserver de données "historique" dans ces tables.

Cette table n'est jamais mise à jour "au fil de l'eau".

Peux-tu développer concernant le Truncate stp ?

Dernière modification par tevrard (Mon 15 October 2018 11:55)

JP LLORENS · Tue 16 October 2018 11:08

Bonjour
Comme le précise N. Ribot, a chaque insertion vous déclenchez un truncate et un INSERT INTO bdd_territoires.data_insee_menages => soit pour 36000 lignes, 36000 truncate ... d'où un temps d'exécution de 5h et la remarque de Pierre !
Peut-être auriez vous intérêt à faire une fonction avec le truncate et les insert, que vous lanceriez une fois par an après avoir mis à jour votre table source.
JP

Pierre · Tue 16 October 2018 11:22

Aloha

Pour faire suite au message de JP

Quelque chose comme

Code:

CREATE OR REPLACE FUNCTION maj_annuelle() RETURNS SETOF character varying 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$

DECLARE
   
BEGIN
TRUNCATE bdd_territoires.data_insee_menages;

INSERT INTO bdd_territoires.data_insee_menages (
    [champs de la table cible])

SELECT [champs de la table source à importer]
FROM bdd_territoires.data_insee_menages_source;

RETURN NULL;
END;
$BODY$;

Et une fois par an vous faites

Code:

select maj_annuelle()

Ou alors utilisez pgagent qui semble permettre de planifier l'exécution de fonction à une date spécifique.

tevrard · Tue 16 October 2018 11:28

JP LLORENS a écrit:

Bonjour
Comme le précise N. Ribot, a chaque insertion vous déclenchez un truncate et un INSERT INTO bdd_territoires.data_insee_menages => soit pour 36000 lignes, 36000 truncate ... d'où un temps d'exécution de 5h et la remarque de Pierre !

Oui, après avoir décoché l'option "Chaque ligne?" (For each row) le trigger s'exécute en 1.5 sec

JP LLORENS a écrit:

Peut-être auriez vous intérêt à faire une fonction avec le truncate et les insert, que vous lanceriez une fois par an après avoir mis à jour votre table source.

C'est la ou j'ai du mal à voir la différence entre ce que vous me proposez et mon trigger. Avec ce dernier, j'ai bien une fonction qui s'exécute après avoir une fois par an, après mise à jour ma table source. L'avantage du trigger, c'est que cela me fait une manip en moins non?
En quoi le fait de vider ma table dans ma fonction de trigger est gênant?

EDIT : Ayant vu le msg de Pierre, j'ai bien du mal à voir l'avantage de ne pas passer par un trigger. PgAgent me permet de lancer une fonction à une date précise, mais le trigger permet de lancer une fonction lors d'un évènement particulier... et c'est bien ce que je veux : après insertion dans ma table source.

En tout cas, merci à tous pour votre aide

Dernière modification par tevrard (Tue 16 October 2018 11:34)

JP LLORENS · Tue 16 October 2018 12:17

En effet si le trigger ne se déclenche qu'à la 36000ème insertion en basculant sur le mode "For each statement" alors oui, il n'y a aucun intérêt à lancer une fonction à la mimine.
Je crois que je viens de comprendre la notion de "simplement pour chaque instruction SQL" décrite dans la doc postgres sur les trigger qui différencie eachrow / each statement => je suppose donc que le trigger ne se déclenchera qu'à la fin de votre insert dans la table source, et non à chaque ligne insérée (each row)

Nicolas Ribot · Tue 16 October 2018 14:21

Bonjour,

Oui, une des caractéristiques importantes des triggers est le mode de déclenchement:
FOR EACH ROW ou FOR EACH STATEMENT.

On utilise FOR EACH ROW quand le trigger doit "jouer" avec la valeur de chaque record concerné par l'opération. Cas le plus fréquent.

FOR EACH STATEMENT effectivement indique au trigger qu'il ne s'execute qu'une fois le statement terminé (peu importe que ce statement concerne une ligne ou plusieurs).
On utilise souvent ce mode pour tracer les modifs sur une table, si on est juste intéressé par qui et quand la table est modifiée. Le trigger remplit alors une table d'historique disant "telle table a ete modifiée (update/delete/insert) par machin a telle date.

Nico

JP LLORENS · Tue 16 October 2018 15:55

Merci beaucoup pour ce complément d'information
JP

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Mon 15 October 2018 10:59

Optimisation trigger d'insertion

Code:

#2 Mon 15 October 2018 11:08

Re: Optimisation trigger d'insertion

#3 Mon 15 October 2018 11:18

Re: Optimisation trigger d'insertion

#4 Mon 15 October 2018 11:31

Re: Optimisation trigger d'insertion

Code:

#5 Mon 15 October 2018 11:54

Re: Optimisation trigger d'insertion

#6 Tue 16 October 2018 11:08

Re: Optimisation trigger d'insertion

#7 Tue 16 October 2018 11:22

Re: Optimisation trigger d'insertion

Code:

Code:

#8 Tue 16 October 2018 11:28

Re: Optimisation trigger d'insertion

#9 Tue 16 October 2018 12:17

Re: Optimisation trigger d'insertion

#10 Tue 16 October 2018 14:21

Re: Optimisation trigger d'insertion

#11 Tue 16 October 2018 15:55

Re: Optimisation trigger d'insertion

Pied de page des forums