Thread: Referential integrity Freeze
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. Thanks in advance Cheers Rudi.
> 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.
Stephan, Thanks for your reply. No I don't have indexes on the *_id fields in bus_pc_idc. Now that you point it out it make perfect sense why this query would take a while and give the impression it's hanging - it's all those seq scans. Thank you kindly, I really appreicate it. Regards Rudi.