Received: from neptun.sns-felb.debis.de (neptun.sns-felb.debis.de [53.122.101.2]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f26982x51799 for ; Tue, 6 Mar 2001 04:08:03 -0500 (EST) (envelope-from jens.hartwig@t-systems.de) Received: by neptun.sns-felb.debis.de; id IAA23482; Tue, 6 Mar 2001 08:11:35 +0100 Received: from unknown(53.113.82.10) by neptun.sns-felb.debis.de via smap (V5.0) id xma023416; Tue, 6 Mar 01 08:11:20 +0100 Received: from mail1.b3.dsh.de (localhost [127.0.0.1]) by dshmail1.dsh.de (8.9.1/8.8.7) with ESMTP id IAA21705 for ; Tue, 6 Mar 2001 08:11:50 +0100 (MET) Received: from 0000864A433A ([138.201.10.193]) by mail1.b3.dsh.de (8.10.0/8.10.0) with SMTP id f267BuX31652 for ; Tue, 6 Mar 2001 08:11:56 +0100 From: "Jens Hartwig" To: "PSQL-Sql \(E-Mail\)" Subject: Problems with RULE Date: Tue, 6 Mar 2001 08:11:48 +0100 Message-ID: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0020_01C0A615.185BD750" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) X-Mimeole: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200103/90 X-Sequence-Number: 1738 This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C0A615.185BD750 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Hello all, 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! I dropped the rule, deleted all records and recreated the rule without the additional WHERE-Clause in the UPDATE-Statement: DROP RULE r_del_xyz; DELETE FROM t_xyz; CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (old.deleted = false) DO INSTEAD UPDATE t_xyz SET deleted = true; INSERT INTO t_xyz VALUES (1, false); INSERT INTO t_xyz VALUES (2, false); The same test again: DELETE FROM t_xyz WHERE id = 1; SELECT * FROM t_xyz ; id | deleted ----+--------- 2 | t It seems to me that PostgreSQL executed the rule, but ignored the keyword INSTEAD and deleted the record after having updated it?! One last test with a slightly different rule (look at the WHERE-clause in the "AS-ON"-clause): DROP RULE r_del_xyz; DELETE FROM t_xyz; CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (1 = 1) DO INSTEAD UPDATE t_xyz SET deleted = true WHERE id = old.id; 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 1 | t DELETE FROM t_xyz WHERE id = 1; SELECT * FROM t_xyz ; Everything is alright now! Am I wrong? Is the WHERE-clause "WHERE (old.deleted = false)" not correct? Any hints? Or it is really a bug? Best regards, Jens Hartwig PS: You will find the scripts in the attachment. ----------------------------------------------------- T-Systems Projektleiter debis Systemhaus GEI GmbH Hausanschrift: Eichhornstraße 3, 10785 Berlin Postanschrift: 10785 Berlin Telefon: (004930) 25 54-32 82 Telefax: (004930) 25 54-31 87 Mobiltelefon: (0170) 167 26 48 E-Mail: jens.hartwig@t-systems.de Internet: http://www.t-systems.de ------=_NextPart_000_0020_01C0A615.185BD750 Content-Type: application/octet-stream; name="rule_error_1.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="rule_error_1.sql" DROP TABLE t_xyz; CREATE TABLE t_xyz (id integer, deleted boolean); insert into t_xyz (id, deleted) values (1, false); insert into t_xyz (id, deleted) values (2, false); CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (deleted =3D false) DO INSTEAD UPDATE t_xyz SET deleted =3D true WHERE id =3D old.id; SELECT * FROM t_xyz; DELETE FROM t_xyz WHERE id =3D 1; SELECT * FROM t_xyz; ------=_NextPart_000_0020_01C0A615.185BD750 Content-Type: application/octet-stream; name="rule_error_2.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="rule_error_2.sql" DROP TABLE t_xyz; CREATE TABLE t_xyz (id integer, deleted boolean); insert into t_xyz (id, deleted) values (1, false); insert into t_xyz (id, deleted) values (2, false); CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (deleted =3D false) DO INSTEAD UPDATE t_xyz SET deleted =3D true; SELECT * FROM t_xyz; DELETE FROM t_xyz WHERE id =3D 1; SELECT * FROM t_xyz; ------=_NextPart_000_0020_01C0A615.185BD750 Content-Type: application/octet-stream; name="rule_error_3.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="rule_error_3.sql" DROP TABLE t_xyz; CREATE TABLE t_xyz (id integer, deleted boolean); insert into t_xyz (id, deleted) values (1, false); insert into t_xyz (id, deleted) values (2, false); CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (1 =3D 1) DO INSTEAD UPDATE t_xyz SET deleted =3D true WHERE id =3D old.id; SELECT * FROM t_xyz; DELETE FROM t_xyz WHERE id =3D 1; SELECT * FROM t_xyz; ------=_NextPart_000_0020_01C0A615.185BD750--