The following bug has been logged online:

Bug reference: 2710
Logged by: Jason Palmer
Email address: jpalmer (AT) dlife (DOT) com
PostgreSQL version: 8.1
Operating system: Redhat enterprise 4
Description: Intermittent hangs on sequence generation
Details:

We can't exactly pinpoint the cause for this so I submit the problem to the
postgres community. We are currently running Postgres 8.1. Postgres is our
main database engine and several of our applications connect to it including
our java stack (tomkat 5.0.28 running java 1.4.2 struts) and Ruby on Rails
(Rails 1.6 w/ ActiveRecord). What we experience is bizarre. We can always
create tables with no sequences. But occasionally the database will hang
when we attempt to create a table with a sequence or foreign key. We can
cancel the operation and when it "hangs" the database will still be able to
insert/update/delete/select records so it's not totally down. But we are
required to reboot the database before we can successfully run the query.
Again, this does not happen all the time, but we've found that it happens
quite a lot.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Jason Palmer" <jpalmer (AT) dlife (DOT) com> writes:
Yup, that's pretty bizarre. Is the backend consuming CPU (or I/O) in
this state, or is it just waiting? If the latter, it's presumably
blocked on some lock, and you need to find out what. First try looking
in pg_locks to see if it's blocked on a user-level lock. If you don't
see anything there, then it's blocked on some internal lock (LWLock or
spinlock) --- you'll need to attach to the backend process with gdb and
get a stack trace to get some clue what's up.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match