Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f26IYRx53106 for ; Tue, 6 Mar 2001 13:34:27 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id SAA17731; Tue, 6 Mar 2001 18:35:35 GMT Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 622612A475; Tue, 6 Mar 2001 18:09:43 +0000 (GMT) From: dev@archonet.com Date: Tue, 06 Mar 2001 18:09:42 GMT Message-ID: <20010306.18094200@client.archonet.com> Subject: Re: Problems with RULE To: Jens Hartwig Cc: PSQL-Sql "(E-Mail)" Reply-To: dev@archonet.com In-Reply-To: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> References: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> X-Mailer: Mozilla/3.0 (compatible; StarOffice/5.2;Linux) X-Priority: 3 (Normal) MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f26IYRx53108 X-Archive-Number: 200103/112 X-Sequence-Number: 1763 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.