wake-up-neo.net

Wie kann ich sicherstellen, dass eine materialisierte Ansicht immer auf dem neuesten Stand ist?

Ich muss REFRESH MATERIALIZED VIEW bei jeder Änderung der betroffenen Tabellen aufrufen, oder? Ich bin überrascht, nicht viel darüber im Web zu finden.

Wie soll ich das machen?

Ich denke, die obere Hälfte der Antwort ist was ich suche: https://stackoverflow.com/a/23963969/168143

Gibt es Gefahren dafür? Wenn die Aktualisierung der Ansicht fehlschlägt, wird die Transaktion beim Aufruf von Aktualisieren, Einfügen usw. zurückgesetzt? (das ist was ich will ... ich denke)

29
John Bachir

Ich muss REFRESH MATERIALIZED VIEW bei jeder Änderung der betroffenen Tabellen aufrufen, oder?

Ja, PostgreSQL selbst wird es niemals automatisch aufrufen, Sie müssen es irgendwie tun.

Wie soll ich das machen?

Viele Möglichkeiten, dies zu erreichen. Bevor Sie einige Beispiele geben, beachten Sie, dass der Befehl REFRESH MATERIALIZED VIEW die Ansicht im AccessExclusive-Modus blockiert. Wenn also die Funktion ausgeführt wird, können Sie SELECT nicht einmal für die Tabelle ausführen.

Wenn Sie jedoch Version 9.4 oder neuer verwenden, können Sie die Option CONCURRENTLY angeben:

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

Dadurch wird ein ExclusiveLock erworben, und SELECT-Abfragen werden nicht blockiert, es kann jedoch ein größerer Aufwand auftreten (abhängig von der geänderten Datenmenge. Wenn sich nur wenige Zeilen geändert haben, kann es schneller sein). Sie können zwar immer noch nicht zwei REFRESH-Befehle gleichzeitig ausführen.

Aktualisieren Sie manuell

Es ist eine Option zu prüfen. Insbesondere beim Laden von Daten oder bei Batch-Aktualisierungen (z. B. bei einem System, das nach langen Zeiträumen nur Tonnen von Informationen/Daten lädt) ist es üblich, Operationen am Ende zu haben, um die Daten zu ändern oder zu verarbeiten, sodass Sie einfach eine REFRESH-Operation hinzufügen können am Ende davon.

Planen der REFRESH-Operation

Die erste und weit verbreitete Option ist die Verwendung eines Planungssystems zum Aufrufen der Aktualisierung. Sie können beispielsweise das Like in einem Cron-Job konfigurieren:

*/30 * * * * psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv"

Dann wird Ihre materialisierte Ansicht alle 30 Minuten aktualisiert.

Überlegungen

Diese Option ist wirklich gut, insbesondere mit der Option CONCURRENTLY, aber nur, wenn Sie die Daten nicht immer zu 100% auf dem neuesten Stand halten können. Beachten Sie, dass der Befehl CONCURRENTLY auch mit oder ohne REFRESH die gesamte Abfrage ausführen muss. Daher müssen Sie sich die Zeit für die Ausführung der inneren Abfrage nehmen, bevor Sie die Zeit für die Planung der REFRESH in Betracht ziehen.

Erfrischend mit einem Abzug

Eine andere Option ist, den REFRESH MATERIALIZED VIEW in einer Triggerfunktion aufzurufen, wie folgt:

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;
    RETURN NULL;
END;
$$;

In jeder Tabelle, die Änderungen in der Ansicht beinhaltet, müssen Sie Folgendes tun:

CREATE TRIGGER tg_refresh_my_mv AFTER INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH STATEMENT EXECUTE PROCEDURE tg_refresh_my_mv();

Überlegungen

Es gibt einige kritische Fallstricke für Leistung und Parallelität:

  1. Jede INSERT/UPDATE/DELETE -Operation muss die Abfrage ausführen (was möglicherweise langsam ist, wenn Sie sich für MV entscheiden).
  2. Selbst mit CONCURRENTLY blockiert eine REFRESH eine andere, sodass INSERT/UPDATE/DELETE für die beteiligten Tabellen serialisiert wird.

Die einzige Situation, die ich für eine gute Idee halten kann, ist, wenn die Änderungen wirklich selten sind.

Aktualisieren Sie mit LISTEN/NOTIFY

Das Problem mit der vorherigen Option besteht darin, dass sie synchron ist und bei jedem Vorgang einen großen Aufwand verursacht. Um dies zu verbessern, können Sie einen Auslöser wie zuvor verwenden, der jedoch nur eine NOTIFY-Operation aufruft:

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, 'my_mv';
    RETURN NULL;
END;
$$;

Dann können Sie eine Anwendung erstellen, die verbunden bleibt, und mit LISTEN operation die Notwendigkeit erkennen, REFRESH aufzurufen. Ein schönes Projekt, mit dem Sie dies testen können, ist pgsidekick . Bei diesem Projekt können Sie das Shell-Skript verwenden, um LISTEN auszuführen, sodass Sie die REFRESH so planen können:

pglisten --listen=refresh_mv --print0 | xargs -0 -n1 -I? psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ?;"

Oder verwenden Sie pglater (auch in pgsidekick), um sicherzustellen, dass Sie REFRESH nicht sehr oft aufrufen. Sie können beispielsweise den folgenden Auslöser verwenden, um ihn REFRESH zu machen, jedoch innerhalb einer Minute (60 Sekunden):

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, '60 REFRESH MATERIALIZED VIEW CONCURRENLTY my_mv';
    RETURN NULL;
END;
$$;

Es wird also nicht REFRESH in einem Abstand von weniger als 60 Sekunden aufgerufen. Wenn Sie NOTIFY viele Male in weniger als 60 Sekunden aufrufen, wird die REFRESH nur einmal ausgelöst.

Überlegungen

Als cron-Option ist diese auch nur dann gut, wenn Sie mit etwas veralteten Daten arbeiten können. Dies hat jedoch den Vorteil, dass die REFRESH nur dann aufgerufen wird, wenn sie wirklich benötigt wird, sodass Sie weniger Overhead haben und auch die Daten näher aktualisiert werden bei Bedarf.

OBS: Ich habe die Codes und Beispiele noch nicht wirklich ausprobiert. Wenn jemand einen Fehler findet, einen Tippfehler versucht oder es versucht und funktioniert (oder nicht), lass es mich wissen.

68
MatheusOl

Lassen Sie mich auf die vorige Antwort von MatheusOl - die Pglater-Technologie - drei Punkte hinweisen.

  1. Als letztes Element des long_options-Arrays sollte es das Element "{0, 0, 0, 0}" enthalten, wie auf https://linux.die.net/man/3/getopt_long durch die Phrase "The last Element des Arrays muss mit Nullen gefüllt sein. " Also sollte es lesen -

    static struct option long_options[] =     {
          //......
          {"help", no_argument, NULL, '?'},
          {0, 0, 0, 0} 
    };
    
  2. Auf der Malloc/free Sache - eine freie (für char listen = malloc (...);) fehlt. Wie auch immer, malloc führte dazu, dass der pglater-Prozess unter CentOS abstürzte (aber nicht unter Ubuntu-I Ich weiß nicht warum.) Ich empfehle daher die Verwendung von char array und weise den Arraynamen dem char-Zeiger zu (sowohl char als auch char **). Sie müssen die Typkonvertierung erzwingen, während Sie dies tun (Zeigerzuweisung).

    char block4[100];
    ...
    password_Prompt = block4;
    ...
    char block1[500];
    const char **keywords = (const char **)&block1;
    ...
    char block3[300];
    char *listen = block3;
    sprintf(listen, "listen %s", id);
    PQfreemem(id);
    res = PQexec(db, listen);
    
  3. Verwenden Sie die nachstehende Tabelle, um das Timeout zu berechnen, wobei md ausläuft_duration ist. Hierbei handelt es sich um die Zeitdifferenz zwischen dem letzten Aktualisierungszeitpunkt (lr) und der aktuellen Uhrzeit.

    wenn md> = callback_delay (cd) ==> Zeitüberschreitung: 0

    wenn md + PING_INTERVAL> = cd ==> Zeitüberschreitung: cd-md [= cd- (now-lr)]

    wenn md + PING_INTERVAL <cd ==> Timeout: PI

Um diesen Algorithmus (3. Punkt) zu implementieren, sollten Sie 'lr' folgendermaßen initiieren:

res = PQexec(db, command);
latest_refresh = time(0);
if (PQresultStatus(res) == PGRES_COMMAND_OK) {
0
Park JongBum