Received: from neptun.sns-felb.debis.de ([53.122.101.2]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f277Pwx00619 for ; Wed, 7 Mar 2001 02:26:00 -0500 (EST) (envelope-from jens.hartwig@t-systems.de) Received: by neptun.sns-felb.debis.de; id IAA22626; Wed, 7 Mar 2001 08:23:19 +0100 Received: from unknown(53.113.82.10) by neptun.sns-felb.debis.de via smap (V5.0) id xma022601; Wed, 7 Mar 01 08:23:07 +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 IAA15134; Wed, 7 Mar 2001 08:23:39 +0100 (MET) Received: from 0000864A433A ([138.201.10.193]) by mail1.b3.dsh.de (8.10.0/8.10.0) with SMTP id f277NjX16848; Wed, 7 Mar 2001 08:23:45 +0100 From: "Jens Hartwig" To: Cc: "'PSQL-Sql \(E-Mail\)'" Subject: AW: Problems with RULE Date: Wed, 7 Mar 2001 08:23:43 +0100 Message-ID: <000e01c0a6d7$8ad0c0d0$c10ac98a@0000864A433A> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <20010306.18094200@client.archonet.com> X-Mimeole: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200103/136 X-Sequence-Number: 1788 Hello Richard, this was a very precise analysis - thanks for the effort you made! Nevertheless the Tom's explanation of the behaviour in case of views was sufficient for me. But still I don't understand the behaviour in my case ... Best regards, Jens PS: I use 7.1b4 ----------------------------------------------------- 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 > -----Ursprüngliche Nachricht----- > Von: dev@archonet.com [mailto:dev@archonet.com] > Gesendet: Dienstag, 6. März 2001 19:10 > An: Jens Hartwig > Cc: PSQL-Sql (E-Mail) > Betreff: Re: [SQL] Problems with RULE > > > On 3/6/01, 7:11:48 AM, Jens Hartwig wrote > regarding [SQL] Problems with RULE: > > > 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. > > Jens - more info > > Definitely a bug if my testing is correct (see below) - I'll file a > report on it and include your example too (hope that's OK) > > - Richard Huxton > > -- OK define a table foo with data and a view voo showing > -- even-numbered entries > -- > richardh=> create table foo (a int, b text); > CREATE > richardh=> insert into foo values (1,'aaa'); > INSERT 1287580 1 > richardh=> insert into foo values (2,'bbb'); > INSERT 1287581 1 > richardh=> insert into foo values (3,'ccc'); > INSERT 1287582 1 > richardh=> insert into foo values (4,'ddd'); > INSERT 1287583 1 > richardh=> create view voo as select * from foo where (a % 2)=0; > CREATE > richardh=> select * from voo; > a | b > ---+----- > 2 | bbb > 4 | ddd > (2 rows) > > -- Now define an insert rule with a where on voo > -- > richardh=> CREATE RULE voo_ins_rule AS ON INSERT TO voo WHERE > (NEW.a % 2)=0 > DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> insert into voo values (98,'yyy'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> select * from foo; > a | b > ---+----- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > (4 rows) > > richardh=> select * from voo; > a | b > ---+----- > 2 | bbb > 4 | ddd > (2 rows) > > -- OK: rule wasn't accepted, so lets add another rule to voo > without a > where > -- > richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO > INSTEAD INSERT > INTO > foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > INSERT 1287602 1 > richardh=> insert into voo values (98,'yyy'); > INSERT 1287604 1 > richardh=> select * from foo; > a | b > ----+----- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > 99 | zzz > 98 | yyy > 98 | yyy > (7 rows) > > richardh=> select * from voo; > a | b > ----+----- > 2 | bbb > 4 | ddd > 98 | yyy > 98 | yyy > (4 rows) > > -- So: looks like either rule2 executes twice or both fire. > -- Is it because we have a second rule? > -- > richardh=> drop rule voo_ins_rule2; > DROP > richardh=> CREATE RULE voo_ins_rule3 AS ON INSERT TO voo > WHERE (NEW.a % > 2)=1 DO > INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> insert into voo values (98,'yyy'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> select * from foo; > a | b > ----+----- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > 99 | zzz > 98 | yyy > 98 | yyy > (7 rows) > > richardh=> select * from voo; > a | b > ----+----- > 2 | bbb > 4 | ddd > 98 | yyy > 98 | yyy > (4 rows) > > -- No: it must be the lack of where on rule2 > -- Let's put rule2 back in and see what executes now > -- > richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO > INSTEAD INSERT > INTO > foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > INSERT 1287608 1 > richardh=> insert into voo values (98,'yyy'); > INSERT 1287610 1 > richardh=> select * from foo; > a | b > ----+----- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > 99 | zzz > 98 | yyy > 98 | yyy > 99 | zzz > 99 | zzz > 98 | yyy > 98 | yyy > (11 rows) > > richardh=> select * from voo; > a | b > ----+----- > 2 | bbb > 4 | ddd > 98 | yyy > 98 | yyy > 98 | yyy > 98 | yyy > (6 rows) > > -- OK: so it looks like rules with "WHERE" don't execute until > -- there is a rule that fires unconditionally, when > -- the "WHERE" is recognised and applies accordingly.