Si has estado buscando como materializar vistas en Postgres habrás visto que de momento no trae esa posibilidad, pero se puede conseguir algo parecido por medio de tablas temporales (CREATE TEMPORARY TABLE). Haciendo por ejemplo:
create temporary table v1 as select * from v1;
el sistema calcularía la vista v1 y guardaría las filas resultantes en una tabla temporal llamada también v1. Esto puede servir para una vista que tarda mucho en ejecutarse. Despues de hacer esto, cada vez que una consulta acceda a v1 accederá a los datos ya calculados y devolverá las filas casi instantáneamente. Además como se puede ver el nombre de la tabla temporal puede ser el de una tabla o vista ya existente. Esa tabla o vista no se borra, simplemente no sería accesible hasta que no se destruya la tabla temporal (DROP TABLE) o se cierre la sesión.
Esta es otra característica de las tablas temporales, son visibles solo en la sesión (conexión a la base de datos) en la que se han creado, por lo que otros usuarios no pueden verla y además se destruye al cerrar la sesión. No hay problema en hacer DROP TABLE v1; se borrará la tabla temporal. En el caso de que no estuviera creada la tabla temporal no te cargarás la vista, ya que eso es con DROP VIEW, y el DROP TABLE fallaría.
Lo de usar el mismo nombre para la vista y para la tabla sirve para no tener que modificar las consultas que ya estuvieran hechas: lo que antes accedía a v1 sigue accediendo a v1 pero más rápido.
El principal problema, y por esto no se le puede llamar realmente vista materializada, es que en la tabla temporal queda una “foto” de lo que devolvía v1 en el momento de hacer el CREATE TEMPORARY TABLE; no se verían los cambios que hayan podido ocurrir desde entonces en las tablas de las cuales la vista v1 tomaba los datos. Esto puede ser conveniente o no. Por ejempo: un usuario tiene que estudiar unas gráficas provenientes de los datos de v1, y pide una gráfica diferente cada 10 minutos. Si hay otros usuarios metiendo datos en la BD a la vez, las gráficas no serían coherentes unas con otras. Con la tabla temporal las gráficas siempre leerían del mismo conjunto de datos.
En otros casos no sería conveniente, ya que normalente se quiere tener los datos actualizados al momento. En ese caso lo que se puede hacer es cada cierto tiempo destruir la tabla temporal y volverla a crear. Todo depende de lo lenta que sea la consulta y de lo actualizados que tengan que estar los datos.
Dependencias
Este sistema puede dar problemas a la hora de materializar varias vistas, si dependen unas de otras, debido al sistema que tiene Postgres para registrar las dependencias entre objetos. En el CREATE TEMPORARY TABLE anterior, la vista v1 es un objeto de la base de datos, y la nueva tabla temporal v1 será otro (el nombre real interno de la tabla temporal sería algo como “pg_temp_1.v1″. Debido a esto y dependiendo de como accedas a v1 puede que estés accediento a la vista, con lo cual iría lento de nuevo.
En mi caso tengo varias vistas que tardan mucho en ejecutarse, y que dependen unas de otras. Por ejemplo si tengo v1, v2 y v3 que no dependen de ninguna vista sino de tablas, luego v4 que depende de v1 y v2; y v5 que depende de v3 y v4. Si hago esto:
create temporary table v1 as select * from v1;
create temporary table v2 as select * from v2;
create temporary table v3 as select * from v3;
create temporary table v4 as select * from v4;
create temporary table v5 as select * from v5;
Las tres primeras sentencias harían lo esperado. La cuarta en cambio no: accedería a las vistas reales v1 y v2 y no a las tablas temporales, a pesar de que justo antes las acabo de crear. Esto es porque cuando se creó la vista v4 (CREATE VIEW) no estaban estas tablas temporales, se hizo contra las vistas v1 y v2, y Postgres internamente tiene registrado que se lea de ahí. Por lo tanto el cuarto CREATE TEMPORARY TABLE funcionará pero muy lento, no se beneficiará de que v1 y v2 ya están materializadas. Dependerá de cada vista si esto es importante o no:
Por ejemplo si en el SELECT de v4 aparecen v1 y v2 en un JOIN, tardará probablemente la suma de lo que tardan las dos y un poco más; sin embargo si es un SELECT con v1 en el FROM y con una subconsulta en el SELECT o en el WHERE en la que aparece v2, se multiplicará el tiempo que necesita (lo que tarde v1 + el número de filas de v1 x lo que tarde v2).
Una solución a esto es crear las tablas temporales en una función de plperl. Este lenguaje, a diferencia de plpgsql, no tiene en cuenta las dependencias entre objetos. En este caso v4 y v5 usarán las tablas temporales creadas anteriormente.
Esta función serviría para “materializar” las vistas que pongáis en la lista @vistas:
CREATE OR REPLACE FUNCTION materializar() RETURNS integer AS
$BODY$
@vistas = ("v1", "v2", "v3", "v4", "v5");
@consultas = ();
foreach $vista (@vistas) {
$sql = “SELECT definition FROM pg_views WHERE viewname=’$vista’”;
$rv = spi_exec_query($sql);
$row = $rv->{rows}[0];
push @consultas, “CREATE TEMPORARY TABLE \”$vista\” AS $row->{definition}”;
}
foreach $consulta (@consultas) {
$rv = spi_exec_query($consulta);
}
return 0;
$BODY$
LANGUAGE ‘plperl’ VOLATILE;
Esta función crea las tablas temporales v1 … v5 en ese orden, leyendo siempre los datos de las tablas temporales recién creadas, por lo que lo hace de la forma más rápida posible. La lista @vistas tiene que ir en orden de dependencia, una vista que dependa de otra tiene que ir especificada después de ella.
