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 по дате отправления: