To: pgsql-sql@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: INSERT ... SELECT FROM .... FOR UPDATE? 
Date: Fri, 14 Jul 2006 11:44:10 -0400
Lines: 34
Message-ID: <e98e61$mrd$1@sea.gmane.org>
References: <e92ulb$n0f$1@sea.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5 (X11/20060405)
In-Reply-To: <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