Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f26KAvx74580 for ; Tue, 6 Mar 2001 15:10:57 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f26KAq907275; Tue, 6 Mar 2001 15:10:52 -0500 (EST) To: "Jens Hartwig" cc: pgsql-sql@postgresql.org Subject: Re: Problems with RULE In-reply-to: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> References: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> Comments: In-reply-to "Jens Hartwig" message dated "Tue, 06 Mar 2001 08:11:48 +0100" Date: Tue, 06 Mar 2001 15:10:52 -0500 Message-ID: <7272.983909452@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/124 X-Sequence-Number: 1776 "Jens Hartwig" writes: > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. > I implemented the following rule: > CREATE RULE r_del_xyz > AS ON DELETE TO t_xyz WHERE (old.deleted = false) > DO INSTEAD > UPDATE t_xyz > SET deleted = true > WHERE id = old.id; > Now I tested the new rule: > INSERT INTO t_xyz VALUES (1, false); > INSERT INTO t_xyz VALUES (2, false); > DELETE FROM t_xyz WHERE id = 1; > SELECT * FROM t_xyz ; > id | deleted > ----+--------- > 2 | f > What has happened? The rule seems to be ignored and the record was deleted! You'd probably have better luck doing this with a trigger. With this rule, the DELETE query expands into two operations, which can be written as: UPDATE t_xyz SET deleted = true WHERE id IN (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND old.deleted = false); DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false); The problem is that the second query can see the results of the first. Unfortunately, while that's bad for this example, it's necessary for other more-useful examples. So I do not think this is a bug. In my experience, anything you want to do that can be expressed as an operation or condition on an individual target tuple of an INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both performance and understandability. Rules are good for things that involve conditions on multiple tuples. regards, tom lane