Deadlock bug - Mailing list pgsql-hackers

We can see it obtains two RowExclusiveLocks on relations "b_pkey" and "b".

I don't know if this is expected, since the row in B references the row in A being updated by process 1.

Because of the foreign key, shouldn't some kind of share lock on A be obtained by process 2, or some other kind of lock?

3. Process 1 tries to execute "UPDATE B SET Col2 = 1 WHERE BID = 2;" and will of course have to wait, because process 2 already has a RowExclusiveLock on the same row in table B.

Process 1 is now waiting...

4. Now, in the other SQL prompt (process 2), we take a look at the vLocks view.

Unexpected observations:

a) both processes have been granted a RowExclusiveLock on table B. How can both be granted a RowExclusiveLock on the same table? Since the table only contains one row, it must be a lock on the same row, which should be impossible, right?

b) process 1 (which is currently waiting) has been granted a lock of type "tuple", page 0, tuple 1, mode "ExclusiveLock" on table B. I don't know what a "tuple" lock is, but what surprises me is process 1 being granted the lock, and not process 2 (since process 2 updated B before 1).

Now, while process 1 is waiting, let's execute the same query in process 2:

5. Process 2 tries to execute "UPDATE B SET Col2 = 1 WHERE BID = 2;" which is exactly the same query as in step 2 above.

Since process 2 already hold a granted RowExclusiveLock on the row in table B it tries to update, I think this query should be executed instantly without any problem. Instead, it causes a deadlock in process 2, allowing process 1 to commit. Very strange.

Could this have any other explanation than a bug (or perhaps feature) in postgres?

--