PostgreSQL 规则有什么用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5894142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are PostgreSQL RULEs good for?
提问by StackExchange saddens dancek
Question
问题
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.
我问这个是出于普遍的兴趣；我对数据库不是很熟悉。
Example of what might be a valid use
可能是有效用途的示例
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;
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 pointsfor finding a reason why this is a dangerous/invalid use of the rule system, but notfor why this is bad design.
我同意这是 hacky，但在这种情况下我无法更改访问数据库的应用程序（甚至抛出错误）。因此，找到一个为什么这是对规则系统的危险/无效使用的原因的奖励积分，而不是为什么这是一个糟糕的设计。
采纳答案by a_horse_with_no_name
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)
RULES 的用例之一是可更新的视图（尽管 9.1 中的更改为该版本引入了视图的 INSTEAD OF 触发器）
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.
对于两者都可以实现的东西，最好取决于数据库的使用情况。为任何受影响的行触发一次触发器。规则操作查询或生成附加查询。因此，如果在一个语句中影响多行，则发出一个额外命令的规则可能比为每一行调用且必须多次执行其操作的触发器更快。然而，触发器方法在概念上比规则方法简单得多，而且新手更容易上手。
(Taken from: http://www.postgresql.org/docs/current/static/rules-triggers.html)
（取自：http: //www.postgresql.org/docs/current/static/rules-triggers.html）
回答by a_horse_with_no_name
Some problems with rules are shown here: http://www.depesz.com/index.php/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/(for instance, if a random() is included in a query, it might get executed twice and return different values).
这里显示了一些规则问题：http: //www.depesz.com/index.php/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/（对于例如，如果一个 random() 包含在查询中，它可能会被执行两次并返回不同的值）。
The biggest drawback of rules is that people don't understandthem.
规则的最大缺点是人们不理解它们。
For example, one might think that having the rule:
例如，人们可能认为拥有规则：
CREATE OR REPLACE RULE protect_data AS
ON UPDATE TO exampletable -- another similar rule for DELETE
WHERE OLD.type = 'protected'
DO INSTEAD NOTHING;
Will mean that if I'll issue:
将意味着，如果我会发出：
update exampletable set whatever = whatever + 1 where type = 'protected'
No query will be ran. Which is not true. The query willbe run, but it will be ran in modified version - with added condition.
不会运行任何查询。这不是真的。查询将运行，但它会在修改后的版本中运行 - 添加条件。
What's more - rules break very useful thing, that is returning clause:
更重要的是 - 规则打破了非常有用的东西，即返回子句：
$ update exampletable set whatever = whatever + 1 where type = 'normal' returning *;
ERROR: cannot perform UPDATE RETURNING on relation "exampletable"
HINT: You need an unconditional ON UPDATE DO INSTEAD rule with a RETURNING clause.
To wrap it - if you really, really, positively have to use writable views, and you're using pre 9.1 PostgreSQL - you mighthave a valid reason to use rules.
包装它 - 如果您真的，真的，肯定必须使用可写视图，并且您使用的是 9.1 之前的 PostgreSQL - 您可能有使用规则的正当理由。
In all other cases - you're most likely shooting yourself in a foot, even if you don't immediately see it.
在所有其他情况下 - 即使您没有立即看到它，您也很可能用脚射击自己。
回答by Denis de Bernardy
I've had a few bitter experiences with rules when dealing with volatile functions (if memory serves, depesz' blog post highlights some of them).
在处理可变函数时，我对规则有过一些痛苦的经历（如果没记错的话，depesz 的博客文章重点介绍了其中的一些）。
I've also broken referential integrity when using them because of the timing in which fkey triggers get fired:
由于 fkey 触发器被触发的时间，我在使用它们时也破坏了参照完整性：
CREATE OR REPLACE RULE protected_example AS
ON DELETE TO example
WHERE OLD.protected
DO INSTEAD NOTHING;
... then add another table, and make example reference that table with an on delete cascade foreign key. Then, delete * from that table... and recoil in horror.
...然后添加另一个表，并使用 on delete 级联外键对该表进行示例引用。然后，从该表中删除 * ......并惊恐地后退。
I reported the above issue as a bug, which got dismissed as a feature/necessary edge case. It's only months later that I made sense of why that might be, i.e. the fkey trigger does its job, and the rule then kicks in and does its own, but the fkey trigger won't check that its job was done properly for performance reasons.
我将上述问题报告为一个错误，该错误被视为功能/必要的边缘情况而被驳回。仅仅几个月后，我才明白为什么会这样，即 fkey 触发器完成它的工作，然后规则启动并执行它自己的工作，但是 fkey 触发器不会因为性能原因检查它的工作是否正确完成.
The practical use-case where I still use rules is when a BEFOREtrigger that pre-manipulates data (the SQL standard says is not allowed, but Postgres will happily oblige) can result in pre-manipulating the affected rows and thus changing their ctid (i.e. it gets updated twice, or doesn't get deleted because an update invalidated the delete).
我仍然使用规则的实际用例是当一个BEFORE预先操作数据的触发器（SQL 标准说是不允许的，但 Postgres 很乐意帮忙）可能导致预先操作受影响的行并因此改变它们的 ctid（即它被更新两次，或者没有被删除，因为更新使删除无效）。
This results in Postgres returning an incorrect number of affected rows, which is no big deal until you monitor that number before issuing subsequent statements.
这会导致 Postgres 返回错误数量的受影响行，这没什么大不了的，除非您在发出后续语句之前监控该数量。
In this case, I've found that using a strategically placed rule or two can allow to pre-emptively execute the offending statement(s), resulting in Postgres returning the correct number of affected rows.
在这种情况下，我发现使用一两个策略性规则可以允许抢先执行违规语句，从而导致 Postgres 返回正确数量的受影响行。
回答by musiKk
How about this: You have a table that needs to be changed into a view. In order to support legacy applications that insert into said table, a rule is created that maps "inserts" to the new view to the underlying table(s).
怎么样：您有一个需要更改为视图的表。为了支持插入到所述表中的遗留应用程序，创建了将“插入”映射到基础表的新视图的规则。
