To: pgsql-sql@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Avoiding RI failure with INSERT ... SELECT FROM
Date: Wed, 12 Jul 2006 09:48:37 -0400
Lines: 31
Message-ID: <e92ulb$n0f$1@sea.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5 (X11/20060405)


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