Avoiding RI failure with INSERT ... SELECT FROM

From: Mark Stosberg <mark(at)summersault(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Avoiding RI failure with INSERT ... SELECT FROM
Date: 2006-07-12 13:48:37
Message-ID: e92ulb$n0f$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 where item_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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Scott Marlowe 2006-07-12 18:13:18 Re: How to find entries missing in 2nd table?
Previous Message Jaime Casanova 2006-07-12 13:12:21 Re: Unexpected SQL error for UPDATE