PostgreSQL 的规则和 nextval()/serial 问题（非常 PostgreSQL 特有的）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1324377/
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
PostgreSQL's rules and nextval()/serial problem (very PostgreSQL-specific)
提问by j.p.
When I use a rewrite rule that splits an insert into one table into inserts to two other tables where one of the inserted values has as default nextval('some_sequence') with the same sequence for both tables, then the inserted default values are different in the two tables. This is probably due to simple text replacement by the rewrite rule. I had hoped instead that the default value would be first resolved and then the same value be written to both tables.
当我使用重写规则将插入到一个表中后插入到另外两个表中，其中插入的值之一作为默认 nextval('some_sequence') 两个表的序列相同，则插入的默认值在两张桌子。这可能是由于重写规则进行了简单的文本替换。我原本希望首先解析默认值，然后将相同的值写入两个表。
Here an example (as you probably guess, I'm trying to implement specialization/generalization using rules):
这是一个示例（正如您可能猜到的，我正在尝试使用规则实现专业化/泛化）：
-- first and third commands can be skipped if id is defined as serial
create sequence parents_id_seq;
create table Parents(
id integer default(nextval('parents_id_seq')) primary key,
type varchar(50) not null check(type in ('Child1', 'Child2')),
unique (id, type),
attribute1 varchar(50) not null unique check(length(attribute1) > 0)
);
alter sequence parents_id_seq owned by parents.id;
The data specific to children of the first kind is kept in
特定于第一类儿童的数据保存在
create table Partial_Children1(
id integer default(nextval('parents_id_seq')) primary key,
type varchar(50) not null check(type = 'Child1'),
foreign key (id, type) references Parents(id, type),
attribute2 varchar(50) not null check(length(attribute2) > 0)
);
Next I defined a view Children1 that joins both tables above (I rewrote the view by stating explicitly what PostgreSQL does to define views according to the documentation)
接下来我定义了一个连接上面两个表的视图 Children1 （我通过明确说明 PostgreSQL 根据文档定义视图所做的工作来重写视图）
create table Children1(
id int default(nextval('parents_id_seq')),
type varchar(50) not null check(type in ('Child1')),
attribute1 varchar(50) not null check(length(attribute1) > 0),
attribute2 varchar(50) not null check(length(attribute2) > 0)
);
create rule "_RETURN" as on select to Children1 do instead
select p.*, c.attribute2
from Parents p
join Partial_Children1 c
on p.id = c.id;
Finally the rewrite rule I'm having problems with:
最后，我遇到了问题的重写规则：
create rule ct_i_children1 as
on insert to Children1
do instead (
insert into Parents(attribute1, type)
values(new.attribute1, 'Child1');
insert into Partial_Children1(attribute2, type)
values(new.attribute2, 'Child1');
);
Trying to insert data with
尝试插入数据
insert into Children1 (attribute1, attribute2)
values ('a1', 'a2'),
('b1', 'b2');
yields the error message
产生错误信息
ERROR: insert or update on table "partial_children1" violates foreign key constraint "partial_children1_id_fkey"
DETAIL: Key (id,type)=(3,Child1) is not present in table "parents".
A way to solve this is replacing the second insert of the rewrite rule by
解决此问题的一种方法是将重写规则的第二个插入替换为
insert into Partial_Children1(id, attribute2, type)
select p.id, new.attribute2, p.type
from Parents p
where p.attribute1 = new.attribute1
but this relies on the uniqueness of attribute1, which I don't want to impose. Another solution would be to insert the values first into an temporary table, and then to select twice from there for the insertions into the two tables. But I don't like it because of performance reasons.
但这依赖于我不想强加的 attribute1 的唯一性。另一种解决方案是先将值插入到临时表中，然后从那里选择两次以插入到两个表中。但由于性能原因，我不喜欢它。
Does anyone have another idea how to get the same default values in both tables (just using rules and nottriggers)?
有没有人有其他想法如何在两个表中获得相同的默认值（仅使用规则而不是触发器）？
采纳答案by Janning
From the docs http://www.postgresql.org/docs/8.4/static/rules.html
从文档 http://www.postgresql.org/docs/8.4/static/rules.html
It (The Rule system) modifies queries to take rules into consideration, and then passes the modified query to the query planner for planning and execution
它（规则系统）修改查询以考虑规则，然后将修改后的查询传递给查询计划器进行计划和执行
so it first rewrites the queries without executing anything.
所以它首先重写查询而不执行任何操作。
you can make it work when you do not insert multipe records at once:
当您不一次插入多条记录时，您可以使其工作：
create or replace rule ct_i_children1 as
on insert to Children1
do instead (
insert into Parents(id, attribute1, type)
values(nextval('parents_id_seq'), new.attribute1, 'Child1');
insert into Partial_Children1(id, attribute2, type)
values(currval('parents_id_seq'), new.attribute2, 'Child1');
);
Then you can do:
然后你可以这样做：
insert into Children1 (attribute1, attribute2) values ('a1', 'a2');
insert into Children1 (attribute1, attribute2) values ('b1', 'b2');
but not
但不是
insert into Children1 (attribute1, attribute2)
values ('a1', 'a2'),
('b1', 'b2');
So you really should not use the rules system with tricky currval() calls.
所以你真的不应该使用带有棘手的 currval() 调用的规则系统。
Additionally take a look at the comments on these pages:
另外看看这些页面上的评论：
- http://www.postgresql.org/docs/8.2/interactive/rules-update.html
- http://archives.postgresql.org/pgsql-sql/2004-10/msg00195.php
- http://archives.postgresql.org/pgsql-general/2009-06/msg00278.php
- http://www.postgresql.org/docs/8.2/interactive/rules-update.html
- http://archives.postgresql.org/pgsql-sql/2004-10/msg00195.php
- http://archives.postgresql.org/pgsql-general/2009-06/msg00278.php
Another tip: the support at the postgresql mailing list is as excellent as the database engine itself!
另一个提示：postgresql 邮件列表的支持与数据库引擎本身一样出色！
And by the way: do your know that postgresql has support for inheritance out-of-the-box?
顺便说一句：您知道 postgresql 支持开箱即用的继承吗？
Summary: you should use triggers or avoid multiple row inserts!
总结：您应该使用触发器或避免多行插入！
回答by Magnus Hagander
Rules will do that for you - they rewrite the query before it's executed.
规则会为您做到这一点 - 他们在执行之前重写查询。
As long as you have an actual table for the base (Children1), I think you'll be able to accomplish the same thing with a TRIGGER instead of a RULE.
只要你有一个实际的基础表（Children1），我认为你就可以用触发器而不是规则来完成同样的事情。
