On Wed, 2020-01-15 at 10:42 +1100, James Sewell wrote: > I am trying to chase down a locking issue - it looks like a materialized view refresh is being > held up by a relation lock which is held by an out of transaction session. My understanding was that > this was not possible (see SQL output below). > > The locking session is making progress (I can see query_start advancing), which makes it even more confusing. > > Any advice? > > # select * from pg_locks l join pg_stat_activity a on l.pid = a.pid where relation = 1438729::regclass; > -[ RECORD 1 ]------+--------------------------------------------------------------------------------- > locktype | relation > database | 16428 > relation | 1438729 > mode | ShareUpdateExclusiveLock > granted | f > fastpath | f > pid | 88955 > wait_event_type | Lock > wait_event | relation > state | active > query | autovacuum: VACUUM supply_nodes (to prevent wraparound) > backend_type | autovacuum worker > -[ RECORD 2 ]------+--------------------------------------------------------------------------------- > locktype | relation > database | 16428 > relation | 1438729 > mode | ExclusiveLock > granted | f > pid | 6839 > wait_event_type | Lock > wait_event | relation > state | active > query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ; > backend_type | client backend > -[ RECORD 3 ]------+--------------------------------------------------------------------------------- > locktype | relation > database | 16428 > relation | 1438729 > mode | ExclusiveLock > granted | t > pid | 65447 > application_name | PostgreSQL JDBC Driver > wait_event_type | Client > wait_event | ClientRead > state | idle > query | COMMIT > backend_type | client backend I cannot explain that either; could it be shared memory corruption? What I would try is SELECT pg_terminate_backend(65447); and see if the session and its lock go away. If that does not do the trick, I would restart PostgreSQL, which should get rid of any possible memory corruption. Then perhaps the anti-wraparoung autovacuum can succeed. This autovacuum would also block you, but you should let it finish, since it is an important system task. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
