Thank you Jeff and others for the responses.
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.
Thank you!
On Mon, Jul 22, 2013 at 12:48 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
On Sun, Jul 21, 2013 at 9:15 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:It still behaves this way in 9.4dev.
> hello
>
> It can be artefact of RI implementation.
>
> see http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-for-key-share-and-for-no-key-update/
>
> Try to 9.3, please, where RI uses more gently locks
I don't think so. While PostgreSQL of course strives for maximum
>>
>> On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube@xxxxxxxxx> wrote:
>>>
>>>
>>> Thank you for the responses. Is it a bug?
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.
Cheers,
Jeff
