Re: locking issue on simple selects?

On 15 September 2010 21:28, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> There are some useful examples of lock views on the wiki:
>
> http://wiki.postgresql.org/wiki/Lock_Monitoring
> http://wiki.postgresql.org/wiki/Lock_dependency_information
> http://wiki.postgresql.org/wiki/Find_Locks

Thanks. I think those pages probably should be merged ... hmm ... if
I manage to solve my locking issues I should probably try and
contribute to the wiki.

Reading the wiki pages, for me it boils down to three things:

1) the current query we're logging seems good enough except that we
should do an outer join except for inner join towards pg_class, so
I've asked our sysadm to fix it.

2) the middle query on http://wiki.postgresql.org/wiki/Lock_Monitoring
seems very useful, and I've asked our sysadm to set up logging of this
one as well.

3) That log_lock_waits config option that you and Brad points to seems
very useful, so I've asked our sysadm to enable it.

I also discovered that there is an attribute pg_stat_activity.waiting
- I suppose it is 't' if a query is waiting for a lock? It seems
quite useful ...

> reducing deadlock_timeout.

It's set to one second, and some of the jams we have been experiencing
has lasted for several minutes. I also think it should say in the pg
log if there is a deadlock situation? I grepped for "deadlock" in the
logs without finding anything.

Well, we'll improve the logging, and wait for the next "jam" to occur
... and I'll make sure to post an update if/when I figure out
something.