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(at)ualg(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
> To: Pete Leonard <pete(at)hero(dot)com>
> cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] stumped on view/rule/delete problem.
>
> Pete Leonard <pete(at)hero(dot)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