Re: Deadlock

[Please be careful with quoting -- you quoted some parts of my reply
but not others so it looks like you wrote those sections. I've
fixed that in this reply.]

On Thu, Sep 15, 2005 at 03:20:02PM -0500, Bart McFarling wrote:
> > > 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.

What kinds of statements did you try? Simple SELECTs? Try querying
some other table, like pg_class or a test table that you created
just for that purpose. If those queries work then the problem is
probably with locking. You mentioned that you were using
user_write_lock_oid() but I don't think that should acquire a strong
enough lock to block all other queries to a table. Are you doing
anything with LOCK? What about database maintenance activities
like VACUUM FULL or CLUSTER?

The next time the "freeze" happens, try running the following query:

SELECT relation::regclass, * FROM pg_locks;

If you have have stats_command_string enabled then it might also be
useful to run this query:

SELECT * FROM pg_stat_activity;

Look for locks that haven't been granted, then look for the process
that holds locks on that table and see what that process is doing.
If that doesn't help track down the problem then you might need to
log every statement so you can see exactly who is doing what.

--
Michael Fuhr