pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrea <andrea@compesa.com.br>
To: Jonathan Parkin <jonathan.parkin@legendplc.com>
To: pgsql-admin@postgresql.org
Subject: Re: PostgreSQL 7.4.10 hanging on delete
Date: Wed, 21 Dec 2005 07:54:44 -0200
Message-ID: <00ce01c60614$91f7b340$ca01a8c0@DBA> (raw)
References: <1135095712.14411.1260.camel@jparkin.local>

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@legendplc.com>
To: "PostgreSQL Admin Mailing List" <pgsql-admin@postgresql.org>
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@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.
>
>
> ---------------------------(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.




view thread (8+ messages)  latest in thread

Message-ID: <00ce01c60614$91f7b340$ca01a8c0@DBA>
Permalink:  ../00ce01c60614$91f7b340$ca01a8c0@DBA/
Also on:    postgresql.org/message-id/00ce01c60614$91f7b340$ca01a8c0@DBA

 · 

reply

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: andrea@compesa.com.br, jonathan.parkin@legendplc.com
  Subject: Re: PostgreSQL 7.4.10 hanging on delete
  In-Reply-To: <00ce01c60614$91f7b340$ca01a8c0@DBA>
  References: <1135095712.14411.1260.camel@jparkin.local> <00ce01c60614$91f7b340$ca01a8c0@DBA>

* 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