From: dev@archonet.com
To: Jens Hartwig <jens.hartwig@t-systems.de>
Cc: PSQL-Sql "(E-Mail)" <pgsql-sql@postgresql.org>
Subject: Re: Problems with RULE
Date: Tue, 06 Mar 2001 18:09:42 GMT
Message-ID: <20010306.18094200@client.archonet.com> (raw)
In-Reply-To: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A>
References: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A>
On 3/6/01, 7:11:48 AM, Jens Hartwig <jens.hartwig@t-systems.de> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, jens.hartwig@t-systems.de
Subject: Re: Problems with RULE
In-Reply-To: <20010306.18094200@client.archonet.com>
References: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> <20010306.18094200@client.archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox