Hello,
A client has recently had a couple of hot standby query conflict pile-ups around AccessShare lock waits on pg_attribute. Here is an example from the log (with the table & column names changed):
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-1] sql_error_code = 00000 LOG: process 118946 still waiting for AccessShareLock on relation 1249 of database 16401 after 1000.127 ms at character 92
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-2] sql_error_code = 00000 DETAIL: Process holding the lock: 9. Wait queue: 118948, 118950, 118708, 118818, 118886, 118961, 118960, 118806, 118963, 118959, 118881, 118887, 118878, 118896, 118964, 118965, 118945, 118949, 118946, 118743, 118966, 118947, 118967, 118968.
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-3] sql_error_code = 00000 STATEMENT: SELECT uc.id,
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-4] uc.some_id,
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-5] uc.utr_id,
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-6] utr.name
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-7] FROM usertable1 uc
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-8] INNER JOIN usertable2 utr
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-9] ON uc.utr_id = utr.id
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-10] WHERE uc.some_id = $1
Mar 27 12:06:37 ip-10-0-125-5 7dc68e48_fbd9_41d7_9ab1_65599036dd75[118946]: [9-11] ORDER BY name
Relation 1249 is pg_attribute and process 9 that was holding the lock was RecoveryWalAll process. I've confirmed that autovacuum had removed some pages from pg_attribute shortly before this, which happens somewhat regularly since this client runs a couple thousand REFERSH MATARIALIZED VIEW queries per day which look to cause inserts and deletes there so it having an exclusive lock on pg_attribute makes sense.
The question then is: Why would these user queries be waiting on an AccessShare lock on pg_attribute? Thus far we've been unable to recreate any transactions with the above query (and others) that show any pg_attribute locks. There is no ORM in play here and these queries are being sent as single query transactions via this Node.js postgres adapter: https://github.com/brianc/node-postgres which is pretty bare bones.
