Re: another foreign key question

On Wed, 19 Dec 2001, Adam Fisher wrote:

> I am trying to add a foregin key constraint to a non-indexed field of a
> table that has 212195 rows. It is referencing the primary key of a table
> that has 89060 rows. The schemas are below. The command I used is:
>
> alter table invhistory
> add constraint fk_invhist_invid_inventory
> foreign key ("invid") references "inventory" ("invid")
>
> The proceedure seems to be working, however it's now been 17 hours and, from
> what I can tell, it's still going. Does this hint at a problem? If not, is
> there any way I could speed up subsequent foreign key operations as I have
> quite a few more that I want to add to my database on tables of similar
> size?

Hmm, maybe, maybe not. It's doing a copy of the trigger for each row which
will take a while, although I'm surprised it's that long.

> If I indexed the field before adding the foreign key constraint, would that
> speed things up? Also, is there anyway that I can tell if it's still

AFAIK Not for the adding of the constraint.  It should speed up the
update/delete checks on inventory, however.

> working, as opposed to being stalled, locked or anything? (Shouldn't be
> locked, as no-one else has access to this box and I only have one instance
> of psql running, but for my own knowledge...)

I'd suggest checking ps or the backend log if you've got debug turned up.