(NOTE: reposting this for the *fifth* time because my previous messages didn't go through). Hi all, We have experienced a really weird problem with postgresql yesterday. When I was called in to take a look, all the non-superuser connections were used up and they were all in a waiting state (SELECT waiting, UPDATE waiting, etc.). I couldn't figure out what the problem is, but I saved the ps, pg_stat_activity, and pg_locks state at the time (attached as condor_db_stats.txt). BTW, when looking at the queries in pg_stat_activity, postgresql cuts them off so you can't see the entire string. Is there any way to prevent that, or at least increase the character limit? We restarted postgresql server and the problem reappeared a few minutes later. I saved that state also (condor_db_stats2.txt). At that point, after we restarted postgresql (again), I ran VACUUM on the entire database and did a few more things that seem to have solved the problem (see below). This particular database is essentially just one flat table (level) with a few small supporting tables. Only the level table is heavily used. (table definition is attached as table.txt). I noticed that one of the indexes (level_owner_index) was a hash index. I remembered what postgresql manual says about hash indexes and concurrency (http://www.postgresql.org/docs/7.4/interactive/locking-indexes.html) and, after VACUUM finished, replaced the hash index with a btree. I then did a REINDEX of the level table and ANALYZE. This seems to have solved the problem -- at least as of this morning we still have not seen any deadlocks. My question is, what could have caused this to happen? Can anyone explain this paragraph from the manual: "Share/exclusive page-level locks are used for read/write access. Locks are released after the page is processed. Page-level locks provide better concurrency than index-level ones but are liable to deadlocks." Any other pointers to help me figure out what went wrong and how to fix it? thanks, Eugene WTF? My message doesn't appear. Trying again without attachments or inline text. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
