Kamil Kaczkowski <kamil@kamil.eisp.pl> writes:
>>> You're mistaken; it takes a row lock on each row it updates. I'm not
>>> sure why the two UPDATEs are visiting the same rows in different orders,
>>> but if they do the failure is certainly possible.
>>
>> One of them could be using an indexscan while the other is not. If the
>> heap is in reverse order compared to the scan, that would explain it.
>>
> In my case deadlock happens between two identical statements executed
> from different transactions and they have the same execution plan(index
> scan on one attribute - 'color' in schema I presented).
That's a bit hard to believe; once the rows are entered in the index
their relative order won't change anymore, so it's real hard to see how
two indexscans could visit them in different orders.
IIRC you said that these commands were being done inside plpgsql
functions, so it's possible that the planner is doing something
different with the parameterized plans than what you see in a simple
EXPLAIN with values already inserted. Still, it's odd that you might
get different plans in different executions of the same function.
I think there is some factor we're not seeing here. Is it possible that
one backend has a cached plan much older than the other one, and that
the planner's plan choice changed over time?
regards, tom lane