> 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.
Hmmm. It seems to me that we'd need a sharelock on the referenced row both times. Is the below sequence missing something? process 1 process 1 locks process 2 process 2 locks update session; exclusive lock session row; update orders; exclusive lock orders row; share lock session row; update orders; exclusive lock requested orders row (blocks); share lock session row; update orders; exclusive lock orders row; share lock session row; (in this example, there is an fk orders.sessionid --> session.id ) It certainly seems that process 2 is acquiring exactly the same locks twice, since the referenced value is never being changed. So why does it need a share lock the 2nd time and not the first? Or is the sharelock in the first cycle being optimized away improperly? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers