PostgreSQL的规则和nextval（）/序列问题（非常特定于PostgreSQL） [英] PostgreSQL's rules and nextval()/serial problem (very PostgreSQL-specific)

问题描述

当我使用重写规则将插入到一个表的拆分成另外两个表的插入时，其中一个插入值的默认值为nextval（'some_sequence'），两个表的序列相同，然后插入的默认值在两个表中是不同的。这可能是由于重写规则可以简单地替换文本。我本来希望可以先解析默认值，然后将相同的值写入两个表。

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.

这里有个例子（您可能猜到了，尝试使用规则实现专业化/一般化）：

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)
);

下一步，我定义了一个ViewChild1视图，该视图将上面的两个表都连接在一起（我通过明确说明PostgreSQL是什么重写了该视图确实根据文档定义了视图）

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)

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');
  );

尝试插入数据，

insert into Children1 (attribute1, attribute2)
  values ('a1', 'a2'),
         ('b1', 'b2');

产生错误消息

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

但这取决于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.

有人有另一个想法如何在两个表中获得相同的默认值（仅使用规则和不是触发器）？

Does anyone have another idea how to get the same default values in both tables (just using rules and not triggers)?

推荐答案

来自文档
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');

但不是

insert into Children1 (attribute1, attribute2)
  values ('a1', 'a2'),
         ('b1', 'b2');

因此，您真的不应该将规则系统与棘手的currval（）调用一起使用。

So you really should not use the rules system with tricky currval() calls.

另外看看这些页面上的评论：

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

另一个提示：postgresql邮件列表中的支持与数据库引擎本身一样出色！

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?

• http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html

• http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html

摘要：您应该使用触发器或避免多次插入行！

Summary: you should use triggers or avoid multiple row inserts!

这篇关于PostgreSQL的规则和nextval（）/序列问题（非常特定于PostgreSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！