Skip site navigation (1) Skip section navigation (2)

Rules with conditions over views don't work

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 (view raw or flat)
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


Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-07-06 18:08:55
Subject: Referential Integrity corrupted sometimes by Rules
Previous:From: pgsql-bugsDate: 2001-07-06 16:39:23
Subject: Query rewriting limit needs to be a little bigger or tunable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group