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-C ONFIG-LOGGING Will do. Ive played with these settings before, If I knew what tables the processes were simultaneously trying to update, I think I could fix it pretty quick. I know which application is locking it, I just cant figure out why or where, this particular program is a beast its updating at least 10 tables with the push of 1 button. The funny thing is that the people who use the system are prone to do each others work at the same time because they don't know that the other has done it. But the deadlocks typically occur between people in other locations (Dallas bumps heads with Memphis) which is weird because they shouldn't even be looking at each others data. Im about to replace the offending software so hopefully the re-written version wont have this problem but a lot of the code was salvaged for use in the new version so it may still suffer. I use the user_write_lock_oid()(or something like that) to keep people from accessing the same record. > 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? I can psql in a get a prompt but any statement will just freeze, regardless of the table. Im not sure if a SELECT now() will freeze. I wish I had gotten this email earlier it hung about 5 minutes before I received this. Yes ive tried looking at PQtrace() data but there are so many connections/transactions going on its hard to tell what's what. -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
