PostgreSQL 规则有什么用? [英] What are PostgreSQL RULEs good for?

问题描述

我经常看到它说应避免使用规则，而应使用触发器.我可以看到规则系统中的危险，但肯定有规则的有效用途，对吗?它们是什么?

I often see it stated that rules should be avoided and triggers used instead. I can see the danger in the rule system, but certainly there are valid uses for rules, right? What are they?

我是出于普遍兴趣提出这个问题；我对数据库不太熟悉.

I'm asking this out of general interest; I'm not very seasoned with databases.

例如，过去我需要锁定某些数据，所以我做了这样的事情:

For instance, in the past I've needed to lock down certain data, so I've done something like this:

CREATE OR REPLACE RULE protect_data AS
  ON UPDATE TO exampletable             -- another similar rule for DELETE
  WHERE OLD.type = 'protected'
  DO INSTEAD NOTHING;

然后如果我想编辑受保护的数据:

Then if I want to edit the protected data:

START TRANSACTION;
  ALTER TABLE exampletable DISABLE RULE protect_data;
  -- edit data as I like
  ALTER TABLE exampletable ENABLE RULE protect_data;
COMMIT;

我同意这是 hacky，但在这种情况下我无法更改访问数据库的应用程序(甚至抛出错误).所以奖励积分是为了找到对规则系统的危险/无效使用，但不是为什么这是糟糕的设计.

I agree this is hacky, but I couldn't change the application(s) accessing the database in this case (or even throw errors at it). So bonus points for finding a reason why this is a dangerous/invalid use of the rule system, but not for why this is bad design.

推荐答案

RULES 的用例之一是可更新的视图(尽管 9.1 中的更改为该版本引入了视图的 INSTEAD OF 触发器)

One of the use cases for RULES are updateable views (although that changes in 9.1 as that version introduces INSTEAD OF triggers for views)

可以在手册中找到另一个很好的解释:

Another good explanation can be found in the manual:

对于两者都可以实现的东西，最好看数据库的使用情况.为任何受影响的行触发一次触发器.规则操作查询或生成附加查询.因此，如果在一个语句中影响多行，则发出一个额外命令的规则可能比为每一行调用且必须多次执行其操作的触发器更快.但是，触发器方法在概念上比规则方法简单得多，新手更容易上手.

For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right.

(取自:http://www.postgresql.org/docs/current/static/rules-triggers.html)

这篇关于PostgreSQL 规则有什么用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！