From: Mark Stosberg <mark@summersault.com>
To: pgsql-sql@postgresql.org
Subject: Avoiding RI failure with INSERT ... SELECT FROM
Date: Wed, 12 Jul 2006 09:48:37 -0400
Message-ID: <e92ulb$n0f$1@sea.gmane.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark@summersault.com
Subject: Re: Avoiding RI failure with INSERT ... SELECT FROM
In-Reply-To: <e92ulb$n0f$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox