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