EG 2.11 Merging records will time out with large amounts of holds

[Blake GH]

EG 2.11

It turns out that the trigger:

reporter_hold_request_record_trigger

which fires when action.hold_request rows are updated, will take a long time to update the corresponding target in reporter.hold_request_record. This is a new table in 2.11. It was a view before.

explain analyze update reporter.hold_request_record
set hold_type='T', target=1932036,bib_record=1932036
where id=313288;
                                                                            QUERY PLAN

 Update on hold_request_record (cost=0.43..4.45 rows=1 width=10) (actual time=13653.487..13653.487 rows=0 loops=1)
   -> Index Scan using reporter_hold_request_record_pkey_idx on hold_request_record (cost=0.43..4.45 rows=1 width=10) (actual time=0.320..0.324 rows=1 loops=1)
         Index Cond: (id = 313288)
 Planning time: 0.904 ms
 Execution time: 13653.535 ms
(5 rows)

13 seconds to update a row in that table!

I don't really know what the solution is.

[Mike Rylander (mrylander)]

The analyze output suggests that either the reporter.hold_request_record table (or the pkey index) is terribly bloated, or something is blocking the update. Is this happening on every hold target change?

[Blake GH (bmagic)]

It is happening on every hold change that I have tested.