X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id F008C9FB229 for ; Wed, 12 Jul 2006 17:07:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 13319-04 for ; Wed, 12 Jul 2006 17:06:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 83F5F9FB217 for ; Wed, 12 Jul 2006 17:06:56 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id o25so301285nfa for ; Wed, 12 Jul 2006 13:06:54 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=OugLaiVusMBhNT9uJluynfeKjG+r8sA95ACTSOqKlk7DUw0SIT/cPKvVj9gEn/0ZwoGOQdAMW2Qh2YXyFE++TQ2CUX+zUxgHvuxa0ZVzLu5eNJBCEoyYFQ6VALsf9jr5n3owCaa0zZ8ojXjri2W2NN5FBc+c4mElUjpLQi39r+E= Received: by 10.78.164.13 with SMTP id m13mr46411hue; Wed, 12 Jul 2006 13:06:54 -0700 (PDT) Received: by 10.78.29.15 with HTTP; Wed, 12 Jul 2006 13:06:54 -0700 (PDT) Message-ID: Date: Wed, 12 Jul 2006 15:06:54 -0500 From: "Aaron Bono" To: "Mark Stosberg" Subject: Re: Avoiding RI failure with INSERT ... SELECT FROM Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8843_28582845.1152734814566" References: X-Google-Sender-Auth: adaff0bbeef5b7ae X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/194 X-Sequence-Number: 25768 ------=_Part_8843_28582845.1152734814566 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/12/06, Mark Stosberg wrote: > > > Hello! I got an unexpected SQL failure last night, and want to see how > to prevent it in the future. This is with 8.1.3. > > I have a couple of insert statements that run in a transaction block, > initializing rows that will be used the next day: > > INSERT into item_hit_log > SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0 > FROM items where item_state = 'available'; > > INSERT into item_view_log > SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0 > FROM items where item_state = 'available'; I would recommend you list your values in your insert statements: INSERT into item_hit_log ( item_id_fk, .... ) SELECT .... That makes it less prone to problems in the future (like if the column orders change) and makes it easier for others to understand and help you with. The "items" table has a few hundred thousand rows in it, so this takes a > bit a run. > > 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. > > What's an alternate design that avoids this possible error? Does the items table allow deletes? If so, your insert may be attempting to do an insert for an item_id that was deleted after the select and before the insert. Don't know if PostgreSQL will prevent that with table locking or not. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_8843_28582845.1152734814566 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/12/06, Mark Stosberg <mark@summersault.com> wrote:

Hello! I got an unexpected SQL failure last night, and want to see how
to prevent it in the future. This is with 8.1.3.

I have a couple of insert statements that run in a transaction block,
initializing rows that will be used the next day:

INSERT into item_hit_log
    SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0
         FROM items where item_state = 'available';

INSERT into item_view_log
         SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0
             FROM items where item_state = 'available';

I would recommend you list your values in your insert statements:

INSERT into item_hit_log (
item_id_fk, ....
)
SELECT ....

That makes it less prone to problems in the future (like if the column orders change) and makes it easier for others to understand and help you with.

The "items" table has a few hundred thousand rows in it, so this takes a
bit a run.

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.

What's an alternate design that avoids this possible error?

Does the items table allow deletes?  If so, your insert may be attempting to do an insert for an item_id that was deleted after the select and before the insert.  Don't know if PostgreSQL will prevent that with table locking or not.


==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_8843_28582845.1152734814566--