This is mostly for my benefit, since I'm debugging nasty locking problems at the moment, but here's the query you run to list open locks on Postgresql, joined to tell you the name of the table the lock is against.
select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;
This gives you something like the following (some rows elided):
As you can see here, process 27423 has a shared lock on the os_user table, probably due to some kind of read. Then process 27471 has asked for an exclusive lock, and since the former process is still holding the shared lock, that lock hasn't yet been granted.
The fact that this is causing a deadlock at a higher level, and 27471 will just wait indefinitely for a lock that isn't going to arrive isn't really the database's fault :)