Date: Mon, 4 Nov 2002 16:05:41 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Rudi Starcevic <rudi@oasis.net.au>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Referential integrity Freeze
In-Reply-To: <3DC7049E.7080007@oasis.net.au>
Message-ID: <20021104160421.C13046-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

>     Hello,
>
> I have two 4 table with referential constraint's that are hanging when I
> try to delete from them.
>
> I have a,
> users table, ( 30000 rows )
> suburbs table ( 16000 rows ),
> regions table ( 54 rows )and
> a bus_pc_idc table ( business type ) ( 30000 rows )
>
> Here is my integrity rules:
>
> create table bus_pc_idc (
> id serial,
> user_id        int4 REFERENCES users(user_id) ON DELETE CASCADE ON
> UPDATE CASCADE NOT NULL,
> sub_id         int4 REFERENCES suburbs(sub_id) ON DELETE CASCADE ON
> UPDATE CASCADE NOT NULL,
> idc_id         int4 REFERENCES idc(id) ON DELETE CASCADE ON UPDATE
> CASCADE NOT NULL,
> active         bool NOT NULL DEFAULT 'f'::bool
> );
>
> As the system is still being developed I want to 'empty' out the
> database from titme to time and rebuild it.
> I think that when I delete from the users table the delete should
> cascade through the bus_pc_idc table.
> However it's justing hanging when I delete all from the users table.
> When the database is hanging the CPU is 99% for the Postgres process.
>
> I can't see what's wrong. Perhaps I'm missing something in the
> referential integrity.

It shouldn't be hanging, however, do you have indexes on the *_id fields
in bus_pc_idc?  Otherwise it's going to be doing 1 sequence scan over
bus_pc_idc for each row in users.