防止CTE查询的Postgres规则 [英] Postgres Rules Preventing CTE Queries

问题描述

使用Postgres 9.3：

Using Postgres 9.3:

在另一个表上执行插入操作时，我试图自动填充一个表。这似乎是规则的很好用法，但是在将规则添加到第一个表之后，我不再能够使用可写CTE在第二个表中执行插入操作。这是一个示例：

I am attempting to automatically populate a table when an insert is performed on another table. This seems like a good use for rules, but after adding the rule to the first table, I am no longer able to perform inserts into the second table using the writable CTE. Here is an example:

CREATE TABLE foo (
    id INT PRIMARY KEY
);

CREATE TABLE bar (
    id INT PRIMARY KEY REFERENCES foo
);

CREATE RULE insertFoo AS ON INSERT TO foo DO INSERT INTO bar VALUES (NEW.id);

WITH a AS (SELECT * FROM (VALUES (1), (2)) b)
INSERT INTO foo SELECT * FROM a

运行此命令时，出现错误

When this is run, I get the error

ERROR ：WITH不能用于由规则
重写为多个查询的查询中。

"ERROR: WITH cannot be used in a query that is rewritten by rules into multiple queries".

我已经搜索了该内容错误字符串，但仅能找到源代码的链接。我知道我可以使用行级触发器来执行上述操作，但是看来我应该能够在语句级执行此操作。为什么这样的查询可以（在这种情况下）很容易地重写为：

I have searched for that error string, but am only able to find links to the source code. I know that I can perform the above using row-level triggers instead, but it seems like I should be able to do this at the statement level. Why can I not use the writable CTE, when queries like this can (in this case) be easily re-written as:

INSERT INTO foo SELECT * FROM (VALUES (1), (2)) a

有人知道吗另一种可以完成我尝试做的事情的方法，除了1）使用规则，它可以防止使用 with查询，或者2）使用行级触发器？谢谢，

Does anyone know of another way that would accomplish what I am attempting to do other than 1) using rules, which prevents the use of "with" queries, or 2) using row-level triggers? Thanks,         

推荐答案

TL; DR：使用触发器，而不是规则。

TL;DR: use triggers, not rules.

通常来说，除非规则是绝对必要的，否则更喜欢触发器而不是规则。 （实际上，从来没有。）

Generally speaking, prefer triggers over rules, unless rules are absolutely necessary. (Which, in practice, they never are.)

使用规则会引入很多问题，这些问题不必要地会使您的生活复杂化。您在这里碰到了一个。另一个（主要）问题是，例如，受影响的行数将与最后一个查询的行数相对应-如果您依赖某处的FOUND并且您的查询错误地报告没有任何行受到查询的影响，您会遇到痛苦的错误。

Using rules introduces heaps of problems which will needlessly complicate your life down the road. You've run into one here. Another (major) one is, for instance, that the number of affected rows will correspond to that of the very last query -- if you're relying on FOUND somewhere and your query is incorrectly reporting that no rows were affected by a query, you'll be in for painful bugs.

此外，偶尔还会有人谈论彻底弃用Postgres规则：

Moreover, there's occasional talk of deprecating Postgres rules outright:

http://postgresql.nabble.com/Deprecating-RULES-td5727689.html

这篇关于防止CTE查询的Postgres规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！