如何使用分区表插入和选择数据？
我根据 http:// 上的文档设置了一组分区表www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
CREATE TABLE t (year, a);
CREATE TABLE t_1980 ( CHECK (year = 1980) ) INHERITS (t);
CREATE TABLE t_1981 ( CHECK (year = 1981) ) INHERITS (t);
CREATE RULE t_ins_1980 AS ON INSERT TO t WHERE (year = 1980)
DO INSTEAD INSERT INTO t_1980 VALUES (NEW.year, NEW.a);
CREATE RULE t_ins_1981 AS ON INSERT TO t WHERE (year = 1981)
DO INSTEAD INSERT INTO t_1981 VALUES (NEW.year, NEW.a);
根据我的理解，如果我插入 t (year, a) VALUES (1980, 5)，它将转到 t_1980，如果我插入 t (year, a) VALUES (1981, 3)，它将转到 t_1981。但是，我的理解似乎不正确。首先，我无法理解文档中的以下内容
“目前没有简单的方法来指定不得将行插入主表中。主表上的 CHECK (false) 约束将由所有子表继承，因此不能用于此目的。一种可能性是在主表上设置一个始终引发错误的 ON INSERT 触发器（或者，可以使用此类触发器将数据重定向到正确的子表中，而不是。使用上面建议的一组规则。）”
上面是否意味着尽管设置了 CHECK 约束和规则，我还必须在主表上创建触发器，以便 INSERT 转到正确的表？如果是这样的话，数据库支持分区的意义何在？我可以自己设置单独的桌子吗？我在主表中插入了一堆值，这些行仍然在主表中，而不是在继承的表中。
第二个问题。检索行时，是从主表中选择，还是必须根据需要从各个表中选择？以下将如何工作？
SELECT year, a FROM t WHERE year IN (1980, 1981);
更新：似乎我已经找到了我自己的问题的答案
“请注意 COPY 命令会忽略规则。如果您使用 COPY 插入数据，则必须将数据复制到正确的子表中，而不是复制到父级中会触发触发器，因此如果您使用触发器方法创建分区表，则可以正常使用它。”
我确实使用 COPY FROM 来加载数据，因此规则被忽略。将尝试使用触发器。
I set up a set of partitioned tables per the docs at http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
CREATE TABLE t (year, a);
CREATE TABLE t_1980 ( CHECK (year = 1980) ) INHERITS (t);
CREATE TABLE t_1981 ( CHECK (year = 1981) ) INHERITS (t);
CREATE RULE t_ins_1980 AS ON INSERT TO t WHERE (year = 1980)
DO INSTEAD INSERT INTO t_1980 VALUES (NEW.year, NEW.a);
CREATE RULE t_ins_1981 AS ON INSERT TO t WHERE (year = 1981)
DO INSTEAD INSERT INTO t_1981 VALUES (NEW.year, NEW.a);
From my understanding, if I INSERT INTO t (year, a) VALUES (1980, 5), it will go to t_1980, and if I INSERT INTO t (year, a) VALUES (1981, 3), it will go to t_1981. But, my understanding seems to be incorrect. First, I can't understand the following from the docs
"There is currently no simple way to specify that rows must not be inserted into the master table. A CHECK (false) constraint on the master table would be inherited by all child tables, so that cannot be used for this purpose. One possibility is to set up an ON INSERT trigger on the master table that always raises an error. (Alternatively, such a trigger could be used to redirect the data into the proper child table, instead of using a set of rules as suggested above.)"
Does the above mean that in spite of setting up the CHECK constraints and the RULEs, I also have to create TRIGGERs on the master table so that the INSERTs go to the correct tables? If that were the case, what would be the point of the db supporting partitioning? I could just set up the separate tables myself? I inserted a bunch of values into the master table, and those rows are still in the master table, not in the inherited tables.
Second question. When retrieving the rows, do I select from the master table, or do I have to select from the individual tables as needed? How would the following work?
SELECT year, a FROM t WHERE year IN (1980, 1981);
Update: Seems like I have found the answer to my own question
"Be aware that the COPY command ignores rules. If you are using COPY to insert data, you must copy the data into the correct child table rather than into the parent. COPY does fire triggers, so you can use it normally if you create partitioned tables using the trigger approach."
I was indeed using COPY FROM to load data, so RULEs were being ignored. Will try with TRIGGERs.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
一定要尝试触发器。
如果您认为您想要实现一条规则，请不要这样做（想到的唯一例外是可更新视图）。请参阅此 depesz 的精彩文章，其中有更多解释。
实际上，Postgres 仅支持读取方面的分区。您将自己设置插入分区的方法 - 在大多数情况下触发。根据需要和应用程序，有时指导应用程序直接插入分区可能会更快。
从分区表中进行选择时，您确实可以在主表上执行 SELECT ... WHERE... 只要正确设置了 CHECK 约束（它们在您的示例中）并且正确设置了constraint_exclusion 参数。
对于 8.4：
对于 < 8.4：
话虽如此，我实际上非常喜欢 Postgres 的工作方式，并且我自己也经常使用它。
Definitely try triggers.
If you think you want to implement a rule, don't (the only exception that comes to mind is updatable views). See this great article by depesz for more explanation there.
In reality, Postgres only supports partitioning on the reading side of things. You're going to have setup the method of insertition into partitions yourself - in most cases TRIGGERing. Depending on the needs and applicaitons, it can sometimes be faster to teach your application to insert directly into the partitions.
When selecting from partioned tables, you can indeed just SELECT ... WHERE... on the master table so long as your CHECK constraints are properly setup (they are in your example) and the constraint_exclusion parameter is set corectly.
For 8.4:
For < 8.4:
All this being said, I actually really like the way Postgres does it and use it myself often.
是的。阅读第 5 点（第 5.9.2 节）< /a>
基本上：子表中的插入必须显式完成（创建触发器，或通过在查询中指定正确的子表）。但分区
对于 SELECTS 来说是透明的，并且（考虑到此模式的存储和索引优势）这就是重点。
（另外，由于分区表是继承的，
架构是从父级继承的，因此具有一致性
已强制执行）。
Yes. Read point 5 (section 5.9.2)
Basically: the INSERTS in the child tables must be done explicitly (either creating TRIGGERS, or by specifying the correct child table in the query). But the partitioning
is transparent for SELECTS, and (given the storage and indexing advantages of this schema) that's the point.
(Besides, because the partitioned tables are inherited,
the schema is inherited from the parent, hence consistency
is enforced).
触发器肯定比规则更好。
今天，我尝试了物化视图表的分区，并遇到了触发器解决方案的问题。
为什么 ？
我正在使用 RETURNING 并且当前的解决方案返回 NULL :)
但这是对我有用的解决方案 - 如果我错了，请纠正我。
1. 我有 3 个表，其中插入了一些数据，有一个视图（我们称之为 viewfoo），其中包含
需要具体化的数据。
2.插入最后一个表有插入物化视图表的触发器
通过 INSERT INTO matviewtable SELECT * FROM viewfoo WHERE recno=NEW.recno;
效果很好，我正在使用 RETURNING recno; （recno 是 SERIAL 类型 - 序列）。
物化视图（表）需要分区，因为它很大，并且
根据我的测试，在这种情况下 SELECT 至少快 10 倍。
分区问题：
* 当前的触发器解决方案 RETURN NULL - 所以我不能使用 RETURNING recno。
（当前触发解决方案 = depesz 页面上解释的触发）。
解决方案：
我已将第三个表的触发器更改为不插入物化视图表（该表是分区表的父表），但创建了插入的新触发器
直接从第三个表分区表并触发 RETURN NEW。
物化视图表会自动更新，并且 RETURNING recno 工作正常。
如果这对任何人有帮助，我会很高兴。
Triggers are definitelly better than rules.
Today I've played with partitioning of materialized view table and run into problem with triggers solution.
Why ?
I'm using RETURNING and current solution returns NULL :)
But here's solution which works for me - correct me if I'm wrong.
1. I have 3 tables which are inserted with some data, there's an view (let we call it viewfoo) which contains
data which need to be materialized.
2. Insert into last table have trigger which inserts into materialized view table
via INSERT INTO matviewtable SELECT * FROM viewfoo WHERE recno=NEW.recno;
That works fine and I'm using RETURNING recno; (recno is SERIAL type - sequence).
Materialized view (table) need to be partitioned because it's huge, and
according to my tests it's at least x10 faster for SELECT in this case.
Problems with partitioning:
* Current trigger solution RETURN NULL - so I cannot use RETURNING recno.
(Current trigger solution = trigger explained at depesz page).
Solution:
I've changed trigger of my 3rd table TO NOT insert into materialized view table (that table is parent of partitioned tables), but created new trigger which inserts
partitioned table directly FROM 3rd table and that trigger RETURN NEW.
Materialized view table is automagically updated and RETURNING recno works fine.
I'll be glad if this helped to anybody.