pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReferential integrity Freeze
3+ messages / 2 participants
[nested] [flat]
* Referential integrity Freeze
@ 2002-11-04 23:37 Rudi Starcevic <rudi@oasis.net.au>
2002-11-05 00:05 ` Re: Referential integrity Freeze Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rudi Starcevic @ 2002-11-04 23:37 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Referential integrity Freeze
2002-11-04 23:37 Referential integrity Freeze Rudi Starcevic <rudi@oasis.net.au>
@ 2002-11-05 00:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-05 00:16 ` Re: Referential integrity Freeze Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2002-11-05 00:05 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Referential integrity Freeze
2002-11-04 23:37 Referential integrity Freeze Rudi Starcevic <rudi@oasis.net.au>
2002-11-05 00:05 ` Re: Referential integrity Freeze Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-11-05 00:16 ` Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 0 replies; 3+ messages in thread
From: Rudi Starcevic @ 2002-11-05 00:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-05 00:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-04 23:37 Referential integrity Freeze Rudi Starcevic <rudi@oasis.net.au>
2002-11-05 00:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-05 00:16 ` Rudi Starcevic <rudi@oasis.net.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox