postgresql - usar - Actualice una vista materializada automáticamente usando una regla o notificación

refrescar vista materializada oracle

Question

Tengo una vista materializada en una base de datos PostgreSQL 9.3 que rara vez cambia (aproximadamente dos veces al día). Pero cuando lo haga, me gustaría actualizar sus datos rápidamente.

Esto es lo que estaba pensando hasta ahora:

Hay una vista materializada mat_view que obtiene sus datos de las tablas table1 y table2 usando alguna instrucción join.

Cada vez que algo en table1 o table2 cambia, ya tengo un trigger que actualiza una configuración de la tabla de config consiste en

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

Entonces, si algo en table1 cambia (hay un disparador en UPDATE y en DELETE para cada instrucción), el campo need_update en la primera fila se establece en TRUE . Lo mismo vale para table2 y la segunda fila.

Obviamente, si need_update es TRUE, entonces la vista materializada debe actualizarse.

ACTUALIZACIÓN : dado que las vistas materializadas no son compatibles con las reglas (como se menciona en @pozs en un comentario a continuación), iría un paso más allá. v_mat_view una vista ficticia v_mat_view con la definición " SELECT * FROM mat_view ". Cuando el usuario hace un SELECT en esta vista, necesito crear una regla ON SELECT que haga lo siguiente:

• compruebe si mat_view debe actualizarse ( SELECT 1 FROM config WHERE mat_view_name=''mat_view'' AND need_update=TRUE )
• restablecer el indicador need_update con UPDATE config SET need_update=FALSE where mat_view_name=''mat_view''
• REFRESH MATERIALIZED VIEW mat_view
• y al final hacer la declaración SELECT original pero con mat_view como el objetivo.

ACTUALIZACIÓN2 : Intenté crear los pasos anteriores:

Crea una función que maneje los cuatro puntos mencionados anteriormente:

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;

Cree la vista v_mat_view que realmente selecciona de la función 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.

El resultado es insatisfactorio

# 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

en comparación con la selección de la mat_view en sí:

# 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

Básicamente, SÍ FUNCIONA, pero el rendimiento puede ser un problema.

Alguien tiene mejores ideas? De lo contrario, tendría que implementarlo de alguna manera en la lógica de la aplicación o algo peor: ejecutar un cronjob simple que se ejecute cada minuto más o menos. :-(

Answer 1

Debe actualizar la vista en desencadenadores después de insertar / actualizar / eliminar / truncar para cada instrucción en table1 y 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 esta forma, su vista materializada siempre estará actualizada. Esta solución simple puede ser difícil de aceptar con inserciones / actualizaciones frecuentes y selecciones esporádicas. En su caso (rara vez cambia aproximadamente dos veces al día), lo ideal es que se ajuste a sus necesidades.

Para realizar la actualización diferida de una vista materializada, necesita una de las siguientes características:

• activador asincrónico
• disparador antes de seleccionar
• gobernar en seleccionar antes

Postgres no tiene ninguno de ellos, por lo que parece que no hay una solución clara de postgres.

Teniendo esto en cuenta, consideraría una función de contenedor para selecciones en mat_view, por ejemplo

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 es aceptable en la práctica, depende de detalles que no conozco.

Answer 2

PostgreSQL 9.4 agregó REFRESH CONCURRENTLY a Vistas Materializadas.

Esto puede ser lo que está buscando cuando describe intentar configurar una actualización asincrónica de la vista materializada.

Los usuarios que seleccionen desde la vista materializada verán datos incorrectos hasta que finalice la actualización, pero en muchos escenarios que utilizan una vista materializada, esta es una compensación aceptable.

Utilice un activador de nivel de extracto que mire las tablas subyacentes para detectar cualquier cambio y luego actualice la vista materializada al mismo tiempo.