Обсуждение: ALTER TABLE hangs

Поиск
Список
Период
Сортировка

ALTER TABLE hangs

От
Mike Baker
Дата:
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

Re: ALTER TABLE hangs

От
Stephan Szabo
Дата:
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.


Re: ALTER TABLE hangs

От
Mike Baker
Дата:
> > 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

Re: ALTER TABLE hangs

От
Stephan Szabo
Дата:
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.