pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems using a rule with the WHERE clause
6+ messages / 2 participants
[nested] [flat]

* Problems using a rule with the WHERE clause
@ 2001-06-25 15:08 Luis Sousa <llsousa@ualg.pt>
  2001-06-25 18:01 ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Luis Sousa @ 2001-06-25 15:08 UTC (permalink / raw)
  To: pgsql-sql

Hello

I'm using a rule without a WHERE clause that's working fine. Now a have
to put a where clause because I only want the rule be get executed only
in some occasions, but I'm having some problems with that.

This rule is over a view called docentesturno.

The code that I was using in the rule when I didn't had the WHERE clause
was:

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


Now the code for the rule with WHERE:

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

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


Do someone knows what can be the problem in this rule?
Apparently I'm doing in the same way of the manual, chapter 13.7.2
!!!!!!!



Best regards

Luis Sousa

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems using a rule with the WHERE clause
  2001-06-25 15:08 Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
@ 2001-06-25 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-26 09:52   ` Re: Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-06-25 18:01 UTC (permalink / raw)
  To: Luis Sousa <llsousa@ualg.pt>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems using a rule with the WHERE clause
  2001-06-25 15:08 Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  2001-06-25 18:01 ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-26 09:52   ` Luis Sousa <llsousa@ualg.pt>
  2001-06-26 14:41     ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Luis Sousa @ 2001-06-26 09:52 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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 = 338
 DO 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 = 0
 DO 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 processing the 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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems using a rule with the WHERE clause
  2001-06-25 15:08 Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  2001-06-25 18:01 ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-26 09:52   ` Re: Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
@ 2001-06-26 14:41     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-28 10:13       ` Re: Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-06-26 14:41 UTC (permalink / raw)
  To: Luis Sousa <llsousa@ualg.pt>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems using a rule with the WHERE clause
  2001-06-25 15:08 Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  2001-06-25 18:01 ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-26 09:52   ` Re: Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  2001-06-26 14:41     ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-28 10:13       ` Luis Sousa <llsousa@ualg.pt>
  2001-06-28 15:55         ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Luis Sousa @ 2001-06-28 10:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

> 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

Attachments:

  example_that_works.tar.gz (application/x-gzip, 678B) download

  script_def.tar.gz (application/x-gzip, 1.6K) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems using a rule with the WHERE clause
  2001-06-25 15:08 Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  2001-06-25 18:01 ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-26 09:52   ` Re: Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
  2001-06-26 14:41     ` Re: Problems using a rule with the WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-28 10:13       ` Re: Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
@ 2001-06-28 15:55         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2001-06-28 15:55 UTC (permalink / raw)
  To: Luis Sousa <llsousa@ualg.pt>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-06-28 15:55 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-25 15:08 Problems using a rule with the WHERE clause Luis Sousa <llsousa@ualg.pt>
2001-06-25 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-26 09:52   ` Luis Sousa <llsousa@ualg.pt>
2001-06-26 14:41     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-28 10:13       ` Luis Sousa <llsousa@ualg.pt>
2001-06-28 15:55         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox