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.