Thread: Hanging Query

Hanging Query

From
vimal.gupta@gmail.com
Date:
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

Postgres 7.3.4 on Linux..

Thanks for any help..

Vimal


Re: Hanging Query

From
Tom Lane
Date:
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