INSERT ... SELECT FROM .... FOR UPDATE?

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