如何在视图上编写删除规则? [英] How to write a delete rule on a view?

问题描述

我正在尝试在视图上编写规则以从组件表中删除元组，但到目前为止只能从其中一个中删除数据.我已经使用带有基本视图的 postgres 一段时间了，但我对视图规则没有任何经验.

I'm trying to write a rule on a view to delete tuples from the component tables, but so far can only remove data from one of them. I've used postgres with basic views for a while, but I don't have any experience with rules on views.

我写了一个愚蠢的小测试用例来找出/展示我的问题.在这个例子中，每个子元组只有一个父元组(我的实际模式当然不是这样的).

I wrote a stupid little test case to figure out/show my problem. There's only one parent tuple per child tuple in this example (my actual schema isn't actually like this of course).

组件表:

CREATE TABLE parent(
   id serial PRIMARY KEY,
   p_data integer NOT NULL UNIQUE
);
CREATE TABLE child(
   id serial PRIMARY KEY,
   parent_id integer NOT NULL UNIQUE REFERENCES parent(id),
   c_data integer NOT NULL
);

查看:

CREATE TABLE child_view(
   id integer,
   p_data integer,
   c_data integer
);
CREATE RULE "_RETURN" AS ON SELECT TO child_view DO INSTEAD
   SELECT child.id, p_data, c_data 
      FROM parent JOIN child ON (parent_id=parent.id);

问题删除规则

CREATE RULE child_delete AS ON DELETE TO child_view DO INSTEAD(
   DELETE FROM child WHERE id=OLD.id;
   DELETE FROM parent WHERE p_data=OLD.p_data;
);

上述规则的目的是从组件表中删除视图中引用的元组.WHERE p_data=OLD.p_data 对我来说似乎很奇怪，但我不知道如何在父表中引用所需的元组.

The intent of the above rule is to remove tuples referenced in the view from the component tables. The WHERE p_data=OLD.p_data seems odd to me, but I don't see how else to reference the desired tuple in the parent table.

当我尝试使用上述规则时会发生以下情况:

Here's what happens when I try to use the above rule:

>SELECT * FROM child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |     10
  2 |      2 |     11
  3 |      3 |     12
(3 rows)

>DELETE FROM child_view WHERE id=3;
DELETE 0

>SELECT * FROM child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |     10
  2 |      2 |     11
(2 rows)

但是查看父表，删除的第二部分不起作用(id=3应该"已删除):

But looking at the parent table, the second part of the delete isn't working (id=3 "should" have been deleted):

>SELECT * FROM parent;
 id | p_data 
----+--------
  1 |      1
  2 |      2
  3 |      3
(3 rows)

我应该如何编写删除规则来删除子元组和父元组?

How should I write the deletion rule to remove both child and parent tuples?

这是使用 postgres v9.

This is using postgres v9.

感谢任何帮助.也将不胜感激指向任何涵盖 postgres 文档之外的视图规则的材料(除非我明显遗漏了某些内容).谢谢.

Any help is appreciated. Also pointers to any materials covering rules on views beyond the postgres docs (unless I've obviously missed something) would also be appreciated. Thanks.

正如 jmz 指出的，在这里使用级联删除比使用规则更容易，但这种方法不适用于我的实际架构.

as jmz points out, it would be easier to use a cascading delete than a rule here, but that approach doesn't work for my actual schema.

推荐答案

您所看到的规则问题是规则系统不能以原子方式处理数据.无论 DO INSTEAD 规则中两条语句的顺序如何，都会执行第一个删除.第二条语句永远不会执行，因为 OLD.id 所指的行已从视图中删除.您可以使用 LEFT JOIN，但这对您没有帮助，因为示例表设计(它可能适用于您的实际数据库架构).

What you're seeing with the rule problem is that the rule system doesn't handle the data atomically. The first delete is executed regardless of the order of the two statements in the DO INSTEAD rule. The second statement is never executed, because the row to which OLD.id refers to has been removed from the view. You could use a LEFT JOIN, but that won't help you because of the example table design (it may work on your actual database schema).

在我看来，根本问题在于您将规则系统视为触发器.

您最好的选择是使用外键和 ON DELETE CASCADE 而不是规则.有了它们，您的示例架构也可以工作:您只需要删除父表即可删除所有子表.

Your best option is to use foreign keys and ON DELETE CASCADE instead of rules. With them your example schema would work too: You'd only need on delete for the parent table to get rid of all the children.

这篇关于如何在视图上编写删除规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！