A sample of updatable materialized views in PostgreSQL 9.3

Hello, habrachelovek! You probably already felt materialized views, appeared in PostgreSQL 9.3. One of the drawbacks is that in the process of updating the representation uses exclusive (ACCESS EXCLUSIVE) lock, making it impossible to query performance. In PostgreSQL 9.4 planiruetsya add ability to read from the view during updates. So, in this post, I want to show one of the ways out of this situation.

Will help us in this matter double buffering. The gist of it is that creates two materialized views, while one is updated (the entity on which it depends the submission locked in ACCESS SHARE mode that allows you to make requests to them), the second can work. Once the update is complete, rearrange them.

Helper functions to create and delete views:
the

CREATE OR REPLACE FUNCTION public.create_materialized_view ( p_viewname text, text p_basename )
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;

A function to exchange buffers — updated back-buffer and rename the back in front and Vice versa.
the

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;

Also need a function to create indexes (as you have to duplicate them for back-buffer):
the

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;

The script update_mv update the view (used in the cron job):
the

#!/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

And, in fact, itself a cron job (for fcron) is updated every hour:
the

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

Example usage:
the

SELECT public.create_materialized_view ( 'public.mv_order', 'public.vw_order' ); -- public.vw_order - view
SELECT public.create_materialized_view ( 'public.mv_delivery, 'public.vw_delivery' ); -- public.vw_delivery - view
SELECT public.create_materialized_view_index ( 'public.mv_order', 'idx_mv_order_purchase', '{purchaser_name,order_date}'::text[] );

Article based on information from habrahabr.ru