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
That is super curious. I hope that someone will jump in with an explanation or theory on this.
I still wonder why the move between partitions is needed though if the work is either done (failed or successful) or not done... not started, retry needed or in progress... it doesn't matter. It needs to get picked up by the next process if it isn't already row locked.
