Re: stumped on view/rule/delete problem. - Mailing list pgsql-general

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)