Выборка из обновляемых материализированных представлений в PostgreSQL 9.3

Здравствуйте, хабрачеловеки! Вы, вероятно, уже пощупали материализированные представления, появившиеся в PostgreSQL 9.3. Одним из недостатков есть то, что в процессе обновления представления используется эксклюзивная (ACCESS EXCLUSIVE) блокировка, делающая невозможным запросы к представлению. В PostgreSQL 9.4 планируется добавить возможность чтения из представления во время его обновления. Ну, а пока, в этой небольшой заметке, я хочу показать один из способов выхода из этого положения.

Поможет нам в этом деле двойная буферизация. Суть ее в том, что создаются два материализированных представления, пока одно обновляется (при этом сущности, от которых зависит это представление блокируются в режиме ACCESS SHARE, что позволяет делать запросы к ним), со вторым можно работать. Как только обновление завершится, меняем их местами.

Вспомогательные функции для создания и удаления представлений:

CREATE OR REPLACE FUNCTION public.create_materialized_view ( p_viewname text, p_basename text ) RETURNS void AS $BODY$ BEGIN 	EXECUTE 'CREATE MATERIALIZED VIEW ' || p_viewname || ' AS SELECT * FROM ' || p_basename; 	EXECUTE 'CREATE MATERIALIZED VIEW ' || p_viewname || '_back AS SELECT * FROM ' || p_basename; END  $BODY$ LANGUAGE plpgsql VOLATILE;  CREATE OR REPLACE FUNCTION public.drop_materialized_view ( p_viewname text ) RETURNS void AS $BODY$ BEGIN 	EXECUTE 'DROP MATERIALIZED VIEW ' || p_viewname; 	EXECUTE 'DROP MATERIALIZED VIEW ' || p_viewname || '_back'; END  $BODY$ LANGUAGE plpgsql VOLATILE; 

Функция для обмена буферов — обновляем back-буфер и переименовываем back в front и наоборот.

CREATE OR REPLACE FUNCTION public.swap_materialized_view ( p_viewname text ) RETURNS void AS $BODY$ BEGIN 	EXECUTE 'REFRESH MATERIALIZED VIEW ' || p_viewname || '_back'; 	EXECUTE 'ALTER MATERIALIZED VIEW ' || p_viewname || ' RENAME TO ' || split_part ( p_viewname, '.', 2 ) || '_temp'; 	EXECUTE 'ALTER MATERIALIZED VIEW ' || p_viewname || '_back RENAME TO ' || split_part ( p_viewname, '.', 2 ); 	EXECUTE 'ALTER MATERIALIZED VIEW ' || p_viewname || '_temp RENAME TO ' || split_part ( p_viewname, '.', 2 ) || '_back'; END  $BODY$ LANGUAGE plpgsql VOLATILE; 

Таже не помешает функция для создания индексов (так как приходится их дублировать для back-буфера):

CREATE OR REPLACE FUNCTION public.create_materialized_view_index ( p_viewname text, p_indexname text, p_columns text[] ) RETURNS void AS $BODY$ BEGIN 	EXECUTE 'CREATE INDEX ' || p_indexname || ' ON ' || p_viewname || ' ( ' || array_to_string ( p_columns, ',' ) || ' )'; 	EXECUTE 'CREATE INDEX ' || p_indexname || '_back ON ' || p_viewname || '_back ( ' || array_to_string ( p_columns, ',' ) || ' )'; END  $BODY$ LANGUAGE plpgsql VOLATILE; 

Скрипт update_mv, обновляющий представления (используется в задании cron):

#!/bin/bash  HOST="localhost" PORT="5432" USER="postgres" DATABASE="mydb"  VIEWS=() VIEWS+=('public.mv_order') VIEWS+=('public.mv_delivery')  VCOUNT=${#VIEWS[@]}  for ((i = 0; i < ${VCOUNT}; i++)) do         VIEW=${VIEWS[$i]}         QUERY="SELECT public.swap_materialized_view('$VIEW')"          if psql -h $HOST -p $PORT -U $USER -d $DATABASE -q -c "SET client_min_messages = ERROR; $QUERY">/dev/null 1>&1; then                 logger -p cron.notice -t update_mv Updated materialized view $VIEW         else                 logger -p cron.notice -t update_mv Can\'t update materialized view $VIEW         fi done 

И, собственно, само cron-задание (для fcron) — обновляем раз в час:

@mail(false),runatreboot(true) 1h update_mv 

Пример использования:

SELECT public.create_materialized_view ( 'public.mv_order', 'public.vw_order' ); -- public.vw_order - представление SELECT public.create_materialized_view ( 'public.mv_delivery, 'public.vw_delivery' ); -- public.vw_delivery - представление SELECT public.create_materialized_view_index ( 'public.mv_order', 'idx_mv_order_purchase', '{purchaser_name,order_date}'::text[] );