Aktualisieren Sie eine materialisierte Ansicht automatisch mithilfe einer Regel oder einer Benachrichtigung

Habe ich eine materialisierte Sicht auf eine PostgreSQL-9.3-Datenbank, die selten Veränderungen (etwa zweimal pro Tag). Aber wenn Sie es tut, würde ich gerne aktualisieren Ihre Daten umgehend.

Hier ist, was ich dachte, über so weit:

Gibt es eine materialisierte Ansicht mat_view die erhält seine Daten aus den Tabellen table1 und table2 mit einigen join-Anweisung.

Wann immer etwas in table1 oder table2 verpasst, ich habe bereits ein trigger wich updates ein wenig Konfiguration Tabelle config bestehend aus

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

So, wenn etwas in table1 änderungen (es gibt einen trigger on UPDATE und on DELETE für jede Aussage), ist das Feld need_update in der ersten Zeile festgelegt ist TRUE.
Das gleiche gilt für table2 und der zweiten Reihe.

Offensichtlich, wenn need_update WAHR ist, dann wird die materialized view aktualisiert werden muss.

UPDATE:
Da materialisierte Ansichten nicht unterstützen, Regeln (wie @pozs erwähnt in einem Kommentar unten), ich würde einen Schritt weiter gehen. Ich würde erstellen Sie eine dummy-anzeigen v_mat_view mit der definition "SELECT * FROM mat_view". Wenn der Nutzer eine WÄHLEN Sie auf dieser Ansicht, muss ich eine Regel erstellen, die AUF WÄHLEN Sie die das folgende tut:

  • prüfen, ob mat_view aktualisiert werden soll (SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE)
  • zurücksetzen der need_update Flagge mit UPDATE config SET need_update=FALSE where mat_view_name='mat_view'
  • REFRESH MATERIALIZED VIEW mat_view
  • und endlich das tun der ursprünglichen SELECT-Anweisung, aber mit mat_view als Ziel.

UPDATE2:
Ich habe versucht, erstellen Sie die oben genannten Schritte:

Erstellen Sie eine Funktion, die Griffe der vier oben genannten Punkte:

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;

Ansicht erstellen v_mat_view die wirklich wählt aus der Funktion 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.

Das Ergebnis ist unbefriedigend:

# 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

im Vergleich zu der Auswahl aus der mat_view selbst:

# 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

So im wesentlichen, es funktioniert, aber die performance könnte ein Problem sein.

Jemand bessere Ideen?
Wenn nicht, dann würde ich umsetzen müssen, es irgendwie in der Anwendungslogik oder noch schlimmer: führen Sie einen einfachen cronjob läuft jede minute oder so. 🙁

Kommentar zu dem Problem
Interessanter Ansatz. Haben Sie versucht, ersetzen Sie Ihre anzeigen "_RETURN" - Regel? Es ist ziemlich einfach postgresql.org/docs/current/static/sql-createrule.html Kommentarautor: pozs
können Sie formulieren Ihre Frage und erarbeiten ein bit? Ich verstehe nicht, was du meinst, auch nach der Lektüre der link, den Sie gepostet. Kommentarautor: mawimawi
Blick auf ein "_RETURN" - Regel standardmäßig. Sie ersetzen möchten, zu aktualisieren Sie Ihre materialisierte Ansicht vor der Rückkehr Blick ist das original wählen. Haben Sie bereits versucht? Kommentarautor: pozs
Never mind, die Neudefinition der Regel (die vorhanden ist!) auf eine materialisierte Ansicht gibt die Fehlermeldung - Regeln auf die materialisierten sichten werden nicht unterstützt SQL-Status: 0A000 Kommentarautor: pozs

InformationsquelleAutor der Frage mawimawi | 2014-05-28

  1. 18

    PostgreSQL 9.4 Hinzugefügt REFRESH CONCURRENTLY zu Materialisierten Sichten.

    Dies kann sein, was Sie suchen, wenn Sie zu beschreiben versuchen, um das setup eine asynchrone Aktualisierung der materialized view.

    Benutzer die Auswahl aus der materialisierten Ansicht werden sehen, falsche Daten, bis die Aktualisierung abgeschlossen ist, aber in vielen Szenarien, in denen eine materialisierte Sicht ist dies ein akzeptabler Kompromiss.

    Verwenden Sie ein statement-level trigger, der Uhren der zugrunde liegenden Tabellen auf änderungen und dann aktualisieren der materialisierten Ansicht gleichzeitig.

    InformationsquelleAutor der Antwort Jeff Widman

  2. 69

    Aktualisieren Sie die Ansicht im Trigger after insert/update/delete/truncate für jede Aussage table1 und 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();

    In dieser Weise Ihre materialized view ist immer up to date. Diese einfache Lösung könnte sein, schwer zu akzeptieren, mit häufigen inserts/updates und sporadischen wählt.
    In deinem Fall (selten wechselt etwa zweimal am Tag) ist es ideal zu Ihren Anforderungen passt.

    Realisieren deferred refresh von einer materialisierten Ansicht benötigen Sie eines der folgenden features:

    • asynchrone trigger
    • auslösen, bevor die Option
    • Regel auf auswählen, bevor Sie

    Postgres hat keiner von Ihnen, so scheint es, dass es keine klar postgres-Lösung.

    Unter dieser Berücksichtigung würde ich überlegen, eine wrapper-Funktion wählt auf mat_view, z.B.

    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;

    Wenn es akzeptabel ist, in der Praxis hängt davon ab, Einzelheiten weiß ich nicht über.

    InformationsquelleAutor der Antwort klin

Schreibe einen Kommentar