Re: delete seems to be getting blocked

On Mon, Jun 12, 2006 at 12:54:15PM +0530, surabhi.ahuja wrote:
> i have four tables in my database
> TAB1, has one primary key T1
>
> TAB2 , has 2 fields, one is the primary ley T2 and the other one
> is the foreign key T1(from TAB1)
>
> TAB3 also has 2 fields, one is the primary ley T3 and the other
> is the foreign key T2(from TAB2)
>
> TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3)

Do the foreign key columns in TAB2, TAB3, and TAB4 have indexes?
How many rows do the tables have?

> the disk is 100% full.
>
> i open psql <datbase_name> and do
> delete from TAB1
>
> nothing seems to be happening for a long time, although when i do
> top, it shows postgres taking 99%.

For each record you delete in TAB1 the database must search TAB2
to check for referential integrity violations or cascading operations
(ON DELETE CASCADE, ON DELETE SET NULL, etc.). If the foreign key
column in TAB2 doesn't have an index then each row deleted from
TAB1 will result in a sequential scan on TAB2; likewise with TAB3
if you modify TAB2 and with TAB4 if you modify TAB3.

If the tables are large then make sure you have indexes on the
foreign key columns. If you create indexes then you might need to
start a new session due to plan caching.

--
Michael Fuhr