postgresql 9.5 - lock child materialized view while parents refresh concurrently

Question

I have a materialized view that depends on several other materialized views.

matviewA matviewB matviewC
    \       |       /
         matviewX

What I'd like to do is refresh the parent materialized views concurrently all at the same time. (This is not really a problem - as shown below.) [ In my current environment, each parent takes about an hour to refresh. ]

psql -c "refresh materialized view concurrently matviewA" &
psql -c "refresh materialized view concurrently matviewB" &
psql -c "refresh materialized view concurrently matviewC" &

However if I kick off a refresh on the child:

psql -c "refresh materialized view concurrently matviewX" &

It runs right away and doesn't wait for the parents to finish refreshing because they don't lock their children from doing refreshes while they themselves are already refreshing concurrently.

I tried putting a lock on the child while the parents are running:

psql -c "lock matviewX in share mode; refresh materialized view concurrently matviewA" &
psql -c "lock matviewX in share mode; refresh materialized view concurrently matviewB" &
psql -c "lock matviewX in share mode; refresh materialized view concurrently matviewC" &

Unfortunately, you can't put explicit locks on materialized views.

If I don't use "concurrently" on the parents, the child materialized view becomes unreadable. (But the child refresh does wait before running.)

I can write some lock management in the (bash) wrapper script that calls "psql -c". Or I could use a more sophisticated third party job scheduler. I was hoping there would be an easier way.

I might be able to write a function and put all of the refreshes in that function and then use a temp table for manual explicit lock management.

Or maybe use advisory locks somehow.

Suggestions?

Ezequiel Tolnay Ezequiel Tolnay · Accepted Answer · 2016-07-07T04:57:02

You could simply use wait (see https://stackoverflow.com/a/18663969/3886053):

for parent in matviewA matviewB matviewC; do
  psql -c "refresh materialized view concurrently $parent" &
  echo "Started refreshing materialized view $parent"
done
echo -n "Waiting for all parents to finish... "
wait
echo "finished. Refreshing now the child materialized view"
psql -c "refresh materialized view concurrently matviewX"

postgresql 9.5 - lock child materialized view while parents refresh concurrently

1 Answers