From: Mark Stosberg <mark@summersault.com>
To: pgsql-sql@postgresql.org
Subject: INSERT ... SELECT FROM .... FOR UPDATE?
Date: Fri, 14 Jul 2006 11:44:10 -0400
Message-ID: <e98e61$mrd$1@sea.gmane.org> (raw)
In-Reply-To: <e92ulb$n0f$1@sea.gmane.org>
References: <e92ulb$n0f$1@sea.gmane.org>
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
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: INSERT ... SELECT FROM .... FOR UPDATE?
In-Reply-To: <e98e61$mrd$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