Thread: Rule ON DELETE, to perform to DELETE querys !
I have a view over a join of tables and when it's performed over the
view a delete i want to delete records in two different tables. The code
that i wrote was:
CREATE RULE "deletetables" AS ON DELETE TO "tables"
DO INSTEAD (
DELETE FROM table2
WHERE id = OLD.id;
DELETE FROM table1
WHERE id=OLD.id
);
table2 references table1 by the field id only for update. I don't them
to be referenced by delete !
When i execute: DELETE FROM tables WHERE id=1; i got these message from
postgres:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The records exists in both tables !!!!!!!
This already happen with some of you ???? How can i do this ????
Thanks
Luis Sousa
Sorry, but it wasn't this list that i wanted to send this post !! I already send it to pgsql-sql@postgresql.org !! Luis Sousa wrote: > I have a view over a join of tables and when it's performed over the > view a delete i want to delete records in two different tables. The code > that i wrote was: > > CREATE RULE "deletetables" AS ON DELETE TO "tables" > DO INSTEAD ( > DELETE FROM table2 > WHERE id = OLD.id; > DELETE FROM table1 > WHERE id=OLD.id > ); > > table2 references table1 by the field id only for update. I don't them > to be referenced by delete ! > > When i execute: DELETE FROM tables WHERE id=1; i got these message from > postgres: > > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > The records exists in both tables !!!!!!! > > This already happen with some of you ???? How can i do this ???? > > Thanks > > Luis Sousa > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Luis Sousa <llsousa@ualg.pt> writes:
> When i execute: DELETE FROM tables WHERE id=1; i got these message from
> postgres:
> pqReadData() -- backend closed the channel unexpectedly.
What PG version are you running?
If it's a current release (7.0.3 or 7.1.2), please provide a
self-contained psql script that demonstrates the problem.
regards, tom lane
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
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