Hello,
This is a re-phrasing of question I asked recently, inquiring about a
an approach I'm considering.
My query is:
> INSERT into item_hit_log (item_id, hit_date, hit_count)> 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, and is likely
to be updated by other queries while this runs.
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.
From reading the docs, it sounds like "SELECT ... FOR UPDATE"
might be the perfect solution here.
http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE
Since it's hard to test for this kind of failure, I wanted to get the
opinions of others here if this would be a good idea.
Thanks!
Mark
