Re: Stuck in "DELETE waiting"

Alexander Staubo <alex(at)purefiction(dot)net> writes:

> On Nov 23, 2006, at 16:27 , Tom Lane wrote:
>
> > Alexander Staubo <alex(at)purefiction(dot)net> writes:
> >> My application's connections against PostgreSQL 8.1.4 seem to get
> >> stuck in deletion operations.
> >> ...
> >> # select * from pg_locks where pid in (18198, 18204, 18208, 18214,
> >> 18216);
> >
> > You really need to show all of pg_locks, not a subset. In this
> > example
> > it appears that 18204 and 18198 are waiting for transactions that are
> > not part of this subset, so it's hard to say what's happening.
>
> See below. Seems 18204 is waiting for a process that is "idle in
> transaction"; makes me wish that PostgreSQL could export more
> information about the age of in-progress transactions. I am turning
> on logging so I can determine what a future hanging process is doing.

Have a look at the query_start field in pg_stat_activity for the
process holding locks that's causing backlog.

If you've got an old idle transaction that did update or delete on
some rows, perhaps terminate these backends after some grace period.

Clever joins over pg_locks and pg_stat_activity can show about all
that's needed generally for investigating MVCC problems.

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant