Joel Jacobson <joel@gluefinance.com> writes:
> I don't understand exactly why this deadlock occurs, but the one thing I
> cannot understand is why process 2 is not allowed to update the same row,
> which it has already updated in the same transaction.
It *is* allowed to, and in fact has already done so. The problem is
that it now needs a sharelock on the referenced row in order to ensure
that the FK constraint remains satisfied, ie, nobody deletes the
referenced row before we commit the update. In the general case where
the referencing row is new (or has a new FK value) in the current
transaction, such a lock is necessary for correctness. Your case would
work if we could optimize away the FK check, but with only a limited
view of what's happened in the current transaction, it's not always
possible to optimize away the check.
regards, tom lane