From: Jonathan Parkin <jonathan.parkin@legendplc.com>
To: PostgreSQL Admin Mailing List <pgsql-admin@postgresql.org>
Subject: PostgreSQL 7.4.10 hanging on delete
Date: 20 Dec 2005 16:21:53 +0000
Message-ID: <1135095712.14411.1260.camel@jparkin.local> (raw)
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.parkin@legendplc.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.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jonathan.parkin@legendplc.com
Subject: Re: PostgreSQL 7.4.10 hanging on delete
In-Reply-To: <1135095712.14411.1260.camel@jparkin.local>
References: <1135095712.14411.1260.camel@jparkin.local>
* 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