Обсуждение: Problems using a rule with the WHERE clause

Hello <p>I'm using a rule without a WHERE clause that's working fine. Now a have to put a where clause because I only
wantthe rule be get executed only in some occasions, but I'm having some problems with that. <p>This rule is over a
viewcalled docentesturno. <p>The code that I was using in the rule when I didn't had the WHERE clause was: <p><b>CREATE
RULE"updateturnodocente" AS ON UPDATE TO "docentesturno"</b><br /><b> DO INSTEAD (</b><br /><b> UPDATE
"pessoalEvento"</b><br/><b>  SET "idPessoal" = NEW."idpessoal",</b><br /><b>   ponderacao = NEW.ponderacao,</b><br
/><b>  envolvimento = NEW.envolvimento</b><br /><b>  WHERE id_pe = OLD.id_pe;</b><br /><b> );</b><br />  <p>Now the
codefor the rule with WHERE: <p><b>CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"</b><br /><b> WHERE
OLD.idpessoal!= 0</b><br /><b> DO INSTEAD (</b><br /><b> UPDATE "pessoalEvento"</b><br /><b>  SET "idPessoal" =
NEW."idpessoal",</b><br/><b>   ponderacao = NEW.ponderacao,</b><br /><b>   envolvimento = NEW.envolvimento</b><br
/><b> WHERE id_pe = OLD.id_pe;</b><br /><b> );</b><b></b><p>When I execute the INSERT into de view docentesturno I got
themessage: <br />            ERROR:  Cannot update a view without an appropriate rule <br />  <p>Do someone knows what
canbe the problem in this rule? <br />Apparently I'm doing in the same way of the manual, chapter 13.7.2 !!!!!!! <br
/> <br />  <p>Best regards <p>Luis Sousa  

Luis Sousa <llsousa@ualg.pt> writes:
> CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"
>  WHERE OLD.idpessoal != 0
>  DO INSTEAD (
>  ...

> When I execute the INSERT into de view docentesturno I got the message:
>             ERROR:  Cannot update a view without an appropriate rule

You failed to supply a rule covering the case OLD.idpessoal = 0.

More specifically, you *must* supply an unconditional INSTEAD rule to
replace the attempt to insert/update in the view.  Possibly what you
want is

CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"
WHERE OLD.idpessoal != 0
DO ( ... );

CREATE RULE "updateturnodocente_default" AS ON UPDATE TO "docentesturno"
DO INSTEAD NOTHING;

Here, the unconditional rule always fires, and the conditional one fires
only when its condition is true.
        regards, tom lane

Tom,

I wrote the rule as you mentioned and I resolve that problem, but after some
tests I realize that the system wasn't following the condition inside the
WHERE clause, to determine which rule to use

There's an example used:

CREATE RULE "updateturnodocente_default" AS ON UPDATE TO "docentesturno"DO INSTEAD NOTHING;

CREATE RULE "updateturnodocente_all" AS ON UPDATE TO "docentesturno"WHERE OLD.idpessoal = 338DO INSTEAD (UPDATE
"pessoalEvento"SET ponderacao = NEW.ponderacao,  envolvimento = NEW.envolvimento WHERE id_pe = OLD.id_pe;);
 

CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"WHERE OLD.idpessoal = 0DO INSTEAD (UPDATE
"pessoalEvento"SET "idPessoal" = NEW."idpessoal",  ponderacao = NEW.ponderacao,  envolvimento = NEW.envolvimento WHERE
id_pe= OLD.id_pe;);
 

I tried with the first two rules and it worked, but when I add the third, I
got an error.

I think this code is OK !!!!! But I got the error message bellow in line 11
of my script, that corresponds to the rule updateturnodocente_default

psql:dev/pessoal/def/views_rules/teste_update:11: pqReadData() -- backend
closed the channel unexpectedly.       This probably means the backend terminated abnormally       before or while
processingthe request.
 
psql:dev/pessoal/def/views_rules/teste_update:11: connection to server was
lost

What can be happening ???


Best regards

Luis Sousa


Tom Lane wrote:

> Luis Sousa <llsousa@ualg.pt> writes:
> > CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"
> >  WHERE OLD.idpessoal != 0
> >  DO INSTEAD (
> >  ...
>
> > When I execute the INSERT into de view docentesturno I got the message:
> >             ERROR:  Cannot update a view without an appropriate rule
>
> You failed to supply a rule covering the case OLD.idpessoal = 0.
>
> More specifically, you *must* supply an unconditional INSTEAD rule to
> replace the attempt to insert/update in the view.  Possibly what you
> want is
>
> CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"
> WHERE OLD.idpessoal != 0
> DO ( ... );
>
> CREATE RULE "updateturnodocente_default" AS ON UPDATE TO "docentesturno"
> DO INSTEAD NOTHING;
>
> Here, the unconditional rule always fires, and the conditional one fires
> only when its condition is true.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Luis Sousa <llsousa@ualg.pt> writes:
> I think this code is OK !!!!!

You probably don't want the second and third rules to be INSTEAD.
However,

> But I got the error message bellow in line 11
> of my script, that corresponds to the rule updateturnodocente_default
>
> psql:dev/pessoal/def/views_rules/teste_update:11: pqReadData() -- backend
> closed the channel unexpectedly.

that shouldn't happen, in any case.  If this is the current release
(7.1.2) then I'd like to see a complete script to reproduce the problem.
(I don't have time to guess at your table declarations...)
        regards, tom lane

> that shouldn't happen, in any case.  If this is the current release
> (7.1.2)

The version that I'm using is 7.1release-3.potato.1

> then I'd like to see a complete script to reproduce the problem.
> (I don't have time to guess at your table declarations...)

I try a simple example that works fine. (see attached)

Then I create a script from the database only with the main tables that are
involved (see attached - script_def.tar.gz).

When I execute the file run_update, that only updates three fields on the
records with turno=15, I'm getting some errors (sometimes I got the 1st error
and other times I got de 2nd error):
    - ERROR:  JOIN qualification may not refer to other relations
    - ERROR:  ExecEvalExpr: unknown expression type 501

The script contains two tables, evento and pessoalEvento, and the view
docentesturno that JOIN's both tables.
The update is done over docentesturno using run_update.


Thank you for your help. Best Regards

Luis Sousa

Luis Sousa <llsousa@ualg.pt> writes:
>> that shouldn't happen, in any case.  If this is the current release
>> (7.1.2)

> The version that I'm using is 7.1release-3.potato.1

I don't see any problem here when I run your script.  I suspect you are
seeing another consequence of the rewriter bug I fixed on 12-June.
Please update to 7.1.2 and apply the patch I posted then.
        regards, tom lane