Time-out inserting into BugNotificationRecipient

[Gavin Panella]

In OOPS-1782H1745, the insert taking nearly 17 seconds (before being cancelled) should be near instantaneous. I suspected a lock or something like that, but it was a while before Chex and I were able to query for locks (using http://pastebin.ubuntu.com/533531/) and nothing suspicious was apparent (https://pastebin.canonical.com/39866/). The user - smoser - reported later that the problem had gone away.

[Deryck Hodge (deryck)]

See also OOPS-1782O1595 where this appeared in bug nominations.

[Данило Шеган (danilo)]

I wonder if we are increasing the chances of locks with a single insert statement: >30 Person rows seem to get locked FOR SHARE in these cases as they are foreign key references (SHARE locks block any modifications on the target table). It wouldn't be hard to switch this to multiple insert statements (eg. 5 per statement, or 1 per statement) to decrease the chances of locks causing timeouts, but ideally, we'd find what is modifying these person records at the same time, and if we can move the data being modified to something like PersonSettings instead.

[Данило Шеган (danilo)]

Also, I believe it would be very nice to log more details about what is starving out the connection in this case: i.e. list all the locks for the affected table and what holds them. Not sure how easy that is (if at all possible).

[Robert Collins (lifeless)]

""I wonder if we are increasing the chances of locks with a single insert statement: >30 Person rows seem to get locked FOR SHARE in these cases as ""

No, because once taken, for share locks last the duration of the transaction - they are preventing FK references mutating under the transactions feet.

You can get conflicting locks with this, or a variation on it:

 with interesting_pids as (select wanting.pid as wpid, held.pid as
hpid from pg_locks wanting, pg_locks held where ((wanting.database,
wanting.relation) = (held.database, held.relation) or
wanting.transactionid=held.transactionid) and not wanting.granted and
held.granted and wanting.pid!=held.pid) select * from pg_locks,
pg_stat_activity, interesting_pids where (pid=wpid or pid=hpid) and
pid=procpid;

[William Grant (wgrant)]

Fixed by FOR KEY SHARE in PostgreSQL 9.3.