Logging lock information

At this point you'd probably like a more automatic way to log locking problems than to just watch pg_locks all day. There are a pair of database parameters that allow doing that, as a by-product of sorts of how the database defends itself against deadlock problems.

Deadlocks

Consider the following sequence:

1. Process 1 acquires a lock on object A
2. Process 2 acquires a lock on object B.
3. Process 2 tries to acquire a lock on object A. It's now waiting for Process 1 to finish.
4. Process 1 tries to acquire a lock on object B.

At this point the two processes are now in what's called deadlock: each is trying to obtain a lock on something owned by the other. They both will wait on each other forever if left in this state. One of them has to give up and release the locks they already have.

To search for this situation and resolve it, PostgreSQL doesn't wait forever for a lock. Instead, it only waits an amount of time determined by the deadlock_timeout...