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