El blog de Juan Palómez

6 junio 2008

Vistas materializadas en PostgreSQL

Filed under: Uncategorized — Etiquetas: , , , , — thisisoneball @ 10:26

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.

6 comentarios »

  1. Su articulo es muy interesante y me ayuda mucho en un trabajo que realizo sobre sincronizacion de BD en postgres, pero me gustaria preguntarle si es posible hacer un refresh de la tabla temporal y que actualice los datos en la tabla base
    gracias.

    Comentario por Refresh — 3 febrero 2010 @ 19:12

  2. Para recargar los datos de las tablas temporales basta con recrearlas (drop table + create temporary table)

    Comentario por thisisoneball — 4 febrero 2010 @ 00:13

  3. Buenos dias Juan Palómez
    ¿Como estas? me gustaria consultar si existe la posibilidad de limitar la tabla temporal a por ejemplo 200 registros?

    Comentario por Nathalia Cazal — 5 marzo 2012 @ 14:55

  4. Prueba con un LIMIT 200 en la SELECT

    Comentario por thisisoneball — 13 marzo 2012 @ 15:14

  5. Hola, me gustaría saber si es posible actualizar la tabla temporal sin necesidad de que me la cree de nuevo totalmente la tabla temporal sino que solo me actualice los cambios echos ya sea un update o un insert. Gracias

    Comentario por Gilberto Varela — 13 septiembre 2012 @ 21:04

    • creo que no se puede, tendrias que hacer el update o el insert tambien sobre la tabla temporal

      Comentario por thisisoneball — 15 septiembre 2012 @ 18:48


RSS feed for comments on this post. TrackBack URI

Responder

Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: