Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA08303 for ; Sat, 22 Apr 2000 20:06:49 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id UAA29471; Sat, 22 Apr 2000 20:06:08 -0400 (EDT) To: Matthew Denny cc: pgsql-sql@postgresql.org Subject: Re: question on UPDATE rules In-reply-to: <14592.60375.884848.74935@caracas.CS.Berkeley.EDU> References: <14592.60375.884848.74935@caracas.CS.Berkeley.EDU> Comments: In-reply-to Matthew Denny message dated "Fri, 21 Apr 2000 17:33:08 -0700" Date: Sat, 22 Apr 2000 20:06:07 -0400 Message-ID: <29468.956448367@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/259 Matthew Denny writes: > I have a rule defined as the following: > CREATE RULE fooBar AS ON > UPDATE TO VectorMessageTable WHERE new.acks = (SELECT > numReplicas FROM VectorTable vt WHERE vt.ID = > new.ID) DO (DELETE FROM VectorMessageTable WHERE ID = new.ID AND > versionNum = new.versionNum); > if I call: > UPDATE VectorMessageTable SET acks = > > Then I the update goes through, but none of the records are > deleted. However, if I run any other subsequent update on these tuples in > VectorMessageTable (even ones that change the acks value) then the > rule fires and the tuples are deleted. Not sure, but maybe you want DO INSTEAD DELETE ... rather than just DO DELETE ... ? As it stands, you're commanding the machine to both delete and update the tuples for which the rule fires. I think that will result in the old tuple being marked deleted (twice!), but the update will still produce a new tuple that's not marked deleted. That seems to match your symptoms... regards, tom lane