From llsousa@ualg.pt Mon Jun 25 15:08:52 2001 Received: from mozart.si.ualg.pt (mozart.si.ualg.pt [193.136.224.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5PF8pa70524 for ; Mon, 25 Jun 2001 11:08:52 -0400 (EDT) (envelope-from llsousa@ualg.pt) Received: from ualg.pt (llsousa@corisco.uceh.ualg.pt [10.10.20.126]) by mozart.si.ualg.pt (8.9.1/8.9.1) with ESMTP id QAA358471 for ; Mon, 25 Jun 2001 16:08:44 +0100 (WEST) Message-ID: <3B3753FB.2E1BA9B0@ualg.pt> Date: Mon, 25 Jun 2001 16:08:44 +0100 From: Luis Sousa Organization: Universidade do Algarve X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17-compact i686) X-Accept-Language: en, pt MIME-Version: 1.0 To: pgsql sql Mailing List Subject: Problems using a rule with the WHERE clause Content-Type: multipart/alternative; boundary="------------E369099EBFCBD69606916C98" X-Archive-Number: 200106/401 X-Sequence-Number: 3698 --------------E369099EBFCBD69606916C98 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------E369099EBFCBD69606916C98 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------E369099EBFCBD69606916C98-- From tgl@sss.pgh.pa.us Mon Jun 25 18:01:31 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5PI1Va58944 for ; Mon, 25 Jun 2001 14:01:31 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5PI1B316746; Mon, 25 Jun 2001 14:01:16 -0400 (EDT) To: Luis Sousa cc: pgsql sql Mailing List Subject: Re: Problems using a rule with the WHERE clause In-reply-to: <3B3753FB.2E1BA9B0@ualg.pt> References: <3B3753FB.2E1BA9B0@ualg.pt> Comments: In-reply-to Luis Sousa message dated "Mon, 25 Jun 2001 16:08:44 +0100" Date: Mon, 25 Jun 2001 14:01:11 -0400 Message-ID: <16743.993492071@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/404 X-Sequence-Number: 3701 Luis Sousa 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 From llsousa@ualg.pt Tue Jun 26 09:52:52 2001 Received: from mozart.si.ualg.pt (mozart.si.ualg.pt [193.136.224.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5Q9qqa58142 for ; Tue, 26 Jun 2001 05:52:52 -0400 (EDT) (envelope-from llsousa@ualg.pt) Received: from ualg.pt (llsousa@corisco.uceh.ualg.pt [10.10.20.126]) by mozart.si.ualg.pt (8.9.1/8.9.1) with ESMTP id KAA68881; Tue, 26 Jun 2001 10:52:30 +0100 (WEST) Message-ID: <3B385B5E.A8A31C59@ualg.pt> Date: Tue, 26 Jun 2001 10:52:30 +0100 From: Luis Sousa Organization: Universidade do Algarve X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17-compact i686) X-Accept-Language: en, pt MIME-Version: 1.0 To: Tom Lane CC: pgsql sql Mailing List Subject: Re: Problems using a rule with the WHERE clause References: <3B3753FB.2E1BA9B0@ualg.pt> <16743.993492071@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200106/410 X-Sequence-Number: 3707 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 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 From tgl@sss.pgh.pa.us Tue Jun 26 14:41:46 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5QEfka86886 for ; Tue, 26 Jun 2001 10:41:46 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5QEfKF03078; Tue, 26 Jun 2001 10:41:21 -0400 (EDT) To: Luis Sousa cc: pgsql sql Mailing List Subject: Re: Problems using a rule with the WHERE clause In-reply-to: <3B385B5E.A8A31C59@ualg.pt> References: <3B3753FB.2E1BA9B0@ualg.pt> <16743.993492071@sss.pgh.pa.us> <3B385B5E.A8A31C59@ualg.pt> Comments: In-reply-to Luis Sousa message dated "Tue, 26 Jun 2001 10:52:30 +0100" Date: Tue, 26 Jun 2001 10:41:20 -0400 Message-ID: <3075.993566480@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/417 X-Sequence-Number: 3714 Luis Sousa 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 From llsousa@ualg.pt Thu Jun 28 10:14:00 2001 Received: from mozart.si.ualg.pt (mozart.si.ualg.pt [193.136.224.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5SADxa36031 for ; Thu, 28 Jun 2001 06:14:00 -0400 (EDT) (envelope-from llsousa@ualg.pt) Received: from ualg.pt (llsousa@corisco.uceh.ualg.pt [10.10.20.126]) by mozart.si.ualg.pt (8.9.1/8.9.1) with ESMTP id LAA283925; Thu, 28 Jun 2001 11:13:41 +0100 (WEST) Message-ID: <3B3B0355.CC5336C6@ualg.pt> Date: Thu, 28 Jun 2001 11:13:41 +0100 From: Luis Sousa Organization: Universidade do Algarve X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17-compact i686) X-Accept-Language: en, pt MIME-Version: 1.0 To: Tom Lane CC: pgsql sql Mailing List Subject: Re: Problems using a rule with the WHERE clause References: <3B3753FB.2E1BA9B0@ualg.pt> <16743.993492071@sss.pgh.pa.us> <3B385B5E.A8A31C59@ualg.pt> <3075.993566480@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------35D40F9BA571BD0EBFF15EEC" X-Archive-Number: 200106/441 X-Sequence-Number: 3738 This is a multi-part message in MIME format. --------------35D40F9BA571BD0EBFF15EEC Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > 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 --------------35D40F9BA571BD0EBFF15EEC Content-Type: application/x-gzip; name="example_that_works.tar.gz" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="example_that_works.tar.gz" H4sICN//OjsAA2V4YW1wbGVfdGhhdF93b3Jrcy50YXIA7drPT6NAFAfwXstf MemFNiGGGfrjYDywdnatW6EBqvFk2DJZid3Whanu/vf7gKKtazWadDcm38+h henw5j149DTZanG1uk1irRp7Y3Pb7ne7Ddu2+aA32PwmoksfDXvg8H5v0O93 Oc13uBg0mL2/lB6tch1njDXm83y5yuOd8177/YPKZ1l6q6+0yvVB/nO+lzVe fv62cHqiev5On45pHu9yIfD8/4Vh4E9Y5H4aS6bjb3PFD42nQ+LQqMbOR/KC 3aXqvmwXGj0OpBvJrctZ22imCUsXWn1XmWU0E5XPrjRnWv3SdKp/36ri9C7O Ztdx1uYdGlzebFwwCUZnbnDJvspL1k6TjtGhlZ5ZSuxYSmwvJd661MgLZRCx kRf5DzWlibWuw1oXYC1vOuzcHU9lyNrcMk+X1wvTMiPTsinIO2IIyxyr+zSn IBPT4u8L4lhmkBbFJhTGrcL8HUdsxBHrOOJpQcexTlVV0bPJvB6EKjpVeZ7O 4qom8b4wVFMY/4gX+jquinI2G6LsydZDU7aYGxqhHMvjiGl+QKGLr/qWFcd1 A8Y5Kw6rQeoKOl/eFGfioM6mOK6baD29GqynC6P5OfDP6qdDUU/9kVcXRldN w5H3pWytx7clmFIHt7LVXJUZ8yJl5ntsOhmWHe5vlmM0hz7drTCS7pB5fnRC 8XaGEq+EujiRgWT+eEgFCHbE+FZ0epvW17WqeuiKZigjVt+xI+bJi/KGUN3r YPT+HZUR0+TQaO6u0nljauLF1MR2amIjNb4ztf/9TwsAAAAAAAAAAAAAAAAA AACwH9j/h/1/2P+H/X/Y/4f9f9j/BwAAAAAAAAAAAAAAAAAA8DH9AUmlidMA UAAA --------------35D40F9BA571BD0EBFF15EEC Content-Type: application/x-gzip; name="script_def.tar.gz" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="script_def.tar.gz" H4sIAG8COzsAA+1bbW/bNhDOV+tXEP7iBKMDkrJkJ0E/OLXWGsjswHbaDRhg qDbbCXAkVZbddb9+R73YoiQr7YZkE6tDUUvHE8nnOfJ4YqhVwO2QL9f84+X2 8+bsWYRQQsxe74wQQvtGP/sLojPTgLK+Tk0TLPsE7HVT758h8jzdkWW3De0A obPNZuvttvZJu6fKayrdrtbtosX0NbLcMPiKxiPE0PkUfswB6V0ZF1psMbEf +TXie+6GHlp89eFmMby9s9D0i8uDa5TQI6y11zNruLCS8nb8TBuda622s17y NnLckH/iAY4U9zxwvLUnadfOduX4G8e12yjkf4ZCt7eD403o+F7mbhe4cgW+ HYT8aOC4a2eVa9h1Vo6ow3nkMAIeffTFCf+IbtFfnsujbuwCe2UnNQd7eyOU Af+029iBs7bXUOP20d5AP0MJiyW1ZK9CZw+VfPC8DbfdqHseYHGBUXHDH0/3 Qru40RIHjOzQRh+9QPaVLvkqoXw0XAwTV0X+GE/m1myBxpPFNOOO2Bk44wKc JR5HjOOYapxwjBNmcUopTonEB7awTBHO8oJTNnDEARbgL9C74d2DNUfnOjV0 PMCd4YY/dnAH/v3awVRcENxhEDS6xOwygmjvWifXhPxEKNgQHS6F3RXDnRAU 7t7hTmB30meMLushCg+wa0LFM0BqbShhgEqmZJbnpN+lg1OcUDJQkBS9R2RS 7iNObjOksC41ETWBhogUIkihQFEH97GalNACJSylxHE/Qlvh1yIJk4e7u+i/ emFlBay6slj1Ataeslh7BaxGFVZdrzVao4DWVBitWUDbVxhtv4B2oDDaQQHt lbIx6qqYehBVwRqledZQzrMMkWfpP0qeZZTmWUM13V+aZymKtTTPUhRraZ51 Gmu9VyejNM9SFm1pnqUs2tI8S1m0pXmWojGqPM9SE6xJooX11H4W6VLSJbpI mrKbfIdBrWKeZdICJaxASS+/76k2JaxslAxlShii/R+IEr1slAzzE4dcpRvB PwAlxeSOUmUDZzG3o1TRt3GDFF9RaGUmW2uwJcO4cnu01mCLaSytzNprDbaY xdLKreA6g6UlO2iVbyi1BlvcG6OV2961BltcepiqLygGLe4qMFV3vQ1W3PZk qu7xGqyYVDBVDw4YrGTOqrqjbbCSOavqMQlDL/mzTOWedq3BlgSoyoMStQZb shGorGfLDrZ9p2e1/AFcn2+3nr2x5HO4fvEgbmT2PzmemzZ8L+u5u/c2e+cx hrL2dh82HPkBXzlbx0vO1rprnpzbLSt/oeO4JtOl47iSE4qncvM+SjyEM375 9wM1wymWmcRZ2p4Yo8ToRX9GObXvpotjp9VTsFZoB6fQ3iqI1jRL0VJFfXt1 Cq2CvqWwtpQenE4X04FicOlJuLen4Yr4jt6NrfeHVVTcoPbaW0EXYFWIF7Ph XJtbd9brBRqN54vxBC74pcgnsB/9+hyD4ogeHkArb708akQ5ECJ+BCPRr6hb XGTXXXjQj68xXFzKK+OxcJm4BpYwkNQwWdHBzFmnBlB2ZDGqM8ux1vp5Nv0l /dAHUOR97nOt9f6tNbPQudRRnIWLY2wJtBQZNCycDb+xsy/QK3Tuy7X4UjV+ XI+fVOSnNflpVf6hLq2F0HAyAvbiYfEKPKu9mU0f7tHtb9XO+UZXFPmPNBVk Hp+IHaFNZyNrJvqTHwtR83HjUdMwEH930Cr+OC/YbfhzfZ33Ak088f0foYae fP9n9CnRQQ+hymDN938vIaPZ9B7NHsR7ghgDSxHiaPtGK9OzE3od9Gm0LFQl gs90gh7uR9EryTQfSrXWaAqry3xhDUdoMl28HU/e3Gin6mPfUF8cnaZ3o8tD 0INAo+tUagpeg5I6chEOAujcWqDjtIaHJ9b7zDwHi7iNTHyPbeRpDXZxTILZ nRjA1UErpnuiFpdHvcCRFojrQ0l2XUg6Fd+WWIjlQbJJV4gbrXVx0l/6P+e3 R76L32ycTDoqr0NlHItGKzkWBiUcC3Upx1FBBcei/CmOMzYyx//13P4WCXbu cuevYQ14vjaeiP/iC/Bj/Dei778po038fwlJ5qg0x5EWzVCUC58itczNWiZ0 UqQ0WmmAkOcpFMV5uCbN187hPpmpkKIjLTdJqZFqkmkGukFOtTx2dVCPmddI I4000kgjjTTSSCONNNJIIy8nfwNECP6qAFAAAA== --------------35D40F9BA571BD0EBFF15EEC-- From tgl@sss.pgh.pa.us Thu Jun 28 15:55:23 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5SFtNa97949 for ; Thu, 28 Jun 2001 11:55:23 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5SFt6F24521; Thu, 28 Jun 2001 11:55:06 -0400 (EDT) To: Luis Sousa cc: pgsql sql Mailing List Subject: Re: Problems using a rule with the WHERE clause In-reply-to: <3B3B0355.CC5336C6@ualg.pt> References: <3B3753FB.2E1BA9B0@ualg.pt> <16743.993492071@sss.pgh.pa.us> <3B385B5E.A8A31C59@ualg.pt> <3075.993566480@sss.pgh.pa.us> <3B3B0355.CC5336C6@ualg.pt> Comments: In-reply-to Luis Sousa message dated "Thu, 28 Jun 2001 11:13:41 +0100" Date: Thu, 28 Jun 2001 11:55:05 -0400 Message-ID: <24518.993743705@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/452 X-Sequence-Number: 3755 Luis Sousa 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