Hi, I dont´t know if i understand exactly your problem, but ... When i tried to delete all of the rows of a table , it seems to never end. So i created an index to each foreing key and the command works very fast. Good Luck! Andréa Pereira Brasil-Recife ----- Original Message ----- From: "Jonathan Parkin" <jonathan.parkin@xxxxxxxxxxxxx> To: "PostgreSQL Admin Mailing List" <pgsql-admin@xxxxxxxxxxxxxx> Sent: Tuesday, December 20, 2005 2:21 PM Subject: [ADMIN] PostgreSQL 7.4.10 hanging on delete > 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 > > -- > Esta mensagem foi verificada pelo sistema de antivírus e > acredita-se estar livre de perigo. > > > > -- > Internal Virus Database is out-of-date. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.6.5 - Release Date: 26/12/2004 > -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.5 - Release Date: 26/12/2004 -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo.
