pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAvoiding RI failure with INSERT ... SELECT FROM
2+ messages / 2 participants
[nested] [flat]
* Avoiding RI failure with INSERT ... SELECT FROM
@ 2006-07-12 13:48 Mark Stosberg <mark@summersault.com>
2006-07-12 20:06 ` Re: Avoiding RI failure with INSERT ... SELECT FROM Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mark Stosberg @ 2006-07-12 13:48 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Avoiding RI failure with INSERT ... SELECT FROM
2006-07-12 13:48 Avoiding RI failure with INSERT ... SELECT FROM Mark Stosberg <mark@summersault.com>
@ 2006-07-12 20:06 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 2+ messages in thread
From: Aaron Bono @ 2006-07-12 20:06 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-sql
On 7/12/06, Mark Stosberg <mark@summersault.com> wrote:
>
>
> 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';
I would recommend you list your values in your insert statements:
INSERT into item_hit_log (
item_id_fk, ....
)
SELECT ....
That makes it less prone to problems in the future (like if the column
orders change) and makes it easier for others to understand and help you
with.
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?
Does the items table allow deletes? If so, your insert may be attempting to
do an insert for an item_id that was deleted after the select and before the
insert. Don't know if PostgreSQL will prevent that with table locking or
not.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-07-12 20:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-12 13:48 Avoiding RI failure with INSERT ... SELECT FROM Mark Stosberg <mark@summersault.com>
2006-07-12 20:06 ` Aaron Bono <postgresql@aranya.com>
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