From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Rules with conditions over views don't work |
Date: | 2001-07-06 16:59:53 |
Message-ID: | 200107061659.f66GxrP37669@hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Jos Mara Fernndez Gonzlez (jmfernandez(at)cnb(dot)uam(dot)es) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Rules with conditions over views don't work
Long Description
Since PostgreSQL 7.1 (or 7.0, I don't remember), if you define a view, and then a rule with a condition over the view, the rule doesn't work when you try to do an operation from the type defined in the rule (it echoes an error message). But, if you also define an empty rule of the same type on the same view with no condition, the rule starts working.
The real problem here is the query rewriting limit, which is reached when many rules of this type are involved in a operation.
Sample Code
-- Sample table
create table a (
b integer,
c text
);
-- View over the table
create view va as
select * from a;
-- Rule to allow insertion
-- but it doesn't work by itself
create rule va_ins as
on insert to va
where new.b > 0
do instead
insert into a values (new.*);
insert into va (b,c) values (3,'a');
ERROR: Cannot insert into a view without an appropriate rule
-- With this trick it works again, but
-- you get nearer the query rewriting limit
create rule va_ins0 as
on insert to va
do instead nothing;
insert into va (b,c) values (3,'a');
INSERT 997472 1
No file was uploaded with this report
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2001-07-06 18:08:55 | Referential Integrity corrupted sometimes by Rules |
Previous Message | pgsql-bugs | 2001-07-06 16:39:23 | Query rewriting limit needs to be a little bigger or tunable |