Forums PostgreSQL.fr

sab31

Membre

Vue modifiable sur vue matérialisée pour compléter autre table général

Bonjour à tous,

Je voudrais savoir s'il est possible de modifier une table 'A' avec les données provenant d'une vue modifiable 'v1' , elle même issue d'une vue matérialisée 'v_mat_1' , elle même issue de la table 'A'.

Je précise ma demande :
- j'ai une table 'A' de 2 millions de lignes d'entités géographiques de type 'linestring'
- j'ai un certains nombres d'utilisateurs ( U1,U2, U3,...) qui doivent modifier les données de cette table via QGIS en fonction des entités présentent sur leur territoire
- j'ai une table de territoire pour chaque utilisateur mais une entité peut intersecter deux territoires différents donc les deux utilisateurs doivent pouvoir la modifier

sur QGIS l'utilisation de la table A en totalité est un enfer car beaucoup trop lourde et je préfère proposer à l'utilisateur une couche ne représentant que ses entités à modifier en fonction de son territoire.
Je veux éviter de découper la table A en plusieurs tables, je préfère diviser les données dans des vues modifiables et que celles-ci puissent recompléter la table A.

J'ai testé une vue modifiable pour un utilisateur qui intersecte les entités de la table 'A' directement avec son territoire ==> la vue met beaucoup trop de temps à se charger sur QGIS et à l'utilisation également
j'ai besoin de gagner en performance pour la modification de ces données sur QGIS pour chaque utilisateur

J'ai donc pensé diviser ma table 'A' en vues matérialisées  'v_mat_1',  'v_mat_2', ....  et faire des vues modifiables sur ces vues matérialisées.

J'ai donc créé une fonction sur ma vue modifiable et un trigger sur cette vue

-- FUNCTION:schema1.fn_edit_v1_utilisateur1()

-- DROP FUNCTION IF EXISTS schema1.fn_edit_v1_utilisateur1();

CREATE OR REPLACE FUNCTION schema1.fn_edit_v1_utilisateur1()
    RETURNS trigger
    LANGUAGE 'plpgsql

   BEGIN
      IF TG_OP = 'UPDATE' THEN

      -- modifier la table A 

		UPDATE schema_1.table_A SET
				
			nom=NEW.nom ,
			classe=NEW.classe , 
			categorie=NEW.categorie ,
			gestionnaire=NEW.gestionnaire ,
			type_gestion=NEW.type_gestion , 			
			affichage=NEW.affichage , 
			ordre=NEW.ordre

	WHERE  schema_1.table_A.id=OLD.id ;
    
    RETURN NEW;

    -- mettre à jour la vue matérialisée
    
    REFRESH MATERIALIZED VIEW schema_1.v_mat_1_utilisateur1; 
	
	
	END IF;
	END;
	$BODY$;
    
    
-- Trigger: tg_edit_v1_utilisateur1 -- sur vue modifiable

DROP TRIGGER IF EXISTS tg_edit_v1_utilisateur1 ON schema1.v1_utilisateur1;

CREATE TRIGGER tg_edit_v1_utilisateur1
    INSTEAD OF UPDATE 
    ON schema1.v1_utilisateur1
    FOR EACH ROW
    EXECUTE FUNCTION fdppma_19.fn_edit_v_cours_eau_fd19_oui();

Lorsque je modifie la vue 'v1' pour test, je n'ai aucun message d'erreur, la modification n'est pas prise en compte sur la vue ( je suppose car elle récupère les données de la vue matérialisée qui elle même n'est pas mise à jour ).

Quelqu'un a t'il une idée sur la façon de procéder pour faire ces déclenchements les uns à la suite des autres..si cela est possible

Merci d'avance de vos lumières

gleu

Administrateur

Re : Vue modifiable sur vue matérialisée pour compléter autre table général

Il faudrait un exemple complet pour savoir ce qui ne va pas. Normalement, un trigger devrait pouvoir gérer la mise à jour.

---

Guillaume.

dverite

Membre

Re : Vue modifiable sur vue matérialisée pour compléter autre table général

Dans le trigger:

    RETURN NEW;

    -- mettre à jour la vue matérialisée
    
    REFRESH MATERIALIZED VIEW schema_1.v_mat_1_utilisateur1; 

Le RETURN NEW fait sortir du trigger donc le REFRESH n'est pas exécuté. Le RETURN NEW doit se faire en dernier.

Sinon d'un point de vue conception, ça paraît extrêmement lourd de rafraîchir une vue matérialisée à chaque mise à jour de ligne.

Peut-être qu'il serait intéressant de creuser les raisons pour lesquelles les vues non matérialisées limitant les données exposées à QGIS ne donnent pas satisfaction. Est-ce que c'est les requêtes qui sont lentes ou c'est QGIS?
Si c'est les requêtes, il faudrait faire des EXPLAIN ANALYZE pour voir où le temps est consommé et pourquoi. Il y a des conseils à ce sujet dans https://wiki.postgresql.org/wiki/Slow_Query_Questions

---

@DanielVerite
http://blog-postgresql.verite.pro/