Обсуждение: [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