Skip site navigation (1) Skip section navigation (2)

PostgreSQL 7.4.10 hanging on delete

From: Jonathan Parkin <jonathan(dot)parkin(at)legendplc(dot)com>
To: PostgreSQL Admin Mailing List <pgsql-admin(at)postgresql(dot)org>
Subject: PostgreSQL 7.4.10 hanging on delete
Date: 2005-12-20 16:21:53
Message-ID: 1135095712.14411.1260.camel@jparkin.local (view raw or flat)
Thread:
Lists: pgsql-admin
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.

The table contains entries for a "parent" and it's "children".
Grandchildren and other descendents never exist in the table.  A before
delete trigger removes the children when the parent is deleted and the
delete is always issued for a parent.  Children have exactly one parent.

By tcpdumping the SQL connection the system consistently hangs on the
same delete for one of a set of rows. As the delete never commits the
transaction is never finished.   Other rows (parents and children) can
be deleted without issue.

Killing the perl process at the other end, and subsequently the
connection timing out on the perl-server end does not stop the postgres
process handling the delete, and it continues to produce no output when
straced.

Restarting the server, doing a VACUUM FULL ANALYZE and a REINDEX of all
tables in the database (including system tables) has no effect.

This was first noticed using PostgreSQL 7.4.7.  Upgrading to 7.4.10 has
not helped. Upgrading to 8.x may not be an option due to the systems
connecting to the database (this is being investigated).

My next logical step is to stop access to the database, take a dump of
it, remove it, and rebuild it.  Can anyone think of a reason why I
should not do this?

Can anyone think of anything else I should try?

I welcome all suggestions, no matter how obvious they may appear.

Thanks,

Jonathan

-- 
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.


Responses

pgsql-admin by date

Next:From: Donald FraserDate: 2005-12-20 16:34:43
Subject: Re: WITH SYSID feature dropped
Previous:From: Tom LaneDate: 2005-12-20 15:57:04
Subject: Re: WITH SYSID feature dropped

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group