Rafraîchir une vue matérialisée automatiquement en utilisant une règle ou une notification

j'ai une vue matérialisée sur une base de données PostgreSQL 9.3 qui change rarement (environ deux fois par jour). Mais quand ce sera le cas, j'aimerais mettre à jour ses données rapidement.

Voici ce que je pensais jusqu'à présent:

Il y a une vue matérialisée mat_view qui reçoit les données des tableaux table1 et table2 en utilisant une déclaration de jointure.

Chaque fois que quelque chose dans table1 ou table2 modifications, j'ai déjà un déclencheur qui met à jour une petite configuration la table config consistant en

table_name | mat_view_name | need_update
-----------+---------------+------------
table1     | mat_view      | TRUE/FALSE
table2     | mat_view      | TRUE/FALSE

Donc, si quelque chose dans table1 change (il y a un déclencheur sur la mise à JOUR et SUPPRESSION pour chaque énoncé), le champ need_update dans la première ligne est réglé sur TRUE. Il en va de même pour table2 et la deuxième ligne.

Évidemment, si need_update est vrai, alors la vue matérialisée doit être rafraîchie.

UPDATE: Depuis les vues matérialisées ne supportent pas les règles( comme @pozs mentionné dans un commentaire ci-dessous), I serait aller un peu plus loin. J'aimerais créer un mannequin vue v_mat_view de la définition de "SELECT * FROM mat_view". Quand l'utilisateur fait un SELECT sur cette vue, je dois créer une règle sur SELECT qui fait ce qui suit:

  • vérifier si mat_view devrait être mis à jour (SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE)
  • réinitialiser le need_update drapeau avec UPDATE config SET need_update=FALSE where mat_view_name='mat_view'
  • REFRESH MATERIALIZED VIEW mat_view
  • et enfin faire l'original de l'instruction SELECT, mais avec mat_view comme la cible.

UPDATE2: J'ai essayé de créer les étapes ci-dessus:

Créer une fonction qui gère les quatre points mentionnés ci-dessus:

CREATE OR REPLACE FUNCTION mat_view_selector()
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY SELECT * FROM mat_view;
END;
$body$ LANGUAGE plpgsql;

Créer la vue v_mat_view qui sélectionne vraiment de la fonction mat_view_selector:

CREATE TABLE v_mat_view AS SELECT * from mat_view LIMIT 1;
DELETE FROM v_mat_view;

CREATE RULE "_RETURN" AS
    ON SELECT TO v_mat_view
    DO INSTEAD 
        SELECT * FROM mat_view_selector();
    -- this also converts the empty table 'v_mat_view' into a view.

le résultat n'est pas satisfaisant:

# explain analyze select field1 from v_mat_view where field2 = 44;
QUERY PLAN
Function Scan on mat_view_selector (cost=0.25..12.75 rows=5 width=4)
(actual time=15.457..18.048 rows=1 loops=1)
Filter: (field2 = 44)
Rows Removed by Filter: 20021
Total runtime: 31.753 ms

par rapport à la sélection à partir du mat_view lui-même:

# explain analyze select field1 from mat_view where field2 = 44;
QUERY PLAN
Index Scan using mat_view_field2 on mat_view (cost=0.29..8.30 rows=1 width=4)
  (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (field2 = 44)
Total runtime: 0.036 ms

donc essentiellement cela fonctionne, mais la performance peut être un problème.

quelqu'un a de meilleures idées? Sinon, je devrais l'implémenter dans la logique de l'application ou pire: lancer un cronjob simple qui court toutes les minutes. :-(

46
demandé sur klin 2014-05-28 12:54:13

2 réponses

PostgreSQL 9.4 ajouté REFRESH CONCURRENTLY à des vues matérialisées.

C'est peut-être ce que vous cherchez quand vous décrivez essayer de configurer une mise à jour asynchrone de la vue matérialisée.

les utilisateurs sélectionnant à partir de la vue matérialisée verront des données incorrectes jusqu'à la fin du rafraîchissement, mais dans de nombreux scénarios qui utilisent une vue matérialisée, c'est un compromis acceptable.

Utilisez un déclencheur de niveau d'énoncé qui surveille les tables sous-jacentes pour tout changement et puis rafraîchit la vue matérialisée simultanément.

19
répondu Jeff Widman 2015-12-17 22:57:32

vous devriez rafraîchir la vue dans triggers après insérer/update/delete/truncate pour chaque énoncé sur table1 et table2.

create or replace function refresh_mat_view()
returns trigger language plpgsql
as $$
begin
    refresh materialized view mat_view;
    return null;
end $$;

create trigger refresh_mat_view
after insert or update or delete or truncate
on table1 for each statement 
execute procedure refresh_mat_view();

create trigger refresh_mat_view
after insert or update or delete or truncate
on table2 for each statement 
execute procedure refresh_mat_view();

De cette façon, votre vue matérialisée est toujours à jour. Cette solution simple pourrait être difficile à accepter avec des insertions/mises à jour fréquentes et des sélections sporadiques. Dans votre cas (change rarement environ deux fois par jour) il convient idéalement à vos besoins.

Pour réaliser rafraîchissement différé d'une vue matérialisée vous avez besoin l'une des caractéristiques suivantes:

  • asynchrone déclencheur
  • déclencher avant de sélectionner
  • règle sur les sélectionner avant

Postgres n'en a pas, il semble donc qu'il n'y en ait pas clear solution de postgres.

compte tenu de cela, je considérerais une fonction de wrapper pour les sélections sur mat_view, par exemple

CREATE OR REPLACE FUNCTION select_from_mat_view(where_clause text)
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY EXECUTE FORMAT ('SELECT * FROM mat_view %s', where_clause);
END;
$body$ LANGUAGE plpgsql;

si elle est acceptable dans la pratique dépend de détails que je ne sais pas sur.

77
répondu klin 2014-05-31 09:44:37