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