Date: Sat, 12 Oct 2002 07:57:39 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Bhuvan A <bhuvansql@myrealbox.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: DELETE command is getting blocked
In-Reply-To: <Pine.LNX.4.44.0210121630001.3869-100000@Bhuvan.bksys.co.in>
Message-ID: <20021012075552.C45930-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Sat, 12 Oct 2002, Bhuvan A wrote:

> I am using 7.2.
>
> I have problem in deleting records from a particular table in my database.
> Say, if the DELETE command results deleting 10 records, the command is
> blocked at the end and is not returning. FYI, that table donot have any
> triggers/constraints/rules on delete. I have checked whether its due to
> the same table access by some other client, but it is not so. No other
> process is accessing this table by that time.
>
> Infact i am able to repeat the problem. To make sure, i wrote a plpgsql
> and tried deleting these 10 records one by one in a for loop. There too it
> is blocked for infinite time, after deleting the last record. what would
> be the problem? Is the table corrupted?
>
> FYI, table has a primary key which is referred from different tables.
> Anyhow i have deleted records from foreign key tables, before deleting
> from the primary key table.

If it's referred from other tables, then it does have triggers on delete.
It's probably the constraint trigger that's blocking, but that should only
occur if something was modifying one of the foreign key tables at the
same time.  You might want to turn on query logging to get a good idea
of what's going on.