Forum OpenACS Development: Re: aolserver/postgres hang
select relname,pid,mode,granted from pg_locks inner join pg_class on pg_class.oid = relation;
And there seemed to be an exclusive lock held by one thread on apm_packages (probably something in the APM package uninstall procs), and another thread trying to acquire a lock (cannot remember what type) on that table, causing a deadlock. This was on a system upgraded from 4.6.3 to 5.0.0, using PG 7.3 (previously upgraded from 7.2), so it's fair to say it was a bit of a shot system.
To be honest, I couldn't ever be bothered to figure out how to resolve it, I just ran the drop scripts by hand. I think (and this is a fairly uneducated guess) that it could have something to do with how PG managed foreign key constraints in 7.2. I think it used to do it with a trigger on the table being referred (in this case apm_packages), and so when dropping the referring table you need to acquire a lock (possibly exclusive, think the PG manuals have something to say about that) on the referred table to delete the trigger. Hence the deadlock.