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
-[ 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.
