To: vimal.gupta@gmail.com
cc: pgsql-performance@postgresql.org
Subject: Re: Hanging Query 
In-reply-to: <1136741670.479720.284080@f14g2000cwb.googlegroups.com> 
References: <1136741670.479720.284080@f14g2000cwb.googlegroups.com>
Comments: In-reply-to vimal.gupta@gmail.com
	message dated "08 Jan 2006 09:34:30 -0800"
Date: Sat, 14 Jan 2006 13:05:35 -0500
Message-ID: <20665.1137261935@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

vimal.gupta@gmail.com writes:
> We have to inserts a records(15000- 20000) into a table which also
> contains (15000-20000) records, then after insertion, we have to delete
> the records according to a business rule.
> Above process is taking place in a transaction and we are using batches
> of 128 to insert records.
> Everything works fine on QA environment but somehow after inserts,
> delete query hangs in production environment. Delete query has some
> joins with other table and a self join. There is no exception as we
> have done enough exception handling. It simply hangs with no trace in
> application logs.

> When I do "ps aux" , I see
> postgres  5294 41.3  2.4 270120 38092 pts/4  R    10:41  52:56
> postgres: nuuser nm 127.0.0.1 DELETE

That doesn't look to me like it's "hanging"; it's trying to process
some unreasonably long-running query.  If I were you I'd be taking
a closer look at that DELETE command.  It may contain an unconstrained
join (cross-product) or some such.  Try EXPLAINing the command and
look for unexpected table scans.

> Postgres 7.3.4 on Linux..

That's mighty ancient and has many known bugs.  Do yourself a favor
and update to some newer version --- at the very least, use the latest
7.3 branch release (we're up to 7.3.13 now).

			regards, tom lane