Hi all, 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 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 3/26588281 pid | 88955 mode | ShareUpdateExclusiveLock granted | f fastpath | f datid | 16428 datname | monitoring pid | 88955 usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | 14-JAN-20 11:50:25.139819 +11:00 xact_start | 14-JAN-20 16:27:40.534726 +11:00 query_start | 14-JAN-20 16:27:40.534726 +11:00 state_change | 14-JAN-20 16:27:40.534726 +11:00 wait_event_type | Lock wait_event | relation state | active backend_xid | backend_xmin | 1655752595 query | autovacuum: VACUUM supply_nodes (to prevent wraparound) backend_type | autovacuum worker -[ RECORD 2 ]------+--------------------------------------------------------------------------------- locktype | relation database | 16428 relation | 1438729 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 254/8624453 pid | 6839 mode | ExclusiveLock granted | f fastpath | f datid | 16428 datname | monitoring pid | 6839 usesysid | 10 usename | postgres application_name | psql.bin client_addr | client_hostname | client_port | -1 backend_start | 14-JAN-20 17:02:53.860451 +11:00 xact_start | 14-JAN-20 18:01:49.211728 +11:00 query_start | 14-JAN-20 18:01:49.211728 +11:00 state_change | 14-JAN-20 18:01:49.21173 +11:00 wait_event_type | Lock wait_event | relation state | active backend_xid | backend_xmin | 1689815577 query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ; backend_type | client backend -[ RECORD 3 ]------+--------------------------------------------------------------------------------- locktype | relation database | 16428 relation | 1438729 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 355/0 pid | 65447 mode | ExclusiveLock granted | t fastpath | f datid | 16428 datname | monitoring pid | 65447 usesysid | 169436 usename | f_process application_name | PostgreSQL JDBC Driver client_addr | 10.153.154.36 client_hostname | client_port | 40899 backend_start | 14-JAN-20 18:00:02.784211 +11:00 xact_start | query_start | 14-JAN-20 18:02:26.831979 +11:00 state_change | 14-JAN-20 18:02:26.833197 +11:00 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | COMMIT backend_type | client backend James Sewell, -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
