From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Adam Fisher <black(at)cia(dot)com(dot)au> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: another foreign key question |
Date: | 2001-12-18 23:55:12 |
Message-ID: | 20011218154853.J64490-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Mitch Vincent | 2001-12-19 00:24:39 | Re: Way to use count() and LIMIT? |
Previous Message | Tom Lane | 2001-12-18 23:53:51 | Re: Too Many Open Files... NetBSD |