[BUGS] pg_dump(1) failures when concurrently refreshing mat views

pg_dump(1) gets an EXCLUSIVE LOCK with NOWAIT and fails if a
not-UNLOGGED, not-TEMP TABLE is locked.  Materialized views (from code
inspection) make not-UNLOGGED, not-TEMP materialization tables, and
REFRESH .. CONCURRENTLY acquires an EXCLUSIVE LOCK on the table.

This means that REFRESH .. CONCURRENTLY can cause a pg_dump(1) to fail.

We've observed this with our alternative view materialization SQL [0],
which is patterned after PostgreSQL's MATERIALIZED VIEWS.

The workaround is to use the -T option to list tables to not dump, and
just not dump materialization tables.

Some possible fixes:
- dump the TEMP table from which a locked materialization table is  being concurrently refreshed
- skip locked materialization tables; cause a refresh on reload
- never dump materialization tables; cause a refresh on reload

Some possible additional options:
- make materialization tables optionally UNLOGGED
- add an option to pg_dump(1) to wait for locks
  (A bad idea if pg_dump(1) acquires all the locks before doing any  work.)

[0] https://github.com/twosigma/postgresql-contrib/blob/master/pseudo_mat_views.sql

Nico
-- 


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs