Re: FKs and deadlocks

"Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
> One more question: i'm surprised there are so many ExclusiveLocks when displaying pg_lock:

> 6514392 14385 ExclusiveLock t
> 6495858 11439 ExclusiveLock t
> ...etc...

Those are the transaction ID locks.

> I found in the documentation "EXCLUSIVE: This lock mode is not automatically acquired by any PostgreSQL command."

With respect to *tables* this is a true statement. For *transactions*,
the owning backend takes ExclusiveLock on its transaction ID, and any
other backend that has to wait for that transaction tries to take
ShareLock on the ID, which will block it until the ExclusiveLock is
released at transaction end. We don't use any other lock modes with
transaction IDs. (This is documented in the pg_locks view's documentation.)

regards, tom lane