Received: from relay.EECS.Berkeley.EDU (relay.EECS.Berkeley.EDU [169.229.34.228]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA55068 for ; Fri, 21 Apr 2000 20:33:09 -0400 (EDT) (envelope-from mdenny@CS.Berkeley.EDU) Received: from CS.Berkeley.EDU (caracas.CS.Berkeley.EDU [128.32.46.231]) by relay.EECS.Berkeley.EDU (8.9.3/8.9.3) with ESMTP id RAA23822 for ; Fri, 21 Apr 2000 17:33:08 -0700 (PDT) Received: (from mdenny@localhost) by CS.Berkeley.EDU (8.9.3/8.9.3) id RAA28377; Fri, 21 Apr 2000 17:33:08 -0700 (PDT) From: Matthew Denny MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Date: Fri, 21 Apr 2000 17:33:08 -0700 (PDT) To: pgsql-sql@postgresql.org Subject: question on UPDATE rules X-Mailer: VM 6.43 under 20.4 "Emerald" XEmacs Lucid Message-ID: <14592.60375.884848.74935@caracas.CS.Berkeley.EDU> X-Archive-Number: 200004/248 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. The crux of the problem seems to be that the "new" pseudo-relation seems to be returning old values, as this is the only way I can explain this behavior. Has anyone else experienced this problem? If so, a workaround would be greatly appreciated. thanks, Matt Denny