Здравствуйте, читатели! Вы, вероятно, уже пощупали материализированные представления, появившиеся в 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[] );
Автор: blackmaster