Thread: [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
Nico Williams <nico@cryptonector.com> writes:
> pg_dump(1) gets an EXCLUSIVE LOCK with NOWAIT
... uh, really? It's not supposed to take anything higher than ACCESS
SHARE, which should coexist fine with anything short of ACCESS EXCLUSIVE.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Apr 19, 2017 at 05:20:31PM -0400, Tom Lane wrote: > Nico Williams <nico@cryptonector.com> writes: > > pg_dump(1) gets an EXCLUSIVE LOCK with NOWAIT > > ... uh, really? It's not supposed to take anything higher than ACCESS > SHARE, which should coexist fine with anything short of ACCESS EXCLUSIVE. Whoops, sorry, that's a mistake in drafting the report. You're right, it takes an ACCESS SHARE MODE lock, NOWAIT. The remainder of the report remains correct. Nico -- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Nico Williams <nico@cryptonector.com> writes:
> On Wed, Apr 19, 2017 at 05:20:31PM -0400, Tom Lane wrote:
>> ... uh, really? It's not supposed to take anything higher than ACCESS
>> SHARE, which should coexist fine with anything short of ACCESS EXCLUSIVE.
> Whoops, sorry, that's a mistake in drafting the report. You're right,
> it takes an ACCESS SHARE MODE lock, NOWAIT. The remainder of the report
> remains correct.
But REFRESH CONCURRENTLY takes EXCLUSIVE lock, not ACCESS EXCLUSIVE lock,
so it won't conflict with pg_dump. There's something missing from your
explanation.
Also, the only use of NOWAIT I see is in a parallel pg_dump worker.
It is known that parallel pg_dump can lead to deadlocks if third
parties are taking ACCESS EXCLUSIVE locks --- but you haven't identified
where the ACCESS EXCLUSIVE request is coming from.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs