Re: Deadlock

On Thu, Sep 15, 2005 at 12:32:05PM -0500, Bart McFarling wrote:
> Is there some kind of log, table or something that I could get more
> information about a deadlock situation that is occurring in my database? I
> just get a transaction number and a process id, which is useless to me
> because my application terminates on any errors from the database?

See "Error Reporting and Logging" in the "Server Run-time Environment"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING

> It occurs infrequently (about once a day) and I have no idea how to
> track it down.

You could log all queries or use log_min_error_statement to log
only queries that result in an error. Typical causes of deadlock
are multiple transactions updating the same records in different
orders, and doing inserts/updates that reference the same foreign
keys in different orders (in released versions of PostgreSQL,
referential integrity checks do a SELECT FOR UPDATE on the referenced
key to ensure that it doesn't change while the transaction is still
active; in 8.1 such locks will be acquired with SELECT FOR SHARE,
which should reduce the incidence of deadlock).

> Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> could this be a deadlock situation? There is nothing in the log about
> deadlock or anything else when it freezes (this happens about once every
> month or so)

What are the symptoms of this "freeze"? Do only some queries block?
Do all queries block, even queries such as "SELECT now()"? Are you
able to connect to the database at all? If you can connect, have
you examined pg_locks? If you can't connect, have you done a process
trace or used a debugger to see what the database is doing?

--
Michael Fuhr