Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

On 20-Aug.-2020 17:42, Michael Lewis wrote:

Can you share an explain analyze for the query that does the select for
update? I wouldn't assume that partition pruning is possible at all with
hash, and it would be interesting to see how it is finding those rows.

Well this got interesting  - the already moved error showed up:  Note, the actual process partitions are regular table partitions, these are not hashed.  Only the incoming and completed are hashed due to row counts at either end of the processing; in flight (where the issue shows up) is quite small:

[queuedb] # explain analyze select queueid,txobject,objectid,state from mq.queue where (state = 'tx_active' or state='tx_fail_retryable') and txobject = 'ticket' limit 250 for update skip locked;
ERROR:  40001: tuple to be locked was already moved to another partition due to concurrent update
LOCATION:  heapam_tuple_lock, heapam_handler.c:405
Time: 579.131 ms