Postgres Pro
Downloads
Home   >   mailing lists

Thread: bug (?) with RULEs with WHERE

bug (?) with RULEs with WHERE

From
Kovacs Zoltan
Date:
I cannot use RULEs with WHERE clauses. What's wrong? Is this a bug? I also
had this problem with 7.1.1. The documentation says this should work.

foo=# SELECT version();                              version
---------------------------------------------------------------------PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by
GCCegcs-2.91.66
 
(1 row)

foo=# CREATE TABLE a(foo integer);
CREATE
foo=# CREATE TABLE b(foo integer);
CREATE
foo=# CREATE VIEW c AS SELECT foo FROM a;
CREATE
foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b;
CREATE
foo=# INSERT INTO c VALUES (5);
ERROR:  Cannot insert into a view without an appropriate rule
foo=# INSERT INTO c VALUES (6);
ERROR:  Cannot insert into a view without an appropriate rule

TIA, Zoltan

--                         Kov\'acs, Zolt\'an                        kovacsz@pc10.radnoti-szeged.sulinet.hu
          http://www.math.u-szeged.hu/~kovzol                        ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

Re: bug (?) with RULEs with WHERE

From
Tom Lane
Date:
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> foo=# CREATE TABLE a(foo integer);
> CREATE
> foo=# CREATE TABLE b(foo integer);
> CREATE
> foo=# CREATE VIEW c AS SELECT foo FROM a;
> CREATE
> foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b;
> CREATE
> foo=# INSERT INTO c VALUES (5);
> ERROR:  Cannot insert into a view without an appropriate rule

You didn't provide a rule covering the new.foo<>5 case.

In practice, you *must* have an unconditional INSTEAD rule present for
any view operation you want to allow.  It can be DO INSTEAD NOTHING,
and then you can do all your useful work in conditional rules, but the
unconditional rule must be there.  Else the system thinks that perhaps
the insert into the view would really happen.
        regards, tom lane

Re: bug (?) with RULEs with WHERE

From
Kovacs Zoltan
Date:
On Sat, 27 Oct 2001, Tom Lane wrote:

> Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> > [...]
> > foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b;
> > CREATE
> > foo=# INSERT INTO c VALUES (5);
> > ERROR:  Cannot insert into a view without an appropriate rule
> 
> You didn't provide a rule covering the new.foo<>5 case.
> 
> In practice, you *must* have an unconditional INSTEAD rule present for
> any view operation you want to allow.  It can be DO INSTEAD NOTHING,
> and then you can do all your useful work in conditional rules, but the
> unconditional rule must be there.  Else the system thinks that perhaps
> the insert into the view would really happen.

Thank you, I see. It works now. But in 7.1.1 on a rather complex view I
experienced that the RULE has been executed as many times as many rows the
view contains, although I added a WHERE to filter the rows: in fact it 
should have been executed only once. In 7.1.3 this problem doesn't
occur. Has anything been changed since 7.1.1 in this code?

So I'm migrating to 7.1.3 now. But currently I'm still having problems
with user authentication (I get "Password authentication failed for user
'xxxx'." errors). I always used

INSERT INTO pg_shadow...

Is this changed? With

ALTER USER...

it works, of course. Do you suggest stopping use "INSERT INTO
pg_shadow..."?

TIA, Zoltan
                        Kov\'acs, Zolt\'an                        kovacsz@pc10.radnoti-szeged.sulinet.hu
       http://www.math.u-szeged.hu/~kovzol                        ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz