A selection of updatable materialized views in PostgreSQL 9.3

Hello, habracheloveki! You probably already felt the materialized views introduced in PostgreSQL 9.3. One of the drawbacks is that in the process of updating the view, an exclusive (ACCESS EXCLUSIVE) lock is used, which makes it impossible to query the view. In PostgreSQL 9.4, it is planned to add the ability to read from a view during its update. Well, for now, in this short article, I want to show one of the ways out of this situation. Double buffering

will help us in this.. Its essence is that two materialized representations are created, while one is updated (while the entities on which this representation depends are blocked in ACCESS SHARE mode, which allows you to make requests to them), you can work with the second. Once the update is complete, swap them.

Helper functions for creating and deleting views:

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;

The function for exchanging buffers is updating the back buffer and renaming back to front and vice versa.

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, the function for creating indices will not hurt (since you have to duplicate them for the back buffer):

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 update_mv script that updates the views (used in the cron job):

#!/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, the cron task itself (for fcron) - updated once an hour:

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

Usage example:

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[] );