From: pgsql-general-owner@xxxxxxxxxxxxxx on behalf of Michael Fuhr
Sent: Mon 6/12/2006 6:18 PM
To: surabhi.ahuja
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] delete seems to be getting blocked
***********************
Your mail has been scanned by
InterScan VirusWall.
***********-***********
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
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list
archives?
http://archives.postgresql.org
