X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2785DD1C7F3 for ; Wed, 23 Jul 2003 04:42:04 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 26134-07 for ; Wed, 23 Jul 2003 01:41:13 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 33586D1C7F1 for ; Wed, 23 Jul 2003 01:41:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6N4fuQ3010285; Wed, 23 Jul 2003 00:41:56 -0400 (EDT) To: paul cannon Cc: pgsql-sql@postgresql.org Subject: Re: rule causes nextval() to be invoked twice In-reply-to: <20030723014700.GE24912@fslc.usu.edu> References: <20030723014700.GE24912@fslc.usu.edu> Comments: In-reply-to paul cannon message dated "Tue, 22 Jul 2003 19:47:00 -0600" Date: Wed, 23 Jul 2003 00:41:55 -0400 Message-ID: <10284.1058935315@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/335 X-Sequence-Number: 14449 paul cannon writes: > I'm having trouble understanding the behavior of rules with regards to > default values. > ... > If I remove the REFERENCES constraint, then I can see why. The insert > made into main behaves as expected; it gets nextval('main_id_seq'), > which comes out to 1. However, the main_insert rule gets _another_ > nextval('main_id_seq'), and the value 2 is inserted into othertable. Yeah. Rules are macros, and as such have the usual issues about multiple evaluations of arguments. Arguments with side effects are bad news. It looks to me like what you are trying to do is reflect a copy of an inserted row into a log table. You'd be better advised to do this with a trigger ... probably an AFTER trigger, so that you know exactly what got inserted. (BEFORE triggers have to consider the possibility that they're not the last BEFORE trigger.) regards, tom lane