Thread: Newbie question on RULEs .. or .. bug ?

     Hello,

   I have been working with a great database system called PostgreSQL for
many years  ;-)  but never had to use any RULEs. I now have to use and
update through a view and have written a few rules to make this possible
as per the manual.

   My insert rules seems to work fine, but I can't make the update rule do
what I want and as I believe stated in the manual.

   In the attached schema I have 2 basic tables and a view combining
those. I have 2 rules for insert on the view and these works fine. The
update rule, however, doesn't do what I want. The manual states that the
origsal query tree (where) is added to the rule qualification,
so I would exspect the update statement below would only update 1 row of
the ganntinfo table, but it updates all 5 ?

   What am I doing wrong ?


projtaskdb=# SELECT * FROM ganntinfo ;
 id  | seq | category |  name
-----+-----+----------+---------
  46 |   0 | SRC      | 2WWE
 172 |   0 | SRC      | RKD60
 138 |   0 | SRC      | Diverse
  34 |   0 | SRC      | VF
  87 |   0 | SRC      | 2WWE
(5 rows)

projtaskdb=# UPDATE tasks SET id = 87, category = 'SRC', name = '2WWE',
customer = 'Custm', description = 'test' WHERE id = 87 and category =
'SRC';
UPDATE 5
projtaskdb=# SELECT * FROM ganntinfo ;
 id | seq | category | name
----+-----+----------+------
 87 |   0 | SRC      | 2WWE
 87 |   0 | SRC      | 2WWE
 87 |   0 | SRC      | 2WWE
 87 |   0 | SRC      | 2WWE
 87 |   0 | SRC      | 2WWE
(5 rows)

projtaskdb=#


    I tried to put an 'explain' in front of the update within the update
rule, but got a syntax error. Why is that ?

    Please help,

 Leif

Leif Jensen <leif@crysberg.dk> writes:
> CREATE RULE update_tasks2taskshead AS
>   ON UPDATE TO tasks WHERE NEW.seq = 0
>   DO NOTHING
> ;

That rule looks a bit useless ...

> CREATE RULE update_tasks2ganntinfo AS
>   ON UPDATE TO tasks
>   DO INSTEAD (
>     update ganntinfo set
>       id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name
>       -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category
>     ;
>   )
> ;

You definitely need a WHERE clause in that rule; otherwise you get
exactly the result you saw: all rows of ganntinfo are updated.  The
comment in the manual about the original WHERE clause really means
that the values of "NEW" will be constrained to take on only the
values determined by the original WHERE.  Your update is basically a join
of ganntinfo with the subset of the tasks view determined by the
original WHERE --- so you have to constrain ganntinfo too.  I suppose
that you want something like

    update ganntinfo set
      category = NEW.category, name = NEW.name
      WHERE id = NEW.id AND seq = NEW.seq
    ;

since id/seq is your primary key for ganntinfo.

            regards, tom lane

   Hello Tom,

  Thank you for the enlightment, I think I understand what you say.

  There are however a few things I'm not sure about still. The update
seems to work as I would expect when I include one or more in there where
clause from the primary key. If I have a field not in the primary key
included in the where, I don't get anything updated, e.g.

   update tasks set seq = 2 where id = 87 and name = '2WWE';

does nothing !??

   Two questions:

1) Is there any way to define a view like this where I can insert and
update without these rules, just as if it was one real table ?

2) If I need to use rules to do update/insert on tasks, how can I make it
'transparent' as in the above example (the update that does nothing) ?

  Greetings,

 Leif


On Tue, 17 May 2005, Tom Lane wrote:

> Leif Jensen <leif@crysberg.dk> writes:
> > CREATE RULE update_tasks2taskshead AS
> >   ON UPDATE TO tasks WHERE NEW.seq = 0
> >   DO NOTHING
> > ;
>
> That rule looks a bit useless ...

   Yeah, just disabled for now ;-)

>
> > CREATE RULE update_tasks2ganntinfo AS
> >   ON UPDATE TO tasks
> >   DO INSTEAD (
> >     update ganntinfo set
> >       id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name
> >       -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category
> >     ;
> >   )
> > ;
>
> You definitely need a WHERE clause in that rule; otherwise you get
> exactly the result you saw: all rows of ganntinfo are updated.  The
> comment in the manual about the original WHERE clause really means
> that the values of "NEW" will be constrained to take on only the
> values determined by the original WHERE.  Your update is basically a join
> of ganntinfo with the subset of the tasks view determined by the
> original WHERE --- so you have to constrain ganntinfo too.  I suppose
> that you want something like
>
>     update ganntinfo set
>       category = NEW.category, name = NEW.name
>       WHERE id = NEW.id AND seq = NEW.seq
>     ;
>
> since id/seq is your primary key for ganntinfo.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>