Re: Mis-firing of rules with a WHERE condition

Richard Huxton (dev(at)archonet(dot)com) writes:
> Jens Hartwig posted a question to pgsql-sql today (2001-03-06)
> regarding rules with where conditions. It seems to be a bug and
> applies to all rule-types.

AFAICT this is not a bug but is operating as designed. The message you
are getting:
> richardh=> insert into voo values (99,'zzz');
> ERROR: Cannot insert into a view without an appropriate rule

is a runtime check that insists that the view have at least one
unconditional DO INSTEAD rule. It's OK to have conditional rules too
(INSTEAD or not doesn't matter) --- but there must be an unconditional
one, else there is no certainty that the undefined operation of
inserting into the view won't occur.

If you want the default to be that nothing happens, fine: add

CREATE RULE voo_ins_default AS ON INSERT TO voo DO INSTEAD NOTHING

and then do the useful work in conditional rules. But you gotta have
the unconditional rule as a backstop.

This runtime check is new in 7.1. In 7.0, the undefined operation of
inserting into the view will actually occur if you are careless enough
to let it. The effective result is that the inserted tuples disappear
(I'll let you consult the archives to learn where they really go);
that's mystified many people, including me when I first got burnt by it.

I haven't had time to look closely at Jens' complaint, but I suspect
that he is using 7.0 and is getting burnt by the undefined case.

regards, tom lane