tipos - vistas en postgresql pdf

¿Cómo puedo asegurarme de que una vista materializada esté siempre actualizada?

Question

Tendré que invocar REFRESH MATERIALIZED VIEW en cada cambio en las tablas involucradas, ¿verdad? Me sorprende no encontrar mucha discusión sobre esto en la web.

¿Cómo debo hacer para hacer esto?

Creo que la mitad superior de la respuesta aquí es lo que estoy buscando: https://stackoverflow.com/a/23963969/168143

¿Hay algún peligro para esto? Si falla la actualización de la vista, ¿se retrotraerá la transacción en la actualización de invocación, inserción, etc.? (esto es lo que quiero ... creo)

Answer 1

Tendré que invocar REFRESH MATERIALIZED VIEW en cada cambio en las tablas involucradas, ¿verdad?

Sí, PostgreSQL por sí mismo nunca lo llamará automáticamente, debes hacerlo de alguna manera.

¿Cómo debo hacer para hacer esto?

Muchas formas de lograr esto. Antes de dar algunos ejemplos, tenga en cuenta que el comando REFRESH MATERIALIZED VIEW bloquea la vista en el modo AccessExclusive, por lo que mientras está funcionando, ni siquiera puede hacer SELECT en la tabla.

Aunque, si está en la versión 9.4 o más reciente, puede darle la opción CONCURRENTLY :

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

Esto adquirirá un ExclusiveLock, y no bloqueará las consultas SELECT , pero puede tener una sobrecarga mayor (depende de la cantidad de datos cambiados, si algunas filas han cambiado, entonces podría ser más rápido). Aunque todavía no puede ejecutar dos comandos REFRESH mismo tiempo.

Actualizar manualmente

Es una opción a considerar. Especialmente en casos de carga de datos o actualizaciones por lotes (por ejemplo, un sistema que solo carga toneladas de información / datos después de largos períodos de tiempo) es común tener operaciones al final para modificar o procesar los datos, por lo que puede incluir simplemente una operación de REFRESH al final de eso

Programación de la operación REFRESH

La primera y ampliamente utilizada opción es usar algún sistema de programación para invocar la actualización, por ejemplo, podría configurar el mismo en un trabajo cron:

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

Y luego su vista materializada se actualizará cada 30 minutos.

Consideraciones

Esta opción es realmente buena, especialmente con la opción CONCURRENTLY , pero solo si puede aceptar que los datos no estén 100% actualizados todo el tiempo. Tenga en cuenta que, incluso con o sin CONCURRENTLY , el comando REFRESH necesita ejecutar la consulta completa, por lo que debe tomar el tiempo necesario para ejecutar la consulta interna antes de considerar el tiempo para programar la REFRESH .

Refrescante con un gatillo.

Otra opción es llamar a REFRESH MATERIALIZED VIEW en una función de activación, como esta:

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

Luego, en cualquier tabla que implique cambios en la vista, usted hace:

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

Consideraciones

Tiene algunos escollos críticos para el rendimiento y la concurrencia:

1. Cualquier operación de INSERTAR / ACTUALIZAR / BORRAR tendrá que ejecutar la consulta (lo cual es posible si está considerando MV);
2. Incluso con CONCURRENTLY , una REFRESH aún bloquea otra, por lo que cualquier INSERT / UPDATE / DELETE en las tablas involucradas se serializará.

La única situación en la que puedo pensar es una buena idea si los cambios son realmente raros.

Actualizar usando ESCUCHAR / NOTIFICAR

El problema con la opción anterior es que es síncrono e impone una gran sobrecarga en cada operación. Para mejorar eso, puede usar un disparador como antes, pero que solo llama a una operación NOTIFY :

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

Entonces, puede crear una aplicación que se mantenga conectada y use la operación LISTEN para identificar la necesidad de llamar a REFRESH . Un buen proyecto que puede usar para probar esto es pgsidekick , con este proyecto puede usar shell script para hacer LISTEN , así puede programar el REFRESH como:

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

O usa pglater (también dentro de pgsidekick ) para asegurarte de no llamar a REFRESH muy a menudo. Por ejemplo, puede usar el siguiente activador para hacerlo REFRESH , pero dentro de 1 minuto (60 segundos):

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

Por lo tanto, no llamará a REFRESH en menos de 60 segundos de diferencia, y también si NOTIFY muchas veces en menos de 60 segundos, el REFRESH se activará solo una vez.

Consideraciones

Como opción de cron, esta también es buena solo si puede usar un poco de datos obsoletos, pero tiene la ventaja de que se llama a REFRESH solo cuando es realmente necesario, por lo que tiene menos gastos generales, y también los datos se actualizan más cerca. a cuando sea necesario.

OBS: Todavía no he probado los códigos y los ejemplos, así que si alguien encuentra un error, lo tipea o lo intenta y funciona (o no), hágamelo saber.

Answer 2

Permítanme señalar tres cosas en la respuesta anterior de MatheusOl: la tecnología pglater.

1. Como el último elemento de la matriz long_options, debe incluir el elemento "{0, 0, 0, 0}" como se indica en https://linux.die.net/man/3/getopt_long por la frase "El último elemento de la matriz Hay que rellenar con ceros ". Por lo tanto, debe leer -

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

2. En el archivo malloc / free - falta uno (para char listen = malloc (...);). De todos modos, malloc causó que el proceso pglater se bloqueara en CentOS (pero no en Ubuntu, no sé por qué). Por lo tanto, recomiendo usar la matriz char y asignar el nombre de la matriz al puntero char (tanto char como char **). Es necesario forzar la conversión de tipos mientras se hace eso (asignación de puntero).

   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. Use la siguiente tabla para calcular el tiempo de espera donde md es mature_duration, que es la diferencia de tiempo entre el último punto de actualización (lr) y el tiempo actual.

   cuando md> = callback_delay (cd) ==> timeout: 0

   cuando md + PING_INTERVAL> = cd ==> timeout: cd-md [= cd- (now-lr)]

   cuando md + PING_INTERVAL <cd ==> timeout: PI

Para implementar este algoritmo (tercer punto), debe iniciar ''lr'' de la siguiente manera:

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