Re: PostgreSQL 7.4.10 hanging on delete

On Tue, 2005-12-20 at 16:37, Tom Lane wrote:
> Jonathan Parkin <jonathan(dot)parkin(at)legendplc(dot)com> writes:
> > I have a reasonably large, live, system-critical database. A perl
> > script on another machine connects and issues a sequence of commands in
> > a transaction, the last of which is a delete. The delete never returns
> > a response, and the connection never times out. The postgres process
> > handling the delete is in a scheduled state, but stracing it produces no
> > output at all.
>
> Sounds like it's stuck in a tight loop. Can you attach to the postgres
> process with gdb and get a stack trace to find out where the loop is?

I've attached two back traces. The first (process 23755) is unique
across the ten or so current hangign processes. All the rest follow the
same trace as the second (process 25376). I notice that first (process
23755) has the lowest-numbered PID of the hanging processes, suggesting
it began before the rest.

The "ignoring modified tuple in DELETE trigger" is reaching syslog and
being logged in /var/log/messages. This normally occurs during
processing. Syslog apears to be operating correctly, and HUPing it has
had no effect.

I should note that, other than the upgrade mentioned previously, no
changes have been made recently that I know of, and that the system has
previously appeared to be working correctly. It is not clear when this
issue began.

> If this is a specific row causing the issue, then I'd wonder about
> data corruption of some sort. It might be worth looking at the
> table with pg_filedump (from http://sources.redhat.com/rhdb/)

Thanks, I hadn't heard of that. I'll see what I can turn up with it.
Anything in particular I should be looking for?

Our current plan is to do a rebuild of the database tonight (UK time).
This would hopefully eliminate any such data corruption.

--
Best Regards

Jonathan Parkin
Developer

Legend Communications plc
T: 0844 390 2049
F: 0844 390 2001
E: jonathan(dot)parkin(at)legendplc(dot)com
W: http://www.legend.co.uk/

The information in this message is confidential and may be legally
privileged. Unauthorised disclosure, copying or distribution, either
whole or in part; or action taken in reliance on its content is
prohibited. If you are not the intended recipient, please notify Legend
Communications immediately.