From: Tom Lane <tgl@sss.pgh.pa.us>
To: vimal.gupta@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Hanging Query
Date: Sat, 14 Jan 2006 13:05:35 -0500
Message-ID: <20665.1137261935@sss.pgh.pa.us> (raw)
In-Reply-To: <1136741670.479720.284080@f14g2000cwb.googlegroups.com>
References: <1136741670.479720.284080@f14g2000cwb.googlegroups.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, vimal.gupta@gmail.com
Subject: Re: Hanging Query
In-Reply-To: <20665.1137261935@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox