pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems 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