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.