Обсуждение: ALTER TABLE hangs
Hi. I am in the process of doing some data migration. I have had no trouble moving my data, but am having problems creating a foreign key. When I run the following command, psql hangs (well, not totally sure if it is hung). alter table t_release_component add constraint t_release_component_fk3 foreign key(parent_release_component_id) references t_release_component; The postmaster process maxes out the processor and then just sits there. The t_release_component table has 380,000 records. I am running PostgreSQL 7.1.2. Here is the output from the server log. There are many threads about this output, but I did not find any resolution. DEBUG: MoveOfflineLogs: remove 0000000100000092 DEBUG: MoveOfflineLogs: remove 0000000100000094 DEBUG: MoveOfflineLogs: remove 0000000100000095 DEBUG: MoveOfflineLogs: remove 0000000100000096 DEBUG: MoveOfflineLogs: remove 0000000100000097 DEBUG: MoveOfflineLogs: remove 0000000100000098 DEBUG: MoveOfflineLogs: remove 0000000100000099 DEBUG: MoveOfflineLogs: remove 000000010000009A DEBUG: MoveOfflineLogs: remove 000000010000009C DEBUG: MoveOfflineLogs: remove 000000010000009B Thanks for the help. Mike __________________________________________________ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com
On Mon, 29 Apr 2002, Mike Baker wrote: > Hi. > > I am in the process of doing some data migration. I > have had no trouble moving my data, but am having > problems creating a foreign key. > > When I run the following command, psql hangs (well, > not totally sure if it is hung). It probably isn't hung, but it's going to go through every row of the table to make sure the constraint holds successfully which is going to result in 380,000 queries again t_release_component. I haven't had time recently to do any of the fk stuff I've been meaning to do, but this is optimizable for most cases, but it hasn't been done.
> > Hi. > > > > I am in the process of doing some data migration. > I > > have had no trouble moving my data, but am having > > problems creating a foreign key. > > > > When I run the following command, psql hangs > (well, > > not totally sure if it is hung). > > It probably isn't hung, but it's going to go through > every row of the table to make sure the constraint > holds > successfully which is going to result in 380,000 > queries again t_release_component. I haven't had > time > recently to do any of the fk stuff I've been meaning > to > do, but this is optimizable for most cases, but it > hasn't > been done. This is true, the process was not hung, however, it took 24 hrs to create this one foreign key. Is there any other way to create the foriegn key, as the next time i go through this process the table may have up to 1 million records. would CREATE TRIGGER be faster? i cannot have my production database down for a week while i create a foreign key. thanks for your help. mike baker __________________________________________________ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com
On Thu, 2 May 2002, Mike Baker wrote: > > > > Hi. > > > > > > I am in the process of doing some data migration. > > I > > > have had no trouble moving my data, but am having > > > problems creating a foreign key. > > > > > > When I run the following command, psql hangs > > (well, > > > not totally sure if it is hung). > > > > It probably isn't hung, but it's going to go through > > every row of the table to make sure the constraint > > holds > > successfully which is going to result in 380,000 > > queries again t_release_component. I haven't had > > time > > recently to do any of the fk stuff I've been meaning > > to > > do, but this is optimizable for most cases, but it > > hasn't > > been done. > > This is true, the process was not hung, however, it > took 24 hrs to create this one foreign key. > > Is there any other way to create the foriegn key, as > the next time i go through this process the table may > have up to 1 million records. > > would CREATE TRIGGER be faster? i cannot have my Doing the set of CREATE CONSTRAINT TRIGGERS will be faster (although it'll assume that the data meets the constraint). You can probably get the necessary lines by making another database with the same table names and the constraint.