When the delete statements hangs, look into the pg_locks table, to check if the delete statement is waiting because it has conflict with rows locked by other transactions. On 20 Dec 2005 16:21:53 +0000, Jonathan Parkin <jonathan.parkin@xxxxxxxxxxxxx> wrote: > 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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Regards Pandu
