Re: PG connections going to 'waiting'

On Sat, Sep 05, 2009 at 01:08:30PM -0400, Alan McKay wrote:
> > pg_locks?  Somebody taking exclusive lock on a widely-used table might
> > explain that.
>
> OK, in theory we could do the following, no?
>
> Use our PITR logs to restore a tertiary system to the point when we
> were having the problem (we have a pretty wide 2 or 3 hour window to
> hit), then query the pg_locks table on that system?

I'm guessing this wouldn't work. pg_locks isn't a table, it's a view, based on
the pg_lock_status function. I don't know how that function works, but I'm
guessing it gathers lock data from shared memory or some such, and would only
contain the same values it did at the time of the crash if you had the same
queries and autovacuum processes currently in flight. Autovacuum presumably
didn't contribute to the locking in meaningful ways, so if you have logs of
the queries you were running at the time, you might be able to get the same or
a similar condition by executing those queries against the restored system.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com