Avoiding RI failure with INSERT ... SELECT FROM

Поиск
Список
Период
Сортировка
От Mark Stosberg
Тема Avoiding RI failure with INSERT ... SELECT FROM
Дата
Msg-id e92ulb$n0f$1@sea.gmane.org
обсуждение исходный текст
Ответы Re: Avoiding RI failure with INSERT ... SELECT FROM
INSERT ... SELECT FROM .... FOR UPDATE?
Список pgsql-sql
Hello! I got an unexpected SQL failure last night, and want to see how
to prevent it in the future. This is with 8.1.3.

I have a couple of insert statements that run in a transaction block,
initializing rows that will be used the next day:

INSERT into item_hit_log   SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0        FROM items where
item_state= 'available';
 

INSERT into item_view_log        SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0            FROM items
whereitem_state = 'available';
 

The "items" table has a few hundred thousand rows in it, so this takes a 
bit a run.

The error we got last night was:
  ERROR:  insert or update on table "item_hit_log" violates foreign key 
constraint "item_id_fk"  DETAIL:  Key (item_id)=(451226) is not present in table "items".

Re-running the transaction block a few minutes later worked.

What's an alternate design that avoids this possible error?

Thanks!
    Mark



В списке pgsql-sql по дате отправления:

Предыдущее
От: "Jaime Casanova"
Дата:
Сообщение: Re: Unexpected SQL error for UPDATE
Следующее
От: aurora
Дата:
Сообщение: Re: Unexpected SQL error for UPDATE