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

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

Поможет нам в этом деле двойная буферизация ^[3]. Суть ее в том, что создаются два материализированных представления, пока одно обновляется (при этом сущности, от которых зависит это представление блокируются в режиме 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[] );

Автор: blackmaster

Источник ^[4]