Re: Rule ON DELETE, to perform to DELETE querys !

Hello Tom

We didn't correct yet the bug that restarts the postmaster, but we already find out
a way of deleting in two tables.
Using inside in the rule for delete a function that deletes in two tables we can get
the results that we want.

There's the code that i used to do that:

----- RULE:

CREATE RULE "deleteturnodocente" AS ON DELETE TO "docentesturno"
 DO INSTEAD (
 SELECT

delete_pessoalevento_pessoal(OLD.cod_disciplina,OLD.var,OLD.tipo,OLD.turno,OLD.periodo,OLD.periodo_pessoal,OLD.idpessoal)
AS ok;
 );

----- FUNCTIONS:

CREATE FUNCTION
delete_pessoalevento_pessoal(text,text,text,integer,integer,integer,integer)
RETURNS boolean AS '
 DECLARE
  f_disciplina ALIAS FOR $1;
  f_var ALIAS FOR $2;
  f_tipo ALIAS FOR $3;
  f_turno ALIAS FOR $4;
  f_id_periodo ALIAS FOR $5;
  f_id_periodo_pe ALIAS FOR $6;
  f_id_pessoal ALIAS FOR $7;

 BEGIN
  DELETE FROM "pessoalEvento"
   WHERE disciplina = f_disciplina
     AND var = f_var
     AND tipo = f_tipo
     AND turno = f_turno
     AND "idPeriodo" = f_id_periodo
     AND "idPeriodoPe" = f_id_periodo_pe
     AND "idPessoal" = f_id_pessoal;

         DELETE FROM evento
   WHERE disciplina = f_disciplina
     AND var = f_var
     AND tipo = f_tipo
     AND turno = f_turno
     AND "idPeriodo" = f_id_periodo
     AND "idPeriodoE" = f_id_periodo_pe;

  RETURN 1;
 END;'
LANGUAGE 'plpgsql';

Tom Lane wrote:

> Luis Sousa <llsousa@ualg.pt> writes:
> >     CREATE RULE "deletetables" AS ON DELETE TO "tables"
> >          DO INSTEAD (
> >          DELETE FROM table2
> >                 WHERE id = OLD.id;
> >          DELETE FROM table1
> >                 WHERE id=OLD.id
> >            );
>
> It turns out you are running into the same problem as Pete Leonard:
> both the same 7.1 bug, and the same difficulty that your rule won't
> work even without the bug.  See attached.
>
>                         regards, tom lane
>
> ------- Forwarded Message
>
> Date:    Tue, 12 Jun 2001 14:05:36 -0400
> From:    Tom Lane <tgl@sss.pgh.pa.us>
> To:      Pete Leonard <pete@hero.com>
> cc:      pgsql-general@postgresql.org
> Subject: Re: [GENERAL] stumped on view/rule/delete problem.
>
> Pete Leonard <pete@hero.com> writes:
> > create table foo (
> > id    serial,
> > name  varchar(50)
> > );
>
> > create table bar (
> > foo_id        integer,
> > name2 varchar(50)
> > );
>
> > create view foobar as
> > select f.id, f.name, b.name2 from foo f, bar b where (f.id = b.foo_id);
>
> > create rule delete_foobar as on delete to foobar
> > do instead (
> > delete from foo where id=OLD.id;
> > delete from bar where foo_id = OLD.id;
> > );
>
> > running the command
> > delete from foobar where id=1;
> > causes the DB to hang.  only way out is an immediate restart of the DB.
>
> This is a bug, for which I propose the attached patch against 7.1.2
> (it'll apply to 7.1 also, but you might as well update to 7.1.2 before
> recompiling...).
>
> However, the above rule will not produce the result you want anyway,
> because OLD is essentially a macro for the view.  As soon as you delete
> a row from foo, there's no longer any such row in the view, so the
> delete from bar doesn't find anything to delete.  Example:
>
> regression=# insert into foo values(1,'a');
> INSERT 157940 1
> regression=# insert into foo values(2,'b');
> INSERT 157941 1
> regression=# insert into bar values(1,'aa');
> INSERT 157942 1
> regression=# insert into bar values(2,'bb');
> INSERT 157943 1
> regression=# select * from foobar;
>  id | name | name2
> ----+------+-------
>   1 | a    | aa
>   2 | b    | bb
> (2 rows)
>
> regression=# delete from foobar where id=1;
> DELETE 0
> regression=# select * from foobar;
>  id | name | name2
> ----+------+-------
>   2 | b    | bb
> (1 row)
>
> regression=# select * from foo;
>  id | name
> ----+------
>   2 | b
> (1 row)
>
> regression=# select * from bar;
>  foo_id | name2
> --------+-------
>       1 | aa
>       2 | bb
> (2 rows)
>
> What you probably want instead is to make bar reference foo as a foreign
> key with ON DELETE CASCADE; then the rule for foobar only needs to
> delete from foo explicitly, and the additional delete from bar is done
> implicitly by the foreign key trigger.
>
>                         regards, tom lane
>
> *** src/backend/rewrite/rewriteHandler.c.orig   Thu May  3 13:47:49 2001
> --- src/backend/rewrite/rewriteHandler.c        Tue Jun 12 13:32:49 2001
> ***************
> *** 82,88 ****
>
>         /*
>          * Adjust rule action and qual to offset its varnos, so that we can
> !        * merge its rtable into the main parsetree's rtable.
>          *
>          * If the rule action is an INSERT...SELECT, the OLD/NEW rtable entries
>          * will be in the SELECT part, and we have to modify that rather than
> --- 82,88 ----
>
>         /*
>          * Adjust rule action and qual to offset its varnos, so that we can
> !        * merge its rtable with the main parsetree's rtable.
>          *
>          * If the rule action is an INSERT...SELECT, the OLD/NEW rtable entries
>          * will be in the SELECT part, and we have to modify that rather than
> ***************
> *** 99,121 ****
>                                    PRS2_OLD_VARNO + rt_length, rt_index, 0);
>
>         /*
> !        * We want the main parsetree's rtable to end up as the concatenation
> !        * of its original contents plus those of all the relevant rule
> !        * actions.  Also store same into all the rule_action rtables. Some of
> !        * the entries may be unused after we finish rewriting, but if we
> !        * tried to clean those out we'd have a much harder job to adjust RT
> !        * indexes in the query's Vars.  It's OK to have unused RT entries,
> !        * since planner will ignore them.
>          *
> !        * NOTE KLUGY HACK: we assume the parsetree rtable had at least one entry
> !        * to begin with (OK enough, else where'd the rule come from?).
> !        * Because of this, if multiple rules nconc() their rtable additions
> !        * onto parsetree->rtable, they'll all see the same rtable because
> !        * they all have the same list head pointer.
> !        */
> !       parsetree->rtable = nconc(parsetree->rtable,
> !                                                         sub_action->rtable);
> !       sub_action->rtable = parsetree->rtable;
>
>         /*
>          * Each rule action's jointree should be the main parsetree's jointree
> --- 99,117 ----
>                                    PRS2_OLD_VARNO + rt_length, rt_index, 0);
>
>         /*
> !        * Generate expanded rtable consisting of main parsetree's rtable
> !        * plus rule action's rtable; this becomes the complete rtable for the
> !        * rule action.  Some of the entries may be unused after we finish
> !        * rewriting, but if we tried to clean those out we'd have a much harder
> !        * job to adjust RT indexes in the query's Vars.  It's OK to have unused
> !        * RT entries, since planner will ignore them.
>          *
> !        * NOTE: because planner will destructively alter rtable, we must ensure
> !        * that rule action's rtable is separate and shares no substructure with
> !        * the main rtable.  Hence do a deep copy here.
> !        */
> !       sub_action->rtable = nconc((List *) copyObject(parsetree->rtable),
> !                                                          sub_action->rtable);
>
>         /*
>          * Each rule action's jointree should be the main parsetree's jointree
> ***************
> *** 128,133 ****
> --- 124,132 ----
>          * data for the quals.  We don't want the original rtindex to be
>          * joined twice, however, so avoid keeping it if the rule action
>          * mentions it.
> +        *
> +        * As above, the action's jointree must not share substructure with
> +        * the main parsetree's.
>          */
>         if (sub_action->jointree != NULL)
>         {
> ***************
> *** 193,205 ****
>    * occurrence of the given rt_index as a top-level join item (we do not look
>    * for it within join items; this is OK because we are only expecting to find
>    * it as an UPDATE or DELETE target relation, which will be at the top level
> !  * of the join).  Returns modified jointree list --- original list is not
> !  * changed.
>    */
>   static List *
>   adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
>   {
> !       List       *newjointree = listCopy(parsetree->jointree->fromlist);
>         List       *jjt;
>
>         if (removert)
> --- 192,204 ----
>    * occurrence of the given rt_index as a top-level join item (we do not look
>    * for it within join items; this is OK because we are only expecting to find
>    * it as an UPDATE or DELETE target relation, which will be at the top level
> !  * of the join).  Returns modified jointree list --- this is a separate copy
> !  * sharing no nodes with the original.
>    */
>   static List *
>   adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
>   {
> !       List       *newjointree = copyObject(parsetree->jointree->fromlist);
>         List       *jjt;
>
>         if (removert)
>
> ------- End of Forwarded Message
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster