One concern that I have is that even cases where there is no deadlock it is still acquiring stronger locks than necessary. I only discovered it because of the deadlock issue but I presume that there are many cases where it is acquiring a lock on the foreign table and really doesn't need to. That would seem to lead to higher lock contention in general which although it may not cause a deadlock in every case, does affect the overall performance.
In my opinion this should be a case where it ought not to acquire any locks on the foreign table at all. It's not as though the columns relevant to the foreign key reference have changed.
Anyway, at least it's good to understand it more. I'll have to find some way to work around this in my application.
>> >> On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube@gmail.com> wrote: >>> >>> >>> Thank you for the responses. Is it a bug?
I don't think so. While PostgreSQL of course strives for maximum concurrency, it makes no guarantee that it uses the weakest theoretically possible locking in all possible cases. But it is kind of unfortunate that updating the same row twice causes a lock escalation when it is not obvious it should do so, because as you found that makes avoiding deadlocks quite difficult.
I'm rather surprised it doesn't block at the first update of the 2nd session, rather than waiting for the 2nd update of that session.
Anyway, when the 2nd session re-updates the same row in the same transaction, it uses a 'multixact' to record this. Doing that apparently defeats some locking optimization that takes place under simpler cases.
Sorry, that probably isn't the definitive answer you were hoping for.