Selection of updated materialized views in PostgreSQL 9.3

Hello, habracheloveki! You have probably already felt the materialized views that appeared in PostgreSQL 9.3. One of the drawbacks is that the process of updating the view uses an exclusive (ACCESS EXCLUSIVE) lock, making it impossible to query the view. In PostgreSQL 9.4 , you plan to add the ability to read from a view while it is being updated. Well, for now, in this small note, 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 views are created, while one is updated (and entities that this view depends on are blocked in ACCESS SHARE mode, which allows you to make requests to them), you can work with the second one. As soon as the update is completed, 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; 

Function for the exchange of buffers - update the back- buffer and rename the 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; 

The function for creating indexes also does 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 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, actually, the cron task itself (for fcron) is updated once per 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[] );