Hoe kan ik ervoor zorgen dat een gerealiseerde weergave altijd up-to-date is?

Ik moet REFRESH MATERIALIZED VIEWaanroepen bij elke wijziging in de betrokken tabellen, toch? Het verbaast me dat er niet veel discussie hierover is op internet.

Hoe moet ik dit doen?

Ik denk dat de bovenste helft van het antwoord hier is wat ik zoek: https://stackoverflow.com/a/ 23963969/168143

Zijn hier gevaren aan verbonden? Als het bijwerken van de weergave mislukt, wordt de transactie op de aanroepende update, insert, etc. dan teruggedraaid? (dit is wat ik wil… denk ik)

Antwoord 1, autoriteit 100%

Ik moet REFRESH MATERIALIZED VIEWaanroepen bij elke wijziging in de betrokken tabellen, toch?

Ja, PostgreSQL zelf zal het nooit automatisch aanroepen, je moet het op de een of andere manier doen.

Hoe moet ik dit doen?

Vele manieren om dit te bereiken. Voordat u enkele voorbeelden geeft, moet u er rekening mee houden dat REFRESH MATERIALIZED VIEW-opdrachtblokkeert de weergave in AccessExclusive-modus, dus terwijl het werkt, kunt u niet eens SELECTop de tafel doen.

Hoewel, als je versie 9.4 of nieuwer hebt, je deze de optie CONCURRENTLYkunt geven:

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

Dit krijgt een ExclusiveLock en blokkeert geen SELECT-query’s, maar kan een grotere overhead hebben (afhankelijk van de hoeveelheid gewijzigde gegevens, als er weinig rijen zijn gewijzigd, kan het sneller zijn) . Hoewel je nog steeds geen twee REFRESH-commando’s tegelijk kunt uitvoeren.

Handmatig vernieuwen

Het is een optie om te overwegen. Vooral in het geval van het laden van gegevens of batch-updates (bijvoorbeeld een systeem dat pas na lange tijd tonnen informatie/gegevens laadt) is het gebruikelijk om bewerkingen aan het einde te hebben om de gegevens te wijzigen of te verwerken, zodat u eenvoudig een REFRESHbewerking aan het einde ervan.

De REFRESH-bewerking plannen

De eerste en veelgebruikte optie is om een ​​planningssysteem te gebruiken om de vernieuwing aan te roepen, je zou bijvoorbeeld zoiets kunnen configureren in een cron-taak:

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

En dan wordt je gematerialiseerde weergave elke 30 minuten vernieuwd.

Overwegingen

Deze optie is echt goed, vooral met de optie CONCURRENTLY, maar alleen als je kunt accepteren dat de gegevens niet altijd 100% up-to-date zijn. Houd er rekening mee dat, zelfs met of zonder CONCURRENTLY, de opdracht REFRESHde hele query moet uitvoeren, dus u moet de tijd nemen die nodig is om de innerlijke query uit te voeren voordat rekening houdend met de tijd om de REFRESHin te plannen.

Verversen met een trigger

Een andere optie is om de REFRESH MATERIALIZED VIEWaan te roepen in een triggerfunctie, zoals deze:

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

Vervolgens, in elke tabel die wijzigingen in de weergave met zich meebrengt, doet u:

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

Overwegingen

Het heeft een aantal kritische valkuilen voor prestaties en gelijktijdigheid:

1. Elke INSERT/UPDATE/DELETE-bewerking moet de query uitvoeren (wat mogelijk traag is als u MV overweegt);
2. Zelfs met CONCURRENTLY, blokkeert een REFRESHnog steeds een andere, dus elke INSERT/UPDATE/DELETE op de betrokken tafels wordt geserialiseerd.

De enige situatie die ik als een goed idee kan bedenken, is als de veranderingen echt zeldzaam zijn.

Ververs met LISTEN/NOTIFY

Het probleem met de vorige optie is dat deze synchroon is en bij elke bewerking een grote overhead met zich meebrengt. Om dat te verbeteren, kun je een trigger gebruiken zoals voorheen, maar die roept alleen een op NOTIFYbewerking:

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

Dus dan kun je een applicatie bouwen die verbonden blijft en gebruikmaakt van LISTEN-bewerkingom de noodzaak te identificeren om REFRESHaan te roepen. Een leuk project dat je kunt gebruiken om dit te testen is pgsidekick, bij dit project kun je shellscript gebruiken om LISTEN, zodat u de REFRESHkunt plannen als:

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

Of gebruik pglater(ook in pgsidekick) om ervoor te zorgen dat je REFRESHniet vaak aanroept. U kunt bijvoorbeeld de volgende trigger gebruiken om het REFRESHte maken, maar binnen 1 minuut (60 seconden):

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;
$$;

Dus het zal REFRESHniet aanroepen in minder dan 60 seconden uit elkaar, en ook als u NOTIFYvele malen in minder dan 60 seconden, de REFRESHwordt slechts één keer geactiveerd.

Overwegingen

Als cron-optie is deze ook alleen goed als je een beetje verouderde gegevens kunt dragen, maar dit heeft het voordeel dat de REFRESHalleen wordt aangeroepen als het echt nodig is, dus je hebt minder overhead, en ook de gegevens worden dichter bij wanneer nodig bijgewerkt.

OBS: ik heb de codes en voorbeelden nog niet echt geprobeerd, dus als iemand een fout of typefout vindt of het probeert en het werkt (of niet), laat het me dan weten.

Antwoord 2

Laat me op drie dingen wijzen op het vorige antwoord van MatheusOl – de pglater-technologie.

1. Als laatste element van de array long_options moet het het element “{0, 0, 0, 0}” bevatten, zoals aangegeven op https://linux.die.net/man/3/getopt_longdoor de zin “Het laatste element van de array moet met nullen worden gevuld.” Het zou dus moeten lezen –

   static struct option long_options[] =     {
         //......
         {"help", no_argument, NULL, '?'},
         {0, 0, 0, 0} 
   };
   

2. Wat betreft malloc/free — één free(for char listen = malloc(…);) ontbreekt. Hoe dan ook, malloc zorgde ervoor dat het pglater-proces crashte op CentOS (maar niet op Ubuntu – ik weet niet waarom). Dus raad ik aan om char array te gebruiken en de arraynaam toe te wijzen aan de char-aanwijzer (aan zowel charals char**). U moet vaak typeconversie forceren terwijl u dat doet (aanwijzertoewijzing).

   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. Gebruik onderstaande tabel om de time-out te berekenen waarbij md mature_duration is, wat het tijdsverschil is tussen het laatste verversings(lr)-tijdstip en de huidige tijd.

   wanneer md >= callback_delay(cd) ==> time-out: 0

   wanneer md + PING_INTERVAL >= cd ==> time-out: cd-md[=cd-(now-lr)]

   wanneer md + PING_INTERVAL < cd ==> time-out: PI

Om dit algoritme (3e punt) te implementeren, moet u ‘lr’ als volgt initiëren –

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