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

问题描述

我经常看到，而不是使用自动递增序列/ 2253594＃2253594>规则。我可以看到规则系统中的危险，但肯定有规则的有效用途，对吗？他们是什么？

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的一个用例是可更新的视图版本为视图引入了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屋！