From: | Mark Stosberg <mark(at)summersault(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | INSERT ... SELECT FROM .... FOR UPDATE? |
Date: | 2006-07-14 15:44:10 |
Message-ID: | e98e61$mrd$1@sea.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Arentz | 2006-07-15 09:24:17 | Querying for name/value pairs in reverse |
Previous Message | Joost Kraaijeveld | 2006-07-14 14:21:40 | Re: Can I do this smarter? |