I'm wondering why insert doesn't temporarily ignore foreign key
constraints until after the rows are inserted and then check referential
integrity. If the inserted rows are invalid, then simply rollback the
transaction.
This might make more sense with an example:
I was rebuilding some tables to use foreign keys and found that
inserting several million rows into a new table with a foreign key
constraint is prohibitively slow (I killed it after > 24 hours). My
guess is that the trigger is called repeatedly as each row is inserted.
Inserting the rows into the new table without the key constraint takes
several minutes. I can then add the constraint to the new table using
"alter table ... add constraint". Adding the constraint takes some time
as the column with the foreign key constraint must be checked for
referential integrity. However, the sum of 1) inserting the rows and 2)
adding the constraint is much less than inserting the rows while the
constraint is in force. It seems that these could be equal if you
didn't trigger the constraint after each row insertion, but instead
waited until all the rows were inserted (remember this is all within a
single insert command and therefore within a single transaction) to
check referential integrity. Does this make sense?
Tim
--
Timothy H. Keitt
National Center for Ecological Analysis and Synthesis
735 State Street, Suite 300, Santa Barbara, CA 93101
Phone: 805-892-2519, FAX: 805-892-2510
http://www.nceas.ucsb.edu/~keitt/
